'Create new column based on previous values from other column by Year and weighted
I have a set of 11 data frames on Python, Pandas, with salary information of a group of employees, for 11 Years, which I have concatenated, with the same column 'Salary weighted'.
['Year'] ['ID Person] ['Weighted Salary']
This column contains a Salary that is weighted 50/50 with the present and previous year, if it exists, or two years prior on the contrary. If no previous data exist, the Actual Salary and the weighted would be the same.
What I want to do is to create a new column with the Actual salary for every year and employee.
For this, I need to iter through the rows of the Salary Weighted, check if there's a previous salary for the specific person 1 or 2 years prior, and return the Actual Salary based on the previous values.
The formula for the Actual Salary would be:
df.Actual_Salary = 2 * df.Weighted current year - df.Weighted year-1
I know the Weighted values.
The condition I've created is:
if len(row.Salary_Today[((df.ID_Person== ID_Person)&(df.Year_int==Year-1))]) >= 1:
df.Actual_Salary = 2 * df.Weighted current year - df.Weighted year-1
elif len(row.Salary_Today[((df.ID_Person== ID_Person)&(df.Year_int==Year-2))]) >= 1:
df.Actual_Salary = 2 * df.Weighted current year - df.Weighted year-2
else : df.Actual_Salary = df.Weighted current year
My problem is that I don't know how to properly iterate through each value and check the previous year's information for that person in order to pass it through the conditions and calculate the new column... Any suggestions?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|