我有以下数据帧(带有日期时间索引(:
col_a col_b col_c col_d col_e col_f col_g col_h fid
7/20/2017 10:00 0 18 45 17 19 2.777778 180 0.92 999000
7/20/2017 11:00 0.03 18 45 17 19 2.2222224 180 0.93 999000
7/20/2017 12:00 0.03 18 45 17 19 2.2222224 180 0.95 999000
7/20/2017 13:00 0.03 17 45 17 19 2.2222224 180 0.95 999000
7/20/2017 14:00 0.04 17 45 17 19 1.6666668 180 0.97 999000
7/20/2017 15:00 0.03 17 45 17 19 1.6666668 180 0.97 999000
7/20/2017 16:00 0.02 17 45 17 19 1.6666668 157.5 0.97 999000
7/20/2017 17:00 0.01 17 45 17 19 1.6666668 135 0.97 999000
7/20/2017 18:00 0.01 17 45 17 19 1.6666668 157.5 0.97 999000
7/20/2017 19:00 0.02 17 45 17 19 1.6666668 157.5 1 999000
7/20/2017 20:00 0.01 17 45 17 19 2.2222224 135 1 999000
7/20/2017 21:00 0.01 18 45 17 19 2.2222224 135 1 999000
7/20/2017 22:00 0.01 18 45 17 19 2.777778 157.5 0.98 999000
7/20/2017 23:00 0.03 19 45 17 19 2.777778 157.5 0.96 999000
7/21/2017 0:00 0.04 19 45 16 21 3.0555558 157.5 0.92 999000
7/21/2017 1:00 0.05 20 45 16 21 3.8888892 157.5 0.88 999000
7/21/2017 2:00 0.03 21 45 16 21 3.8888892 157.5 0.83 999000
7/21/2017 3:00 0.02 21 45 16 21 3.8888892 157.5 0.8 999000
7/21/2017 4:00 0.03 21 45 16 21 4.4444448 157.5 0.78 999000
7/21/2017 5:00 0.03 21 45 16 21 4.4444448 157.5 0.79 999000
7/21/2017 6:00 0.02 21 45 16 21 3.8888892 157.5 0.83 999000
7/21/2017 7:00 0.03 20 45 16 21 3.6111114 135 0.86 999000
7/21/2017 8:00 0.04 19 45 16 21 3.0555558 157.5 0.91 999000
7/21/2017 9:00 0.03 18 45 16 21 2.777778 157.5 0.92 999000
7/21/2017 10:00 0.03 18 45 16 21 2.777778 157.5 0.92 999000
7/21/2017 11:00 0.03 18 45 16 21 2.777778 157.5 0.92 999000
7/21/2017 12:00 0.02 17 45 16 21 2.777778 135 0.94 999000
7/21/2017 13:00 0.03 17 45 16 21 2.777778 135 0.95 999000
7/21/2017 14:00 0.03 17 45 16 21 2.777778 135 0.98 999000
7/21/2017 15:00 0.03 17 45 16 21 2.777778 157.5 0.97 999000
7/21/2017 16:00 0.04 17 45 16 21 2.777778 135 0.97 999000
7/21/2017 17:00 0.04 17 45 16 21 2.777778 135 0.98 999000
7/21/2017 18:00 0.04 17 45 16 21 2.777778 135 1 999000
7/21/2017 19:00 0.03 16 45 16 21 2.777778 135 1 999000
7/21/2017 20:00 0.03 17 45 16 21 3.0555558 135 1 999000
7/21/2017 21:00 0.03 17 45 16 21 3.0555558 135 1 999000
7/21/2017 22:00 0.03 17 45 16 21 3.0555558 135 0.99 999000
7/21/2017 23:00 0.03 17 45 16 21 3.0555558 157.5 0.97 999000
我想计算col_a的每日平均值,col_b... col_h。fid 列似乎包含数字,但它们实际上存储为字符串。对于该列,我只需要每天的唯一字符串。但是,当我这样做时:
df.resample('D').mean()
fid
列从最终输出中消失。如何在最终输出中获取它?
如果需要以不同的方式重新采样某些值(例如列fid
,因为文本列(可以使用Resampler.agg
bydict
,可以动态创建。
最后添加reindex_axis
以获取与输入df
中相同的列顺序。
#all columns without `fid` are aggregate by mean
d = {x:'mean' for x in df.columns.difference(['fid'])}
#added new item to dict - column fid is aggregate by first
d['fid'] = 'first'
print (d)
{'col_e': 'mean', 'col_c': 'mean', 'col_b': 'mean', 'col_f': 'mean',
'col_a': 'mean', 'col_d': 'mean', 'fid': 'first', 'col_h': 'mean', 'col_g': 'mean'}
df1 = df.resample('D').agg(d).reindex_axis(df.columns, axis=1)
print (df1)
col_a col_b col_c col_d col_e col_f col_g
2017-07-20 0.020000 17.500000 45 17 19 2.103175 162.321429
2017-07-21 0.031667 18.333333 45 16 21 3.206019 147.187500
col_h fid
2017-07-20 0.967143 999000
2017-07-21 0.921250 999000
如果仅按Resampler.mean
重新采样,则排除所有非数字列(类似于aggregation
(:
df1 = df.resample('D').mean()
print (df1)
col_a col_b col_c col_d col_e col_f col_g
2017-07-20 0.020000 17.500000 45.0 17.0 19.0 2.103175 162.321429
2017-07-21 0.031667 18.333333 45.0 16.0 21.0 3.206019 147.187500
col_h
2017-07-20 0.967143
2017-07-21 0.921250
另一种解决方案是Grouper
如果fid
中的数据每天相同,则使用:
df1 = df.groupby(['fid', pd.Grouper(freq='D')])
.mean()
.reset_index()
.reindex_axis(df.columns, axis=1)
print (df1)
col_a col_b col_c col_d col_e col_f col_g col_h
0 0.020000 17.500000 45.0 17.0 19.0 2.103175 162.321429 0.967143
1 0.031667 18.333333 45.0 16.0 21.0 3.206019 147.187500 0.921250
fid
0 999000
1 999000