% Increase value in number record

user_12681165 0
Viewed 52

my dataset

name date        record   
A    2018-09-18      95       
A    2018-10-11     104      
A    2018-10-30     230       
A    2018-11-23     124       
B    2020-01-24      95       
B    2020-02-11     167       
B    2020-03-07      78

As you can see, there are several records by name and date.

Compared to the previous record, I would like to see the record that rose the most.

output what I want

name record_before_date record_before record_increase_date record_increase increase_rate
A            2018-10-11           104           2018-10-30             230        121.25
B            2020-01-24            95           2020-02-11             167         75.79

I`m not comparing the lowest to the highest, but I want to check the record with the highest ascent rate when the next record comes, and the rate of ascent.

increase rate formula = (record_increase - record_before) / record_before * 100

Any help would be appreciated. thanks for reading.

pandas compare numpy python
1 Answers

Use:

#get percento change per groups
s = df.groupby("name")["record"].pct_change()
#get row with maximal percent change
df1 = df.loc[s.groupby(df['name']).idxmax()].add_suffix('_increase')
#get row with previous maximal percent change
df2 = (df.loc[s.groupby(df['name'])
         .apply(lambda x: x.shift(-1).idxmax())].add_suffix('_before'))
#join together
df = pd.concat([df2.set_index('name_before'), 
                df1.set_index('name_increase')], axis=1).rename_axis('name').reset_index()
#apply formula
df['increase_rate'] = (df['record_increase'].sub(df['record_before'])
                                            .div(df['record_before'])
                                            .mul(100))
print (df)
  name date_before  record_before date_increase  record_increase  \
0    A  2018-10-11            104    2018-10-30              230   
1    B  2020-01-24             95    2020-02-11              167   

   increase_rate  
0     121.153846  
1      75.789474
user_29010020
Related