CODEWITHSUNDEEP
pythonpandas
J.RAM
J.RAM

Reputation: 15

Pandas Timegrouper on Dataframe using aggregate function count

I'm working on Timegrouper on Data Frame from Excel and trying to do a Pviot using Date as column header and Time as row and aggregate count on Y is "Barton LLC".

Data.xls 
X        Y               Z               D
740150  Barton LLC  B1-20000    2014-01-01 02:21:51
740150  Barton LLC  B1-50809    2014-01-01 02:21:51
740150  Barton LLC  B1-53102    2014-01-01 02:21:51
740150  Barton LLC  S2-16558    2014-01-02 21:21:01
740150  Barton LLC  B1-86481    2014-01-02 21:21:01
740150  Curlis L    S1-06532    2014-01-02 21:21:01
740150  Barton LLC  S1-47412    2014-01-02 21:21:01
740150  Barton LLC  B1-33364    2014-01-02 21:21:01
740150  Barton LLC  S1-93683    2014-02-07 04:34:50
740150  Barton LLC  S2-10342    2014-02-07 04:34:50

Tried using resample and pivot and timegrouper but got sequence of errors

import pandas as pd
import numpy as np
df = pd.read_excel("data.xlsx")
ndf = df[df['Type'].eq('df')].pivot_table(columns= ['Y'],values='Y',
index=pd.Grouper(key='D',freq='H'),aggfunc='count',fill_value=0)

Result 

         2014-01-01,2014-01-02,2014-02-07
 02:21    3,NaN,NaN              
 21:21    NaN,4,NaN
 04:34    NaN,NaN,2

Upvotes: 1

Views: 90

Answers (2)

jezrael
jezrael

Reputation: 862511

Use crosstab with strftime for convert datetimes to custom strings:

df.D = pd.to_datetime(df.D)

ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d')) 
print (ndf)
D      2014-01-01  2014-01-02  2014-02-07
H                                        
02:21           3           0           0
04:34           0           0           2
21:21           0           5           0

ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date) 
print (ndf)
D         2014-01-01  2014-01-02  2014-02-07
T                                           
02:21:51           3           0           0
04:34:50           0           0           2
21:21:01           0           5           0

Upvotes: 1

yatu
yatu

Reputation: 88226

You could split the datetime column in date and time and use pivot_table:

df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')

date       2014-01-01  2014-01-02  2014-02-07
time                                        
02:21:51         3.0         NaN         NaN
04:34:50         NaN         NaN         2.0
21:21:01         NaN         5.0         NaN

Note that you were missing one count for the date 2014-01-02 21:21:01

Upvotes: 3

Related Questions