Pandas多文件合并：基于日期关联不同结构数据集

Got it, let's tackle this merge problem step by step. The core issue here is that your two files use different data formats—File1 is in long format (one row per State-Date pair), while File2 is in wide format (one row per State, with dates as column headers). We need to reshape File2 first to match File1's structure, then perform the merge.

Step 1: Read in the Data #

First, let's load both files into Pandas DataFrames. I'll assume they're CSV files (adjust to pd.read_excel if they're Excel files):

import pandas as pd

# Load File1 (long format)
df1 = pd.read_csv("File1.csv")

# Load File2 (wide format)
df2 = pd.read_csv("File2.csv")

Step 2: Reshape File2 to Long Format #

We'll use Pandas' melt() function to convert File2's date columns into a single Date column, and the corresponding values into a new column (let's call it Value—you can rename this to whatever makes sense for your data):

# Reshape File2: keep 'State' as the identifier, melt all date columns into rows
df2_long = df2.melt(
    id_vars="State",  # Column to keep as is
    var_name="Date",  # Name for the new date column
    value_name="Value"  # Name for the column holding the numeric values
)

Critical: Standardize Date Formats #

To avoid matching failures due to string format inconsistencies, convert the Date columns in both DataFrames to the same datetime type:

# Convert Date columns to datetime (using your dd/mm/yy format)
df1["Date"] = pd.to_datetime(df1["Date"], format="%d/%m/%y")
df2_long["Date"] = pd.to_datetime(df2_long["Date"], format="%d/%m/%y")

Step 3: Merge the DataFrames #

Now we can merge the two DataFrames using both State and Date as the matching keys. Choose the how parameter based on your needs:

• left: Keep all rows from File1, even if there's no match in File2 (fills missing values with NaN)
• inner: Only keep rows where there's a match in both files
• right: Keep all rows from the reshaped File2

Here's an example with a left join (most likely what you need to retain all entries from File1):

# Merge on State and Date
merged_df = pd.merge(df1, df2_long, on=["State", "Date"], how="left")

Example Output #

Using your sample data:

• File1 has rows like NSW, 01/02/16, VIC, 01/04/16, TAS, 01/12/17
• File2 has rows like VIC, 10000, 12000, 14000, 17600, NSW, 50000, ...

The merged DataFrame will look like this:

If you need to fill those NaN values (e.g., with 0), you can add:

merged_df["Value"] = merged_df["Value"].fillna(0)

内容的提问来源于stack exchange，提问作者Joono