Pandas perform functions using multiple dataframes

Question

I have two pandas dataframes in Python, one only has one row of data. The other has many rows. I would like to go through each row of the first one and subtract the second one from it. I would then like to store the results in a dataframe the size of the second one. The first one is

df1 = pd.DataFrame({"Amounts": [1.1, 2.2, 3.3]})

And the second one is

df2 = pd.DataFrame({"A": [500, 600, 700, 800, 900], 
                    "B": [250, 750, 900, 500, 200], 
                    "C": [450, 125, 600, 200, 800]})

I am trying to subtract one from the other (1.1 from A, 2.2 from B, 3.3 from C) to get the following output:

A     B    C
[498.9, 247.8, 446.7],
[598.9, 747.8, 121.7],
[698.9, 897.8, 596.7],
[798.9, 497.8, 196.7],
[898.9, 197.8, 796.7]

And save it as another dataframe. Any assistance you could provide would be very helpful! Bonus points if you could also provide an answer for similar arithmetic (i.e. multplying instead of subtracting)

mozway · Accepted Answer · 2022-09-21 14:59:05Z

2

You need to convert df1 to Series and to numpy array to bypass index alignment:

df3 = df2.sub(df1['Amounts'].to_numpy(), axis=1)

There are many alternative, like full numpy:

df3 = df2.sub(df1.to_numpy().ravel(), axis=1)

Or full pandas:

df3 = df2.sub(df1['Amounts'].set_axis(df2.columns), axis=1)

output:

       A      B      C
0  498.9  247.8  446.7
1  598.9  747.8  121.7
2  698.9  897.8  596.7
3  798.9  497.8  196.7
4  898.9  197.8  796.7

answered Sep 21, 2022 at 14:59

mozway

267k13 gold badges56 silver badges106 bronze badges

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

1 Comment

Tim Starr Over a year ago

The full pandas option worked great, thanks! It also turns out there was an issue with the csv I was reading in for df2.

Niraj Gautam · Accepted Answer · 2022-09-21 15:15:23Z

1

import pandas as pd
import numpy as np

df1 = pd.DataFrame({"Amounts": [1.1, 2.2, 3.3]})

df2 = pd.DataFrame({"A": [500, 600, 700, 800, 900], 
                "B": [250, 750, 900, 500, 200], 
                "C": [450, 125, 600, 200, 800]})

final = pd.DataFrame()
if len(df1) != len(df2.columns):
     pass
else:
   for i in range(len(df1)):
       final[df2.columns[i]]=df2[df2.columns[i]].astype(float)-df1['Amounts'] [i].astype(float)

final



   A    B   C
0   498.9   247.8   446.7
1   598.9   747.8   121.7
2   698.9   897.8   596.7
3   798.9   497.8   196.7
4   898.9   197.8   796.7

answered Sep 21, 2022 at 15:15

Niraj Gautam

1777 bronze badges

Collectives™ on Stack Overflow

Pandas perform functions using multiple dataframes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related