5

I need some help with Pandas.

I have following dataframe:

df = pd.DataFrame({'1Country': ['FR', 'FR', 'GER','GER','IT','IT', 'FR','GER','IT'],
               '2City': ['Paris', 'Paris', 'Berlin', 'Berlin', 'Rome', 'Rome','Paris','Berlin','Rome'],
               'F1': ['A', 'B', 'C', 'B', 'B', 'C', 'A', 'B', 'C'],
               'F2': ['B', 'C', 'A', 'A', 'B', 'C', 'A', 'B', 'C'],
               'F3': ['C', 'A', 'B', 'C', 'C', 'C', 'A', 'B', 'C']})

screenshot

I am trying to do a groupby on first two columns 1Country and 2City and do value_counts on columns F1 and F2. So far I was only able to do groupby and value_counts on 1 column at a time with

df.groupby(['1Country','2City'])['F1'].apply(pd.Series.value_counts)

How can I do value_counts on multiple columns and get a datframe as a result?

Seanny123
  • 8,776
  • 13
  • 68
  • 124
amongo
  • 61
  • 1
  • 9

2 Answers2

8

You could use agg, something along these lines:

df.groupby(['1Country','2City']).agg({i:'value_counts' for i in df.columns[2:]})

               F1   F2   F3
FR  Paris  A  2.0  1.0  2.0
           B  1.0  1.0  NaN
           C  NaN  1.0  1.0
GER Berlin A  NaN  2.0  NaN
           B  2.0  1.0  2.0
           C  1.0  NaN  1.0
IT  Rome   B  1.0  1.0  NaN
           C  2.0  2.0  3.0
sacuL
  • 49,704
  • 8
  • 81
  • 106
  • This answer works even better since I have a lot of columns to work with and using dictionary can be a bit tedious. Thank you! – amongo Aug 24 '18 at 19:35
5

You can pass a dict to agg as follows:

df.groupby(['1Country', '2City']).agg({'F1': 'value_counts', 'F2': 'value_counts'})
Silenced Temporarily
  • 944
  • 9
  • 21