How to divide multiple columns based on three conditions

user_15268135 0
Viewed 53

This is my dataset where I have different countries, different models for the different countries, years and the price and volume.

  data_dic = {
    "Country" : [1,1,1,1,2,2,2,2],
    "Model" : ["A","B","B","A","A","B","B","A"],
    "Year": [2005,2005,2020,2020,2005,2005,2020,2020],
    "Price" : [100,172,852,953,350,452,658,896],
    "Volume" : [4,8,9,10,12,6,8,9]
}

    Country Model   Year    Price   Volume
0   1   A   2005    100 4
4   2   A   2005    350 12
3   1   A   2020    953 10
7   2   A   2020    896 9
1   1   B   2005    172 8
5   2   B   2005    452 6
2   1   B   2020    852 9
6   2   B   2020    658 8

I would like to obtain the following where 1) column "Division_Price" is the division of price for Country 1 of Model A between the year 2005 and 2020 and 2) column "Division_Volume" is the division in volume for Country 1 of Model A between the year 2005 and 2020.

data_dic2 = {
    "Country" : [1,1,1,1,2,2,2,2],
    "Model" : ["A","B","B","A","A","B","B","A"],
    "Year": [2005,2005,2020,2020,2005,2005,2020,2020],
    "Price" : [100,172,852,953,350,452,658,896],
    "Volume" : [4,8,9,10,12,6,8,9],
    "Division_Price": [0.953,4.95,4.95,0.953,2.56,1.45,1.45,2.56],
    "Division_Volume": [2.5,1.125,1.125,2.5,1,1.33,1.33,1],
}
print(data_dic2)



Country Model   Year    Price   Volume  Division_Price  Division_Volume
0   1   A   2005    100 4   0.953   2.500
4   2   A   2005    350 12  2.560   1.000
3   1   A   2020    953 10  0.953   2.500
7   2   A   2020    896 9   2.560   1.000
1   1   B   2005    172 8   4.950   1.125
5   2   B   2005    452 6   1.450   1.330
2   1   B   2020    852 9   4.950   1.125
6   2   B   2020    658 8   1.450   1.330

My whole dataset has up to 50 countries and I have up to 10 models with years ranging 1990 to 2030. I am still unsure how to account for the multiple conditions of three columns so that I can divide automatically the column Price and Volume based on the three conditions (i.e., Country, Year and Models)?

Thanks !

conditional-statements dataframe if-statement python
1 Answers

You can try the following, using df.pivot, df.stack() and df.merge:

>>> df2 = ( df.pivot(['Year'], columns=['Model', 'Country'], values=['Price', 'Volume'])
              .diff().bfill(downcast='infer').abs().stack().stack()
              .sort_index(level=-1).add_prefix('Difference_')
          )
>>> df2
                    Difference_Price  Difference_Volume
Year Country Model                                     
2005 1       A                   853                  6
     2       A                   546                  3
2020 1       A                   853                  6
     2       A                   546                  3
2005 1       B                   680                  1
     2       B                   206                  2
2020 1       B                   680                  1
     2       B                   206                  2

>>> df.merge(df2, on=['Country', 'Model', 'Year'], how='right')

   Country Model  Year  Price  Volume  Difference_Price  Difference_Volume
0        1     A  2005    100       4               853                  6
1        2     A  2005    350      12               546                  3
2        1     A  2020    953      10               853                  6
3        2     A  2020    896       9               546                  3
4        1     B  2005    172       8               680                  1
5        2     B  2005    452       6               206                  2
6        1     B  2020    852       9               680                  1
7        2     B  2020    658       8               206                  2

EDIT:

For your new dataframe, I think the 0.953 would be 9.530, if so, you can use pct_change and add 1:

>>> df2 = ( df.pivot(['Year'], columns=['Model', 'Country'], values=['Price', 'Volume'])
              .pct_change(1).add(1).bfill(downcast='infer').abs().stack().stack()
              .sort_index(level=-1).add_prefix('Division_').round(3)
          )
>>> df2
                      Division_Price    Division_Volume
Year Country Model                                     
2005 1       A                 9.530              2.500
     2       A                 2.560              0.750
2020 1       A                 9.530              2.500
     2       A                 2.560              0.750
2005 1       B                 4.953              1.125
     2       B                 1.456              1.333
2020 1       B                 4.953              1.125
     2       B                 1.456              1.333

>>> df.merge(df2, on=['Country', 'Model', 'Year'], how='right')
 
   Country Model  Year  Price  Volume    Division_Price    Division_Volume
0        1     A  2005    100       4             9.530              2.500
1        2     A  2005    350      12             2.560              0.750
2        1     A  2020    953      10             9.530              2.500
3        2     A  2020    896       9             2.560              0.750
4        1     B  2005    172       8             4.953              1.125
5        2     B  2005    452       6             1.456              1.333
6        1     B  2020    852       9             4.953              1.125
7        2     B  2020    658       8             1.456              1.333
user_54317910
Related