import datetimeimport pandas as pdpd.DataFrame({'date': {0: datetime.date(2020, 8, 15), 1: datetime.date(2020, 8, 16), 2: datetime.date(2020, 8, 16), 3: datetime.date(2020, 8, 17), 4: datetime.date(2020, 8, 17), 5: datetime.date(2020, 8, 18), 6: datetime.date(2020, 8, 19), 7: datetime.date(2020, 8, 19)}, 'sign_change': {0: 0, 1: 0, 2: 0, 3: 1, 4: 1, 5: 0, 6: 1, 7: 1}, 'distance (desired_output)': {0: 2, 1: 1, 2: 1, 3: 0, 4: 0, 5: 1, 6: 0, 7: 0}}) date sign_change distance (desired_output)0 2020-08-15 0 21 2020-08-16 0 12 2020-08-16 0 13 2020-08-17 1 04 2020-08-17 1 05 2020-08-18 0 16 2020-08-19 1 07 2020-08-19 1 0對于每一行，我想找到到最近的行的距離（以天為單位），其中sign_change == 1。我已在上面的數據框中手動輸入了所需的輸出。
2 回答
回首憶惘然
TA貢獻1847條經驗 獲得超11個贊
我們來嘗試一下廣播：
s = df.sign_change!=1
offset = (np.abs(df.loc[s,'date'].values[None,:] - df.loc[~s,['date']].values).min(0)
/pd.to_timedelta('1D')
)
df['distance'] = 0
df.loc[s,'distance'] = offset
輸出：
date sign_change distance (desired_output) distance
0 2020-08-15 0 2 2.0
1 2020-08-16 0 1 1.0
2 2020-08-16 0 1 1.0
3 2020-08-17 1 0 0.0
4 2020-08-17 1 0 0.0
5 2020-08-18 0 1 1.0
6 2020-08-19 1 0 0.0
7 2020-08-19 1 0 0.0
繁星coding
TA貢獻1797條經驗 獲得超4個贊
您可以使用where,bfill()和ffill()。本質上，.where符號是1，您返回日期，否則返回NaN。從那里您可以bfill或向后填寫該日期back到下一個日期1；您可以ffill將該日期填寫forward到下一個日期1。fill然后取該日期和該日期的差值。最后，.fillna(0)是數據幀中的最后一個值。
解決方案#1 - 只期待最近的日期（有關整體最近的日期，請參閱解決方案#2）：
df['distance (desired_output)'] = ((df['date'].where(df['sign_change'] == 1).bfill()
- df['date']).dt.days).fillna(0)
df
Out[1]:
date sign_change distance (desired_output)
0 2020-08-15 0 2.0
1 2020-08-16 0 1.0
2 2020-08-16 0 1.0
3 2020-08-17 1 0.0
4 2020-08-17 1 0.0
5 2020-08-18 0 1.0
6 2020-08-19 1 0.0
7 2020-08-19 0 0.0
解決方案#2（此解決方案ffill()與bfill()系列進行比較，并返回最接近日期的最小天數或天數，無論之前還是之后。
import datetime
import pandas as pd
df = pd.DataFrame({'date': {0: datetime.date(2020, 8, 15),
1: datetime.date(2020, 8, 16),
2: datetime.date(2020, 8, 16),
3: datetime.date(2020, 8, 17),
4: datetime.date(2020, 8, 17),
5: datetime.date(2020, 8, 18),
6: datetime.date(2020, 8, 19),
7: datetime.date(2020, 8, 19),
8: datetime.date(2020, 8, 20),
9: datetime.date(2020, 8, 21)},
'sign_change': {0: 0, 1: 0, 2: 0, 3: 1, 4: 1, 5: 0, 6: 1, 7: 1, 8: 0, 9: 0},
'distance (desired_output)': {0: 2, 1: 1, 2: 1, 3: 0, 4: 0, 5: 1, 6: 0, 7: 0}})
df['date'] = pd.to_datetime(df['date'])
s = (df['date'].where(df['sign_change'] == 1))
b = (s.bfill() - df['date']).dt.days
f = (s.ffill() - df['date']).dt.days.abs()
df['distance (desired_output)'] = np.where((b <= f) | (b.notnull()), b, f)
df
Out[2]:
date sign_change distance (desired_output)
0 2020-08-15 0 2.0
1 2020-08-16 0 1.0
2 2020-08-16 0 1.0
3 2020-08-17 1 0.0
4 2020-08-17 1 0.0
5 2020-08-18 0 1.0
6 2020-08-19 1 0.0
7 2020-08-19 1 0.0
8 2020-08-20 0 1.0
9 2020-08-21 0 2.0
添加回答
舉報
0/150
提交
取消