Pandas GroupBy frequency of values

Question

I have this set of sample data

STATE   CAPSULES     LIQUID         TABLETS  
Alabama NaN          Prescription   OTC
Georgia Prescription NaN            OTC
Texas   OTC          OTC            NaN
Texas   Prescription NaN            NaN
Florida NaN          Prescription   OTC
Georgia OTC          Prescription   Prescription
Texas   Prescription NaN            OTC
Alabama NaN          OTC            OTC
Georgia OTC          NaN            NaN

I have tried multiple groupby configurations to get the following ideal result:

State   capsules_OTC    capsules_prescription   liquid_OTC  liquid_prescription tablets_OTC tablets_prescription
Alabama    0             0                         0              0               0           0
Florida    0             0                         0              0               0           0
Georgia    1             1                         1              1               1           1
Texas      1             2                         2              2               2           2

For example, tried this

df.groupby(['STATE','CAPSULES'])

to try and get at least the first column wrangled, no dice. Perhaps this not such an easy answer, but I figured I am missing something simple with groupby and perhaps count() or some other apply function?

Answer 1

Use pd.get_dummies with groupby and sum:

pd.get_dummies(df, columns=['CAPSULES', 'LIQUID', 'TABLETS'])\
  .groupby('STATE', as_index=False).sum()

Output:

     STATE  CAPSULES_OTC  CAPSULES_Prescription  LIQUID_OTC  LIQUID_Prescription  TABLETS_OTC  TABLETS_Prescription
0  Alabama             0                      0           1                    1            2                     0
1  Florida             0                      0           0                    1            1                     0
2  Georgia             2                      1           0                    1            1                     1
3    Texas             1                      2           1                    0            1                     0