Python Pandas DataFrame通过周一至周的每周定义将每日数据重新采样到一周吗? [英] Python Pandas DataFrame resample daily data to week by Mon-Sun weekly definition?
问题描述
import pandas as pd
import numpy as np
dates = pd.date_range('20141229',periods=14, name='Day')
df = pd.DataFrame({'Sum1': [1667, 1229, 1360, 9232, 8866, 4083, 3671, 10085, 10005, 8730, 10056, 10176, 3792, 3518],
'Sum2': [91, 75, 75, 254, 239, 108, 99, 259, 395, 355, 332, 386, 96, 111],
'Sum3': [365.95, 398.97, 285.12, 992.17, 1116.57, 512.11, 504.47, 1190.96, 1753.6, 1646.25, 1344.05, 1582.67, 560.95, 736.44],
'Sum4': [5, 5, 1, 5, 8, 8, 2, 10, 12, 16, 16, 6, 6, 3]},index=dates); print(df)
df
生成的外观像这样:
Sum1 Sum2 Sum3 Sum4
Day
2014-12-29 1667 91 365.95 5
2014-12-30 1229 75 398.97 5
2014-12-31 1360 75 285.12 1
2015-01-01 9232 254 992.17 5
2015-01-02 8866 239 1116.57 8
2015-01-03 4083 108 512.11 8
2015-01-04 3671 99 504.47 2
2015-01-05 10085 259 1190.96 10
2015-01-06 10005 395 1753.60 12
2015-01-07 8730 355 1646.25 16
2015-01-08 10056 332 1344.05 16
2015-01-09 10176 386 1582.67 6
2015-01-10 3792 96 560.95 6
2015-01-11 3518 111 736.44 3
我说重新采样 Dataframe
尝试将每日数据汇总成每周行:
Let's say I resample the Dataframe
to try and sum the daily data into weekly rows:
df_resampled = df.resample('W', how='sum', label='left'); print(df_resampled)
这将产生以下内容:
Sum1 Sum2 Sum3 Sum4
Day
2014-12-28 30108 941 4175.36 34
2015-01-04 56362 1934 8814.92 69
问题1 :我对一周的定义是周一至周日。由于我的数据从 2014-12-29
(星期一)开始,因此我希望我的日
label
也要在该天开始。如何将日
索引
标签
作为日期
Question 1: my definition of a week is Mon - Sun. Since my data starts on 2014-12-29
(a Monday), I want my Day
label
to also start on that day. How would I make the Day
index
label
be the date of every Monday instead of every Sunday?
所需的输出:
Sum1 Sum2 Sum3 Sum4
Day
2014-12-29 30108 941 4175.36 34
2015-01-05 56362 1934 8814.92 69
我对问题1尝试了什么?
我更改了'W'
到'W-MON'
,但是通过计算 2014-12-29
在 2014-12-22
行中,这不是我想要的:
I changed 'W'
to 'W-MON'
but it produced 3 rows by counting 2014-12-29
in 2014-12-22
row which is not what I want:
Sum1 Sum2 Sum3 Sum4
Day
2014-12-22 1667 91 365.95 5
2014-12-29 38526 1109 5000.37 39
2015-01-05 46277 1675 7623.96 59
问题2 :如何格式化天
索引
标签看起来像范围?例如:
Question 2: how would I format the Day
index
label to look like a range? Ex:
Sum1 Sum2 Sum3 Sum4
Day
2014-12-29 - 2015-01-04 30108 941 4175.36 34
2015-01-05 - 2015-01-11 56362 1934 8814.92 69
推荐答案
这可能有帮助。
import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(1, 1000, (100, 4)), columns='Sum1 Sum2 Sum3 Sum4'.split(), index=pd.date_range('2014-12-29', periods=100, freq='D'))
def func(group):
return pd.Series({'Sum1': group.Sum1.sum(), 'Sum2': group.Sum2.sum(),
'Sum3': group.Sum3.sum(), 'Sum4': group.Sum4.sum(), 'Day': group.index[1], 'Period': '{0} - {1}'.format(group.index[0].date(), group.index[-1].date())})
df.groupby(lambda idx: idx.week).apply(func)
Out[386]:
Day Period Sum1 Sum2 Sum3 Sum4
1 2014-12-30 2014-12-29 - 2015-01-04 3559 3692 3648 4086
2 2015-01-06 2015-01-05 - 2015-01-11 2990 3658 3348 3304
3 2015-01-13 2015-01-12 - 2015-01-18 3168 3720 3518 3273
4 2015-01-20 2015-01-19 - 2015-01-25 2275 4968 4095 2366
5 2015-01-27 2015-01-26 - 2015-02-01 4146 2167 3888 4576
.. ... ... ... ... ... ...
11 2015-03-10 2015-03-09 - 2015-03-15 4035 3518 2588 2714
12 2015-03-17 2015-03-16 - 2015-03-22 3399 3901 3430 2143
13 2015-03-24 2015-03-23 - 2015-03-29 3227 3308 3185 3814
14 2015-03-31 2015-03-30 - 2015-04-05 4278 3369 3623 4167
15 2015-04-07 2015-04-06 - 2015-04-07 1466 632 1136 1392
[15 rows x 6 columns]
这篇关于Python Pandas DataFrame通过周一至周的每周定义将每日数据重新采样到一周吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!