CODEWITHSUNDEEP
pythonpython-3.xpandaspandas-groupby
Whysmerhill
Whysmerhill

Reputation: 231

Panda Group by time and count value of column

Let say I have an array with event and log time, like this:

Time             Event
01/01/2019 8h00  X
01/01/2019 8h10  Y
01/01/2019 9h10  X
02/01/2019 7h10  Z
02/01/2019 8h10  Y
02/01/2019 9h10  Y
...

I want to have an output like this:

01/01/2019 [(X,2), (Y,1)]
02/01/2019 [(Y, 2), (Z,1)]
...

For now I only succeed to group by time and count all event or list all unique events

data = pd.read_csv('my.csv')    
s1 = data['Time'].groupby(data['Time'].dt.floor('d')).size()    
s2 = data.groupby(data['Time'].dt.floor('d')['Event'].unique().reset_index()

s1 output:

01/01/2019 3
02/01/2019 3

s2 output:

01/01/2019 [X, Y]
02/01/2019 [Y, Z]

How can I achieve to group by time and count the number of each events ?

Upvotes: 1

Views: 44

Answers (1)

jezrael
jezrael

Reputation: 863741

Use custom lambda function with value_counts:

df = (data.groupby(data['Time'].dt.floor('d'))['Event']
          .apply(lambda x: list(x.value_counts().items()))
          .reset_index())
print (df)
        Time             Event
0 2019-01-01  [(X, 2), (Y, 1)]
1 2019-02-01  [(Y, 2), (Z, 1)]

Upvotes: 3

Related Questions