如何在R代码中对具有两级标题的数据框进行透视

Question

我对R还不太熟悉，这个问题困扰了我几天。我找到了一些解决方案，但我想要一个简短的代码，并且尽量避免使用循环，但目前这是我能想到的唯一方法。 我的数据如下： 我希望它看起来像这样：

Country    Year       ATM      POS  
France     2015        1       10    
France     2014        5        2    
France     2013       12        4     
France     2012        2       12   

我还没有找到一个简便的方法来解决这个问题，所以我将其分解成步骤：我禁用了标题，尝试将1行向量化，之后我打算先对年份进行长旋转，但是在此时出现了错误：

ATM <- pivot_longer(data = dat, cols = dat[,2:6],names_to = "Year",values_to = headervector) 

这也需要一个POS部分的循环。因此，除了修复这个问题，是否有更好的方法不需要循环呢？

非常感谢！

- Lea7885

---

请使用dput()函数将你的数据框(dat)输出并附在你的帖子中以便我们帮助你！ - Duck

@Duck是正确的；更具体地说，如果您[编辑]问题以包括R命令dput(dat)的输出（或者对于某些n，如果dat有许多行，则为dput(head(dat，n))），那么这将有助于我们帮助您。 - duckmayr

正如其他人所要求的那样，请使用 dput(head(dat, ...)) 来共享您实际使用的样本。然而，这也似乎与另一个最近的问题非常相似。R 的 data.frame 没有多个标题，因此您需要将多个标题级别粘贴在一起，就像我在 我的答案中所做的那样。 - A5C1D2H2I1M1N2O1R2T1

大家好，如果我的信息有点混乱，很抱歉。我正在导入一个xlsx文件，并尝试将其拆分为header<-("ATM","POS")和dat是整个表格。请原谅我的无知，dput是用来共享数据还是代码？非常感谢您的帮助！ - Lea7885

从“country”行开始阅读，读取数据后创建新的列名，然后进行“融合”。 - A5C1D2H2I1M1N2O1R2T1

@A5C1D2H2I1M1N2O1R2T1，谢谢。您的意思是在包含国家和年份的行上创建新的列名并使它们唯一吗？例如：2015.ATM、2014.ATM、、、2015.POS？ - Lea7885

Answer 1

当您读入数据时，使用选项指定从找到“Country”行开始读取。例如，您将使用startRow参数来执行read.xlsx操作 - 不确定您用于读取Excel的工具是什么。 ...代表read.xlsx中的任何其他参数。

x <- read.xlsx("your_file.xlsx", startRow = row_that_country_is_found, ...)

在完成此操作后，您可能需要清理列名。年份可能会被读取为“X2015”或类似的内容。我们假设“x”现在看起来像这样（随机数据）：

x
#   Country      2015      2014      2013      2012       2011      2010
# 1       A 0.6883601 0.9199372 0.8996433 0.9644212 0.97940387 0.7564401
# 2       B 0.1572208 0.6507811 0.9444197 0.9420349 0.06572698 0.1445383
# 3       C 0.7599602 0.8948640 0.6020316 0.7315661 0.90211468 0.5831917
#          2015       2014      2013      2012      2011       2010
# 1 0.26770837 0.45293675 0.2363191 0.9718356 0.3290432 0.57801166
# 2 0.39756729 0.06007054 0.7108505 0.6843454 0.1690740 0.93432731
# 3 0.05011677 0.30123347 0.2633371 0.5079645 0.9527117 0.04442355

从这里开始，您应该重新命名您的列。

names(x) <- c("Country", paste(rep(c("ATM", "POS"), each = 6),
              names(x)[-1], sep = "_"))

你的名称现在应该如下所示：

names(x)
#  [1] "Country"  "ATM_2015" "ATM_2014" "ATM_2013" "ATM_2012" "ATM_2011"
#  [7] "ATM_2010" "POS_2015" "POS_2014" "POS_2013" "POS_2012" "POS_2011"
# [13] "POS_2010"

然后，您可以使用pivot_longer函数将数据变为长格式，使用separate函数将名称分隔成多个变量，然后使用pivot_wider函数来获得所需的输出。

x %>% 
  pivot_longer(-Country) %>% 
  separate(name, into = c("Type", "Year")) %>% 
  pivot_wider(names_from = Type, values_from = value)
# # A tibble: 18 x 4
#    Country Year     ATM    POS
#    <chr>   <chr>  <dbl>  <dbl>
#  1 A       2015  0.688  0.268 
#  2 A       2014  0.920  0.453 
#  3 A       2013  0.900  0.236 
#  4 A       2012  0.964  0.972 
#  5 A       2011  0.979  0.329 
#  6 A       2010  0.756  0.578 
#  7 B       2015  0.157  0.398 
#  8 B       2014  0.651  0.0601
# ... 
# 17 C       2011  0.902  0.953 
# 18 C       2010  0.583  0.0444
          

- A5C1D2H2I1M1N2O1R2T1

---

非常感谢您抽出时间给我提供如此详尽的回复。它绝对有效，我会使用它！ - Lea7885

Answer 2

我们还可以使用来自data.table的melt，它也可以通过在列名中指定任何patterns来拆分。正则表达式^匹配列名的开头，后跟子字符串'ATM'或'POS'。因此，所有带有“ATM”的列将进入单个列“ATM”，而“POS”则作为下一个列。

library(data.table)
melt(setDT(df1), measure = patterns('^ATM', "^POS"), 
          value.name = c("ATM", "POS"), variable.name = "Year")

---

或者使用pivot_longer命令，加上names_sep参数并指定names_to参数，这样就可以在分隔符_处拆分列。 names_to的顺序表示'year'和该列中值（.value）的位置。 在这里，我们希望列名后缀为'year'，而前缀（在_之前）应该取得该列的值。

library(tidyr)
library(dplyr)
df1 %>%
    pivot_longer(cols = -Country, names_to = c(".value", "Year"), names_sep="_")
# A tibble: 18 x 4
#   Country Year     ATM    POS
#   <chr>   <chr>  <dbl>  <dbl>
# 1 A       2015  0.688  0.268 
# 2 A       2014  0.920  0.453 
# 3 A       2013  0.900  0.236 
# 4 A       2012  0.964  0.972 
# 5 A       2011  0.979  0.329 
# 6 A       2010  0.756  0.578 
# 7 B       2015  0.157  0.398 
# 8 B       2014  0.651  0.0601
# 9 B       2013  0.944  0.711 
#10 B       2012  0.942  0.684 
#11 B       2011  0.0657 0.169 
#12 B       2010  0.145  0.934 
#13 C       2015  0.760  0.0501
#14 C       2014  0.895  0.301 
#15 C       2013  0.602  0.263 
#16 C       2012  0.732  0.508 
#17 C       2011  0.902  0.953 
#18 C       2010  0.583  0.0444

注意：这里是基于_的。因此，它可以处理任意数量的列，并且与另一篇文章中描述的不同。

数据

df1 <- structure(list(Country = c("A", "B", "C"), ATM_2015 = c(0.6883601, 
0.1572208, 0.7599602), ATM_2014 = c(0.9199372, 0.6507811, 0.894864
), ATM_2013 = c(0.8996433, 0.9444197, 0.6020316), ATM_2012 = c(0.9644212, 
0.9420349, 0.7315661), ATM_2011 = c(0.97940387, 0.06572698, 0.90211468
), ATM_2010 = c(0.7564401, 0.1445383, 0.5831917), POS_2015 = c(0.26770837, 
0.39756729, 0.05011677), POS_2014 = c(0.45293675, 0.06007054, 
0.30123347), POS_2013 = c(0.2363191, 0.7108505, 0.2633371), POS_2012 = c(0.9718356, 
0.6843454, 0.5079645), POS_2011 = c(0.3290432, 0.169074, 0.9527117
), POS_2010 = c(0.57801166, 0.93432731, 0.04442355)), class = "data.frame",
row.names = c(NA, 
-3L))

- akrun

---

嘿，感谢您提供这个替代方案。我尝试了正则表达式部分，因为我喜欢代码的简洁性。然而，列名更改的方式遵循相反的模式：“ATM_2015”，所以正则表达式^在这里不起作用。您有什么建议吗？ - Lea7885

@Lea7885。我假设列名为“ATM_2015”，而不是“2015_ATM”，这就是我展示的方式。如果是“2015_ATM”，那么你的names_to顺序将是c("Year", ".value")。 - akrun

Answer 3

您可以使用此解决方案适用于任意数量的设备和年份，相比之前仅处理ATM和POS的解决方案。这是通过使用您的第一个数据屏幕（只需加载您的Excel文件）完成的。

library(xlsx)
library(zoo)
library(reshape2)
library(dplyr)
library(tidyr)

#Data
dat <- structure(list(NA. = structure(1:2, .Label = c("Country", "France"
), class = "factor"), ATM = c(2015, 12), NA..1 = c(2014, 3), 
    NA..2 = c(2013, 4), NA..3 = c(2012, 6), NA..4 = c(2011, 7
    ), NA..5 = c(2010, 8), POS = c(2015, 9), NA..6 = c(2014, 
    9), NA..7 = c(2013, 12), NA..8 = c(2012, 11), NA..9 = c(2011, 
    56), NA..10 = c(2010, 78)), class = "data.frame", row.names = c(NA, 
-2L))

#Process names
names(dat)[1]<-'Country'
vecnames <- names(dat)[-1]
vecnames[which(grepl('NA',vecnames))]<-NA
vecnames <- na.locf(vecnames)
#Include year
vecnames2 <- dat[1,-1]
#Join
vecnames3 <- paste0(vecnames,'_',vecnames2)
#Assign names
names(dat)[-1]<-vecnames3
#Remove first row
dat <- dat[-1,]
#Melt data
melt.data <- melt(dat,id.vars = 'Country')
melt.data$variable<-as.character(melt.data$variable)
#Create columns
melt.data %>%
  separate(variable, c("Device", "Year"), "_") -> melt.data
#Output
DataG <- reshape(melt.data, idvar=c('Country','Year'), timevar='Device', direction="wide")

输出：

  Country Year value.ATM value.POS
1  France 2015        12         9
2  France 2014         3         9
3  France 2013         4        12
4  France 2012         6        11
5  France 2011         7        56
6  France 2010         8        78

- Duck

---

谢谢你，你实际上预见到了我尝试akrun的解决方案时注意到的一些问题。它不能处理超过2个变量的情况。我实际上正在处理更多的变量，并且还将此代码应用于多个工作表。A5C1D2H2I1M1N2O1R2T1的第一个解决方案到目前为止已经奏效。我可能会同时使用两种方法:D - Lea7885

与NA..1、NA..2等不同，我有这种模式的名称：...5、...6、...7等。如何使步骤vecnames[which(grepl('NA', vecnames))] <- NA？ 我尝试过vecnames[which(grepl('...', vecnames))] <- NA，但所有名称都变成了NA。 - Danilo Imbimbo