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.
Advertisement
Answer
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]