Template:COVID-19 vaccination data/doc

Add links
Source: Wikipedia, the free encyclopedia.
< Template:COVID-19 vaccination data

This is an old revision of this page, as edited by Tol (talk | contribs) at 00:37, 11 June 2021 (Split bot notice into another notice). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

Main articles: COVID-19 vaccine and Deployment of COVID-19 vaccines
See also: Template:COVID-19 testing by country
Semi-automated update instructions

To manually update, reference values included in this GitHub source table. Columns labeled people_vaccinated and people_vaccinated_per_hundred contain relevant data. For corresponding visuals, look to these charts on Our World in Data.

To update the full table in <1 minute, use the Excel macro provided below.

Alternatively, to update the full table in ~3 minutes, run through the following steps:

 Excel update instructions
Requires Microsoft Excel (2007 or later)
  1. Open vaccinations.csv in Excel (GitHub source for reference and last updated date)
  2. Enable filters [Data > Filter]
  3. Sort by date (column C) newest to oldest
  4. Select [Data > Remove Duplicates]. Click 'Unselect all' then check off Location and click 'Ok' (this will remove all older entries)
  5. Sort by total vaccinations (column D) largest to smallest
  6. Select cell M2[a] and paste this formula into the Formula field at the top of the screen[b] (to the right of the 'fx' symbol):
=IF(A2="World","|{{pad|0.1em}}[[File:Emojione 1F310.svg|23x15px|alt=|link=]]{{pad|0.4em}}World{{efn|name=world-total}}",IF(OR(A2="Northern Ireland",A2="Scotland",A2="Wales",A2="England",A2="Europe",A2="Africa",A2="Oceania",A2="North America",A2="South America",A2="Asia"),"|******REMOVE ROW******","|{{flag+link|COVID-19 pandemic in|"&A2&"}}"))&IF(ISBLANK(E2),"{{efn|name=incorrect-total}}||{{font color|darkred|"&TEXT(D2,"#,#")&"}}||--","||"&TEXT(E2,"#,#")&"||"&TEXT(J2/100,"0.0%"))&"<tr>"
  2. Press the enter key to register the formula. Extend the formula to the last row (Click on M2 and double click the black square in the bottom right of the cell)
  3. Copy and paste into Wikipedia.
  4. Delete all rows marked REMOVE.
 Google Sheets update instructions
  1. Open vaccinations.csv in a browser
  2. Copy (Ctrl+A then Ctrl+C) and paste (Ctrl+V) the contents into Google Sheets (be sure cell A1 is selected before pasting)
  3. Enable filters [Data > Create a Filter]
  4. Sort by date (column C) Z to A (most recent first)
  5. Select [Data > Remove Duplicates]. Uncheck 'Select all' and check off 'Column A' for location. (this will remove older entries)
  6. Sort by total vaccinations (column D) Z to A (largest to smallest)
  7. Select cell M2 and paste this formula into the Formula field at the top of the screen[b] (to the right of the 'fx' symbol):
=IF(A2="World","|{{pad|0.1em}}[[File:Emojione 1F310.svg|23x15px|alt=|link=]]{{pad|0.4em}}World{{efn|name=world-total}}",IF(OR(A2="Northern Ireland",A2="Scotland",A2="Wales",A2="England",A2="Europe",A2="Africa",A2="Oceania",A2="North America",A2="South America",A2="Asia"),"|******REMOVE ROW******","|{{flag+link|COVID-19 pandemic in|"&A2&"}}"))&IF(ISBLANK(E2),"{{efn|name=incorrect-total}}||{{font color|darkred|"&TEXT(D2,"#,#")&"}}||--","||"&TEXT(E2,"#,#")&"||"&TEXT(J2/100,"0.0%"))&"<tr>"
  2. Press the enter key to register the formula. Extend the formula to the last row (click on M2 and then double click on the black square in the bottom right of the cell)
  3. Copy and paste into Wikipedia.
  4. Delete all rows marked REMOVE.
  1. ^ If OWID adds additional columns to the source data, paste the formula in the last empty column.
  2. ^ a b Do not attempt to paste directly into the cell
 Excel macro:
  1. Open vaccinations.csv in Excel (if copying and pasting into Excel, you'll likely need to use [Data: Text to Columns] (set to comma delimited) to convert to table layout.
  2. Run the following macro:
Sub OwidUpdate()
'
' Run OWID update routine
'

Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key:= _
Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
 :=xlSortNormal
With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("A1:L" & Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:= _
xlYes
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key:= _
Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
 :=xlSortNormal
With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-12]=""World"",""|{{pad|0.1em}}[[File:Emojione 1F310.svg|23x15px|alt=|link=]]{{pad|0.4em}}World{{efn|name=world-total}}"",IF(OR(RC[-12]=""Northern Ireland"",RC[-12]=""Scotland"",RC[-12]=""Wales"",RC[-12]=""England"",RC[-12]=""Europe"",RC[-12]=""Africa"",RC[-12]=""Oceania"",RC[-12]=""North America"",RC[-12]=""South America"",RC[-12]=""Asia""),""|******REMOVE ROW******"",""|{{flag+link|COVID-19 pandemic in|""&RC[-12]&""}}""))&IF(ISBLANK(RC[-8]),""{{efn|name=incorrect-total}}||{{font color|darkred|" & _
"""&TEXT(RC[-9],""#,#"")&""}}||--"",""||""&TEXT(RC[-8],""#,#"")&""||""&TEXT(RC[-3]/100,""0.0%""))&""<tr>"""
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)
' Remove bad rows
Dim rng As Range
Dim pos As Integer
Set rng = ActiveSheet.UsedRange
For i = rng.Cells.Count To 1 Step -1
pos = InStr(LCase(rng.Item(i).Value), LCase("remove"))
If pos > 0 Then
rng.Item(i).EntireRow.Delete
End If
Next i
' Select and copy range for Wikipedia
Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
End Sub

References

Usage


Retrieved from "https://en.wikipedia.org/w/index.php?title=Template:COVID-19_vaccination_data/doc&oldid=1027957102"