基于公司月度计数生成柱状图及CSV文件的技术实现需求

Great job getting your data preprocessed! Let's extend that code to generate the monthly CSV files and bar charts you need. Here's a step-by-step breakdown:

1. Generate Monthly CSV Files #

We can iterate over each unique month in your grouped data, extract the records for that month, and save them to a CSV file named with the month (e.g., 2019-01_counts.csv):

import pandas as pd

# Your existing preprocessing code
df = pd.read_csv("Sample_Data.csv")
df['Date Modified'] = pd.to_datetime(df['Date']).dt.to_period('M')
df = df.groupby(["Date Modified", "Company"]).sum()

# Generate CSV for each month
for month in df.index.get_level_values('Date Modified').unique():
    # Filter data for the current month and reset index to make Company a column
    monthly_data = df.loc[month].reset_index()  
    # Save to CSV with month in filename
    monthly_data.to_csv(f"{month}_counts.csv", index=False)
    print(f"Generated CSV for {month}")

This will create a separate CSV file for each month, with columns for Company and your count metrics.

2. Generate Monthly Bar Charts #

Using matplotlib, we can create a bar chart for each month, with companies on the X-axis and counts on the Y-axis. We'll save each chart as an image file (e.g., 2019-01_counts.png):

First, make sure you have matplotlib installed:

pip install matplotlib

Then add this code to your script:

import matplotlib.pyplot as plt

# Generate bar chart for each month
for month in df.index.get_level_values('Date Modified').unique():
    monthly_data = df.loc[month]
    # Extract companies and their counts (assuming the first numeric column is your count)
    companies = monthly_data.index
    counts = monthly_data.iloc[:, 0]  # Adjust column index if needed
    
    # Create bar chart
    plt.figure(figsize=(10, 6))
    bars = plt.bar(companies, counts)
    
    # Add labels and title
    plt.xlabel("Company")
    plt.ylabel("Count")
    plt.title(f"Company Counts - {month}")
    plt.xticks(rotation=45, ha='right')  # Rotate company names for readability
    
    # Add count labels on top of bars (optional but helpful)
    for bar in bars:
        height = bar.get_height()
        plt.text(bar.get_x() + bar.get_width()/2., height,
                 f'{int(height)}',
                 ha='center', va='bottom')
    
    # Adjust layout to prevent label cutoff
    plt.tight_layout()
    
    # Save the chart
    plt.savefig(f"{month}_counts.png")
    plt.close()  # Close the figure to free memory
    print(f"Generated bar chart for {month}")

Combined Script #

Here's the full script that handles preprocessing, CSV generation, and chart creation all in one:

import pandas as pd
import matplotlib.pyplot as plt

# Preprocess the data
df = pd.read_csv("Sample_Data.csv")
df['Date Modified'] = pd.to_datetime(df['Date']).dt.to_period('M')
df = df.groupby(["Date Modified", "Company"]).sum()

# Generate monthly CSV files
for month in df.index.get_level_values('Date Modified').unique():
    monthly_data = df.loc[month].reset_index()
    monthly_data.to_csv(f"{month}_counts.csv", index=False)
    print(f"Saved CSV: {month}_counts.csv")

# Generate monthly bar charts
for month in df.index.get_level_values('Date Modified').unique():
    monthly_data = df.loc[month]
    companies = monthly_data.index
    counts = monthly_data.iloc[:, 0]
    
    plt.figure(figsize=(10, 6))
    bars = plt.bar(companies, counts)
    
    plt.xlabel("Company")
    plt.ylabel("Count")
    plt.title(f"Company Counts - {month}")
    plt.xticks(rotation=45, ha='right')
    
    for bar in bars:
        height = bar.get_height()
        plt.text(bar.get_x() + bar.get_width()/2., height,
                 f'{int(height)}',
                 ha='center', va='bottom')
    
    plt.tight_layout()
    plt.savefig(f"{month}_counts.png")
    plt.close()
    print(f"Saved chart: {month}_counts.png")

Quick Notes: #

• If your count column has a specific name (instead of using the first column), replace monthly_data.iloc[:, 0] with monthly_data['YourCountColumnName'] for clarity.
• Adjust the figsize parameter in plt.figure() to change the size of your charts.
• The rotation=45 on the X-axis labels ensures company names don't overlap.

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