Get percentage change in last 12(changeable) months for each id in pandas

user_15900640 0
Viewed 101

I have a dataset like this for each ID;

Months ID Salary
2020-12-01 1 5240.0
2020-11-01 1 4532.0
2020-10-01 1 4532.0
2020-09-01 1 4532.0
2020-08-01 1 4532.0
2020-07-01 1 4532.0
2020-06-01 1 4532.0
2020-05-01 1 4532.0
2020-04-01 1 4532.0
2020-03-01 1 4532.0
2020-02-01 1 3808.0
2020-01-01 1 3808.0
2019-12-01 1 3808.0
2019-11-01 1 3808.0
2019-10-01 1 3808.0
2019-09-01 1 3808.0
2019-08-01 1 3808.0
2019-07-01 1 3808.0
2019-06-01 1 3808.0
2019-05-01 1 3808.0
2019-04-01 1 3096.0
2019-03-01 1 3096.0
2019-02-01 1 3096.0
2019-01-01 1 3096.0
2018-12-01 1 3096.0
2018-11-01 1 3096.0
2018-10-01 1 3096.0
2018-09-01 1 2669.0
2018-08-01 1 2669.0
2018-07-01 1 2669.0
2020-12-01 2 1632.0
2020-11-01 2 1632.0
2020-10-01 2 1632.0
2020-09-01 2 1600.0

I want to find percentage of salary change in last month, in last 6 month and in last 12 month for each id. I used the code below;

    unique_all['SalaryDifferencewithLastMonth%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-1).fillna(0))
    unique_all['SalaryDifferenceinLast6Months%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-6).fillna(0))
    unique_all['SalaryDifferenceinLast12Months%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-12).fillna(0))

And i got that dataset;

Months ID Salary SalaryDifferencewithLastMonth% SalaryDifferenceinLast6Months% SalaryDifferenceinLast12Months%
2020-12-01 1 5240.0 0.156222 0.156222 0.376050
2020-11-01 1 4532.0 0.000000 0.000000 0.190126
2020-10-01 1 4532.0 0.000000 0.000000 0.190126
2020-09-01 1 4532.0 0.000000 0.000000 0.190126
2020-08-01 1 4532.0 0.000000 0.190126 0.190126
2020-07-01 1 4532.0 0.000000 0.190126 0.190126
2020-06-01 1 4532.0 0.000000 0.190126 0.190126
2020-05-01 1 4532.0 0.000000 0.190126 0.190126
2020-04-01 1 4532.0 0.000000 0.190126 0.463824
2020-03-01 1 4532.0 0.190126 0.190126 0.463824
2020-02-01 1 3808.0 0.000000 0.000000 0.229974
2020-01-01 1 3808.0 0.000000 0.000000 0.229974
2019-12-01 1 3808.0 0.000000 0.000000 0.229974
2019-11-01 1 3808.0 0.000000 0.000000 0.229974
2019-10-01 1 3808.0 0.000000 0.229974 0.229974
2019-09-01 1 3808.0 0.000000 0.229974 0.426752
2019-08-01 1 3808.0 0.000000 0.229974 0.426752
2019-07-01 1 3808.0 0.000000 0.229974 0.426752
2019-06-01 1 3808.0 0.000000 0.229974 0.000000
2019-05-01 1 3808.0 0.229974 0.229974 0.000000
2019-04-01 1 3096.0 0.000000 0.000000 0.000000
2019-03-01 1 3096.0 0.000000 0.159985 0.000000
2019-02-01 1 3096.0 0.000000 0.159985 0.000000
2019-01-01 1 3096.0 0.000000 0.159985 0.000000
2018-12-01 1 3096.0 0.000000 0.000000 0.000000
2018-11-01 1 3096.0 0.000000 0.000000 0.000000
2018-10-01 1 3096.0 0.159985 0.000000 0.000000
2018-09-01 1 2669.0 0.000000 0.000000 0.000000
2018-08-01 1 2669.0 0.000000 0.000000 0.000000
2018-07-01 1 2669.0 0.000000 0.000000 0.000000
2020-12-01 2 1632.0 0.00 0.0 0.0
2020-11-01 2 1632.0 0.00 0.0 0.0
2020-10-01 2 1632.0 0.02 0.0 0.0
2020-09-01 2 1600.0 0.00 0.0 0.0

It works fine for those with data but not good for remaining rows. If ID has no data for 12 months and there is a change in the last 6 months, it returns the value, but for the last 12 months, it returns 0. I want 12 months to cover 6 months. So if it can't go back 12 months, i want it to go to last month of the ID and find the change with that month's salary.

I want a dataset like this;

Months ID Salary SalaryDifferencewithLastMonth% SalaryDifferenceinLast6Months% SalaryDifferenceinLast12Months%
2020-12-01 1 5240.0 0.156222 0.156222 0.376050
2020-11-01 1 4532.0 0.000000 0.000000 0.190126
2020-10-01 1 4532.0 0.000000 0.000000 0.190126
2020-09-01 1 4532.0 0.000000 0.000000 0.190126
2020-08-01 1 4532.0 0.000000 0.190126 0.190126
2020-07-01 1 4532.0 0.000000 0.190126 0.190126
2020-06-01 1 4532.0 0.000000 0.190126 0.190126
2020-05-01 1 4532.0 0.000000 0.190126 0.190126
2020-04-01 1 4532.0 0.000000 0.190126 0.463824
2020-03-01 1 4532.0 0.190126 0.190126 0.463824
2020-02-01 1 3808.0 0.000000 0.000000 0.229974
2020-01-01 1 3808.0 0.000000 0.000000 0.229974
2019-12-01 1 3808.0 0.000000 0.000000 0.229974
2019-11-01 1 3808.0 0.000000 0.000000 0.229974
2019-10-01 1 3808.0 0.000000 0.229974 0.229974
2019-09-01 1 3808.0 0.000000 0.229974 0.426752
2019-08-01 1 3808.0 0.000000 0.229974 0.426752
2019-07-01 1 3808.0 0.000000 0.229974 0.426752
2019-06-01 1 3808.0 0.000000 0.229974 0.426752
2019-05-01 1 3808.0 0.229974 0.229974 0.426752
2019-04-01 1 3096.0 0.000000 0.000000 0.159985
2019-03-01 1 3096.0 0.000000 0.159985 0.159985
2019-02-01 1 3096.0 0.000000 0.159985 0.159985
2019-01-01 1 3096.0 0.000000 0.159985 0.159985
2018-12-01 1 3096.0 0.000000 0.159985 0.159985
2018-11-01 1 3096.0 0.000000 0.159985 0.159985
2018-10-01 1 3096.0 0.159985 0.159985 0.159985
2018-09-01 1 2669.0 0.000000 0.000000 0.000000
2018-08-01 1 2669.0 0.000000 0.000000 0.000000
2018-07-01 1 2669.0 0.000000 0.000000 0.000000
2020-12-01 2 1632.0 0.00 0.02 0.02
2020-11-01 2 1632.0 0.00 0.02 0.02
2020-10-01 2 1632.0 0.02 0.02 0.02
2020-09-01 2 1600.0 0.00 0.0 0.0

If there a way to do this? Thank you in advance.

pandas dataframe date python
1 Answers

There is probably a better solution, but for now I used a method like this.

    #Find the percentage changes, but it remains Nan for the last months.

    unique_all['SalaryDifferencewithLastMonth%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-1))
    unique_all['SalaryDifferenceinLast6Months%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-6))
    unique_all['SalaryDifferenceinLast12Months%'] = (unique_all.groupby(['ID'])['Salary'].pct_change(-12))

    #Find the salary of the latest month(at the bottom) for each id

    latest_salary = pd.DataFrame(unique_all.loc[unique_all.groupby('ID').Months.idxmin()][['ID','Salary']].reset_index(drop=True))
    latest_salary = latest_salary.rename(columns={'Salary':'latest_salary'})
    unique_all= unique_all.merge(latest_salary,on='ID',how='left')
    
    #Find the percentage change with the latest month of the id and assign to the Nan values

    unique_all['salary_diff2'] = ((unique_all['Salary'] - unique_all['latest_salary']) * 100 / unique_all['latest_salary']) / 100

    unique_all['SalaryDifferenceinLast12Months%'] = unique_all['SalaryDifferenceinLast12Months%'].fillna(unique_all['salary_diff2'].loc[unique_all['SalaryDifferenceinLast12Months%'].isnull()]) 
    unique_all['SalaryDifferenceinLast6Months%'] = unique_all['SalaryDifferenceinLast6Months%'].fillna(unique_all['salary_diff2'].loc[unique_all['SalaryDifferenceinLast6Months%'].isnull()]) 
    unique_all['SalaryDifferencewithLastMonth%'] = unique_all['SalaryDifferencewithLastMonth%'].fillna(unique_all['salary_diff2'].loc[unique_all['SalaryDifferencewithLastMonth%'].isnull()])
user_159006400
Related