Iterate through several lists and get a new list as a result-CodePudding

I have two DataFrames that look like this:

dfH
  TICKER  Qty     PPC        Date      PxQ     PPerc
0      C    6  4185.0  2021-11-13  25110.0  0.097416
1   AAPL   20  3058.0  2021-11-13  61160.0  0.237274
2    JPM    3  5915.0  2021-11-13  17745.0  0.068843
3     KO   15  2481.0  2021-11-13  37215.0  0.144378
4   MSFT   10  5825.6  2021-11-13  58256.0  0.226008
5     PG    5  6280.0  2021-11-13  31400.0  0.121818
6    WMT    5  5375.0  2021-11-13  26875.0  0.104263

dfMerged
Date,C,AAPL,JPM,KO,MSFT,PG,WMT
2020-11-10,2380.000,1759.000,3480.000,1601.000,3189.500,4269.000,3665.000
2020-11-11,2475.000,1798.000,3500.000,1626.000,3286.000,4352.000,3780.000
2020-11-12,2409.000,1765.000,3392.000,1590.000,3208.000,4305.000,3687.000
2020-11-13,2425.000,1770.000,3400.000,1590.000,3245.000,4322.500,3780.000
2020-11-16,2472.000,1792.000,3460.000,1600.000,3215.000,4240.000,3805.000
2020-11-17,2535.000,1810.000,3489.000,1610.000,3220.000,4300.000,3793.000

Like Vlookup in excel I'm trying to pick PPerc value from dfH and multiply it with the correspondent column in dfMerged, acummulate row values and append it in dfMerged as a new column. With the expression below I manage to do the math but I'm having troubles to accumulate this iteration on dfMerged["Ind"], I'm just getting last iteration values.

for i in list(dfMerged.columns):
    if i != 'Date':
        index = (dfH[dfH["TICKER"]==i]["PPerc"].values[0] * dfMerged[i])

dfMerged["Ind"] = index



       Date       C    AAPL     JPM  ...    MSFT      PG     WMT         Ind
0    2020-11-10  2380.0  1759.0  3480.0  ...  3189.5  4269.0  3665.0  382.124817
1    2020-11-11  2475.0  1798.0  3500.0  ...  3286.0  4352.0  3780.0  394.115091
2    2020-11-12  2409.0  1765.0  3392.0  ...  3208.0  4305.0  3687.0  384.418609
3    2020-11-13  2425.0  1770.0  3400.0  ...  3245.0  4322.5  3780.0  394.115091
4    2020-11-16  2472.0  1792.0  3460.0  ...  3215.0  4240.0  3805.0  396.721672

CodePudding user response：

If I you understand correctly, this works:

new_col = sum([df2[symbol] * df1['PPerc'][i] for i, symbol in enumerate(df1['TICKER'])])

Output:

>>> new_col
0    76190.0
1    77730.0
2    75660.0
3    75950.0
4    77240.0
5    78340.0
dtype: float64

CodePudding user response：

Solved using Join and then sum

for i in list(dfMerged.columns):
   if i != 'Date':
       index = (dfH[dfH["TICKER"]==i]["PPerc"].values[0] * dfMerged[i])
       tmp = tmp.join(index,how="right")
       
       
tmp["index"]=tmp.sum(axis=1)
dfMerged["Ind"] = tmp['index']

— @Billy101