Error in from functools import reduce for multi dataframe diffrent column inner join

Using Streamlit
, , ,
1

dear sir

I am attempting to perform an inner join on multiple dataframes with differing columns using the ‘from functools import reduce’ library. However, I am encountering errors with my code. I have tried two methods, but both resulted in errors. Please review my code and the associated error messages, and provide the correct solution and code for performing an inner join on multiple dataframes with different columns, similar to an Oracle inner join.

import streamlit as st

import pandas as pd

from functools import reduce

# Sample DataFrames

df1 = pd.DataFrame({‘id_1’: [1, 2], ‘val1’: [‘A’, ‘B’]})

df2 = pd.DataFrame({‘id_2’: [1, 2], ‘val2’: [‘X’, ‘Y’]})

df3 = pd.DataFrame({‘id_3’: [1, 2], ‘val3’: [‘M’, ‘N’]})

# List your DataFrames

dfs = [df1, df2, df3] # try - 1

df_list = [df1, df2, df3]

result = pd.concat(df_list, axis=0) # try - 2

# Map your keys sequentially matching the order of dfs

# (df1 joins df2 via id_1 & id_2; df2 joins df3 via id_2 & id_3)

left_keys = [‘id_1’, ‘id_2’]

right_keys = [‘id_2’, ‘id_3’]

def merge_dfs(merged, next_df):

\# Track the current iteration step to pull keys

idx = dfs.index(next_df) - 1

return pd.merge(merged, next_df, left_on=left_keys\[idx\], right_on=right_keys\[idx\], how='outer')

final_df = reduce(merge_dfs, dfs)

print(final_df)

see error

image
image788×560 44.4 KB

image (1)
image (1)884×547 28.1 KB

2

Hey there, thanks for sharing your code and error details! :waving_hand: It looks like you’re running into issues because you’re trying to use dfs.index(next_df) to find the position of a DataFrame in your list, but DataFrames can’t be compared directly like that—hence the “truth value of a DataFrame is ambiguous” error. Also, your merge logic assumes a sequential join with different keys, but the reduce pattern needs careful key management.

It seems likely that the best way to perform an inner join across multiple DataFrames with different join keys is to merge step-by-step, explicitly specifying the keys at each step. Here’s a working example based on your scenario, using reduce and a list of key pairs, as discussed in this Streamlit forum post:

import pandas as pd
from functools import reduce

df1 = pd.DataFrame({'id_1': [1, 2], 'val1': ['A', 'B']})
df2 = pd.DataFrame({'id_2': [1, 2], 'val2': ['X', 'Y']})
df3 = pd.DataFrame({'id_3': [1, 2], 'val3': ['M', 'N']})

dfs = [df1, df2, df3]
# Define the join keys for each merge step
join_keys = [
    ('id_1', 'id_2'),  # df1.id_1 == df2.id_2
    ('id_2', 'id_3')   # df2.id_2 == df3.id_3
]

def merge_dfs(left, right_with_keys):
    right, (left_key, right_key) = right_with_keys
    return pd.merge(left, right, left_on=left_key, right_on=right_key, how='inner')

# Prepare a list of (df, (left_key, right_key)) for reduce
dfs_with_keys = list(zip(dfs[1:], join_keys))
final_df = reduce(merge_dfs, dfs_with_keys, dfs[0])
print(final_df)

This approach avoids ambiguous DataFrame comparisons and lets you specify different join keys for each step. Let us know if you need a breakdown of each step or want to see how to adapt this for more DataFrames!

Sources: