create new column based on weekly change, based on ID-CodePudding

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.

CodePudding user response：

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]