我想最好使用python将列中的现有值替换为同一列的平均值。我想把付款平均分配到从付款的第一个月到最后一个月的所有月份。平均每月付款应按cust_id和sub_id进行分配。
付款可能会跳过几个月,并且不相同。
我希望你能在这方面帮助我,因为我才刚刚开始学习python。
数据如下:
cust_id | sub_id | 日期付款 |
---|---|---|
1 | A | 12/1/20200 |
1 | A | 2/2/21200 |
1 | A | 2/3/21100 |
1 | A | 5/1/21200 |
1 | B | 1/2/2150 |
1 | B | 20 |
1 | B | 3/1/21>80 |
1 | B | 4/23>90 |
2 | C | 200 |
2 | C | 300 |
使用resample()
和transform()
函数只需几个步骤即可完成:
首先,我们将丢失的月份添加到原始表中,将所有日期值更改为该月的第一个,将同一个月的行与添加的原始付款值合并,并将0放在新行中的付款列中:
resampled_df = (df
.set_index('date')
.groupby(['cust_id', 'sub_id'])
.resample('MS')
.agg({'payment': sum})
.reset_index()
)
然后,我们计算每组所有月份的平均值,并将该平均值分配给组中的每一行,将结果分配给一个新列:
resampled_df['avg_monthly_payment'] = (resampled_df
.groupby(['cust_id', 'sub_id'])['payment']
.transform('mean')
)
正如评论中所指出的,您对cust_id=2和sub_id='C'的回答似乎与您的要求不一致,所以我采用后者。
首先,我们将日期聚合为最小值、最大值,并将付款聚合为总和:
df2 = df.groupby(['cust_id','sub_id']).agg({'date':[min,max], 'payment':sum})
df2.columns = df2.columns.get_level_values(1)
df2
我们得到
min max sum
cust_id sub_id
1 A 2020-12-01 2021-05-01 700
B 2021-01-02 2021-04-23 240
2 C 2021-01-04 2021-01-09 500
然后,我们为每一行创建一个从最小到最大的月度时间表。在这里,你可能需要稍微调整一下日期,才能把它们排好,我只是做了一些基本的工作来展示这个想法:
from datetime import timedelta
df2['schedule'] = df2.apply(lambda row: pd.date_range(row['min'],row['max'] + timedelta(days = 31), freq = '1M'),axis=1)
现在df2
看起来是这样的:
min max sum schedule
-------- ------------------- ------------------- ----- ---------------------------------------------------------------------------------------------------------
(1, 'A') 2020-12-01 00:00:00 2021-05-01 00:00:00 700 DatetimeIndex(['2020-12-31', '2021-01-31', '2021-02-28', '2021-03-31',
'2021-04-30', '2021-05-31'],
dtype='datetime64[ns]', freq='M')
(1, 'B') 2021-01-02 00:00:00 2021-04-23 00:00:00 240 DatetimeIndex(['2021-01-31', '2021-02-28', '2021-03-31', '2021-04-30'], dtype='datetime64[ns]', freq='M')
(2, 'C') 2021-01-04 00:00:00 2021-01-09 00:00:00 500 DatetimeIndex(['2021-01-31'], dtype='datetime64[ns]', freq='M')
现在我们explode
我们的"时间表"和平均分配付款,并对列名等进行一些清理:
df3 = df2.groupby(['cust_id','sub_id'], as_index = False).apply(lambda g: g.explode('schedule'))
(df3.groupby(['cust_id','sub_id'], as_index = False)
.apply(lambda g: g.assign(sum = g['sum']/len(g)))
.reset_index(drop = False)
.drop(columns = ['min','max','level_0'])
.rename(columns = {'sum':'payment'})
)
获取
cust_id sub_id payment schedule
-- --------- -------- --------- -------------------
0 1 A 116.667 2020-12-31 00:00:00
1 1 A 116.667 2021-01-31 00:00:00
2 1 A 116.667 2021-02-28 00:00:00
3 1 A 116.667 2021-03-31 00:00:00
4 1 A 116.667 2021-04-30 00:00:00
5 1 A 116.667 2021-05-31 00:00:00
6 1 B 60 2021-01-31 00:00:00
7 1 B 60 2021-02-28 00:00:00
8 1 B 60 2021-03-31 00:00:00
9 1 B 60 2021-04-30 00:00:00
10 2 C 500 2021-01-31 00:00:00