Pandas perform functions using multiple dataframes-CodePudding

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)

CodePudding user response：

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

CodePudding user response：

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