How to Concatenate Multiple DataFrames with the Same Indexes and Columns in Pandas

 

When working with data in Python, Pandas is a powerful tool for data manipulation. One common task is to concatenate multiple DataFrames that share the same structure. This blog post will guide you through the process of concatenating three DataFrames while maintaining their indexes and columns, specifically focusing on achieving a multi-index table where values are presented row by row.

Scenario

Suppose you have three DataFrames with the same columns and indexes, and you need to concatenate them such that the data from each DataFrame is identifiable and aligned row by row under each index.

Initial Setup

Let’s start by creating three DataFrames:

import pandas as pd

# Sample data for the DataFrames
data1 = {'A': [1, 2], 'B': [3, 4]}
data2 = {'A': [5, 6], 'B': [7, 8]}
data3 = {'A': [9, 10], 'B': [11, 12]}

# Creating DataFrames
df1 = pd.DataFrame(data1, index=['X', 'Y'])
df2 = pd.DataFrame(data2, index=['X', 'Y'])
df3 = pd.DataFrame(data3, index=['X', 'Y'])

Concatenation with MultiIndex

To concatenate these DataFrames and maintain a clear structure, use pd.concat with a keys argument to create a hierarchical index:

# Concatenating the DataFrames
concat_df = pd.concat([df1, df2, df3], keys=['First', 'Second', 'Third'])
print(concat_df)

This code snippet results in a DataFrame where each original DataFrame is labeled and stacked on top of each other under the corresponding key.

Desired Structure Adjustment

The initial output might not be exactly what you need. If you want each DataFrame’s entries under each index to be horizontally aligned (side by side), you can adjust the structure as follows:

# Adjusting the structure
multi_index_df = concat_df.unstack(level=0)
print(multi_index_df)

This adjustment rearranges the DataFrame so that the values from each original DataFrame are presented side by side for each index.

Fine-tuning with MultiIndex and Sorting

For finer control over the indexing and to ensure the DataFrame is sorted by indexes correctly, you can use the swaplevel and sort_index methods:

# Creating a multi-level index and sorting
final_df = (pd.concat([df1, df2, df3], keys=['DF1', 'DF2', 'DF3'])
               .swaplevel(0, 1)
               .sort_index(level=0))

print(final_df)

This will organize the DataFrame such that each index from the original DataFrames is the primary index, and the keys are used as secondary indexes, providing a clear and organized structure.

Output Visualization

The final structure allows you to easily see which values belong to which original DataFrame under each index. It’s particularly useful when dealing with data where the relationship between rows across DataFrames is significant.

Concatenating multiple DataFrames with the same indexes and columns in Pandas can be accomplished efficiently using a combination of pd.concat, multi-level indexing, and appropriate sorting. This approach is robust and flexible, allowing for the clear organization of combined data from multiple sources.

By understanding these techniques, you can handle complex data merging tasks in Pandas, making your data analysis tasks easier and more streamlined.