Python Pandas中转换年月为datetime并生成指定格式CSV

Hey there! Let's work through this problem together since you're new to Pandas and datetime handling. Your goal is to expand your existing DataFrame into a row-per-month CSV, filling missing months with 0.0 and creating a proper datetime column. Here's a step-by-step solution:

Step 1: Transform the Raw Wide Data #

Your original DataFrame has fiscal years (like 1967-68) and rainfall data for June, July, August. First, we'll convert this wide-format data into a long format, and link each data point to its correct natural year and month:

import pandas as pd

# Your original DataFrame
df = pd.DataFrame({
 'S.No.':[1,2,3,4,5],
 'YEAR':['1967-68','1968-69','1969-70','1970-71','1971-72'],
 'JUNE':['77.19','415.48','236.71','108.38','6.19'],
 'JULY':['76.19','435.48','26.71','138.38','9.19'],
 'AUGUST':['75.19','415.48','226.71','78.38','3.19']
})

# Extract the starting natural year from each fiscal year (e.g., 1967 from '1967-68')
df['start_year'] = df['YEAR'].str.split('-').str[0].astype(int)

# Convert month columns (JUNE/JULY/AUGUST) into individual rows
melted_df = df.melt(
    id_vars=['start_year'],
    value_vars=['JUNE', 'JULY', 'AUGUST'],
    var_name='month_name',
    value_name='data'
)

# Map month names to numerical values
month_map = {'JUNE': 6, 'JULY': 7, 'AUGUST': 8}
melted_df['month'] = melted_df['month_name'].map(month_map)

# Create datetime column using year, month, and first day of the month
melted_df['datetime'] = pd.to_datetime(melted_df[['start_year', 'month']].assign(day=1))

# Clean up: keep only needed columns, convert data to float type
melted_df = melted_df[['datetime', 'data']]
melted_df['data'] = melted_df['data'].astype(float)

Step 2: Generate Full Date Range and Fill Missing Data #

Next, we'll create a complete list of every month from 1967-01 to 1972-12, then merge our existing data into this list to fill missing months with 0.0:

# Create a full sequence of monthly dates (first day of each month)
full_dates = pd.date_range(start='1967-01-01', end='1972-12-01', freq='MS')
full_df = pd.DataFrame({'datetime': full_dates})

# Merge our data with the full date list, fill missing values with 0.0
result_df = full_df.merge(melted_df, on='datetime', how='left').fillna(0.0)

Step 3: Add Year/Month/Day Columns and Export CSV #

Finally, we'll extract year, month, and day from the datetime column, reorder columns to match your desired output, and save as a semicolon-separated CSV:

# Extract year, month, day directly from datetime
result_df['year'] = result_df['datetime'].dt.year
result_df['month'] = result_df['datetime'].dt.month
result_df['day'] = result_df['datetime'].dt.day

# Reorder columns to match your expected format
result_df = result_df[['datetime', 'year', 'month', 'day', 'data']]

# Export to CSV with correct formatting
result_df.to_csv('output.csv', sep=';', index=False, date_format='%Y-%m-%d %H:%M:%S')

Quick Breakdown of Key Steps #

• melt(): Turns your wide table (one row per fiscal year) into a long table (one row per month with data) — this makes it easier to work with monthly data.
• pd.date_range(): Ensures we have every single month in your desired date range, so no months are missing from the final CSV.
• Left merge + fillna(0.0): Fills any month that doesn't have data with 0.0, exactly as you requested.
• dt.year/month/day: Extracts these values directly from the datetime column, so you don't have to calculate them manually.

When you run this code, you'll get a CSV that matches your example perfectly: each row represents a month starting from 1967-01-01, with 0.0 for months without data, and the correct values for June-August of each year.

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