Sum with rows from two dataframes

Question

I have two dataframes. One has months 1-5 and a value for each month, which are the same for ever ID, the other has an ID and a unique multiplier e.g.:

data = [['m', 10], ['a', 15], ['c', 14]]
 
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['ID', 'Unique'])

data2=[[1,0.2],[2,0.3],[3,0.01],[4,0.5],[5,0.04]]
df2 = pd.DataFrame(data2, columns=['Month', 'Value'])

I want to do sum ( value / (1+unique)^(Month/12) ). E.g. for ID m, I want to do (value/(1+10)^(Month/12)), for every row in df2, and sum them. I wrote a for-loop to do this but since my real table has 277,000 entries this takes too long!

df['baseTotal']=0
for i in df.index.unique():
    for i in df2.Month.unique():
        df['base']= df2['Value']/pow(1+df.loc[i,'Unique'],df2['Month']/12.0)
        df['baseTotal']=df['baseTotal']+df['base']

Is there a more efficient way to do this?

Panda Kim · Accepted Answer · 2022-12-20 12:13:23Z

2

df['Unique'].apply(lambda x: (df2['Value']/((1+x) ** (df2['Month']/12))).sum())


0    0.609983
1    0.563753
2    0.571392
Name: Unique, dtype: float64

answered Dec 20, 2022 at 12:13

Panda Kim

13.6k2 gold badges8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum with rows from two dataframes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related