如何用Pandas通过groupby生成值为列的新DataFrame？按日期重塑数据有对应函数吗？

Hey there! Let's break down your two Pandas questions with practical examples so you can implement these solutions easily.

1. Creating a new DataFrame with original values as columns using groupby #

You can achieve this by combining groupby with unstack, or use more intuitive functions like pivot or pivot_table depending on your data structure. Here's how:

First, let's create a sample DataFrame to work with:

import pandas as pd

data = {
    'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
    'Product': ['A', 'B', 'A', 'B'],
    'Sales': [100, 150, 120, 180]
}
df = pd.DataFrame(data)

Option 1: Groupby + Unstack #

Group by your target columns (e.g., Date and Product), aggregate the values, then unstack the categorical column to turn its values into new columns:

# Group by Date and Product, sum Sales, then unstack Product into columns
grouped_unstacked = df.groupby(['Date', 'Product'])['Sales'].sum().unstack()

Option 2: Pivot (for unique combinations) #

If your index-column pairs have no duplicates, pivot is a cleaner shortcut:

# Pivot Date as index, Product as columns, Sales as values
pivoted_df = df.pivot(index='Date', columns='Product', values='Sales')

Option 3: Pivot Table (for duplicate combinations) #

If you have duplicate (index, column) pairs, use pivot_table with an aggregation function (like sum, mean) to handle them:

# Aggregate duplicate entries with sum
pivot_table_df = df.pivot_table(index='Date', columns='Product', values='Sales', aggfunc='sum')

2. Reshaping DataFrame by Date field in Python (Pandas functions available?) #

Absolutely! Pandas has several built-in functions to reshape your DataFrame around Date fields, depending on whether you want to go from long-to-wide or wide-to-long format:

Case 1: Long-to-Wide (Date as index, categories as columns) #

This is exactly what we covered in the first question—use pivot, pivot_table, or groupby + unstack to turn date into your index and spread other values into columns.

Case 2: Wide-to-Long (Date as a column) #

If your DataFrame has dates as column headers (wide format), use melt to reshape it into a long format with Date as a dedicated column:

# Sample wide-format DataFrame
wide_data = {
    'Product': ['A', 'B'],
    '2023-01-01': [100, 150],
    '2023-01-02': [120, 180]
}
wide_df = pd.DataFrame(wide_data)

# Reshape to long format: Date becomes a column
long_df = wide_df.melt(id_vars='Product', var_name='Date', value_name='Sales')

# Don't forget to convert Date to datetime type for easier time-series operations
long_df['Date'] = pd.to_datetime(long_df['Date'])

Case 3: Reshaping with multiple date-related metrics #

If you have columns like Sales_20230101 and Stock_20230101, use pd.wide_to_long to split the date suffix into a separate Date column:

# Sample DataFrame with multiple metrics per date
multi_data = {
    'Product': ['A', 'B'],
    'Sales_20230101': [100, 150],
    'Sales_20230102': [120, 180],
    'Stock_20230101': [50, 70],
    'Stock_20230102': [60, 80]
}
multi_df = pd.DataFrame(multi_data)

# Reshape to long format, separating Date and metric type
long_multi_df = pd.wide_to_long(
    multi_df,
    stubnames=['Sales', 'Stock'],  # Prefixes of metric columns
    i='Product',  # Identifier column(s)
    j='Date',  # Name for the new date column
    sep='_',  # Separator between metric and date
    suffix=r'\d+'  # Regex for date suffix
)
# Convert Date to datetime and reset index for readability
long_multi_df.reset_index(inplace=True)
long_multi_df['Date'] = pd.to_datetime(long_multi_df['Date'], format='%Y%m%d')

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