-2
df = pd.DataFrame({'Date':['1/1/2011', 1/1/2011', 1/1/2011', '11/2/2011', '11/2/2011', '13/2/2011'], 
                    'Site':[MUC. MUC, MUC, BER, BER, BER]
                   'Activity':[X, X, K, K, R, D]})

I'd like to have the following in a new data frame.

'Date'    'Site' 'X'    'K'  'R'  'D'
1/1/2011     MUC  2      1    0    0
11/2/2011    BER  0      1    1    0
13/2/2011    BER  0      0    0    1
General Grievance
  • 4,555
  • 31
  • 31
  • 45
amit singh
  • 197
  • 6

1 Answers1

0

You can do that with a combination of get_dummies and groupby.

df = pd.DataFrame({'Date':['1/1/2011', '1/1/2011', '1/1/2011', '11/2/2011', '11/2/2011', '13/2/2011'], 'Site':['MUC', 'MUC', 'MUC', 'BER', 'BER', 'BER'], 'Activity':['X', 'X', 'K', 'K', 'R', 'D']})

pd.get_dummies(df, columns=['Activity'], prefix='', prefix_sep='').groupby(['Date', 'Site']).sum().reset_index()

Result:

        Date Site  D  K  R  X
0   1/1/2011  MUC  0  1  0  2
1  11/2/2011  BER  0  1  1  0
2  13/2/2011  BER  1  0  0  0
Wouter
  • 3,201
  • 6
  • 17