Group distribution of last names by country in dataframe

Viewed 10

I have a very big datagframe with, amongst other things, the last names of the country of origin of ~2000 people, like this (obviously dummy information used):

ID    lastname     country     age     children
1     Smith        USA         44      4
2     Smith        UK          33      0
3     Kim          USA         14      0
4     Smith        Australia   24      0
5     Kim          USA         64      3
6     Sanchez      UK          22      0
7     Smith        USA         45      1

What I want to do is create a dataframe that groups lastname and country by lastname and puts every country as a new column and counts the number of occurances, like this

  lastname     USA         UK      Australia
  Smith        2           1       1
  Kim          2           0       0
  Sanchez      0           1       0

I tried the following which worked quite well,

print(dataset.groupby(['lastname','country']).size())

with the result

  lastname              
  Smith        USA         2
  Smith        UK          1
  Smith        Australia   1
  Kim          USA         2
  Sanchez      UK          1

This works quite well with this example, but since there are way more rows and countries, the first option would be much more readable.

0 Answers
Related