我有一个带有很多列的pandas数据框,其中一些在周末具有价值.
我现在正尝试删除所有周末行,但需要将我删除的值添加到下周一的相应行中.
Thu: 4
Fri: 5
Sat: 2
Sun: 1
Mon: 4
Tue: 3
需要成为
Thu: 4
Fri: 5
Mon: 7
Tue: 3
我已经想出了如何仅对工作日进行切片(使用df.index.dayofweek),但是在进行此操作之前无法想到一种巧妙的聚合方法.
这是一些虚拟代码开始：
index = pd.date_range(datetime.datetime.now().date() -
datetime.timedelta(20),
periods = 20,
freq = 'D')
df = pd.DataFrame({
'Val_1': np.random.rand(20),
'Val_2': np.random.rand(20),
'Val_3': np.random.rand(20)
},
index = index)
df['Weekday'] = df.index.dayofweek
任何帮助,将不胜感激！
解决方法:
设定
我包括了一个随机种子
np.random.seed([3, 1415])
index = pd.date_range(datetime.datetime.now().date() -
datetime.timedelta(20),
periods = 20,
freq = 'D')
df = pd.DataFrame({
'Val_1': np.random.rand(20),
'Val_2': np.random.rand(20),
'Val_3': np.random.rand(20)
},
index = index)
df['day_name'] = df.index.day_name()
df.head(6)
Val_1 Val_2 Val_3 day_name
2018-07-18 0.444939 0.278735 0.651676 Wednesday
2018-07-19 0.407554 0.609862 0.136097 Thursday
2018-07-20 0.460148 0.085823 0.544838 Friday
2018-07-21 0.465239 0.836997 0.035073 Saturday
2018-07-22 0.462691 0.739635 0.275079 Sunday
2018-07-23 0.016545 0.866059 0.706685 Monday
解
我用随后的星期一(星期六和星期日)填写一系列日期.可以按操作分组使用.
weekdays = df.index.to_series().mask(df.index.dayofweek >= 5).bfill()
d_ = df.groupby(weekdays).sum()
d_
Val_1 Val_2 Val_3
2018-07-18 0.444939 0.278735 0.651676
2018-07-19 0.407554 0.609862 0.136097
2018-07-20 0.460148 0.085823 0.544838
2018-07-23 0.944475 2.442691 1.016837
2018-07-24 0.850445 0.691271 0.713614
2018-07-25 0.817744 0.377185 0.776050
2018-07-26 0.777962 0.225146 0.542329
2018-07-27 0.757983 0.435280 0.836541
2018-07-30 2.645824 2.198333 1.375860
2018-07-31 0.926879 0.018688 0.746060
2018-08-01 0.721535 0.700566 0.373741
2018-08-02 0.117642 0.900749 0.603536
2018-08-03 0.145906 0.764869 0.775801
2018-08-06 0.738110 1.580137 1.266593
相比
df.join(d_, rsuffix='_')
Val_1 Val_2 Val_3 day_name Val_1_ Val_2_ Val_3_
2018-07-18 0.444939 0.278735 0.651676 Wednesday 0.444939 0.278735 0.651676
2018-07-19 0.407554 0.609862 0.136097 Thursday 0.407554 0.609862 0.136097
2018-07-20 0.460148 0.085823 0.544838 Friday 0.460148 0.085823 0.544838
2018-07-21 0.465239 0.836997 0.035073 Saturday NaN NaN NaN
2018-07-22 0.462691 0.739635 0.275079 Sunday NaN NaN NaN
2018-07-23 0.016545 0.866059 0.706685 Monday 0.944475 2.442691 1.016837
2018-07-24 0.850445 0.691271 0.713614 Tuesday 0.850445 0.691271 0.713614
2018-07-25 0.817744 0.377185 0.776050 Wednesday 0.817744 0.377185 0.776050
2018-07-26 0.777962 0.225146 0.542329 Thursday 0.777962 0.225146 0.542329
2018-07-27 0.757983 0.435280 0.836541 Friday 0.757983 0.435280 0.836541
2018-07-28 0.934829 0.700900 0.538186 Saturday NaN NaN NaN
2018-07-29 0.831104 0.700946 0.185523 Sunday NaN NaN NaN
2018-07-30 0.879891 0.796487 0.652151 Monday 2.645824 2.198333 1.375860
2018-07-31 0.926879 0.018688 0.746060 Tuesday 0.926879 0.018688 0.746060
2018-08-01 0.721535 0.700566 0.373741 Wednesday 0.721535 0.700566 0.373741
2018-08-02 0.117642 0.900749 0.603536 Thursday 0.117642 0.900749 0.603536
2018-08-03 0.145906 0.764869 0.775801 Friday 0.145906 0.764869 0.775801
2018-08-04 0.199844 0.253200 0.091238 Saturday NaN NaN NaN
2018-08-05 0.437564 0.548054 0.504035 Sunday NaN NaN NaN
2018-08-06 0.100702 0.778883 0.671320 Monday 0.738110 1.580137 1.266593