unable to combine dataframes in python

January 17, 2024

i have 4 data frames. All have two columns. the first column in all the dataframes is symbol and they have the same symbols. all symbol column in each of the dataframe have 150 symbols.

now i want to combine them in one dataframe in which there will be symbol column and 4 other columns
from all the four data frames

following is the code snippet

result_df1 = max_open_int_ce_rows.loc[:, ['SYMBOL', '1 CE OI']]
result_df2 = second_largest_ce_rows.loc[:, ['SYMBOL','2nd CE OI']]
result_df3 = max_open_int_pe_rows.loc[:, ['SYMBOL','1 PE OI']]
result_df4 = second_largest_pe_rows.loc[:, ['SYMBOL','2nd PE OI']]

# Merge DataFrames on 'SYMBOL'
merged_df = result_df1.merge(result_df2, on='SYMBOL', how='outer') \
                .merge(result_df3, on='SYMBOL', how='outer') \
                .merge(result_df4, on='SYMBOL', how='outer') \
                .merge(result_df5, on='SYMBOL', how='outer') \
                .merge(result_df6, on='SYMBOL', how='outer') \
                .merge(result_df7, on='SYMBOL', how='outer') \
                .merge(result_df8, on='SYMBOL', how='outer')

i tried using merge. it gives the following error –

ValueError: ‘SYMBOL’ is both an index level and a column label, which is ambiguous.

>Solution :

If ‘SYMBOL’ is both an index level and a column label, you can resolve the ambiguity by resetting the index before performing the merge operation. Here’s how you can modify the code:

import pandas as pd

# Reset index for each dataframe
result_df1.reset_index(inplace=True)
result_df2.reset_index(inplace=True)
result_df3.reset_index(inplace=True)
result_df4.reset_index(inplace=True)

# Assuming 'SYMBOL' is the common column in all dataframes

# Merge result_df1 and result_df2 on 'SYMBOL'
merged_df1 = pd.merge(result_df1, result_df2, on='SYMBOL', how='outer')

# Merge merged_df1 and result_df3 on 'SYMBOL'
merged_df2 = pd.merge(merged_df1, result_df3, on='SYMBOL', how='outer')

# Merge merged_df2 and result_df4 on 'SYMBOL'
final_result_df = pd.merge(merged_df2, result_df4, on='SYMBOL', how='outer')

# Display the final result
print(final_result_df)

By resetting the index with reset_index(inplace=True), you’re converting the ‘SYMBOL’ from an index level to a regular column, and this should resolve the ambiguity issue. After the merge operations, you can further adjust the index as needed.