How do I divide two different dataframes by positions in the each dataframe values?

user_2110417 0
Viewed 87

I have two dataframes: df1:

df1 = pd.DataFrame({'Name': ["cat", "dog", "fish"],
    'Set1': [20, 10, 0],
    'Set2': [15, 0, 12],
    'Set3': [0, 0, 6]})

df1

    Name    Set1    Set2    Set3
0   cat     20      15      0
1   dog     10      0       0
2   fish    0       12      6

df2:

df2 = pd.DataFrame({'Name': ["cat", "dog", "fish"],
    'AK': [2, 2, 0],
    'BD': [5, 1, 3],
    'CL': [1, 0, 6]})

df2

    Name    AK  BD  CL
0   cat     2   5   1
1   dog     2   1   0
2   fish    0   3   6

I want to divide df1 by df2 by each position in both dataframe:

The expected output is:

Name    Set1    Set2    Set3
cat     10      3       0
dog     5       0       0
fish    0       4       1

I tried the code in following way:

df1.set_index('Name', inplace=True)
df2.set_index('Name', inplace=True)

df3 = df1.reset_index(drop=True) / df2.reset_index(drop=True)

but I get all row values as NaN

output

pandas dataframe python
2 Answers

You can divide converted values to numpy array, divide by DataFrame.div and last replace NaNs to 0 by DataFrame.fillna:

df1.set_index('Name', inplace=True)
df2.set_index('Name', inplace=True)

df3 = df1.div(df2.to_numpy()).fillna(0).reset_index()

print (df3)
   Name  Set1  Set2  Set3
0   cat  10.0   3.0   0.0
1   dog   5.0   0.0   0.0
2  fish   0.0   4.0   1.0

One row solution(s):

df3 = df1.set_index('Name').div(df2.iloc[:, 1:].to_numpy()).fillna(0).reset_index()

Or:

df3 = df1.set_index('Name').div(df2.set_index('Name').to_numpy()).fillna(0).reset_index()

print (df3)
   Name  Set1  Set2  Set3
0   cat  10.0   3.0   0.0
1   dog   5.0   0.0   0.0
2  fish   0.0   4.0   1.0
user_29010020

Try using set_index and divide:

>>> df1.set_index('Name').divide(df2.drop('Name', axis=1).to_numpy()).fillna(0)
      Set1  Set2  Set3
Name                  
cat   10.0   3.0   0.0
dog    5.0   0.0   0.0
fish   0.0   4.0   1.0
>>>

Then fill the nones with 0.

If you want to make Name a column again:

>>> df1.set_index('Name').divide(df2.drop('Name', axis=1).to_numpy()).fillna(0).reset_index()
   Name  Set1  Set2  Set3
0   cat  10.0   3.0   0.0
1   dog   5.0   0.0   0.0
2  fish   0.0   4.0   1.0
>>>
user_87083640
Related