Pandas: How to multiple 2 DataFrames element-wise (4 examples)

Overview

Manipulating and analyzing data with Python’s pandas library is a fundamental skill for data scientists and analysts. A common task is performing operations on two DataFrames. This tutorial will walk you through how to multiply two DataFrames element-wise, using four examples with increasing complexity.

Before diving into examples, ensure you have pandas installed in your environment. If not, you can install it using pip:

pip install pandas

Example 1: Basic Element-wise Multiplication

Starting with the basics, let’s create two simple DataFrames and multiply them element-wise.

import pandas as pd
df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df2 = pd.DataFrame({'A': [10, 20, 30], 'B': [40, 50, 60]})
result = df1 * df2
print(result)

Output:

     A    B
0   10  160
1   40  250
2   90  360

This example demonstrates the simplest form of element-wise multiplication. The corresponding elements in each DataFrame are multiplied, and the result is a new DataFrame with the same structure.

Example 2: Handling Mismatched indices

In real scenarios, DataFrames might not align perfectly. This example shows how to handle mismatched indices.

df1 = pd.DataFrame({'A': [1, 2, 3]}, index=[0,1,2])
df2 = pd.DataFrame({'A': [10, 20, 30]}, index=[1,2,3])
result = df1.mul(df2, fill_value=0)
print(result)

Output:

      A
0   0.0
1  20.0
2  60.0
3   0.0

This example uses .mul() with the fill_value parameter. Non-matching indices result in multiplication with zero, thus preserving the size of the result DataFrame without losing any index.

Example 3: Multiplying With Different Column Names

What happens if our DataFrames have different column names? This section will show you a way to handle such cases effectively.

df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df2 = pd.DataFrame({'C': [10, 20, 30], 'D': [40, 50, 60]})
df1.columns = df2.columns  # Rename df1 columns to match df2
result = df1 * df2
print(result)

Output:

      C    D
0   10  160
1   40  250
2   90  360

By renaming the columns of df1 to match df2, we align the two DataFrames for element-wise multiplication. This requires that both DataFrames have an identical structure.

Example 4: Advanced Scenario – Applying a Function After Multiplication

For a more advanced use case, you might want to perform an operation on the result immediately after multiplication. Here, we’ll multiply two DataFrames element-wise, then apply a function to the result.

df1 = pd.DataFrame({'A': [1, 2, -3], 'B': [-4, 5, 6]})
df2 = pd.DataFrame({'A': [10, 20, 30], 'B': [40, 50, 60]})
result = df1 * df2
result = result.applymap(lambda x: 'positive' if x > 0 else 'negative')
print(result)

Output:

           A        B
0   positive  negative
1   positive  positive
2  negative  positive

This example multiplies two DataFrames and then uses applymap() to apply a lambda function to each element of the result. This transforms the numerical result into a categorical one based on the sign of the numbers.

Conclusion

Multiplying two DataFrames element-wise in pandas can range from straightforward operations to handling advanced scenarios involving mismatched indices or applying functions post-multiplication. Understanding these examples provides a solid foundation for tackling more complex data manipulation tasks with pandas.