如何拼接两个Pandas DataFrame？尝试后未获预期结果求指导

Hey there! Let's figure out how to properly combine these two DataFrames—since their structures are pretty different, the first thing we need to clarify is what you want the final combined data to look like (stack rows on top of each other, or join columns side-by-side based on a shared value like date?). Let's break down both scenarios with step-by-step solutions:

First, let's recap your data #

Data Source Code

from datetime import date, timedelta

file_date = str((date.today() - timedelta(days = 2)).strftime('%m-%d-%Y'))
github_dir_path = 'https://github.com/CSSEGISandData/COVID-19/raw/master/csse_covid_19_data/csse_covid_19_daily_reports/'
file_path = github_dir_path + file_date + '.csv'

DataFrame 1 (US County Daily Reports)

DataFrame 2 (Kerala Time-Series Data)

This appears to be daily cumulative data for Kerala, India, with some redundant columns. Here's a cleaned-up view of its core structure:

Scenario 1: Stack Rows (Combine all records into a single table) #

If you want to merge the US county data and Kerala data into one table (so each row is a region-date record), you first need to align their column structures:

1. Clean up the Kerala DataFrame to match the US DataFrame's columns:

   import pandas as pd
   
   # Assume your Kerala DataFrame is named df_kerala
   # Keep only useful columns and rename them to match df_us
   df_kerala_clean = df_kerala[["Date", "Region", "Confirmed", "Deaths", "Recovered"]].rename(
       columns={
           "Date": "Last_Update",
           "Region": "Province_State",
           # Match the exact column names from df_us
           "Confirmed": "Confirmed",
           "Deaths": "Deaths",
           "Recovered": "Recovered"
       }
   )
   
   # Fill in missing columns from df_us
   df_kerala_clean["Country_Region"] = "India"
   df_kerala_clean["Active"] = df_kerala_clean["Confirmed"] - df_kerala_clean["Deaths"] - df_kerala_clean["Recovered"]
   df_kerala_clean["Combined_Key"] = df_kerala_clean["Province_State"] + ", India"
   # Fill non-applicable columns with NaN
   df_kerala_clean[["FIPS", "Admin2", "Lat", "Long_"]] = pd.NA
   
   # Ensure date formats match (convert to datetime if needed)
   df_kerala_clean["Last_Update"] = pd.to_datetime(df_kerala_clean["Last_Update"])
   

2. Now stack the two DataFrames together:

   # Assume your US DataFrame is named df_us
   combined_df = pd.concat([df_us, df_kerala_clean], ignore_index=True)
   

Scenario 2: Join Columns (Link US data with Kerala data by date) #

If you want to pair each US county record with Kerala's data from the same date (e.g., see US county stats alongside Kerala's stats for 2020-04-28), you'll need to join on the shared date field:

1. Standardize the date format in both DataFrames:

   # Strip time from df_us's Last_Update to get just the date
   df_us["Date"] = pd.to_datetime(df_us["Last_Update"]).dt.date
   # Convert Kerala's date column to the same date format
   df_kerala["Date"] = pd.to_datetime(df_kerala["Date"]).dt.date
   

2. Create a slim version of the Kerala DataFrame with only date and key stats, then merge:

   # Keep only date and Kerala-specific stats, rename to avoid column conflicts
   df_kerala_slim = df_kerala[["Date", "Confirmed", "Deaths", "Recovered"]].rename(
       columns={
           "Confirmed": "Kerala_Confirmed",
           "Deaths": "Kerala_Deaths",
           "Recovered": "Kerala_Recovered"
       }
   )
   
   # Merge the two DataFrames on the shared Date column
   # Use how="left" to keep all US county records even if there's no Kerala data for that date
   combined_df = pd.merge(df_us, df_kerala_slim, on="Date", how="left")
   

Key Tips #

• Always start by defining your end goal: do you want a single list of all region-date records, or do you want to compare stats across regions on the same date?
• For row stacking, column alignment is critical—fill missing columns with NaN if they don't apply to one of the datasets.
• For column joining, make sure your join key (like date) is in the same format across both DataFrames to avoid mismatches.

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