I have a pandas dataframe
df = pd.DataFrame({'Firm': ['Firm1','Firm1','Firm1','Firm1','Firm1','Firm1','Firm2','Firm2','Firm2','Firm2','Firm2','Firm2'],'Location' : ['Country1', 'Country1', 'Country1', 'Country2', 'Country2', 'Country2','Country1', 'Country1', 'Country1', 'Country2', 'Country2', 'Country2'], 'Currency' : ['Curr1', 'Curr2', 'Curr3', 'Curr1', 'Curr2', 'Curr3','Curr1', 'Curr2', 'Curr3', 'Curr1', 'Curr2', 'Curr3'], 'Value' : [100, 105, 110, 100, 95, 120, 95, 110, 115, 105, 120, 90] })
which looks like this:
df:
Firm Location Currency Value
0 Firm1 Country1 Curr1 100
1 Firm1 Country1 Curr2 105
2 Firm1 Country1 Curr3 110
3 Firm1 Country2 Curr1 100
4 Firm1 Country2 Curr2 95
5 Firm1 Country2 Curr3 120
6 Firm2 Country1 Curr1 95
7 Firm2 Country1 Curr2 110
8 Firm2 Country1 Curr3 115
9 Firm2 Country2 Curr1 105
10 Firm2 Country2 Curr2 120
11 Firm2 Country2 Curr3 90
Now I would like to calculate the difference between Curr3 and Curr2 (of column Value) for each Firm-Location group and change the value of Curr3 based on the outcome. The resulting df should then look like this:
Firm Location Currency Value
0 Firm1 Country1 Curr1 100
1 Firm1 Country1 Curr2 105
2 Firm1 Country1 Curr3 5
3 Firm1 Country2 Curr1 100
4 Firm1 Country2 Curr2 95
5 Firm1 Country2 Curr3 25
6 Firm2 Country1 Curr1 95
7 Firm2 Country1 Curr2 110
8 Firm2 Country1 Curr3 5
9 Firm2 Country2 Curr1 105
10 Firm2 Country2 Curr2 120
11 Firm2 Country2 Curr3 -30
I have tried using .groupby and .apply which gives me the results, however I would like to do the transformation in the original dataframe.
df2 = df.groupby(['Firm','Location']).apply(lambda g: g[g.Currency == 'Curr3'].Value.values[0] - g[g.Currency == 'Curr2'].Value.values[0])
df2:
Firm Location 0
Firm1 Country1 5
Firm1 Country2 25
Firm2 Country1 5
Firm2 Country2 -30
I cannot figure out how to do this inplace in the original df. I also tried the same this using .transform, however it creates an error:
df2 = df.groupby(['Firm','Location']).transform(lambda g: g[g.Currency == 'Curr3'].Value.values[0] - g[g.Currency == 'Curr2'].Value.values[0])
AttributeError: ("'Series' object has no attribute 'Currency'", 'occurred at index Currency')
---- Update based on Erfan's solution:
newvals = (
df.where(df['Currency'].isin(['Curr2', 'Curr3']))
.groupby(['Firm', 'Location'])['Value'].diff()
)
df['Value'] = newvals.fillna(df['Value'])
If df looks like this however (Currency not sorted), the solution no longer works (as diff() only calculates the difference to the previous value
Firm Location Currency Value
0 Firm1 Country1 Curr2 100
1 Firm1 Country1 Curr1 105
2 Firm1 Country1 Curr3 110
3 Firm1 Country2 Curr3 100
4 Firm1 Country2 Curr2 95
5 Firm1 Country2 Curr1 120
6 Firm2 Country1 Curr1 95
7 Firm2 Country1 Curr2 110
8 Firm2 Country1 Curr3 115
9 Firm2 Country2 Curr2 105
10 Firm2 Country2 Curr3 120
11 Firm2 Country2 Curr1 90
-> result:
Firm Location Currency Value
0 Firm1 Country1 Curr2 100.0
1 Firm1 Country1 Curr1 105.0
2 Firm1 Country1 Curr3 10.0
3 Firm1 Country2 Curr3 100.0
4 Firm1 Country2 Curr2 -5.0
5 Firm1 Country2 Curr1 120.0
6 Firm2 Country1 Curr1 95.0
7 Firm2 Country1 Curr2 110.0
8 Firm2 Country1 Curr3 5.0
9 Firm2 Country2 Curr2 105.0
10 Firm2 Country2 Curr3 15.0
11 Firm2 Country2 Curr1 90.0
Now, it is no longer the case that each time the difference between Curr3 and Curr 2 is calculated and replaces the value for Curr3.