多列月份数据转单列日期：宽表转长表的实现方案求助

Google Sheets 数组公式方案 #

假设原始数据位于A1:AZ6001（A列为Variable，B1:AZ1为Apr-19格式的月份表头，A2:AZ6001为数据），在空白单元格（比如BA1）输入以下公式，自动生成目标长表：

=ARRAYFORMULA(
  LET(
    raw_data, A2:AZ6001,
    keywords, INDEX(raw_data,,1),
    month_headers, B1:AZ1,
    dates, TEXT(DATEVALUE(month_headers&"-01"), "yyyy-mm-dd"),
    values_range, INDEX(raw_data,,SEQUENCE(1,COLUMNS(month_headers),2)),
    expanded_keywords, FLATTEN(IF(values_range<>"", keywords, "")),
    expanded_dates, FLATTEN(TRANSPOSE(IF(values_range<>"", dates, ""))),
    expanded_values, FLATTEN(values_range),
    FILTER({expanded_dates, expanded_keywords, expanded_values}, expanded_values<>"")
  )
)

关键说明： #

• 自动将Apr-19格式的表头转换为2019-04-01标准日期
• 过滤掉空值行，避免无效数据
• 若原始数据范围不同，修改A2:AZ6001和B1:AZ1为实际区域即可

R (Tidyverse) 方案 #

解决你之前pivot_longer失败的常见问题（日期格式不匹配、列选择错误），代码如下：

library(tidyverse)
library(lubridate)

# 读取Google Sheets数据（需安装googlesheets4包）
# gs4_deauth() # 公开表格无需授权
# df <- read_sheet("你的表格URL", sheet = "工作表名称")

# 宽表转长表
df_long <- df %>%
  # 除Variable列外，所有列转为行
  pivot_longer(cols = -Variable, names_to = "Date", values_to = "Volume") %>%
  # 转换日期格式：Apr-19 → 2019-04-01
  mutate(
    Date = parse_date_time(Date, orders = "my", locale = "en_US") %>% as.Date(),
    Keyword = Variable # 重命名为目标列名
  ) %>%
  # 保留目标列并过滤空值
  select(Date, Keyword, Volume) %>%
  drop_na(Volume)

# 导出回Google Sheets（可选）
# write_sheet(df_long, "你的表格URL", sheet = "长表结果")

关键说明： #

• 使用lubridate::parse_date_time兼容不同大小写的月份（如APR-19或apr-19）
• 确保cols = -Variable正确排除了不需要转置的列

Python (Pandas) 方案 #

适合处理大规模数据，代码如下：

import pandas as pd
import gspread
from google.auth import default
import google.auth

# 授权访问Google Sheets（Colab环境）
google.auth.default()
gc = gspread.authorize(google.auth.default()[0])

# 读取原始数据
worksheet = gc.open("你的表格名称").worksheet("工作表名称")
data = worksheet.get_all_values()
df = pd.DataFrame(data[1:], columns=data[0]) # 跳过表头

# 宽表转长表
df_long = df.melt(
    id_vars=["Variable"],
    var_name="Date",
    value_name="Volume"
)

# 处理日期格式与列名
df_long["Date"] = pd.to_datetime(df_long["Date"], format="%b-%y").dt.strftime("%Y-%m-%d")
df_long = df_long.rename(columns={"Variable": "Keyword"}).dropna(subset=["Volume"])

# 转换Volume为数值类型（可选）
df_long["Volume"] = pd.to_numeric(df_long["Volume"], errors="coerce")

# 导出回Google Sheets
worksheet_out = gc.open("你的表格名称").add_worksheet(title="长表结果", rows="300000", cols="3")
worksheet_out.update([df_long.columns.tolist()] + df_long.values.tolist())

Excel Power Query 方案 #

无需代码，可视化操作：

1. 选中原始数据区域（含表头），点击「数据」→「从表格/区域」，确认「我的表格有标题」。
2. 在Power Query编辑器中，选中Variable列，点击「转换」→「逆透视列」→「逆透视其他列」。
3. 重命名列：将「属性」改为Date，「值」改为Volume，「Variable」改为Keyword。
4. 选中Date列，点击「转换」→「数据类型」→「日期」，右键设置格式为yyyy-mm-dd。
5. 点击「主页」→「删除行」→「删除空值行」，最后点击「关闭并上载」到新工作表。

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