Python中按年月分组两个DataFrame并同图可视化绿色项目占比趋势的技术求助

Got it, let's work through this step by step to get your dual-trend chart up and running! The key steps are: standardizing your date grouping, calculating only the green project percentages for each DataFrame, then combining them into a single visualization.

Step 1: Refine Date Grouping & Calculate Green Percentages #

Your current code groups by individual dates, but we need to aggregate by year and month instead. We'll also simplify the calculation to focus only on the percentage of green projects (since that's your core metric).

Let's assume your two DataFrames are named cur_df (your existing Cur) and other_df (the second project set). Here's how to process each:

For Your First DataFrame (Current Projects): #

import pandas as pd

# Convert date column to datetime, then extract year-month as a clean period
cur_df_processed = cur_df.assign(
    YearMonth=pd.to_datetime(cur_df["Customer Signed Date"]).dt.to_period('M')
)

# Calculate % of green projects per year-month
cur_green = (cur_df_processed
             .groupby("YearMonth")["ProjectHealth"]
             .apply(lambda x: (x == "Green").mean() * 100)
             .reset_index(name="Green_Percentage"))

For the Second DataFrame (Other Project Set): #

# Repeat the same logic for your second dataset
other_df_processed = other_df.assign(
    YearMonth=pd.to_datetime(other_df["Date"]).dt.to_period('M')
)

other_green = (other_df_processed
               .groupby("YearMonth")["ProjectHealth"]
               .apply(lambda x: (x == "Green").mean() * 100)
               .reset_index(name="Green_Percentage"))

What this does:

• dt.to_period('M') converts dates to a year-month format (e.g., 2023-05) for clean monthly grouping, avoiding clutter from individual dates.
• The lambda function calculates the proportion of "Green" entries in each group, then multiplies by 100 to get a percentage (way more direct than your original value_counts approach for this specific metric).

Step 2: Visualize Both Trends in One Chart #

Now we can plot both datasets on the same axis. We'll cover two options: basic matplotlib for full customization, and seaborn for a polished, low-effort look.

Option 1: Matplotlib (Simple & Customizable) #

import matplotlib.pyplot as plt

plt.figure(figsize=(12, 6))

# Plot each dataset with distinct markers/styles
plt.plot(
    cur_green["YearMonth"].astype(str),  # Convert period to string for readable x-axis labels
    cur_green["Green_Percentage"],
    label="Current Projects",
    marker="o",
    linestyle="-",
    color="#2ecc71"  # Green to match your project status
)
plt.plot(
    other_green["YearMonth"].astype(str),
    other_green["Green_Percentage"],
    label="Other Project Set",
    marker="s",
    linestyle="--",
    color="#3498db"  # Blue for contrast
)

# Add labels and formatting
plt.title("Trend of Green Health Project Percentage Over Time", fontsize=14)
plt.xlabel("Year-Month", fontsize=12)
plt.ylabel("Percentage of Green Projects (%)", fontsize=12)
plt.xticks(rotation=45, ha="right")  # Rotate x-labels to avoid overlap
plt.legend(fontsize=12)
plt.tight_layout()  # Adjust layout to prevent label cutoff
plt.grid(axis="y", alpha=0.3)  # Light horizontal grid for readability

plt.show()

Option 2: Seaborn (Cleaner, Statistical Focus) #

First, combine the two processed datasets with a group identifier, then plot:

import seaborn as sns

# Add a group column to distinguish the two datasets
cur_green["Project_Group"] = "Current Projects"
other_green["Project_Group"] = "Other Project Set"

combined_data = pd.concat([cur_green, other_green])

# Create the line plot
plt.figure(figsize=(12, 6))
sns.lineplot(
    data=combined_data,
    x="YearMonth",
    y="Green_Percentage",
    hue="Project_Group",
    marker="o",
    linewidth=2
)

# Format the plot
plt.title("Trend of Green Health Project Percentage Over Time", fontsize=14)
plt.xlabel("Year-Month", fontsize=12)
plt.ylabel("Percentage of Green Projects (%)", fontsize=12)
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.show()

Troubleshooting Tips #

• Missing Dates: If some months have no data, you can fill gaps with 0 (or NaN) by reindexing to a full range of year-months. For example:

  # Create a full range of year-months
  full_date_range = pd.period_range(start=cur_green["YearMonth"].min(), end=cur_green["YearMonth"].max(), freq='M')
  cur_green = cur_green.set_index("YearMonth").reindex(full_date_range, fill_value=0).reset_index()
  

• Date Format Issues: Ensure your date columns are parsed correctly with pd.to_datetime()—use the format parameter if your dates are non-standard (e.g., pd.to_datetime(df["Date"], format="%d/%m/%Y")).
• Inconsistent Column Names: Double-check that date column names match your actual DataFrames (your code uses "Customer Signed Date" for one and "Date" for the other—our code accounts for that, but adjust if needed).

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