Creating a new column based on several groupby conditions in python

Question

I have the following dataframe. I want to create another variable Bond that divides the Rate for Pool Collateral by Single Collateral for each country and for each year. How would you do that? For example, you would divide the rate in line 0 by the rate in line 4 or the rate in line 8 by the rate in line 7. For the countries that do not have an observation for Pool and Single Collateral, I want a missing value.

import pandas as pd

df={'Date': [2016, 2016, 2016, 2016, 2016, 2016, 2016, 2017, 2017, 2017, 2017, 2017, 2017],
   'Country':['DE', 'AT','GB', 'FR', 'DE', 'FR', 'AT', 'GB', 'GB', 'DE', 'AT', 'FR' , 'AT'],
  'Collateral':['Pool', 'Pool', 'Single', 'Single', 'Single', 'Pool', 'Single', 'Single', 'Pool', 'Pool', 'Pool', 'Pool', 'Single'],
  'Rate': [0.2, 0.3, 0.1, 0.4, 0.6, 0.002, 0.005, 0.2, 0.9, 0.5, 0.67, 0.08, 0.4]}
                  
df = pd.DataFrame(df) 

df

Some countries don't have both Pool and Single collateral for some years. For instance, DE and FR only have Pool for 2017 while GB only has Single for 2016. — DarrylG, Jul 25 '22 at 10:45
Yes that is correct. For those countries, I want a missing value created. I will specify it in the question. — Lisa, Jul 25 '22 at 10:56
Can we assume that for each country and year, pool and single collaterals do not occur more than once? — ko3, Jul 25 '22 at 11:10

MThiele · Accepted Answer · 2022-07-25T11:20:25.467

How about something like this?

df_pool = df.query("Collateral == 'Pool'")
df_single = df.query("Collateral == 'Single'")
df_merge = pd.merge_ordered(df_pool, df_single, on=["Country","Date"], suffixes=["_pool", "_single"])
df_merge["Bond"] = df_merge["Rate_pool"]/df_merge["Rate_single"]

Output:

   Date Country  Rate_pool  Rate_single       Bond
0  2016      AT      0.300        0.005  60.000000
1  2017      AT      0.670        0.400   1.675000
2  2016      DE      0.200        0.600   0.333333
3  2017      DE      0.500          NaN        NaN
4  2016      FR      0.002        0.400   0.005000
5  2017      FR      0.080          NaN        NaN
6  2016      GB        NaN        0.100        NaN
7  2017      GB      0.900        0.200   4.500000

DarrylG · Answer 2 · 2022-07-25T11:23:09.377

Using method from Set column name for apply result over groupby

df2 = (df.groupby(['Country', 'Date'], as_index = True)
       .apply(lambda grp: pd.Series({'Bond': grp[grp.Collateral=='Pool'].Rate.mean()/
                                              grp[grp.Collateral=='Single'].Rate.mean()})))
print(df2)

Output

                  Bond
Country Date           
AT      2016  60.000000
        2017   1.675000
DE      2016   0.333333
        2017        NaN
FR      2016   0.005000
        2017        NaN
GB      2016        NaN
        2017   4.500000

Creating a new column based on several groupby conditions in python

2 Answers2