使用R语言reshape()函数实现物种列表长格式转宽格式遇阻求助

问题背景 #

尝试用R语言的reshape()函数将物种观测的长格式数据转换为宽格式，多次调整代码未成功，尝试了以下两种写法：

reshape(idvar = c("Site_ID", "date.only", "am.pm", "reserve", "on.VS.off", "Wind_strength", 
                  "Cloud_cover", "Feeding_resources", "Moisture_conditions"), 
        v.names = "English_Name", direction = "wide", data = data)

# 第二种写法
reshape(idvar = c("Site_ID", "date.only", "am.pm", "reserve", "on.VS.off", "Wind_strength", 
                  "Cloud_cover", "Feeding_resources", "Moisture_conditions"), 
        timevar = "English_Name", direction = "wide", data = data)

原始数据（可复现） #

origional <- structure(list(Site_ID = c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 1L, 
2L, 2L, 2L), date.only = c("11/12/2023", "11/12/2023", "11/12/2023", 
"11/12/2023", "11/12/2023", "11/12/2023", "11/12/2023", "11/12/2023", 
"11/12/2023", "11/12/2023", "11/12/2023", "11/12/2023"), am.pm = c("am", 
"am", "am", "am", "am", "am", "am", "pm", "pm", "pm", "pm", "pm"
), reserve = c("a", "a", "a", "b", "b", "b", "b", "a", "a", "b", 
"b", "b"), on.VS.off = c("on", "on", "on", "off", "off", "off", 
"off", "on", "on", "off", "off", "off"), Wind_strength = c("branches", 
"branches", "branches", "twigs", "twigs", "twigs", "twigs", "leaves", 
"leaves", "branches", "branches", "branches"), Cloud_cover = c(2L, 
2L, 2L, 1L, 1L, 1L, 1L, 0L, 0L, 4L, 4L, 4L), Feeding_resources = c("low", 
"low", "low", "medium", "medium", "medium", "medium", "low", 
"low", "medium", "medium", "medium"), Moisture_conditions = c("no rain", 
"no rain", "no rain", "last 24hrs", "last 24hrs", "last 24hrs", 
"last 24hrs", "no rain", "no rain", "last 24hrs", "last 24hrs", 
"last 24hrs"), English_name = c("Australian Magpie", "Silver Gull", 
"European Starling", "Australian Magpie", "Silver Gull", "Masked Lapwing", 
"Galah", "Australian Magpie", "Silver Gull", "Masked Lapwing", 
"Silver Gull", "Magpie Lark"), count = c(1L, 3L, 6L, 2L, 4L, 
1L, 5L, 1L, 1L, 2L, 4L, 2L)), class = "data.frame", row.names = c(NA, 
-12L))

# 查看原始数据
origional

原始数据展示：

Site_ID  date.only am.pm reserve on.VS.off Wind_strength Cloud_cover Feeding_resources Moisture_conditions      English_name count
1        1 11/12/2023    am       a        on      branches           2               low             no rain Australian Magpie     1
2        1 11/12/2023    am       a        on      branches           2               low             no rain       Silver Gull     3
3        1 11/12/2023    am       a        on      branches           2               low             no rain European Starling     6
4        2 11/12/2023    am       b       off         twigs           1            medium          last 24hrs Australian Magpie     2
5        2 11/12/2023    am       b       off         twigs           1            medium          last 24hrs       Silver Gull     4
6        2 11/12/2023    am       b       off         twigs           1            medium          last 24hrs    Masked Lapwing     1
7        2 11/12/2023    am       b       off         twigs           1            medium          last 24hrs             Galah     5
8        1 11/12/2023    pm       a        on        leaves           0               low             no rain Australian Magpie     1
9        1 11/12/2023    pm       a        on        leaves           0               low             no rain       Silver Gull     1
10       2 11/12/2023    pm       b       off      branches           4            medium          last 24hrs    Masked Lapwing     2
11       2 11/12/2023    pm       b       off      branches           4            medium          last 24hrs       Silver Gull     4
12       2 11/12/2023    pm       b       off      branches           4            medium          last 24hrs       Magpie Lark     2

期望输出格式（可复现） #

desired <- structure(list(Site_ID = c(1L, 2L, 1L, 2L), date.only = c("11/12/2023", 
"11/12/2023", "11/12/2023", "11/12/2023"), am.pm = c("am", "am", 
"pm", "pm"), reserve = c("a", "b", "a", "b"), on.VS.off = c("on", 
"off", "on", "off"), Wind_strength = c("branches", "twigs", "leaves", 
"branches"), Cloud_cover = c(2L, 1L, 0L, 4L), Feeding_resources = c("low", 
"medium", "low", "medium"), Moisture_conditions = c("no rain", 
"last 24hrs", "no rain", "last 24hrs"), Australian.Magpie = c(1L, 
2L, 1L, NA), Silver.Gull = c(3L, 4L, 1L, 4L), European.Starling = c(6L, 
NA, NA, NA), Maked.Lapwing = c(NA, 1L, NA, 2L), Galah = c(NA, 
5L, NA, NA), Magpie.Lark = c(NA, NA, NA, 2L)), class = "data.frame", row.names = c(NA, 
-4L))

# 查看期望数据
desired

期望数据展示：

Site_ID  date.only am.pm reserve on.VS.off Wind_strength Cloud_cover Feeding_resources Moisture_conditions Australian.Magpie
1       1 11/12/2023    am       a        on      branches           2               low             no rain                 1
2       2 11/12/2023    am       b       off         twigs           1            medium          last 24hrs                 2
3       1 11/12/2023    pm       a        on        leaves           0               low             no rain                 1
4       2 11/12/2023    pm       b       off      branches           4            medium          last 24hrs                NA
  Silver.Gull European.Starling Maked.Lapwing Galah Magpie.Lark
1           3                 6            NA    NA          NA
2           4                NA             1     5          NA
3           1                NA            NA    NA          NA
4           4                NA             2    NA           2

问题分析与解决方案 #

你的代码存在两个关键问题：

1. 变量名大小写不匹配：原始数据中物种列名为English_name（小写n），但代码中写的是English_Name（大写N），导致函数无法识别目标列。
2. 未指定要展开的数值列：需要将count列作为每个物种对应的数值，因此要通过v.names指定count，而不是物种名称列。

正确的reshape代码 #

wide_data <- reshape(
  data = origional,
  idvar = c("Site_ID", "date.only", "am.pm", "reserve", "on.VS.off", 
            "Wind_strength", "Cloud_cover", "Feeding_resources", "Moisture_conditions"),
  timevar = "English_name",
  v.names = "count",
  direction = "wide"
)

# 查看结果
wide_data

替代方案：使用tidyr::pivot_wider（更直观） #

如果觉得base R的reshape函数参数繁琐，推荐使用tidyverse中的pivot_wider，语法更清晰：

library(tidyr)

wide_data_tidy <- origional %>%
  pivot_wider(
    id_cols = c(Site_ID, date.only, am.pm, reserve, on.VS.off, 
                Wind_strength, Cloud_cover, Feeding_resources, Moisture_conditions),
    names_from = English_name,
    values_from = count
  )

# 查看结果
wide_data_tidy

若需要将列名中的空格替换为点号（与期望格式一致），可添加列名转换逻辑：

wide_data_tidy <- origional %>%
  pivot_wider(
    id_cols = c(Site_ID, date.only, am.pm, reserve, on.VS.off, 
                Wind_strength, Cloud_cover, Feeding_resources, Moisture_conditions),
    names_from = English_name,
    values_from = count,
    names_transform = ~ gsub(" ", ".", .x)
  )

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