0

So let's say I have this dataframe

data = [['MS', 'Boston', 'emergency', 'hname11'], ['MS', 'Boston', 'emergency', 'hname47'], ['MS', 'Boston', 'normal', 'hname72'], ['MS', 'Cambridge', 'emergency', 'hname32'], ['FL', 'Miami', 'normal', 'hname67'], ['CA', 'Los Angeles', 'emergency', 'hname91']]
df = pd.DataFrame(data, columns = ['state', 'city', 'status', 'name'])
df
state   city        status      name
0   MS  Boston      emergency   hname11
1   MS  Boston      emergency   hname47
2   MS  Boston      normal      hname72
3   MS  Cambridge   emergency   hname32
4   FL  Miami       normal      hname67
5   CA  Los Angeles emergency   hname91

So I need to count the emergency and normal values for each city and put them as a column, like this

state    city          emergency_count    normal_count
MS       Boston        2                  1
MS       Cambridge     1                  0
FL       Miami         0                  1
CA       Los Angeles   1                  0

My goal is to have just one descriptive row for each city and not several rows with splitted information. So so far I've been using groupby and I get almost what I need

df.groupby(['state', 'city'])['status'].value_counts().reset_index(name='total')
state   city        status      total
0   CA  Los Angeles emergency   1
1   FL  Miami       normal      1
2   MS  Boston      emergency   2
3   MS  Boston      normal      1
4   MS  Cambridge   emergency   1

But I'm unable to create the columns with the sum values of the emergency and normal statuses for each city.

Paulo Madroñero
  • 320
  • 4
  • 14
  • I accepted an answer, but I'd like to share this link shared by @jezrael that was actually quite useful https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe – Paulo Madroñero Apr 16 '20 at 21:21

1 Answers1

0

Please groupby city and state, value_counts status and unstack. Then rename columns

df2=df.groupby(['state','city'])['status'].value_counts().unstack().reset_index().fillna(0)

df2.columns=['state','city','emergency_count','normal_count']
df2

Output

enter image description here

wwnde
  • 26,119
  • 6
  • 18
  • 32