df=pd.read_csv('https://raw.githubusercontent.com/amanaroratc/hello-world/master/test_df.csv')
id rank date
1991513 FCWFKZVFAHFK7WP4 32 2021-06-01
1991514 FCWEUHFSM2BSQY2N 33 2021-06-01
1991515 FCWFV6T2GGPM8T2P 34 2021-06-01
1991516 FCWEQ8B4QDJJUNEH 35 2021-06-01
1991517 FCWFAUSPJFGDUBRG 36 2021-06-01
I have the above data for 1 month and I want to create a new column delta_rank_7 which tells me the change in rank in last 7 days for each id (NaNs for 2021-06-01 to 2021-06-07)
I can do something like mentioned here Calculating difference between two rows in Python / Pandas
df.set_index('date').diff(periods=7)
but I have multiple entries for each date and I want to do this for each id.
If there are duplicated id use:
df = df.set_index('date')
df['delta_rank_7'] = df.groupby('id')['rank'].diff(periods=7)
If need differencies by 7 days use DataFrameGroupBy.shift and subtract:
file = 'https://raw.githubusercontent.com/amanaroratc/hello-world/master/test_df.csv'
df=pd.read_csv(file, parse_dates=['date'])
df = df.sort_values(['id','date'])
df = df.merge((df.set_index(['id','date'])['rank']
.sub(df.set_index('date').groupby('id')['rank'].shift(7, freq='d'))
.reset_index(name='delta_rank_7'))
)
print (df)
id rank date delta_rank_7
0 CBKFGPBZMG48K5SF 2 2021-06-15 NaN
1 CBKFGPBZMG48K5SF 19 2021-06-19 NaN
2 CBKFGPBZMG48K5SF 2 2021-06-21 NaN
3 CBKFGPBZMG48K5SF 2 2021-06-22 0.0
4 CBKFGPBZMG48K5SF 48 2021-06-24 NaN
... ... ... ...
10010 FRNEUJZRVQGT94SP 112 2021-06-23 38.0
10011 FRNEUJZRVQGT94SP 109 2021-06-24 35.0
10012 FRNEUJZRVQGT94SP 68 2021-06-27 -73.0
10013 FRNEUJZRVQGT94SP 85 2021-06-28 NaN
10014 FRNEUJZRVQGT94SP 133 2021-06-30 21.0
[10015 rows x 4 columns]