Search code examples
pythonpandasdataframerow

Arithmetic operations on row level in pandas dataframe


I have a dataframe as below:

df:
    Heir_1      Heir_2          Amount_1        Amount_2
0   New         Argentina       251823845.90    225432949.80
1   New         Venice          219982836.00    183705325.60
2   New         Denmark         419848669.41    546624742.50
3   New         Russia          218120340.46    151480060.80
4   New         Global          4706066755.41   4432657926.66

The concept here is Global will always be sum of Argentina, Venice and Others. But Others row will never be mentioned. I want to maintain the same structure of dataframe since it has to go through further code.

So how can i create a separate row of Others in the table by using the formula : Others = Global - Argentina - Venice

Expected Dataframe:

df:
    Heir_1      Heir_2          Amount_1        Amount_2
0   New         Argentina       251823845.90    225432949.80
1   New         Venice          219982836.00    183705325.60
2   New         Denmark         419848669.41    546624742.50
3   New         Russia          218120340.46    151480060.80
4   New         Global          4706066755.41   4432657926.66
5   New         Others          4234260073.51   4023519651.25

Related issue:

Also there is one issue, it is not necessary that all those 3 rows will be present in all scenarios. There might be a day where either Argentina is absent, or Venice is absent or Global is absent.

There are 3^3 possibilities of scenarios in this case. I can use if statements in this case but not sure if that would be a good way considering all the coding


Solution

  • Try with this

    if df[df['Heir_2']=='Global'].shape[0]:
      df_special = df[df['animal'].isin(['Argentina','Venecia'])].sum()
      amount_1 = df[df['Heir_2']=='Global']['Amount_1'] - df_special['Amount_1']
      amount_2 = df[df['Heir_2']=='Global']['Amount_2'] - df_special['Amount_2']
      df.append({'Heir_1':'New','Heir_2':'Others','Amount_1':amount_1,'Amount_2':amount_2},ignore_index=True)