Using one dataframe to create groups on other dataframe then taking averages

user_12016989 0
Viewed 89

The premise of my question is I would like to use one dataframe (peergroups) to create groups of stocks that are peers of other stocks and then calculate averages on another dataframe (fun_data) but I do not know how to use one dataframe to create the groups by year and ticker and then apply the groups, find the average of multiple columns, and create new columns for those averages in another dataframe. Any help is appreciated. The data I have so far is below.

I start with two dataframes, one with fundamental data and one that shows peer groups of companies for each year

fun_data

import numpy as np
import pandas as pd
fun_data = {{'data': [12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 12/30/1983, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984, 1/3/1984],
             'ticker': ['AA', 'KO', 'AMB', 'AMX', 'AR', 'AS', 'BUD', 'CLF', 'CRS', 'DOC', 'EC', 'EFU', 'FTX', 'HM', 'RJR', 'AA', 'KO', 'AMB', 'AMX', 'AR', 'AS', 'BUD', 'CLF', 'CRS', 'DOC', 'EC', 'EFU', 'FTX', 'HM', 'RJR'],
             'mkt_cap': [10382076219, 28615981356, 89124668974, 96863568587, 69017311359, 71368368637, 36604633897, 91086629072, 87580223715, 70605054110, 93225158261, 91412455851, 76327466814, 60245266890, 33751408249, 92924687267, 97193082284, 43372080824, 94712408349, 60356743279, 32484886660, 18571138143, 64690517329, 24838868675, 23278782495, 34286838121, 46008417484, 24020283962, 3560654158, 79189294007],
             'pe_ratio': [15, 24, 15, 20, 22, 19, 16, 22, 18, 13, 18, 16, 14, 24, 15, 12, 18, 22, 16, 21, 20, 16, 24, 18, 15, 24, 24, 18, 13, 18],}
df1 = pd.DataFrame(data=fun_data)
df1

peergroups

import numpy as np
import pandas as pd
peergroup = {'year': [1983, 1983, 1983, 1983, 1983, 1984, 1984, 1984, 1984, 1984, 1983, 1983, 1983, 1984, 1984, 1984],
             'ticker': ['AA', 'AA', 'AA', 'AA', 'AA', 'AA', 'AA', 'AA', 'AA', 'AA', 'KO', 'KO', 'KO', 'KO', 'KO', 'KO'],
             'peer': [AMX, AS, CLF, CRS, EFU, HM, AMX, AR, EC, FTX, AMB, BUD, DOC, AMB, BUD, RJR]}
df2 = pd.DataFrame(data=peergroup)
df2

Once I have those dataframes, I imagine the code doing these steps (feel free to adjust if there is a better way to do this)

  1. Find the date and ticker from the fun_data dataframe (12/30/1983, AA)
  2. Find AA's peers for 1983 from the peergroup dataframe (AMX, AS, CLF, CRS, EFU)
  3. Find the mkt_cap and pe_ratio data for the peers on that date from the fun_data dataframe
  4. Calculate the average mkt_Cap and pe_ratio for AA's peers
  5. Create two columns for peer_avg_mkt_cap and peer_avg_pe_ratio and input the calculated values in those columns
  6. Iterate for all firms with peers for all dates in fun_data
  7. If no peers are found for that date, leave a 0 (will fill in with data from FF library)

Desired output

quantitative-finance pandas dataframe numpy python
1 Answers

I think you need merges. First to create the mean per groups, you can merge df1 on the column year created from the date and ticker, and in df2 year and peer. then groupby the tocker column from df2 in this merged dataframe:

df_ = (df1.assign(year=pd.to_datetime(df1['data']).dt.year)
          .merge(df2, 
                 left_on=['year','ticker'], right_on=['year','peer'], 
                 how='outer', suffixes=('_',''))
          .groupby(['year', 'ticker'])
          [['mkt_cap', 'pe_ratio']].mean()
          .add_prefix('avg_')
      )
print(df_)
              avg_mkt_cap  avg_pe_ratio
year ticker                            
1983 AA      8.766225e+10     19.000000
     KO      6.544479e+10     14.666667
1984 AA      4.338739e+10     18.400000
     KO      4.704417e+10     18.666667

Note I don't get the same value than you expected output, but I'm not sure how you got 19.3 for KO in 1983 knowing that in your df2, it is linked to three ticker that all have values under 16, so I think it is what you want.

Now you just need to merge it back to df1 again on year and ticker that are the index of df_ created just before, plus some fillna and drop the column year

df3 = (df1.assign(year=pd.to_datetime(df1['data']).dt.year)
          .merge(df_, 
                 left_on=['year','ticker'], 
                 right_index=True, 
                 how='left')
          .fillna(0)
          .drop('year',axis=1)
      )
print(df3)
          data ticker      mkt_cap  pe_ratio   avg_mkt_cap  avg_pe_ratio
0   12/30/1983     AA  10382076219        15  8.766225e+10     19.000000
1   12/30/1983     KO  28615981356        24  6.544479e+10     14.666667
2   12/30/1983    AMB  89124668974        15  0.000000e+00      0.000000
3   12/30/1983    AMX  96863568587        20  0.000000e+00      0.000000
4   12/30/1983     AR  69017311359        22  0.000000e+00      0.000000
5   12/30/1983     AS  71368368637        19  0.000000e+00      0.000000
6   12/30/1983    BUD  36604633897        16  0.000000e+00      0.000000
7   12/30/1983    CLF  91086629072        22  0.000000e+00      0.000000
8   12/30/1983    CRS  87580223715        18  0.000000e+00      0.000000
9   12/30/1983    DOC  70605054110        13  0.000000e+00      0.000000
10  12/30/1983     EC  93225158261        18  0.000000e+00      0.000000
11  12/30/1983    EFU  91412455851        16  0.000000e+00      0.000000
12  12/30/1983    FTX  76327466814        14  0.000000e+00      0.000000
13  12/30/1983     HM  60245266890        24  0.000000e+00      0.000000
14  12/30/1983    RJR  33751408249        15  0.000000e+00      0.000000
15    1/3/1984     AA  92924687267        12  4.338739e+10     18.400000
16    1/3/1984     KO  97193082284        18  4.704417e+10     18.666667
17    1/3/1984    AMB  43372080824        22  0.000000e+00      0.000000
18    1/3/1984    AMX  94712408349        16  0.000000e+00      0.000000
19    1/3/1984     AR  60356743279        21  0.000000e+00      0.000000
20    1/3/1984     AS  32484886660        20  0.000000e+00      0.000000
...
user_92747320
Related