CODEWITHSUNDEEP
pythonpandas
pema
pema

Reputation: 43

How to Resample and have value counts with value as new column header and counts as its value

After resampling i want the unique values to be a new column header and its count as the value.

i have tried 

new_df.set_index('time').resample('60T').apply(lambda x: list(x))

        time            camera_id
0   2019-05-06 12:00:00 [cam02]
1   2019-05-06 13:00:00 []
2   2019-05-06 14:00:00 [cam01, cam01, cam01, cam02, cam02, cam02, cam...
3   2019-05-06 15:00:00 [cam02, cam02, cam02, cam02, cam02, cam02, cam...

i want it like this 

    time                cam01  cam02  cam03
0   2019-05-06 12:00:00 0         1     0
1   2019-05-06 13:00:00 0         0     0
2   2019-05-06 14:00:00 0         10    0
3   2019-05-06 15:00:00 1         5     1

Upvotes: 3

Views: 872

Answers (1)

jezrael
jezrael

Reputation: 862761

I believe you need DataFrame.groupby before resample with Resampler.size and reshape by Series.unstack:

df = (new_df.set_index('time')
            .groupby('camera_id')
            .resample('60T')
            .size()
            .unstack(0, fill_value=0))
print (df)
camera_id            cam01  cam02  cam03
time                                    
2019-05-06 12:00:00      0      1      0
2019-05-06 13:00:00      0      0      0
2019-05-06 14:00:00      0     10      0
2019-05-06 15:00:00      1      5      1

Upvotes: 2

Related Questions