我有以下数据帧(示例(:
import pandas as pd
data = [['A', '2022-09-01', False, 2], ['A', '2022-09-02', False, 1], ['A', '2022-09-03', False, 1], ['A', '2022-09-04', True, 3],
['A', '2022-09-05', False, 3], ['A', '2022-09-06', False, 2], ['A', '2022-09-07', False, 1], ['A', '2022-09-07', False, 2],
['A', '2022-09-08', False, 4], ['A', '2022-09-09', False, 2],
['B', '2022-09-01', False, 2], ['B', '2022-09-02', False, 2], ['B', '2022-09-03', False, 4], ['B', '2022-09-04', False, 2],
['B', '2022-09-05', True, 2], ['B', '2022-09-06', False, 2], ['B', '2022-09-07', False, 1], ['B', '2022-09-08', False, 3],
['B', '2022-09-09', False, 3], ['B', '2022-09-10', False, 2]]
df = pd.DataFrame(data = data, columns = ['group', 'date', 'indicator', 'value'])
# Add diff_days which is difference in days with closest row with True condition per group
df['date'] = pd.to_datetime(df['date'])
df = (
pd.merge_asof(df.sort_values('date'),
df.loc[df['indicator'], ['group','date']].sort_values('date')
.assign(diff_days=lambda x: x['date']),
by='group', on='date', direction='nearest')
.assign(diff_days=lambda x: (x['date']-x['diff_days']).dt.days)
.sort_values(['group','date'])
.reset_index(drop=True)
)
group date indicator value diff_days
0 A 2022-09-01 False 2 -3
1 A 2022-09-02 False 1 -2
2 A 2022-09-03 False 1 -1
3 A 2022-09-04 True 3 0
4 A 2022-09-05 False 3 1
5 A 2022-09-06 False 2 2
6 A 2022-09-07 False 2 3
7 A 2022-09-07 False 1 3
8 A 2022-09-08 False 4 4
9 A 2022-09-09 False 2 5
10 B 2022-09-01 False 2 -4
11 B 2022-09-02 False 2 -3
12 B 2022-09-03 False 4 -2
13 B 2022-09-04 False 2 -1
14 B 2022-09-05 True 2 0
15 B 2022-09-06 False 2 1
16 B 2022-09-07 False 1 2
17 B 2022-09-08 False 3 3
18 B 2022-09-09 False 3 4
19 B 2022-09-10 False 2 5
我想添加一个名为";斜率";其返回对于条件为"n"的行的n天的斜率(在这种情况下n=3(;indicator=True";每组。以下是所需的输出:
data = [['A', '2022-09-01', False, 2, -3, -0.5], ['A', '2022-09-02', False, 1, -2, -0.5], ['A', '2022-09-03', False, 1, -1, -0.5], ['A', '2022-09-04', True, 3, 0, 0],
['A', '2022-09-05', False, 3, 1, -0.5], ['A', '2022-09-06', False, 2, 2, -0.5], ['A', '2022-09-07', False, 2, 3, -0.5], ['A', '2022-09-07', False, 1, 3, 0.5],
['A', '2022-09-08', False, 4, 4, 0.5], ['A', '2022-09-09', False, 2, 5, 0.5],
['B', '2022-09-01', False, 2, -4], ['B', '2022-09-02', False, 2, -3, 0], ['B', '2022-09-03', False, 4, -2, 0], ['B', '2022-09-04', False, 2, -1, 0],
['B', '2022-09-05', True, 2, 0, 0], ['B', '2022-09-06', False, 2, 1, 0.5], ['B', '2022-09-07', False, 1, 2, 0.5], ['B', '2022-09-08', False, 3, 3, 0.5],
['B', '2022-09-09', False, 3, 4, -1], ['B', '2022-09-10', False, 2, 5, -1]]
df_desired = pd.DataFrame(data = data, columns = ['group', 'date', 'indicator', 'value', 'diff_days', 'slope'])
group date indicator value diff_days slope
0 A 2022-09-01 False 2 -3 -0.5
1 A 2022-09-02 False 1 -2 -0.5
2 A 2022-09-03 False 1 -1 -0.5
3 A 2022-09-04 True 3 0 0.0
4 A 2022-09-05 False 3 1 -0.5
5 A 2022-09-06 False 2 2 -0.5
6 A 2022-09-07 False 2 3 -0.5
7 A 2022-09-07 False 1 3 0.5
8 A 2022-09-08 False 4 4 0.5
9 A 2022-09-09 False 2 5 0.5
10 B 2022-09-01 False 2 -4 NaN
11 B 2022-09-02 False 2 -3 0.0
12 B 2022-09-03 False 4 -2 0.0
13 B 2022-09-04 False 2 -1 0.0
14 B 2022-09-05 True 2 0 0.0
15 B 2022-09-06 False 2 1 0.5
16 B 2022-09-07 False 1 2 0.5
17 B 2022-09-08 False 3 3 0.5
18 B 2022-09-09 False 3 4 -1.0
19 B 2022-09-10 False 2 5 -1.0
让我们来解释组B的计算。斜率(使用"diff_days"作为x值(是针对n=3相对于指示符==True的行计算的,该行是数据帧中的第15行
- 对于第12、13、14行,斜率为:linregression([-3、-2、-1]、[2、4、2](=0
- 第11行是单独的,因为它不适合特定行的3天范围(指标==True(,这意味着:linregression([-4],[2](=NaN
- 对于第16、17、18行,斜率为:linregression([1,2,3],[2,1,3](=0.5
- 对于第19、20行,斜率为:linregression([4,5],[3,2](=-1.0
请注意:条件为(indicator==True(的行的斜率值应为0。
所以,我想知道是否有人知道如何使用pandas
计算n天相对于每组某一行的斜率?
我对你的例子有点困惑-10(你称之为第11行(中有错误吗?如果我理解正确,你想要的是在a(indicator
变为True或b(每3行后将每组计算为新组。可以这样做:
from scipy.stats import linregress
def count_every_n(grp, n):
return pd.Series([k // n for k in range(len(grp))])
def get_slope(grp):
return pd.Series(linregress(grp.diff_days, grp.value).slope, index=grp.index)
indicator_change = (df.indicator != df.indicator.shift()).cumsum()
every_n_within_groups = (df
.groupby(indicator_change, group_keys=False)
.apply(lambda g: count_every_n(g, n=3))
.reset_index(drop=True))
df['slope'] = (df
.groupby([indicator_change, every_n_within_groups])
.apply(get_slope)
.reset_index(drop=True)
.fillna(0))