Pandas DataFrame转换：将行业名称设为表头、年月合并为日期列

你的数据结构特征明确：读取后的DataFrame中，第一列交替出现行业名称、年份（仅行业名下方一行有有效值），后续行年份为NaN，同时包含月份字段和对应行业的数值。以下是具体实现步骤：

步骤1：预处理数据，补全年份值 #

先给列临时命名方便操作，再用向下填充补全所有行的年份：

# 给列临时命名
df.columns = ['category', 'month', 'value']

# 提取年份并向下填充：仅保留category列中的数字（年份），其余用上方有效值补全
df['year'] = df['category'].where(df['category'].str.isdigit()).ffill()

# 过滤掉行业名称行、年份行，仅保留有月份数据的有效行
monthly_data = df[df['month'].notna()].copy()

步骤2：标记行业归属，提取列标题 #

给每个数据行标记所属行业，同时提取所有行业名称作为后续列标题：

# 标记每行数据对应的行业：保留category列中的行业名称，其余用上方有效值补全
df['industry'] = df['category'].where(~df['category'].str.isdigit()).ffill()

# 提取所有行业名称列表
industry_names = df[~df['category'].str.isdigit() & df['category'].notna()]['category'].tolist()

步骤3：重塑数据结构，生成DATE列 #

将数据转为宽表结构，再合并年份与月份为标准日期列：

# 把数据重塑为宽表：年份+月份为索引，行业名为列，数值为对应值
structured_df = monthly_data.pivot(
    index=['year', 'month'],
    columns='industry',
    values='value'
).reset_index()

# 合并年份和月份为DATE列（假设月份是英文缩写，如Jan/Feb，若为数字则把format改为'%Y-%m'）
structured_df['DATE'] = pd.to_datetime(
    structured_df['year'] + '-' + structured_df['month'],
    format='%Y-%b'
)

# 整理最终列顺序，移除多余的year、month列
final_df = structured_df[['DATE'] + industry_names].copy()

完整可运行代码 #

import pandas as pd

url = "https://www-genesis.destatis.de/genesisWS/rest/2020/data/tablefile?username=DEB924AL95&password=P@ssword123&name=45213-0005&area=all&compress=false&transpose=false&startyear=1900&endyear=&timeslices=&regionalvariable=&regionalkey=&classifyingvariable1=WERTE4&classifyingkey1=REAL&classifyingvariable2=WERT03&classifyingkey2=BV4KSB&classifyingvariable3=WZ2008&classifyingkey3=WZ08-551&format=xlsx&job=false&stand=01.01.1970&language=en"

df = pd.read_excel(url)
df = df.iloc[7:-5]

# 预处理列名
df.columns = ['category', 'month', 'value']

# 填充年份
df['year'] = df['category'].where(df['category'].str.isdigit()).ffill()

# 标记所属行业
df['industry'] = df['category'].where(~df['category'].str.isdigit()).ffill()

# 过滤有效月度数据
monthly_data = df[df['month'].notna()].copy()

# 提取行业名称
industry_names = df[~df['category'].str.isdigit() & df['category'].notna()]['category'].tolist()

# 重塑为宽表
structured_df = monthly_data.pivot(
    index=['year', 'month'],
    columns='industry',
    values='value'
).reset_index()

# 生成DATE列
structured_df['DATE'] = pd.to_datetime(
    structured_df['year'] + '-' + structured_df['month'],
    format='%Y-%b'
)

# 整理最终DataFrame
final_df = structured_df[['DATE'] + industry_names].copy()

print(final_df.head())

注意事项 #

• 若月份格式为数字（如1/2/3），需将pd.to_datetime中的format参数改为'%Y-%m'
• 代码支持多行业场景，会自动提取所有行业名称作为列标题

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