multiply and summing same named columns based on name in 2 different DF pandas python

Question

it's easy to multiply 2 columns, but some how my code returns NAN that i don't understand.

so i have these columns in df2 that have the same name also in df1. my goal was to multiply and then sum them together.

df1.columns=['ISIN', 'DSCD', 'GEOGN', 'SIC', 'GEOGC', 'COMMONSHARESOUTSTANDING',
       'year', 'date', 'month', 'Mcap', 'TotalAssets', 'NItoCommon',
       'NIbefEIPrefDiv', 'PrefDiv', 'NIbefPrefDiv', 'Sales',
       'GainLossAssetSale', 'PPT', 'LTDebt', 'CommonEquity', 'PrefStock',
       'OtherIncome', 'TotalLiabilities', 'PreTaxIncome', 'IncomeTaxes',
       'OtherTA', 'OtherLiabilities', 'CashSTInv', 'OtherCA', 'OtherCL',
       'TotalDiv', 'Country', 'Industry', 'IsSingleCountry', 'Mcap_w',
       'NItoCommon_w', 'NIbefEIPrefDiv_w', 'PrefDiv_w', 'NIbefPrefDiv_w',
       'Sales_w', 'GainLossAssetSale_w', 'PPT_w', 'LTDebt_w', 'CommonEquity_w',
       'PrefStock_w', 'OtherIncome_w', 'TotalLiabilities_w', 'PreTaxIncome_w',
       'IncomeTaxes_w', 'OtherTA_w', 'OtherLiabilities_w', 'CashSTInv_w',
       'OtherCA_w', 'OtherCL_w', 'TotalDiv_w', 'fair_value', 'month__1',
       'month__2', 'month__3', 'month__4', 'month__5', 'month__6', 'month__7',
       'month__8', 'month__9', 'month__10', 'month__11', 'month__12',
       'constant', 'V'

df2 has only one row while the other one is more than one row.

df2.columns = ['constant', 'TotalAssets', 'NItoCommon_w', 'NIbefEIPrefDiv_w',
       'NIbefPrefDiv_w', 'Sales_w', 'GainLossAssetSale_w', 'PPT_w', 'LTDebt_w',
       'CommonEquity_w', 'PrefStock_w', 'OtherIncome_w', 'TotalLiabilities_w',
       'PreTaxIncome_w', 'IncomeTaxes_w', 'OtherTA_w', 'OtherLiabilities_w',
       'CashSTInv_w', 'OtherCA_w', 'OtherCL_w', 'TotalDiv_w', 'month__1',
       'month__2', 'month__3', 'month__4', 'month__5', 'month__6', 'month__7',
       'month__8', 'month__9', 'month__10', 'month__11', 'month__12',
       'Country', 'predicted_Mcap']

i need to multiply and sum these columns:

'TotalAssets', 'NItoCommon_w', 'NIbefEIPrefDiv_w',
       'NIbefPrefDiv_w', 'Sales_w', 'GainLossAssetSale_w', 'PPT_w', 'LTDebt_w',
       'CommonEquity_w', 'PrefStock_w', 'OtherIncome_w', 'TotalLiabilities_w',
       'PreTaxIncome_w', 'IncomeTaxes_w', 'OtherTA_w', 'OtherLiabilities_w',
       'CashSTInv_w', 'OtherCA_w', 'OtherCL_w', 'TotalDiv_w'

my code:

#adding a column to df1 to save the result in it. 
    df1['V']=0
    for col in df2.iloc[:,1:20]:        
        df1['V']+=df1[col]*df2[col]

but the result is all NAN which is strange for me.

please provide your complete code and a reproducible example of the data. You shouldn't need to loop. Are you sure your indices are common? — mozway, Aug 31 '23 at 14:03
My guess is why your code is returning all NaN values is because you are trying to multiply columns that have NaN values. When you multiply a NaN value by any other value, the result will be NaN. — Devam Sanghvi, Aug 31 '23 at 14:25
@DevamSanghvi the indexes don't match. df2 has only one row while df1 has many rows — Mostafa Bouzari, Aug 31 '23 at 14:27
@Mostafa this is not a minimal reproducible example, please check [here](https://stackoverflow.com/q/20109391/16343464) how to do it. Ideally only a few **dummy** rows/cols and the **data** (not just the column names) — mozway, Aug 31 '23 at 14:38

score 0 · Answer 1 · answered Aug 31 '23 at 14:37

0

for col in co: df_Ger_2015_V['V']+=df_Ger_2015_V[col]*co[col].values[0]

answered Aug 31 '23 at 14:37

Mostafa Bouzari

9,207
3
16
26

multiply and summing same named columns based on name in 2 different DF pandas python

1 Answers1