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.