在数据帧上使用聚合函数coun

Question

我在Excel的数据框上开发Timegrouper，并尝试使用Date作为列标题，Time作为行，聚合计数Y是“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

尝试使用重采样和pivot和timegrouper，但出现了一系列错误

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) 

结果

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

Answer 1

使用^{}和^{}将datetime转换为自定义字符串：

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

Answer 2

可以拆分date和time中的datetime列并使用^{}：

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

请注意，日期2014-01-02 21:21:01缺少一个计数