Pandas Functions: Advanced Groupbys with Grouper, Assign, and Query
Introduction
Pandas groupbys are some of the most useful functions in a data scientist's toolkit. And yet, time and again I have found that colleagues do not realize the flexibility these ubiquitous functions can grant them. In the following post, I will demonstrate some of my favorite uses of groupbys in the hope that it will help others in the future.
The Data
Let's start with a simple example and work our way up in difficulty. We'll start with reading in Covid19 data that Johns Hopkins University aggregated from the World Health Organization. The dataset contains approximately 300,000 observations from different countries and regions of the world on Covid19 cases, recoveries, and deaths.
from pandas as import read_csv, Grouper
from datetime import timedelta
covid_ts = read_csv("covid_19_data.csv").dropna()
covid_ts['ObservationDate'] = pd.to_datetime(covid_ts['ObservationDate']
Now that we've loaded our data, we can take a look.
covid_ts.head()
| ObservationDate | Province/State | Country/Region | Last | Update | Confirmed | Deaths | Recovered |
|---|---|---|---|---|---|---|---|
| 01/22/2020 | Anhui | Mainland China | 1/22/2020 | 17:00 | 1.0 | 0.0 | 0.0 |
| 01/22/2020 | Beijing | Mainland China | 1/22/2020 | 17:00 | 14.0 | 0.0 | 0.0 |
| 01/22/2020 | Chongqing | Mainland China | 1/22/2020 | 17:00 | 6.0 | 0.0 | 0.0 |
| 01/22/2020 | Fujian | Mainland China | 1/22/2020 | 17:00 | 1.0 | 0.0 | 0.0 |
| 01/22/2020 | Gansu | Mainland China | 1/22/2020 | 17:00 | 0.0 | 0.0 | 0.0 |
Here we can confirm that we have seven columns of regional and national-level disease data. ObservationDate and Last Update don't differ by much, so for our purposes we'll simply stick with Observation Date, dropping the other.
Since we'll be leveraging the temporality of our dataset, let's quickly check the range of our data.
start = min(covid_ts['ObservationDate'])
end = max(covid_ts['ObservationDate'])
range_ = end - start + timedelta(days=1) # inclusive range
print(f"Observations range form {str(start.date())} to {str(end.date())}, or {range_.days} days"
Observations range from 2020-01-22 to 2021-05-29, or 494 days.
Okay, with that let's jump into some groupbys.
Advanced Groupbys
Being Covid timeseries data, the first thing we can do is check how the disease has progressed over the weeks. Enter pandas.Grouper, a groupby-specific function that allows users to control how their data will be grouped at a time-based level. All we have to do is invoke Grouper within a typical groupby function, provide an offset alias (e.g. D for daily, W for weekly, Y for yearly), and an aggregation metric (e.g. sum, mean, count) such as is done in the following lines of code:
Multiline functions can be surrounded with () for readability
(
covid_ts.groupby(Grouper(key="ObservationDate", freq="1W"))
[['Confirmed']]
.sum()
.reset_index()
)
Resulting in the Weekly Aggregate Covid19 Cases table below:
WeeklyAggregate Covid19 Cases
| ObservationDate | Confirmed |
|---|---|
| 2020-01-26 | 6059 |
| 2020-02-02 | 61132 |
| 2020-02-09 | 212706 |
| 2020-02-16 | 398536 |
| 2020-02-23 | 530646 |
| 2020-03-01 | 555826 |
| 2020-03-08 | 571316 |
| 2020-03-15 | 611431 |
| 2020-03-22 | 796662 |
| 2020-03-29 | 1258958 |
| 2020-04-05 | 2431633 |
| 2020-04-12 | 4016436 |
| 2020-04-19 | 5548413 |
| 2020-04-26 | 7071798 |
| 2020-05-03 | 8550311 |
| 2020-05-10 | 9924735 |
| 2020-05-17 | 13640944 |
| 2020-05-24 | 19038630 |
| 2020-05-31 | 22987145 |
| 2020-06-07 | 30014291 |
| 2020-06-14 | 37135729 |
| 2020-06-21 | 43814150 |
| 2020-06-28 | 49634016 |
| 2020-07-05 | 55998840 |
| 2020-07-12 | 63062417 |
| 2020-07-19 | 71136866 |
| 2020-07-26 | 80165769 |
| 2020-08-02 | 89766838 |
| 2020-08-09 | 99485337 |
| 2020-08-16 | 109304057 |
| 2020-08-23 | 118832590 |
| 2020-08-30 | 128335973 |
| 2020-09-06 | 138370846 |
| 2020-09-13 | 148141308 |
| 2020-09-20 | 158402217 |
| 2020-09-27 | 168849411 |
| 2020-10-04 | 179041022 |
| 2020-10-11 | 189900364 |
| 2020-10-18 | 200923793 |
| 2020-10-25 | 213166206 |
| 2020-11-01 | 227018598 |
| 2020-11-08 | 242825744 |
| 2020-11-15 | 262675742 |
| 2020-11-22 | 283806018 |
| 2020-11-29 | 303487746 |
| 2020-12-06 | 323398270 |
| 2020-12-13 | 344855993 |
| 2020-12-20 | 367122719 |
| 2020-12-27 | 388942368 |
| 2021-01-03 | 409729713 |
| 2021-01-10 | 433898614 |
| 2021-01-17 | 459369919 |
| 2021-01-24 | 481613763 |
| 2021-01-31 | 501123103 |
| 2021-02-07 | 517491260 |
| 2021-02-14 | 530940304 |
| 2021-02-21 | 542301462 |
| 2021-02-28 | 553580949 |
| 2021-03-07 | 564515173 |
| 2021-03-14 | 576244907 |
| 2021-03-21 | 589155584 |
| 2021-03-28 | 604094179 |
| 2021-04-04 | 620294702 |
| 2021-04-11 | 638289571 |
| 2021-04-18 | 660556439 |
| 2021-04-25 | 687026232 |
| 2021-05-02 | 716197375 |
| 2021-05-09 | 745782837 |
| 2021-05-16 | 773254529 |
| 2021-05-23 | 796128490 |
| 2021-05-30 | 697481369 |
By grouping "confirmed" cases in covid_ts using Grouper, the offset alias 1W, and sum, we have easily aggregated weekly confirmed cases over the date range of our data. If we wanted more granular aggregations we could have easily changed our offsets to D, where is any number of day offets. But this is only step one of what we can achieve with Grouper, and groupby aggregations in general.
The convenience of Grouper is extended by its ability to aggregate subgroups by the offsets it's provided—all that matters is the placement of Grouper relative to other groupby columns. For example, in the groupby snippet below, "Country/Region" is placed in a list before our Grouper function at the 1M (one month) offset, producing the National/Regional Covid Cases By Month table below.
(
covid_ts
.groupby(["Country/Region", Grouper(key="ObservationDate", freq="1M")])
[['Confirmed']]
.sum()
.reset_index()
)
National/Regional Covid Cases By Month
| Country/Region | ObservationDate | Confirmed |
|---|---|---|
| Australia | 2020-01-31 | 33 |
| Australia | 2020-02-29 | 486 |
| Australia | 2020-03-31 | 35620 |
| Australia | 2020-04-30 | 187842 |
| Australia | 2020-05-31 | 217625 |
| Australia | 2020-06-30 | 222611 |
| Australia | 2020-07-31 | 361723 |
| Australia | 2020-08-31 | 711091 |
| Australia | 2020-09-30 | 800899 |
| Australia | 2020-10-31 | 848364 |
| Australia | 2020-11-30 | 832514 |
| Australia | 2020-12-31 | 872046 |
| Australia | 2021-01-31 | 889012 |
| Australia | 2021-02-28 | 809190 |
| Australia | 2021-03-31 | 903649 |
| Australia | 2021-04-30 | 885710 |
| Australia | 2021-05-31 | 869015 |
| Austria | 2020-02-29 | 2 |
| Belgium | 2020-11-30 | 10509939 |
| Belgium | 2020-12-31 | 19046283 |
| Belgium | 2021-01-31 | 20983559 |
| Belgium | 2021-02-28 | 20733707 |
| Belgium | 2021-03-31 | 25421637 |
| Belgium | 2021-04-30 | 28207302 |
| Belgium | 2021-05-31 | 29831648 |
| Brazil | 2020-05-31 | 4737799 |
| Brazil | 2020-06-30 | 28128253 |
| Brazil | 2020-07-31 | 62641865 |
| Brazil | 2020-08-31 | 102903896 |
| Brazil | 2020-09-30 | 132059962 |
| Brazil | 2020-10-31 | 160991539 |
| Brazil | 2020-11-30 | 176837421 |
| Brazil | 2020-12-31 | 218249737 |
| Brazil | 2021-01-31 | 261187142 |
| Brazil | 2021-02-28 | 276456100 |
| Brazil | 2021-03-31 | 361059664 |
| Brazil | 2021-04-30 | 412565115 |
| Brazil | 2021-05-31 | 451476923 |
| Canada | 2020-01-31 | 12 |
| Canada | 2020-02-29 | 234 |
| Canada | 2020-03-31 | 52012 |
| Canada | 2020-04-30 | 927235 |
| Canada | 2020-05-31 | 2369720 |
| Canada | 2020-06-30 | 3016433 |
| Canada | 2020-07-31 | 3460185 |
| Canada | 2020-08-31 | 3854051 |
| Canada | 2020-09-30 | 4290890 |
| Canada | 2020-10-31 | 6126216 |
| Canada | 2020-11-30 | 9141329 |
| Canada | 2020-12-31 | 15031370 |
| Canada | 2021-01-31 | 21663417 |
| Canada | 2021-02-28 | 23280680 |
| Canada | 2021-03-31 | 28667193 |
| Canada | 2021-04-30 | 33242319 |
| Canada | 2021-05-31 | 38374241 |
| Chile | 2020-05-31 | 913924 |
| Chile | 2020-06-30 | 5806627 |
| Chile | 2020-07-31 | 9983781 |
| Chile | 2020-08-31 | 11936049 |
| Chile | 2020-09-30 | 13147124 |
| Chile | 2020-10-31 | 15134758 |
| Chile | 2020-11-30 | 15945192 |
| Chile | 2020-12-31 | 17938094 |
| Chile | 2021-01-31 | 20661911 |
| Chile | 2021-02-28 | 21754084 |
| Chile | 2021-03-31 | 28062147 |
| Chile | 2021-04-30 | 33124646 |
| Chile | 2021-05-31 | 37172836 |
| China | 2020-01-31 | 0 |
| China | 2020-02-29 | 0 |
| China | 2020-03-31 | 0 |
| Colombia | 2020-05-31 | 103500 |
| Colombia | 2020-06-30 | 1698303 |
| Colombia | 2020-07-31 | 5598108 |
| Colombia | 2020-08-31 | 14404679 |
| Colombia | 2020-09-30 | 21921158 |
| Colombia | 2020-10-31 | 29361707 |
| Colombia | 2020-11-30 | 35992828 |
| Colombia | 2020-12-31 | 45578807 |
| Colombia | 2021-01-31 | 58374546 |
| Colombia | 2021-02-28 | 61356188 |
| Colombia | 2021-03-31 | 71861747 |
| Colombia | 2021-04-30 | 78567887 |
| Colombia | 2021-05-31 | 89997493 |
| Denmark | 2020-03-31 | 12126 |
| Denmark | 2020-04-30 | 5845 |
| Denmark | 2020-05-31 | 6151 |
| Denmark | 2020-06-30 | 6000 |
| Denmark | 2020-07-31 | 6444 |
| Denmark | 2020-08-31 | 11118 |
| Denmark | 2020-09-30 | 13369 |
| Denmark | 2020-10-31 | 15461 |
| Denmark | 2020-11-30 | 15467 |
| Denmark | 2020-12-31 | 17396 |
| Denmark | 2021-01-31 | 20883 |
| Denmark | 2021-02-28 | 19232 |
| Denmark | 2021-03-31 | 21422 |
| Denmark | 2021-04-30 | 20789 |
| Denmark | 2021-05-31 | 20523 |
| France | 2020-03-31 | 102660 |
| France | 2020-04-30 | 34314 |
| France | 2020-05-31 | 73566 |
| France | 2020-06-30 | 151652 |
| France | 2020-07-31 | 317736 |
| France | 2020-08-31 | 434039 |
| France | 2020-09-30 | 645690 |
| France | 2020-10-31 | 1021451 |
| France | 2020-11-30 | 1443595 |
| France | 2020-12-31 | 1774323 |
| France | 2021-01-31 | 2013527 |
| France | 2021-02-28 | 2149196 |
| France | 2021-03-31 | 2747350 |
| France | 2021-04-30 | 2986432 |
| France | 2021-05-31 | 3289110 |
| Germany | 2020-01-31 | 17 |
| Germany | 2020-05-31 | 3228720 |
| Germany | 2020-06-30 | 5670762 |
| Germany | 2020-07-31 | 6265079 |
| Germany | 2020-08-31 | 7024733 |
| Germany | 2020-09-30 | 8023521 |
| Germany | 2020-10-31 | 11676143 |
| Germany | 2020-11-30 | 24572075 |
| Germany | 2020-12-31 | 44141683 |
| Germany | 2021-01-31 | 62555458 |
| Germany | 2021-02-28 | 65660655 |
| Germany | 2021-03-31 | 81277883 |
| Germany | 2021-04-30 | 93513080 |
| Germany | 2021-05-31 | 103826264 |
| Hong Kong | 2020-01-31 | 65 |
| Hong Kong | 2020-02-29 | 1543 |
| Hong Kong | 2020-03-31 | 8018 |
| Hong Kong | 2020-04-30 | 29443 |
| Hong Kong | 2020-05-31 | 32692 |
| Hong Kong | 2020-06-30 | 34000 |
| Hong Kong | 2020-07-31 | 57318 |
| Hong Kong | 2020-08-31 | 134221 |
| Hong Kong | 2020-09-30 | 149065 |
| Hong Kong | 2020-10-31 | 161825 |
| Hong Kong | 2020-11-30 | 167377 |
| Hong Kong | 2020-12-31 | 239977 |
| Hong Kong | 2021-01-31 | 297899 |
| Hong Kong | 2021-02-28 | 301335 |
| Hong Kong | 2021-03-31 | 349435 |
| Hong Kong | 2021-04-30 | 349045 |
| Hong Kong | 2021-05-31 | 342677 |
| India | 2020-06-30 | 8775360 |
| India | 2020-07-31 | 32781165 |
| India | 2020-08-31 | 82734792 |
| India | 2020-09-30 | 151651367 |
| India | 2020-10-31 | 228634268 |
| India | 2020-11-30 | 265834390 |
| India | 2020-12-31 | 307985456 |
| India | 2021-01-31 | 326908857 |
| India | 2021-02-28 | 305984490 |
| India | 2021-03-31 | 357487053 |
| India | 2021-04-30 | 447598593 |
| India | 2021-05-31 | 704714394 |
| Iraq | 2020-02-29 | 0 |
| Israel | 2020-02-29 | 5 |
| Italy | 2020-05-31 | 4114467 |
| Italy | 2020-06-30 | 7115232 |
| Italy | 2020-07-31 | 7560745 |
| Italy | 2020-08-31 | 7919622 |
| Italy | 2020-09-30 | 8749361 |
| Italy | 2020-10-31 | 13281216 |
| Italy | 2020-11-30 | 35324374 |
| Italy | 2020-12-31 | 58293082 |
| Italy | 2021-01-31 | 72917277 |
| Italy | 2021-02-28 | 76429829 |
| Italy | 2021-03-31 | 101162913 |
| Italy | 2021-04-30 | 114799462 |
| Italy | 2021-05-31 | 120086716 |
| Japan | 2020-05-31 | 66738 |
| Japan | 2020-06-30 | 525609 |
| Japan | 2020-07-31 | 771595 |
| Japan | 2020-08-31 | 1701916 |
| Japan | 2020-09-30 | 2297182 |
| Japan | 2020-10-31 | 2856221 |
| Japan | 2020-11-30 | 3650314 |
| Japan | 2020-12-31 | 5880523 |
| Japan | 2021-01-31 | 9902662 |
| Japan | 2021-02-28 | 11627561 |
| Japan | 2021-03-31 | 13976951 |
| Japan | 2021-04-30 | 15835437 |
| Japan | 2021-05-31 | 19573346 |
| Lebanon | 2020-02-29 | 1 |
| Macau | 2020-01-31 | 46 |
| Macau | 2020-02-29 | 283 |
| Macau | 2020-03-31 | 556 |
| Macau | 2020-04-30 | 1335 |
| Macau | 2020-05-31 | 1395 |
| Macau | 2020-06-30 | 1356 |
| Macau | 2020-07-31 | 1426 |
| Macau | 2020-08-31 | 1426 |
| Macau | 2020-09-30 | 1380 |
| Macau | 2020-10-31 | 1426 |
| Macau | 2020-11-30 | 1380 |
| Macau | 2020-12-31 | 1426 |
| Macau | 2021-01-31 | 1437 |
| Macau | 2021-02-28 | 1340 |
| Macau | 2021-03-31 | 1488 |
| Macau | 2021-04-30 | 1464 |
| Macau | 2021-05-31 | 1441 |
| Mainland China | 2020-01-31 | 38340 |
| Mainland China | 2020-02-29 | 1631535 |
| Mainland China | 2020-03-31 | 2506812 |
| Mainland China | 2020-04-30 | 2469365 |
| Mainland China | 2020-05-31 | 2571194 |
| Mainland China | 2020-06-30 | 2497323 |
| Mainland China | 2020-07-31 | 2595103 |
| Mainland China | 2020-08-31 | 2628794 |
| Mainland China | 2020-09-30 | 2557006 |
| Mainland China | 2020-10-31 | 2655726 |
| Mainland China | 2020-11-30 | 2590049 |
| Mainland China | 2020-12-31 | 2690556 |
| Mainland China | 2021-01-31 | 2735735 |
| Mainland China | 2021-02-28 | 2513697 |
| Mainland China | 2021-03-31 | 2792080 |
| Mainland China | 2021-04-30 | 2714147 |
| Mainland China | 2021-05-31 | 2635134 |
| Mexico | 2020-05-31 | 881055 |
| Mexico | 2020-06-30 | 4692440 |
| Mexico | 2020-07-31 | 10107738 |
| Mexico | 2020-08-31 | 16108026 |
| Mexico | 2020-09-30 | 20309152 |
| Mexico | 2020-10-31 | 26077771 |
| Mexico | 2020-11-30 | 30342144 |
| Mexico | 2020-12-31 | 39586956 |
| Mexico | 2021-01-31 | 50652332 |
| Mexico | 2021-02-28 | 55678011 |
| Mexico | 2021-03-31 | 67295623 |
| Mexico | 2021-04-30 | 68840861 |
| Mexico | 2021-05-31 | 68985170 |
| Netherlands | 2020-03-31 | 14701 |
| Netherlands | 2020-04-30 | 4764 |
| Netherlands | 2020-05-31 | 6199 |
| Netherlands | 2020-06-30 | 6217 |
| Netherlands | 2020-07-31 | 797069 |
| Netherlands | 2020-08-31 | 1987056 |
| Netherlands | 2020-09-30 | 2805482 |
| Netherlands | 2020-10-31 | 7020285 |
| Netherlands | 2020-11-30 | 13649806 |
| Netherlands | 2020-12-31 | 20507503 |
| Netherlands | 2021-01-31 | 28343170 |
| Netherlands | 2021-02-28 | 29303549 |
| Netherlands | 2021-03-31 | 36901518 |
| Netherlands | 2021-04-30 | 42290027 |
| Netherlands | 2021-05-31 | 46763492 |
| Others | 2020-02-29 | 10076 |
| Others | 2020-03-31 | 16152 |
| Pakistan | 2020-06-30 | 3594515 |
| Pakistan | 2020-07-31 | 7910429 |
| Pakistan | 2020-08-31 | 8935125 |
| Pakistan | 2020-09-30 | 9118734 |
| Pakistan | 2020-10-31 | 10005964 |
| Pakistan | 2020-11-30 | 10891173 |
| Pakistan | 2020-12-31 | 13839663 |
| Pakistan | 2021-01-31 | 16029828 |
| Pakistan | 2021-02-28 | 15805181 |
| Pakistan | 2021-03-31 | 19174307 |
| Pakistan | 2021-04-30 | 22498648 |
| Pakistan | 2021-05-31 | 25442064 |
| Peru | 2020-05-31 | 603705 |
| Peru | 2020-06-30 | 6931248 |
| Peru | 2020-07-31 | 10657640 |
| Peru | 2020-08-31 | 16414082 |
| Peru | 2020-09-30 | 22054476 |
| Peru | 2020-10-31 | 26653391 |
| Peru | 2020-11-30 | 28025436 |
| Peru | 2020-12-31 | 30624534 |
| Peru | 2021-01-31 | 32876154 |
| Peru | 2021-02-28 | 34491490 |
| Peru | 2021-03-31 | 44328085 |
| Peru | 2021-04-30 | 50368511 |
| Peru | 2021-05-31 | 54460864 |
| Russia | 2020-06-30 | 16097579 |
| Russia | 2020-07-31 | 23233041 |
| Russia | 2020-08-31 | 28508044 |
| Russia | 2020-09-30 | 32289140 |
| Russia | 2020-10-31 | 42549555 |
| Russia | 2020-11-30 | 57925419 |
| Russia | 2020-12-31 | 84102182 |
| Russia | 2021-01-31 | 108422409 |
| Russia | 2021-02-28 | 112712911 |
| Russia | 2021-03-31 | 135088504 |
| Russia | 2021-04-30 | 138817103 |
| Russia | 2021-05-31 | 141404158 |
| Spain | 2020-05-31 | 4219335 |
| Spain | 2020-06-30 | 7331192 |
| Spain | 2020-07-31 | 8134290 |
| Spain | 2020-08-31 | 11159043 |
| Spain | 2020-09-30 | 18316260 |
| Spain | 2020-10-31 | 29494693 |
| Spain | 2020-11-30 | 43932783 |
| Spain | 2020-12-31 | 55067758 |
| Spain | 2021-01-31 | 71180786 |
| Spain | 2021-02-28 | 85568038 |
| Spain | 2021-03-31 | 99280629 |
| Spain | 2021-04-30 | 101966198 |
| Spain | 2021-05-31 | 104480995 |
| Sweden | 2020-06-30 | 1431896 |
| Sweden | 2020-07-31 | 2360582 |
| Sweden | 2020-08-31 | 2600324 |
| Sweden | 2020-09-30 | 2632351 |
| Sweden | 2020-10-31 | 3240753 |
| Sweden | 2020-11-30 | 5546377 |
| Sweden | 2020-12-31 | 10695430 |
| Sweden | 2021-01-31 | 15946013 |
| Sweden | 2021-02-28 | 17201658 |
| Sweden | 2021-03-31 | 22569251 |
| Sweden | 2021-04-30 | 26658585 |
| Sweden | 2021-05-31 | 29888569 |
| Taiwan | 2020-01-31 | 52 |
| Taiwan | 2020-02-29 | 616 |
| Taiwan | 2020-03-31 | 436 |
| UK | 2020-03-31 | 23768 |
| UK | 2020-04-30 | 28370 |
| UK | 2020-05-31 | 40553 |
| UK | 2020-06-30 | 6103975 |
| UK | 2020-07-31 | 9150130 |
| UK | 2020-08-31 | 9928933 |
| UK | 2020-09-30 | 11564506 |
| UK | 2020-10-31 | 22002570 |
| UK | 2020-11-30 | 40830975 |
| UK | 2020-12-31 | 61365366 |
| UK | 2021-01-31 | 102180395 |
| UK | 2021-02-28 | 113211684 |
| UK | 2021-03-31 | 132721966 |
| UK | 2021-04-30 | 131952179 |
| UK | 2021-05-31 | 129432185 |
| US | 2020-01-31 | 37 |
| US | 2020-02-29 | 718 |
| US | 2020-03-31 | 1088640 |
| US | 2020-04-30 | 19534148 |
| US | 2020-05-31 | 45256716 |
| US | 2020-06-30 | 65059848 |
| US | 2020-07-31 | 111580930 |
| US | 2020-08-31 | 166786671 |
| US | 2020-09-30 | 199653685 |
| US | 2020-10-31 | 252160215 |
| US | 2020-11-30 | 340017832 |
| US | 2020-12-31 | 528139775 |
| US | 2021-01-31 | 730894746 |
| US | 2021-02-28 | 774254261 |
| US | 2021-03-31 | 916643837 |
| US | 2021-04-30 | 944543693 |
| US | 2021-05-31 | 953529915 |
| Ukraine | 2020-06-30 | 1011298 |
| Ukraine | 2020-07-31 | 1797522 |
| Ukraine | 2020-08-31 | 2952987 |
| Ukraine | 2020-09-30 | 5019534 |
| Ukraine | 2020-10-31 | 9292272 |
| Ukraine | 2020-11-30 | 16972054 |
| Ukraine | 2020-12-31 | 29178756 |
| Ukraine | 2021-01-31 | 36713095 |
| Ukraine | 2021-02-28 | 37009908 |
| Ukraine | 2021-03-31 | 47885644 |
| Ukraine | 2021-04-30 | 58596168 |
| Ukraine | 2021-05-31 | 63796837 |
Whereas, by placing "Country/Region" in a list after Grouper, one can get a similar, but slightly different aggregation of the data: Monthly Covid Cases by Country/Region.
(
covid_ts
.groupby([
Grouper(key="ObservationDate", freq="1M"), "Country/Region"])
[['Confirmed']]
.sum()
.reset_index()
)
Monthly Covid Cases by Country/Region
| ObservationDate | Country/Region | Confirmed |
|---|---|---|
| 2020-01-31 | Australia | 33 |
| 2020-01-31 | Canada | 12 |
| 2020-01-31 | China | 0 |
| 2020-01-31 | Germany | 17 |
| 2020-01-31 | Hong Kong | 65 |
| 2020-01-31 | Macau | 46 |
| 2020-01-31 | Mainland China | 38340 |
| 2020-01-31 | Taiwan | 52 |
| 2020-01-31 | US | 37 |
| 2020-02-29 | Australia | 486 |
| 2020-02-29 | Austria | 2 |
| 2020-02-29 | Canada | 234 |
| 2020-02-29 | China | 0 |
| 2020-02-29 | Hong Kong | 1543 |
| 2020-02-29 | Iraq | 0 |
| 2020-02-29 | Israel | 5 |
| 2020-02-29 | Lebanon | 1 |
| 2020-02-29 | Macau | 283 |
| 2020-02-29 | Mainland China | 1631535 |
| 2020-02-29 | Others | 10076 |
| 2020-02-29 | Taiwan | 616 |
| 2020-02-29 | US | 718 |
| 2020-03-31 | Australia | 35620 |
| 2020-03-31 | Canada | 52012 |
| 2020-03-31 | China | 0 |
| 2020-03-31 | Denmark | 12126 |
| 2020-03-31 | France | 102660 |
| 2020-03-31 | Hong Kong | 8018 |
| 2020-03-31 | Macau | 556 |
| 2020-03-31 | Mainland China | 2506812 |
| 2020-03-31 | Netherlands | 14701 |
| 2020-03-31 | Others | 16152 |
| 2020-03-31 | Taiwan | 436 |
| 2020-03-31 | UK | 23768 |
| 2020-03-31 | US | 1088640 |
| 2020-04-30 | Australia | 187842 |
| 2020-04-30 | Canada | 927235 |
| 2020-04-30 | Denmark | 5845 |
| 2020-04-30 | France | 34314 |
| 2020-04-30 | Hong Kong | 29443 |
| 2020-04-30 | Macau | 1335 |
| 2020-04-30 | Mainland China | 2469365 |
| 2020-04-30 | Netherlands | 4764 |
| 2020-04-30 | UK | 28370 |
| 2020-04-30 | US | 19534148 |
| 2020-05-31 | Australia | 217625 |
| 2020-05-31 | Brazil | 4737799 |
| 2020-05-31 | Canada | 2369720 |
| 2020-05-31 | Chile | 913924 |
| 2020-05-31 | Colombia | 103500 |
| 2020-05-31 | Denmark | 6151 |
| 2020-05-31 | France | 73566 |
| 2020-05-31 | Germany | 3228720 |
| 2020-05-31 | Hong Kong | 32692 |
| 2020-05-31 | Italy | 4114467 |
| 2020-05-31 | Japan | 66738 |
| 2020-05-31 | Macau | 1395 |
| 2020-05-31 | Mainland China | 2571194 |
| 2020-05-31 | Mexico | 881055 |
| 2020-05-31 | Netherlands | 6199 |
| 2020-05-31 | Peru | 603705 |
| 2020-05-31 | Spain | 4219335 |
| 2020-05-31 | UK | 40553 |
| 2020-05-31 | US | 45256716 |
| 2020-06-30 | Australia | 222611 |
| 2020-06-30 | Brazil | 28128253 |
| 2020-06-30 | Canada | 3016433 |
| 2020-06-30 | Chile | 5806627 |
| 2020-06-30 | Colombia | 1698303 |
| 2020-06-30 | Denmark | 6000 |
| 2020-06-30 | France | 151652 |
| 2020-06-30 | Germany | 5670762 |
| 2020-06-30 | Hong Kong | 34000 |
| 2020-06-30 | India | 8775360 |
| 2020-06-30 | Italy | 7115232 |
| 2020-06-30 | Japan | 525609 |
| 2020-06-30 | Macau | 1356 |
| 2020-06-30 | Mainland China | 2497323 |
| 2020-06-30 | Mexico | 4692440 |
| 2020-06-30 | Netherlands | 6217 |
| 2020-06-30 | Pakistan | 3594515 |
| 2020-06-30 | Peru | 6931248 |
| 2020-06-30 | Russia | 16097579 |
| 2020-06-30 | Spain | 7331192 |
| 2020-06-30 | Sweden | 1431896 |
| 2020-06-30 | UK | 6103975 |
| 2020-06-30 | US | 65059848 |
| 2020-06-30 | Ukraine | 1011298 |
| 2020-07-31 | Australia | 361723 |
| 2020-07-31 | Brazil | 62641865 |
| 2020-07-31 | Canada | 3460185 |
| 2020-07-31 | Chile | 9983781 |
| 2020-07-31 | Colombia | 5598108 |
| 2020-07-31 | Denmark | 6444 |
| 2020-07-31 | France | 317736 |
| 2020-07-31 | Germany | 6265079 |
| 2020-07-31 | Hong Kong | 57318 |
| 2020-07-31 | India | 32781165 |
| 2020-07-31 | Italy | 7560745 |
| 2020-07-31 | Japan | 771595 |
| 2020-07-31 | Macau | 1426 |
| 2020-07-31 | Mainland China | 2595103 |
| 2020-07-31 | Mexico | 10107738 |
| 2020-07-31 | Netherlands | 797069 |
| 2020-07-31 | Pakistan | 7910429 |
| 2020-07-31 | Peru | 10657640 |
| 2020-07-31 | Russia | 23233041 |
| 2020-07-31 | Spain | 8134290 |
| 2020-07-31 | Sweden | 2360582 |
| 2020-07-31 | UK | 9150130 |
| 2020-07-31 | US | 111580930 |
| 2020-07-31 | Ukraine | 1797522 |
| 2020-08-31 | Australia | 711091 |
| 2020-08-31 | Brazil | 102903896 |
| 2020-08-31 | Canada | 3854051 |
| 2020-08-31 | Chile | 11936049 |
| 2020-08-31 | Colombia | 14404679 |
| 2020-08-31 | Denmark | 11118 |
| 2020-08-31 | France | 434039 |
| 2020-08-31 | Germany | 7024733 |
| 2020-08-31 | Hong Kong | 134221 |
| 2020-08-31 | India | 82734792 |
| 2020-08-31 | Italy | 7919622 |
| 2020-08-31 | Japan | 1701916 |
| 2020-08-31 | Macau | 1426 |
| 2020-08-31 | Mainland China | 2628794 |
| 2020-08-31 | Mexico | 16108026 |
| 2020-08-31 | Netherlands | 1987056 |
| 2020-08-31 | Pakistan | 8935125 |
| 2020-08-31 | Peru | 16414082 |
| 2020-08-31 | Russia | 28508044 |
| 2020-08-31 | Spain | 11159043 |
| 2020-08-31 | Sweden | 2600324 |
| 2020-08-31 | UK | 9928933 |
| 2020-08-31 | US | 166786671 |
| 2020-08-31 | Ukraine | 2952987 |
| 2020-09-30 | Australia | 800899 |
| 2020-09-30 | Brazil | 132059962 |
| 2020-09-30 | Canada | 4290890 |
| 2020-09-30 | Chile | 13147124 |
| 2020-09-30 | Colombia | 21921158 |
| 2020-09-30 | Denmark | 13369 |
| 2020-09-30 | France | 645690 |
| 2020-09-30 | Germany | 8023521 |
| 2020-09-30 | Hong Kong | 149065 |
| 2020-09-30 | India | 151651367 |
| 2020-09-30 | Italy | 8749361 |
| 2020-09-30 | Japan | 2297182 |
| 2020-09-30 | Macau | 1380 |
| 2020-09-30 | Mainland China | 2557006 |
| 2020-09-30 | Mexico | 20309152 |
| 2020-09-30 | Netherlands | 2805482 |
| 2020-09-30 | Pakistan | 9118734 |
| 2020-09-30 | Peru | 22054476 |
| 2020-09-30 | Russia | 32289140 |
| 2020-09-30 | Spain | 18316260 |
| 2020-09-30 | Sweden | 2632351 |
| 2020-09-30 | UK | 11564506 |
| 2020-09-30 | US | 199653685 |
| 2020-09-30 | Ukraine | 5019534 |
| 2020-10-31 | Australia | 848364 |
| 2020-10-31 | Brazil | 160991539 |
| 2020-10-31 | Canada | 6126216 |
| 2020-10-31 | Chile | 15134758 |
| 2020-10-31 | Colombia | 29361707 |
| 2020-10-31 | Denmark | 15461 |
| 2020-10-31 | France | 1021451 |
| 2020-10-31 | Germany | 11676143 |
| 2020-10-31 | Hong Kong | 161825 |
| 2020-10-31 | India | 228634268 |
| 2020-10-31 | Italy | 13281216 |
| 2020-10-31 | Japan | 2856221 |
| 2020-10-31 | Macau | 1426 |
| 2020-10-31 | Mainland China | 2655726 |
| 2020-10-31 | Mexico | 26077771 |
| 2020-10-31 | Netherlands | 7020285 |
| 2020-10-31 | Pakistan | 10005964 |
| 2020-10-31 | Peru | 26653391 |
| 2020-10-31 | Russia | 42549555 |
| 2020-10-31 | Spain | 29494693 |
| 2020-10-31 | Sweden | 3240753 |
| 2020-10-31 | UK | 22002570 |
| 2020-10-31 | US | 252160215 |
| 2020-10-31 | Ukraine | 9292272 |
| 2020-11-30 | Australia | 832514 |
| 2020-11-30 | Belgium | 10509939 |
| 2020-11-30 | Brazil | 176837421 |
| 2020-11-30 | Canada | 9141329 |
| 2020-11-30 | Chile | 15945192 |
| 2020-11-30 | Colombia | 35992828 |
| 2020-11-30 | Denmark | 15467 |
| 2020-11-30 | France | 1443595 |
| 2020-11-30 | Germany | 24572075 |
| 2020-11-30 | Hong Kong | 167377 |
| 2020-11-30 | India | 265834390 |
| 2020-11-30 | Italy | 35324374 |
| 2020-11-30 | Japan | 3650314 |
| 2020-11-30 | Macau | 1380 |
| 2020-11-30 | Mainland China | 2590049 |
| 2020-11-30 | Mexico | 30342144 |
| 2020-11-30 | Netherlands | 13649806 |
| 2020-11-30 | Pakistan | 10891173 |
| 2020-11-30 | Peru | 28025436 |
| 2020-11-30 | Russia | 57925419 |
| 2020-11-30 | Spain | 43932783 |
| 2020-11-30 | Sweden | 5546377 |
| 2020-11-30 | UK | 40830975 |
| 2020-11-30 | US | 340017832 |
| 2020-11-30 | Ukraine | 16972054 |
| 2020-12-31 | Australia | 872046 |
| 2020-12-31 | Belgium | 19046283 |
| 2020-12-31 | Brazil | 218249737 |
| 2020-12-31 | Canada | 15031370 |
| 2020-12-31 | Chile | 17938094 |
| 2020-12-31 | Colombia | 45578807 |
| 2020-12-31 | Denmark | 17396 |
| 2020-12-31 | France | 1774323 |
| 2020-12-31 | Germany | 44141683 |
| 2020-12-31 | Hong Kong | 239977 |
| 2020-12-31 | India | 307985456 |
| 2020-12-31 | Italy | 58293082 |
| 2020-12-31 | Japan | 5880523 |
| 2020-12-31 | Macau | 1426 |
| 2020-12-31 | Mainland China | 2690556 |
| 2020-12-31 | Mexico | 39586956 |
| 2020-12-31 | Netherlands | 20507503 |
| 2020-12-31 | Pakistan | 13839663 |
| 2020-12-31 | Peru | 30624534 |
| 2020-12-31 | Russia | 84102182 |
| 2020-12-31 | Spain | 55067758 |
| 2020-12-31 | Sweden | 10695430 |
| 2020-12-31 | UK | 61365366 |
| 2020-12-31 | US | 528139775 |
| 2020-12-31 | Ukraine | 29178756 |
| 2021-01-31 | Australia | 889012 |
| 2021-01-31 | Belgium | 20983559 |
| 2021-01-31 | Brazil | 261187142 |
| 2021-01-31 | Canada | 21663417 |
| 2021-01-31 | Chile | 20661911 |
| 2021-01-31 | Colombia | 58374546 |
| 2021-01-31 | Denmark | 20883 |
| 2021-01-31 | France | 2013527 |
| 2021-01-31 | Germany | 62555458 |
| 2021-01-31 | Hong Kong | 297899 |
| 2021-01-31 | India | 326908857 |
| 2021-01-31 | Italy | 72917277 |
| 2021-01-31 | Japan | 9902662 |
| 2021-01-31 | Macau | 1437 |
| 2021-01-31 | Mainland China | 2735735 |
| 2021-01-31 | Mexico | 50652332 |
| 2021-01-31 | Netherlands | 28343170 |
| 2021-01-31 | Pakistan | 16029828 |
| 2021-01-31 | Peru | 32876154 |
| 2021-01-31 | Russia | 108422409 |
| 2021-01-31 | Spain | 71180786 |
| 2021-01-31 | Sweden | 15946013 |
| 2021-01-31 | UK | 102180395 |
| 2021-01-31 | US | 730894746 |
| 2021-01-31 | Ukraine | 36713095 |
| 2021-02-28 | Australia | 809190 |
| 2021-02-28 | Belgium | 20733707 |
| 2021-02-28 | Brazil | 276456100 |
| 2021-02-28 | Canada | 23280680 |
| 2021-02-28 | Chile | 21754084 |
| 2021-02-28 | Colombia | 61356188 |
| 2021-02-28 | Denmark | 19232 |
| 2021-02-28 | France | 2149196 |
| 2021-02-28 | Germany | 65660655 |
| 2021-02-28 | Hong Kong | 301335 |
| 2021-02-28 | India | 305984490 |
| 2021-02-28 | Italy | 76429829 |
| 2021-02-28 | Japan | 11627561 |
| 2021-02-28 | Macau | 1340 |
| 2021-02-28 | Mainland China | 2513697 |
| 2021-02-28 | Mexico | 55678011 |
| 2021-02-28 | Netherlands | 29303549 |
| 2021-02-28 | Pakistan | 15805181 |
| 2021-02-28 | Peru | 34491490 |
| 2021-02-28 | Russia | 112712911 |
| 2021-02-28 | Spain | 85568038 |
| 2021-02-28 | Sweden | 17201658 |
| 2021-02-28 | UK | 113211684 |
| 2021-02-28 | US | 774254261 |
| 2021-02-28 | Ukraine | 37009908 |
| 2021-03-31 | Australia | 903649 |
| 2021-03-31 | Belgium | 25421637 |
| 2021-03-31 | Brazil | 361059664 |
| 2021-03-31 | Canada | 28667193 |
| 2021-03-31 | Chile | 28062147 |
| 2021-03-31 | Colombia | 71861747 |
| 2021-03-31 | Denmark | 21422 |
| 2021-03-31 | France | 2747350 |
| 2021-03-31 | Germany | 81277883 |
| 2021-03-31 | Hong Kong | 349435 |
| 2021-03-31 | India | 357487053 |
| 2021-03-31 | Italy | 101162913 |
| 2021-03-31 | Japan | 13976951 |
| 2021-03-31 | Macau | 1488 |
| 2021-03-31 | Mainland China | 2792080 |
| 2021-03-31 | Mexico | 67295623 |
| 2021-03-31 | Netherlands | 36901518 |
| 2021-03-31 | Pakistan | 19174307 |
| 2021-03-31 | Peru | 44328085 |
| 2021-03-31 | Russia | 135088504 |
| 2021-03-31 | Spain | 99280629 |
| 2021-03-31 | Sweden | 22569251 |
| 2021-03-31 | UK | 132721966 |
| 2021-03-31 | US | 916643837 |
| 2021-03-31 | Ukraine | 47885644 |
| 2021-04-30 | Australia | 885710 |
| 2021-04-30 | Belgium | 28207302 |
| 2021-04-30 | Brazil | 412565115 |
| 2021-04-30 | Canada | 33242319 |
| 2021-04-30 | Chile | 33124646 |
| 2021-04-30 | Colombia | 78567887 |
| 2021-04-30 | Denmark | 20789 |
| 2021-04-30 | France | 2986432 |
| 2021-04-30 | Germany | 93513080 |
| 2021-04-30 | Hong Kong | 349045 |
| 2021-04-30 | India | 447598593 |
| 2021-04-30 | Italy | 114799462 |
| 2021-04-30 | Japan | 15835437 |
| 2021-04-30 | Macau | 1464 |
| 2021-04-30 | Mainland China | 2714147 |
| 2021-04-30 | Mexico | 68840861 |
| 2021-04-30 | Netherlands | 42290027 |
| 2021-04-30 | Pakistan | 22498648 |
| 2021-04-30 | Peru | 50368511 |
| 2021-04-30 | Russia | 138817103 |
| 2021-04-30 | Spain | 101966198 |
| 2021-04-30 | Sweden | 26658585 |
| 2021-04-30 | UK | 131952179 |
| 2021-04-30 | US | 944543693 |
| 2021-04-30 | Ukraine | 58596168 |
| 2021-05-31 | Australia | 869015 |
| 2021-05-31 | Belgium | 29831648 |
| 2021-05-31 | Brazil | 451476923 |
| 2021-05-31 | Canada | 38374241 |
| 2021-05-31 | Chile | 37172836 |
| 2021-05-31 | Colombia | 89997493 |
| 2021-05-31 | Denmark | 20523 |
| 2021-05-31 | France | 3289110 |
| 2021-05-31 | Germany | 103826264 |
| 2021-05-31 | Hong Kong | 342677 |
| 2021-05-31 | India | 704714394 |
| 2021-05-31 | Italy | 120086716 |
| 2021-05-31 | Japan | 19573346 |
| 2021-05-31 | Macau | 1441 |
| 2021-05-31 | Mainland China | 2635134 |
| 2021-05-31 | Mexico | 68985170 |
| 2021-05-31 | Netherlands | 46763492 |
| 2021-05-31 | Pakistan | 25442064 |
| 2021-05-31 | Peru | 54460864 |
| 2021-05-31 | Russia | 141404158 |
| 2021-05-31 | Spain | 104480995 |
| 2021-05-31 | Sweden | 29888569 |
| 2021-05-31 | UK | 129432185 |
| 2021-05-31 | US | 953529915 |
| 2021-05-31 | Ukraine | 63796837 |
Note here that as we progress down our table, we pass the total number of covid cases for each country reporting, whereas previously we would pass through the entire history of reported covid cases per country. As ever, the order our data is listed as is determined by the placement of columns in our groupby. With Grouper, our options are simply extended to aggregations of date-values—and the extensibility does not end there.
Complementary Functions
After grouping our data, we often want to operate on the values we have derived. Luckily, pandas provides us with assign, a function for manipulating newly derived columns in place. To use assign, most often one will also want to be comfortable with lambda expressions, so we'll be sure to implement them here as a reminder. Let's return to our National/Regional Covid Cases By Month for a use-case.
First, we'll rename our columns with some named aggregations for clarity, replacing "Confirmed" with the more accurate "Total_Cases".
(
covid_ts
.groupby(["Country/Region",
.Grouper(key="ObservationDate", freq="1M"), ])
.agg(Total_Cases=("Confirmed", "sum"),
)
.reset_index()
.head()
)
| Country/Region | ObservationDate | Total_Cases |
|---|---|---|
| Australia | 2020-01-31 | 33 |
| Australia | 2020-02-29.486 | |
| Australia | 2020-03-3135620 | |
| Australia | 2020-04-30 | 187842 |
| Australia | 2020-05-31 | 217625 |
Next, we'll add additional data to our table by inserting a named aggregation for "Total_Deaths" per country per month.
(
covid_ts
.groupby(["Country/Region",
.Grouper(key="ObservationDate", freq="1M"), ])
.agg(Total_Deaths=("Deaths", "sum"),
Total_Cases=("Confirmed", "sum"),
)
.reset_index()
.head()
)
| Country/Region | ObservationDate | Total_Deaths | Total_Cases |
|---|---|---|---|
| Australia | 2020-01-31 | 0 | 33 |
| Australia | 2020-02-29 | 0 | 486 |
| Australia | 2020-03-31 | 192 | 35620 |
| Australia | 2020-04-30 | 1817 | 187842 |
| Australia | 2020-05-31 | 3071 | 217625 |
Finally, we'll leverage assign by referencing our new "Total_Deaths" and "Total_Cases" in-line, using them to create an entirely new column of data: "Death_Case_Ratio", or "Total_Deaths" divided by "Total_Cases". By multiplying our new ratio by 100 we can derive an informative metric: the percentage of infected individuals who die each month in a given country/region. Finally, we'll rename our columns to more aesthetic titles, as spaces aren't allowed in named aggregations.
(
covid_ts
.groupby(["Country/Region",
pd.Grouper(key="ObservationDate", freq="1M"), ])
.agg(Total_Deaths=("Deaths", "sum"),
Total_Cases=("Confirmed", "sum")
)
.assign(
Death_Case_Ratio=lambda x: round(x['Total_Deaths']/x['Total_Cases']*100,2) )
.reset_index()
.rename({'Total_Deaths':'Total Deaths',
'Total_Cases':'Total Cases',
'Death_Case_Ratio':'Death/Case Ratio (%)'},
axis=1)
)
And wala we have the following National/Regional Death/Case Ratio By Month table.
National/Regional Death/Case Ratio By Month
| Country/Region | ObservationDate | Total Deaths | Total Cases | Death/Case Ratio (%) |
|---|---|---|---|---|
| Australia | 2020-01-31 | 0 | 33 | 0.0 |
| Australia | 2020-02-29 | 0 | 486 | 0.0 |
| Australia | 2020-03-31 | 192 | 35620 | 0.54 |
| Australia | 2020-04-30 | 1817 | 187842 | 0.97 |
| Australia | 2020-05-31 | 3071 | 217625 | 1.41 |
| Australia | 2020-06-30 | 3075 | 222611 | 1.38 |
| Australia | 2020-07-31 | 3996 | 361723 | 1.1 |
| Australia | 2020-08-31 | 13006 | 711091 | 1.83 |
| Australia | 2020-09-30 | 24475 | 800899 | 3.06 |
| Australia | 2020-10-31 | 27949 | 848364 | 3.29 |
| Australia | 2020-11-30 | 27212 | 832514 | 3.27 |
| Australia | 2020-12-31 | 28153 | 872046 | 3.23 |
| Australia | 2021-01-31 | 28179 | 889012 | 3.17 |
| Australia | 2021-02-28 | 25452 | 809190 | 3.15 |
| Australia | 2021-03-31 | 28179 | 903649 | 3.12 |
| Australia | 2021-04-30 | 27289 | 885710 | 3.08 |
| Australia | 2021-05-31 | 26390 | 869015 | 3.04 |
| Austria | 2020-02-29 | 0 | 2 | 0.0 |
| Belgium | 2020-11-30 | 292870 | 10509939 | 2.79 |
| Belgium | 2020-12-31 | 565082 | 19046283 | 2.97 |
| Belgium | 2021-01-31 | 631655 | 20983559 | 3.01 |
| Belgium | 2021-02-28 | 606413 | 20733707 | 2.92 |
| Belgium | 2021-03-31 | 698886 | 25421637 | 2.75 |
| Belgium | 2021-04-30 | 709354 | 28207302 | 2.51 |
| Belgium | 2021-05-31 | 715015 | 29831648 | 2.4 |
| Brazil | 2020-05-31 | 290996 | 4737799 | 6.14 |
| Brazil | 2020-06-30 | 1353418 | 28128253 | 4.81 |
| Brazil | 2020-07-31 | 2370476 | 62641865 | 3.78 |
| Brazil | 2020-08-31 | 3345405 | 102903896 | 3.25 |
| Brazil | 2020-09-30 | 4004352 | 132059962 | 3.03 |
| Brazil | 2020-10-31 | 4732789 | 160991539 | 2.94 |
| Brazil | 2020-11-30 | 4986319 | 176837421 | 2.82 |
| Brazil | 2020-12-31 | 5702548 | 218249737 | 2.61 |
| Brazil | 2021-01-31 | 6483686 | 261187142 | 2.48 |
| Brazil | 2021-02-28 | 6713342 | 276456100 | 2.43 |
| Brazil | 2021-03-31 | 8826515 | 361059664 | 2.44 |
| Brazil | 2021-04-30 | 10952414 | 412565115 | 2.65 |
| Brazil | 2021-05-31 | 12576769 | 451476923 | 2.79 |
| Canada | 2020-01-31 | 0 | 12 | 0.0 |
| Canada | 2020-02-29 | 0 | 234 | 0.0 |
| Canada | 2020-03-31 | 777 | 52012 | 1.49 |
| Canada | 2020-04-30 | 54080 | 927235 | 5.83 |
| Canada | 2020-05-31 | 198652 | 2369720 | 8.38 |
| Canada | 2020-06-30 | 254101 | 3016433 | 8.42 |
| Canada | 2020-07-31 | 276342 | 3460185 | 7.99 |
| Canada | 2020-08-31 | 282360 | 3854051 | 7.33 |
| Canada | 2020-09-30 | 278084 | 4290890 | 6.48 |
| Canada | 2020-10-31 | 305041 | 6126216 | 4.98 |
| Canada | 2020-11-30 | 335239 | 9141329 | 3.67 |
| Canada | 2020-12-31 | 432388 | 15031370 | 2.88 |
| Canada | 2021-01-31 | 556738 | 21663417 | 2.57 |
| Canada | 2021-02-28 | 594361 | 23280680 | 2.55 |
| Canada | 2021-03-31 | 697637 | 28667193 | 2.43 |
| Canada | 2021-04-30 | 706012 | 33242319 | 2.12 |
| Canada | 2021-05-31 | 721084 | 38374241 | 1.88 |
| Chile | 2020-05-31 | 9447 | 913924 | 1.03 |
| Chile | 2020-06-30 | 101418 | 5806627 | 1.75 |
| Chile | 2020-07-31 | 239082 | 9983781 | 2.39 |
| Chile | 2020-08-31 | 323785 | 11936049 | 2.71 |
| Chile | 2020-09-30 | 361583 | 13147124 | 2.75 |
| Chile | 2020-10-31 | 419247 | 15134758 | 2.77 |
| Chile | 2020-11-30 | 444645 | 15945192 | 2.79 |
| Chile | 2020-12-31 | 495826 | 17938094 | 2.76 |
| Chile | 2021-01-31 | 540938 | 20661911 | 2.62 |
| Chile | 2021-02-28 | 546762 | 21754084 | 2.51 |
| Chile | 2021-03-31 | 676397 | 28062147 | 2.41 |
| Chile | 2021-04-30 | 744626 | 33124646 | 2.25 |
| Chile | 2021-05-31 | 803908 | 37172836 | 2.16 |
| China | 2020-01-31 | 0 | 0 | nan |
| China | 2020-02-29 | 0 | 0 | nan |
| China | 2020-03-31 | 0 | 0 | nan |
| Colombia | 2020-05-31 | 3495 | 103500 | 3.38 |
| Colombia | 2020-06-30 | 58061 | 1698303 | 3.42 |
| Colombia | 2020-07-31 | 196918 | 5598108 | 3.52 |
| Colombia | 2020-08-31 | 466128 | 14404679 | 3.24 |
| Colombia | 2020-09-30 | 696562 | 21921158 | 3.18 |
| Colombia | 2020-10-31 | 889237 | 29361707 | 3.03 |
| Colombia | 2020-11-30 | 1023991 | 35992828 | 2.84 |
| Colombia | 2020-12-31 | 1233616 | 45578807 | 2.71 |
| Colombia | 2021-01-31 | 1501117 | 58374546 | 2.57 |
| Colombia | 2021-02-28 | 1608609 | 61356188 | 2.62 |
| Colombia | 2021-03-31 | 1906300 | 71861747 | 2.65 |
| Colombia | 2021-04-30 | 2035213 | 78567887 | 2.59 |
| Colombia | 2021-05-31 | 2343680 | 89997493 | 2.6 |
| Denmark | 2020-03-31 | 42 | 12126 | 0.35 |
| Denmark | 2020-04-30 | 0 | 5845 | 0.0 |
| Denmark | 2020-05-31 | 0 | 6151 | 0.0 |
| Denmark | 2020-06-30 | 0 | 6000 | 0.0 |
| Denmark | 2020-07-31 | 0 | 6444 | 0.0 |
| Denmark | 2020-08-31 | 0 | 11118 | 0.0 |
| Denmark | 2020-09-30 | 0 | 13369 | 0.0 |
| Denmark | 2020-10-31 | 0 | 15461 | 0.0 |
| Denmark | 2020-11-30 | 0 | 15467 | 0.0 |
| Denmark | 2020-12-31 | 0 | 17396 | 0.0 |
| Denmark | 2021-01-31 | 26 | 20883 | 0.12 |
| Denmark | 2021-02-28 | 28 | 19232 | 0.15 |
| Denmark | 2021-03-31 | 31 | 21422 | 0.14 |
| Denmark | 2021-04-30 | 30 | 20789 | 0.14 |
| Denmark | 2021-05-31 | 29 | 20523 | 0.14 |
| France | 2020-03-31 | 2951 | 102660 | 2.87 |
| France | 2020-04-30 | 700 | 34314 | 2.04 |
| France | 2020-05-31 | 1437 | 73566 | 1.95 |
| France | 2020-06-30 | 1984 | 151652 | 1.31 |
| France | 2020-07-31 | 3185 | 317736 | 1.0 |
| France | 2020-08-31 | 4057 | 434039 | 0.93 |
| France | 2020-09-30 | 5174 | 645690 | 0.8 |
| France | 2020-10-31 | 8435 | 1021451 | 0.83 |
| France | 2020-11-30 | 11761 | 1443595 | 0.81 |
| France | 2020-12-31 | 14503 | 1774323 | 0.82 |
| France | 2021-01-31 | 15896 | 2013527 | 0.79 |
| France | 2021-02-28 | 15702 | 2149196 | 0.73 |
| France | 2021-03-31 | 20868 | 2747350 | 0.76 |
| France | 2021-04-30 | 24131 | 2986432 | 0.81 |
| France | 2021-05-31 | 27025 | 3289110 | 0.82 |
| Germany | 2020-01-31 | 0 | 17 | 0.0 |
| Germany | 2020-05-31 | 148037 | 3228720 | 4.59 |
| Germany | 2020-06-30 | 264281 | 5670762 | 4.66 |
| Germany | 2020-07-31 | 281460 | 6265079 | 4.49 |
| Germany | 2020-08-31 | 286250 | 7024733 | 4.07 |
| Germany | 2020-09-30 | 281455 | 8023521 | 3.51 |
| Germany | 2020-10-31 | 304666 | 11676143 | 2.61 |
| Germany | 2020-11-30 | 393286 | 24572075 | 1.6 |
| Germany | 2020-12-31 | 763375 | 44141683 | 1.73 |
| Germany | 2021-01-31 | 1428068 | 62555458 | 2.28 |
| Germany | 2021-02-28 | 1824513 | 65660655 | 2.78 |
| Germany | 2021-03-31 | 2289398 | 81277883 | 2.82 |
| Germany | 2021-04-30 | 2390995 | 93513080 | 2.56 |
| Germany | 2021-05-31 | 2494331 | 103826264 | 2.4 |
| Hong Kong | 2020-01-31 | 0 | 65 | 0.0 |
| Hong Kong | 2020-02-29 | 37 | 1543 | 2.4 |
| Hong Kong | 2020-03-31 | 105 | 8018 | 1.31 |
| Hong Kong | 2020-04-30 | 120 | 29443 | 0.41 |
| Hong Kong | 2020-05-31 | 124 | 32692 | 0.38 |
| Hong Kong | 2020-06-30 | 145 | 34000 | 0.43 |
| Hong Kong | 2020-07-31 | 371 | 57318 | 0.65 |
| Hong Kong | 2020-08-31 | 2010 | 134221 | 1.5 |
| Hong Kong | 2020-09-30 | 3015 | 149065 | 2.02 |
| Hong Kong | 2020-10-31 | 3255 | 161825 | 2.01 |
| Hong Kong | 2020-11-30 | 3227 | 167377 | 1.93 |
| Hong Kong | 2020-12-31 | 3862 | 239977 | 1.61 |
| Hong Kong | 2021-01-31 | 5059 | 297899 | 1.7 |
| Hong Kong | 2021-02-28 | 5385 | 301335 | 1.79 |
| Hong Kong | 2021-03-31 | 6291 | 349435 | 1.8 |
| Hong Kong | 2021-04-30 | 6229 | 349045 | 1.78 |
| Hong Kong | 2021-05-31 | 6090 | 342677 | 1.78 |
| India | 2020-06-30 | 270564 | 8775360 | 3.08 |
| India | 2020-07-31 | 811787 | 32781165 | 2.48 |
| India | 2020-08-31 | 1582152 | 82734792 | 1.91 |
| India | 2020-09-30 | 2475710 | 151651367 | 1.63 |
| India | 2020-10-31 | 3480895 | 228634268 | 1.52 |
| India | 2020-11-30 | 3909659 | 265834390 | 1.47 |
| India | 2020-12-31 | 4468495 | 307985456 | 1.45 |
| India | 2021-01-31 | 4714113 | 326908857 | 1.44 |
| India | 2021-02-28 | 4362199 | 305984490 | 1.43 |
| India | 2021-03-31 | 4941266 | 357487053 | 1.38 |
| India | 2021-04-30 | 5389224 | 447598593 | 1.2 |
| India | 2021-05-31 | 7849949 | 704714394 | 1.11 |
| Iraq | 2020-02-29 | 0 | 0 | nan |
| Israel | 2020-02-29 | 0 | 5 | 0.0 |
| Italy | 2020-05-31 | 585807 | 4114467 | 14.24 |
| Italy | 2020-06-30 | 1028841 | 7115232 | 14.46 |
| Italy | 2020-07-31 | 1084927 | 7560745 | 14.35 |
| Italy | 2020-08-31 | 1095146 | 7919622 | 13.83 |
| Italy | 2020-09-30 | 1069880 | 8749361 | 12.23 |
| Italy | 2020-10-31 | 1137835 | 13281216 | 8.57 |
| Italy | 2020-11-30 | 1385115 | 35324374 | 3.92 |
| Italy | 2020-12-31 | 2046224 | 58293082 | 3.51 |
| Italy | 2021-01-31 | 2531790 | 72917277 | 3.47 |
| Italy | 2021-02-28 | 2622056 | 76429829 | 3.43 |
| Italy | 2021-03-31 | 3201000 | 101162913 | 3.16 |
| Italy | 2021-04-30 | 3472382 | 114799462 | 3.02 |
| Italy | 2021-05-31 | 3591221 | 120086716 | 2.99 |
| Japan | 2020-05-31 | 3560 | 66738 | 5.33 |
| Japan | 2020-06-30 | 28137 | 525609 | 5.35 |
| Japan | 2020-07-31 | 30609 | 771595 | 3.97 |
| Japan | 2020-08-31 | 34874 | 1701916 | 2.05 |
| Japan | 2020-09-30 | 43820 | 2297182 | 1.91 |
| Japan | 2020-10-31 | 51571 | 2856221 | 1.81 |
| Japan | 2020-11-30 | 56891 | 3650314 | 1.56 |
| Japan | 2020-12-31 | 82231 | 5880523 | 1.4 |
| Japan | 2021-01-31 | 140750 | 9902662 | 1.42 |
| Japan | 2021-02-28 | 195564 | 11627561 | 1.68 |
| Japan | 2021-03-31 | 267223 | 13976951 | 1.91 |
| Japan | 2021-04-30 | 287341 | 15835437 | 1.81 |
| Japan | 2021-05-31 | 333106 | 19573346 | 1.7 |
| Lebanon | 2020-02-29 | 0 | 1 | 0.0 |
| Macau | 2020-01-31 | 0 | 46 | 0.0 |
| Macau | 2020-02-29 | 0 | 283 | 0.0 |
| Macau | 2020-03-31 | 0 | 556 | 0.0 |
| Macau | 2020-04-30 | 0 | 1335 | 0.0 |
| Macau | 2020-05-31 | 0 | 1395 | 0.0 |
| Macau | 2020-06-30 | 0 | 1356 | 0.0 |
| Macau | 2020-07-31 | 0 | 1426 | 0.0 |
| Macau | 2020-08-31 | 0 | 1426 | 0.0 |
| Macau | 2020-09-30 | 0 | 1380 | 0.0 |
| Macau | 2020-10-31 | 0 | 1426 | 0.0 |
| Macau | 2020-11-30 | 0 | 1380 | 0.0 |
| Macau | 2020-12-31 | 0 | 1426 | 0.0 |
| Macau | 2021-01-31 | 0 | 1437 | 0.0 |
| Macau | 2021-02-28 | 0 | 1340 | 0.0 |
| Macau | 2021-03-31 | 0 | 1488 | 0.0 |
| Macau | 2021-04-30 | 0 | 1464 | 0.0 |
| Macau | 2021-05-31 | 0 | 1441 | 0.0 |
| Mainland China | 2020-01-31 | 905 | 38340 | 2.36 |
| Mainland China | 2020-02-29 | 46380 | 1631535 | 2.84 |
| Mainland China | 2020-03-31 | 98374 | 2506812 | 3.92 |
| Mainland China | 2020-04-30 | 118170 | 2469365 | 4.79 |
| Mainland China | 2020-05-31 | 143639 | 2571194 | 5.59 |
| Mainland China | 2020-06-30 | 139020 | 2497323 | 5.57 |
| Mainland China | 2020-07-31 | 143654 | 2595103 | 5.54 |
| Mainland China | 2020-08-31 | 143654 | 2628794 | 5.46 |
| Mainland China | 2020-09-30 | 139020 | 2557006 | 5.44 |
| Mainland China | 2020-10-31 | 143654 | 2655726 | 5.41 |
| Mainland China | 2020-11-30 | 139020 | 2590049 | 5.37 |
| Mainland China | 2020-12-31 | 143654 | 2690556 | 5.34 |
| Mainland China | 2021-01-31 | 143680 | 2735735 | 5.25 |
| Mainland China | 2021-02-28 | 129808 | 2513697 | 5.16 |
| Mainland China | 2021-03-31 | 143716 | 2792080 | 5.15 |
| Mainland China | 2021-04-30 | 139080 | 2714147 | 5.12 |
| Mainland China | 2021-05-31 | 134444 | 2635134 | 5.1 |
| Mexico | 2020-05-31 | 96694 | 881055 | 10.97 |
| Mexico | 2020-06-30 | 562682 | 4692440 | 11.99 |
| Mexico | 2020-07-31 | 1164238 | 10107738 | 11.52 |
| Mexico | 2020-08-31 | 1749213 | 16108026 | 10.86 |
| Mexico | 2020-09-30 | 2150492 | 20309152 | 10.59 |
| Mexico | 2020-10-31 | 2649132 | 26077771 | 10.16 |
| Mexico | 2020-11-30 | 2962546 | 30342144 | 9.76 |
| Mexico | 2020-12-31 | 3594393 | 39586956 | 9.08 |
| Mexico | 2021-01-31 | 4367196 | 50652332 | 8.62 |
| Mexico | 2021-02-28 | 4863060 | 55678011 | 8.73 |
| Mexico | 2021-03-31 | 6056567 | 67295623 | 9.0 |
| Mexico | 2021-04-30 | 6318085 | 68840861 | 9.18 |
| Mexico | 2021-05-31 | 6383863 | 68985170 | 9.25 |
| Netherlands | 2020-03-31 | 456 | 14701 | 3.1 |
| Netherlands | 2020-04-30 | 319 | 4764 | 6.7 |
| Netherlands | 2020-05-31 | 571 | 6199 | 9.21 |
| Netherlands | 2020-06-30 | 570 | 6217 | 9.17 |
| Netherlands | 2020-07-31 | 92695 | 797069 | 11.63 |
| Netherlands | 2020-08-31 | 192279 | 1987056 | 9.68 |
| Netherlands | 2020-09-30 | 189698 | 2805482 | 6.76 |
| Netherlands | 2020-10-31 | 211846 | 7020285 | 3.02 |
| Netherlands | 2020-11-30 | 256715 | 13649806 | 1.88 |
| Netherlands | 2020-12-31 | 322195 | 20507503 | 1.57 |
| Netherlands | 2021-01-31 | 401458 | 28343170 | 1.42 |
| Netherlands | 2021-02-28 | 418927 | 29303549 | 1.43 |
| Netherlands | 2021-03-31 | 503675 | 36901518 | 1.36 |
| Netherlands | 2021-04-30 | 511880 | 42290027 | 1.21 |
| Netherlands | 2021-05-31 | 512773 | 46763492 | 1.1 |
| Others | 2020-02-29 | 35 | 10076 | 0.35 |
| Others | 2020-03-31 | 154 | 16152 | 0.95 |
| Pakistan | 2020-06-30 | 71404 | 3594515 | 1.99 |
| Pakistan | 2020-07-31 | 166284 | 7910429 | 2.1 |
| Pakistan | 2020-08-31 | 190811 | 8935125 | 2.14 |
| Pakistan | 2020-09-30 | 191953 | 9118734 | 2.11 |
| Pakistan | 2020-10-31 | 205956 | 10005964 | 2.06 |
| Pakistan | 2020-11-30 | 220356 | 10891173 | 2.02 |
| Pakistan | 2020-12-31 | 282857 | 13839663 | 2.04 |
| Pakistan | 2021-01-31 | 340044 | 16029828 | 2.12 |
| Pakistan | 2021-02-28 | 345787 | 15805181 | 2.19 |
| Pakistan | 2021-03-31 | 423884 | 19174307 | 2.21 |
| Pakistan | 2021-04-30 | 483568 | 22498648 | 2.15 |
| Pakistan | 2021-05-31 | 566161 | 25442064 | 2.23 |
| Peru | 2020-05-31 | 17075 | 603705 | 2.83 |
| Peru | 2020-06-30 | 210200 | 6931248 | 3.03 |
| Peru | 2020-07-31 | 424053 | 10657640 | 3.98 |
| Peru | 2020-08-31 | 759195 | 16414082 | 4.63 |
| Peru | 2020-09-30 | 923911 | 22054476 | 4.19 |
| Peru | 2020-10-31 | 1039335 | 26653391 | 3.9 |
| Peru | 2020-11-30 | 1056154 | 28025436 | 3.77 |
| Peru | 2020-12-31 | 1140356 | 30624534 | 3.72 |
| Peru | 2021-01-31 | 1204526 | 32876154 | 3.66 |
| Peru | 2021-02-28 | 1222507 | 34491490 | 3.54 |
| Peru | 2021-03-31 | 1526553 | 44328085 | 3.44 |
| Peru | 2021-04-30 | 1692814 | 50368511 | 3.36 |
| Peru | 2021-05-31 | 1902554 | 54460864 | 3.49 |
| Russia | 2020-06-30 | 215268 | 16097579 | 1.34 |
| Russia | 2020-07-31 | 366477 | 23233041 | 1.58 |
| Russia | 2020-08-31 | 484136 | 28508044 | 1.7 |
| Russia | 2020-09-30 | 565674 | 32289140 | 1.75 |
| Russia | 2020-10-31 | 738104 | 42549555 | 1.73 |
| Russia | 2020-11-30 | 1000094 | 57925419 | 1.73 |
| Russia | 2020-12-31 | 1490416 | 84102182 | 1.77 |
| Russia | 2021-01-31 | 1990841 | 108422409 | 1.84 |
| Russia | 2021-02-28 | 2207943 | 112712911 | 1.96 |
| Russia | 2021-03-31 | 2830301 | 135088504 | 2.1 |
| Russia | 2021-04-30 | 3087531 | 138817103 | 2.22 |
| Russia | 2021-05-31 | 3294444 | 141404158 | 2.33 |
| Spain | 2020-05-31 | 496839 | 4219335 | 11.78 |
| Spain | 2020-06-30 | 828405 | 7331192 | 11.3 |
| Spain | 2020-07-31 | 880773 | 8134290 | 10.83 |
| Spain | 2020-08-31 | 889890 | 11159043 | 7.97 |
| Spain | 2020-09-30 | 907525 | 18316260 | 4.95 |
| Spain | 2020-10-31 | 1046133 | 29494693 | 3.55 |
| Spain | 2020-11-30 | 1232707 | 43932783 | 2.81 |
| Spain | 2020-12-31 | 1497045 | 55067758 | 2.72 |
| Spain | 2021-01-31 | 1671602 | 71180786 | 2.35 |
| Spain | 2021-02-28 | 1817907 | 85568038 | 2.12 |
| Spain | 2021-03-31 | 2253421 | 99280629 | 2.27 |
| Spain | 2021-04-30 | 2304772 | 101966198 | 2.26 |
| Spain | 2021-05-31 | 2297321 | 104480995 | 2.2 |
| Sweden | 2020-06-30 | 129864 | 1431896 | 9.07 |
| Sweden | 2020-07-31 | 172917 | 2360582 | 7.33 |
| Sweden | 2020-08-31 | 179376 | 2600324 | 6.9 |
| Sweden | 2020-09-30 | 175676 | 2632351 | 6.67 |
| Sweden | 2020-10-31 | 183272 | 3240753 | 5.66 |
| Sweden | 2020-11-30 | 187760 | 5546377 | 3.39 |
| Sweden | 2020-12-31 | 240064 | 10695430 | 2.24 |
| Sweden | 2021-01-31 | 316144 | 15946013 | 1.98 |
| Sweden | 2021-02-28 | 347461 | 17201658 | 2.02 |
| Sweden | 2021-03-31 | 408718 | 22569251 | 1.81 |
| Sweden | 2021-04-30 | 412253 | 26658585 | 1.55 |
| Sweden | 2021-05-31 | 413737 | 29888569 | 1.38 |
| Taiwan | 2020-01-31 | 0 | 52 | 0.0 |
| Taiwan | 2020-02-29 | 14 | 616 | 2.27 |
| Taiwan | 2020-03-31 | 10 | 436 | 2.29 |
| UK | 2020-03-31 | 820 | 23768 | 3.45 |
| UK | 2020-04-30 | 974 | 28370 | 3.43 |
| UK | 2020-05-31 | 2438 | 40553 | 6.01 |
| UK | 2020-06-30 | 854141 | 6103975 | 13.99 |
| UK | 2020-07-31 | 1399546 | 9150130 | 15.3 |
| UK | 2020-08-31 | 1368183 | 9928933 | 13.78 |
| UK | 2020-09-30 | 1254542 | 11564506 | 10.85 |
| UK | 2020-10-31 | 1358294 | 22002570 | 6.17 |
| UK | 2020-11-30 | 1573977 | 40830975 | 3.85 |
| UK | 2020-12-31 | 2042273 | 61365366 | 3.33 |
| UK | 2021-01-31 | 2759728 | 102180395 | 2.7 |
| UK | 2021-02-28 | 3272231 | 113211684 | 2.89 |
| UK | 2021-03-31 | 3896724 | 132721966 | 2.94 |
| UK | 2021-04-30 | 3822739 | 131952179 | 2.9 |
| UK | 2021-05-31 | 3709653 | 129432185 | 2.87 |
| US | 2020-01-31 | 0 | 37 | 0.0 |
| US | 2020-02-29 | 1 | 718 | 0.14 |
| US | 2020-03-31 | 18447 | 1088640 | 1.69 |
| US | 2020-04-30 | 971766 | 19534148 | 4.97 |
| US | 2020-05-31 | 2706969 | 45256716 | 5.98 |
| US | 2020-06-30 | 3499386 | 65059848 | 5.38 |
| US | 2020-07-31 | 4307847 | 111580930 | 3.86 |
| US | 2020-08-31 | 5265411 | 166786671 | 3.16 |
| US | 2020-09-30 | 5887121 | 199653685 | 2.95 |
| US | 2020-10-31 | 6790817 | 252160215 | 2.69 |
| US | 2020-11-30 | 7501823 | 340017832 | 2.21 |
| US | 2020-12-31 | 9676987 | 528139775 | 1.83 |
| US | 2021-01-31 | 12443946 | 730894746 | 1.7 |
| US | 2021-02-28 | 13633706 | 774254261 | 1.76 |
| US | 2021-03-31 | 16649633 | 916643837 | 1.82 |
| US | 2021-04-30 | 16967994 | 944543693 | 1.8 |
| US | 2021-05-31 | 16981908 | 953529915 | 1.78 |
| Ukraine | 2020-06-30 | 27975 | 1011298 | 2.77 |
| Ukraine | 2020-07-31 | 45117 | 1797522 | 2.51 |
| Ukraine | 2020-08-31 | 65862 | 2952987 | 2.23 |
| Ukraine | 2020-09-30 | 101902 | 5019534 | 2.03 |
| Ukraine | 2020-10-31 | 174947 | 9292272 | 1.88 |
| Ukraine | 2020-11-30 | 302110 | 16972054 | 1.78 |
| Ukraine | 2020-12-31 | 503232 | 29178756 | 1.72 |
| Ukraine | 2021-01-31 | 670535 | 36713095 | 1.83 |
| Ukraine | 2021-02-28 | 719876 | 37009908 | 1.95 |
| Ukraine | 2021-03-31 | 947071 | 47885644 | 1.98 |
| Ukraine | 2021-04-30 | 1214256 | 58596168 | 2.07 |
| Ukraine | 2021-05-31 | 1436619 | 63796837 | 2.25 |
Aggregating Text and Filtering
Groupbys are not simply convenient for aggregating numerical data—they are also useful for summarizing text data too. Let's return again to our National/Regional Death/Case Ratio by Month table. This time, we'll leverage a lambda function within our agg function, expanding its flexibility to its fullest extent.
(
covid_ts
.groupby(["Country/Region",
pd.Grouper(key="ObservationDate", freq="1M"), ])
.agg(Total_Deaths=("Deaths", "sum"),
Total_Cases=("Confirmed", "sum"),
City_States=('Province/State', lambda x: ', '.join(set(x)))
)
.assign(
Death_Case_Ratio=lambda x: round(x['Total_Deaths']/x['Total_Cases']*100,2) )
.reset_index()
.rename({'Total_Deaths':'Total Deaths',
'Total_Cases':'Total Cases',
'City_States':'City/States',
'Death_Case_Ratio':'Death/Case Ratio (%)'},
axis=1)
.query(""" <code>Country/Region == 'US'""")
)
Here we generate a "City_States" column (subsequently renamed to "City/States") in which we aggregate "Province/State" text data to the monthly and country level. By implementing a join function, and filtering redundant data with set, we are instructing pandas to list out which cities and towns comprise our data. To visualize this, a filter was also added in the form of Pandas' powerful query function, which allows us to filter columns in place using boolean expressions. Here we filtered "Country/Region" to only include the United States. The result of our work can be seen in the US Death/Case Ratio by Month table below.
US Death/Case Ratio by Month
| Country/Region | ObservationDate | Total Deaths | Total Cases | City/States | Death/Case Ratio (%) |
|---|---|---|---|---|---|
| US | 2020-02-29 | 1 | 718 | Lackland TX Boston MA Humboldt County CA Unassigned Location (From Diamond Princess) Lackland TX (From Diamond Princess) Santa Clara CA San Antonio TX Ashland NE Madison WI San Diego County CA Orange CA Travis CA (From Diamond Princess) Seattle WA Snohomish County WA Tempe AZ Los Angeles CA Chicago IL Omaha NE (From Diamond Princess) Sacramento County CA San Benito CA Portland OR Travis CA | 0.14 |
| US | 2020-03-31 | 18447 | 1088640 | Delaware Mississippi San Mateo CA Montgomery County MD Hillsborough FL Collin County TX Illinois Maryland Humboldt County CA Colorado West Virginia Norfolk County MA US Fulton County GA Harris County TX North Dakota Yolo County CA St. Louis County MO Guam Placer County CA Johnson County KS Minnesota Ramsey County MN New Hampshire Chatham County NC Indiana Riverside County CA Volusia County FL American Samoa Tempe AZ New Mexico Alabama Recovered Rockingham County NH Douglas County OR Omaha NE (From Diamond Princess) Fayette County KY San Benito CA Alameda County CA Plymouth County MA Bergen County NJ Floyd County GA Kershaw County SC Harford County MD Utah Saratoga County NY Oregon Polk County GA Rockland County NY Montgomery County PA Montana Unknown Location MA Marion County OR Santa Clara CA Wyoming Grand Princess Cruise Ship Idaho Jefferson County WA Washington D.C. Hawaii Washington Spokane County WA Grand Princess Virginia Nebraska Douglas County CO Davidson County TN Douglas County NE District of Columbia Berkeley CA Shelby County TN Iowa Los Angeles CA Manatee County FL Madera County CA Fort Bend County TX Chicago IL Pennsylvania United States Virgin Islands Summit County CO Wisconsin Maricopa County AZ Shasta County CA Maine Arkansas Grafton County NH Clark County WA Cherokee County GA New York City NY Honolulu County HI Rhode Island Louisiana South Carolina Norwell County MA Lackland TX (From Diamond Princess) Jefferson Parish LA San Antonio TX Norfolk County MA Montgomery County TX Arizona Texas Orange County CA Massachusetts San Diego County CA Madison WI Queens County NY Sarasota FL Pierce County WA Ulster County NY Orange CA Delaware County PA Nevada Cook County IL Unassigned Location VT Carver County MN South Dakota Wake County NC Clark County NV Harrison County KY Snohomish County WA Wuhan Evacuee Washoe County NV Pinal County AZ Providence County RI Connecticut El Paso County CO Berkshire County MA Diamond Princess cruise ship Tulsa County OK San Francisco County CA Bennington County VT Santa Rosa County FL Fresno County CA Sacramento County CA Unassigned Location WA Klamath County OR Williamson County TN Denver County CO Broward County FL Santa Cruz County CA New Jersey Suffolk County MA California New York County NY Jefferson County KY Hudson County NJ New York Charlotte County FL Grant County WA Johnson County IA Boston MA Spartanburg County SC Unassigned Location (From Diamond Princess) Westchester County NY Middlesex County MA Wayne County PA Missouri Charleston County SC Ohio Lee County FL Providence RI Vermont Nassau County NY Davis County UT Contra Costa County CA Kittitas County WA Tennessee Virgin Islands Washington County OR Hendricks County IN Travis CA (From Diamond Princess) Fairfield County CT Jackson County OR Marion County IN Cobb County GA Seattle WA North Carolina Kansas Suffolk County NY King County WA Okaloosa County FL Michigan Umatilla OR Puerto Rico Fairfax County VA Florida Sonoma County CA Oklahoma Alaska Santa Clara County CA Georgia Virgin Islands U.S. Kentucky Portland OR Northern Mariana Islands | 1.69 |
| US | 2020-04-30 | 971766 | 19534148 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 4.97 |
| US | 2020-05-31 | 2706969 | 45256716 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 5.98 |
| US | 2020-06-30 | 3499386 | 65059848 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 5.38 |
| US | 2020-07-31 | 4307847 | 111580930 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 3.86 |
| US | 2020-08-31 | 5265411 | 166786671 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 3.16 |
| US | 2020-09-30 | 5887121 | 199653685 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 2.95 |
| US | 2020-10-31 | 6790817 | 252160215 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 2.69 |
| US | 2020-11-30 | 7501823 | 340017832 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 2.21 |
| US | 2020-12-31 | 9676987 | 528139775 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.83 |
| US | 2021-01-31 | 12443946 | 730894746 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.7 |
| US | 2021-02-28 | 13633706 | 774254261 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.76 |
| US | 2021-03-31 | 16649633 | 916643837 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.82 |
| US | 2021-04-30 | 16967994 | 944543693 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.8 |
| US | 2021-05-31 | 16981908 | 953529915 | Delaware Mississippi Illinois Oregon Arkansas Louisiana Maryland New York Rhode Island South Carolina Colorado West Virginia Montana Missouri North Dakota Ohio Arizona Wyoming Idaho Texas Vermont Massachusetts Guam Hawaii Nevada Washington Tennessee Virgin Islands Minnesota Utah New Hampshire South Dakota Grand Princess Nebraska Indiana Virginia District of Columbia Kansas North Carolina Alabama Michigan New Mexico Iowa Connecticut Recovered Diamond Princess cruise ship Puerto Rico Oklahoma Florida Alaska Georgia Pennsylvania Wisconsin New Jersey Kentucky Maine Northern Mariana Islands California | 1.78 |
Summary
As we can see, pandas groupbys are far more flexible than they are typically used for. We have seen that when we leverage functions such as Grouper, we are able to aggregate timeseries data using offset aliases. By implementing the agg function with named aggregations, we can reference and manipulate these new columns in place by appending an assign function to our code. Finally, we have seen that even text need not be ignored, thanks to our ability to use lambda functions within agg functions as well. To top it off, we saw that Pandas' powerful query function allows us to filter our data to whatever granularity we'd like using boolean expressions.