数据

df<-
structure(list(CODE = c(1L, 12L, 8L), NAME = c("A", "B", "C"), 
    M_2010_1 = c(10L, 11L, 9L), M_2011_1 = c(11L, 13L, 2L), M_2012_1 = c(10L, 
    15L, 4L), M_2010_3 = c(9L, 15L, 2L), M_2011_3 = c(10L, 14L, 
    8L), M_2012_3 = c(13L, 11L, 8L)), class = "data.frame", row.names = c(NA, 
-3L))

代码

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = -c(CODE,NAME),names_sep = "_",names_to = c("aux1","YEAR","aux2")) %>% 
  unite(aux,aux1,aux2,sep = "") %>% 
  pivot_wider(names_from = aux,values_from = value)

输出

# A tibble: 9 x 5
   CODE NAME  YEAR     M1    M3
  <int> <chr> <chr> <int> <int>
1     1 A     2010     10     9
2     1 A     2011     11    10
3     1 A     2012     10    13
4    12 B     2010     11    15
5    12 B     2011     13    14
6    12 B     2012     15    11
7     8 C     2010      9     2
8     8 C     2011      2     8
9     8 C     2012      4     8

使用reshape的一个线性函数，允许定义所有函数。

reshape(dat, idv=1:2, var=list(3:5, 6:8), dir='long', timev='YEAR', times=2010:2012, v.n=c('M1', 'M2'))
#           CODE NAME YEAR M1 M2
# 1.A.2010     1    A 2010 10  9
# 12.B.2010   12    B 2010 11 15
# 8.C.2010     8    C 2010  9  2
# 1.A.2011     1    A 2011 11 10
# 12.B.2011   12    B 2011 13 14
# 8.C.2011     8    C 2011  2  8
# 1.A.2012     1    A 2012 10 13
# 12.B.2012   12    B 2012 15 11
# 8.C.2012     8    C 2012  4  8

• 数据：*

dat <- structure(list(CODE = c(1L, 12L, 8L), NAME = c("A", "B", "C"), 
    M_2010_1 = c(10L, 11L, 9L), M_2011_1 = c(11L, 13L, 2L), M_2012_1 = c(10L, 
    15L, 4L), M_2010_3 = c(9L, 15L, 2L), M_2011_3 = c(10L, 14L, 
    8L), M_2012_3 = c(13L, 11L, 8L)), class = "data.frame", row.names = c(NA, 
-3L))

我们可以在重新排列列名中的子字符串之后在pivot_longer中执行此操作

library(dplyr)
library(stringr)
library(tidyr)
df1 %>% 
  rename_with(~ str_replace(.x, "_(\\d+)_(\\d+)", "\\2_\\1"), 
     starts_with("M_")) %>% 
  pivot_longer(cols = starts_with("M"), 
     names_to = c(".value", "year"), names_sep = "_")

• 输出

# A tibble: 9 × 5
   CODE NAME  year     M1    M3
  <int> <chr> <chr> <int> <int>
1     1 A     2010     10     9
2     1 A     2011     11    10
3     1 A     2012     10    13
4    12 B     2010     11    15
5    12 B     2011     13    14
6    12 B     2012     15    11
7     8 C     2010      9     2
8     8 C     2011      2     8
9     8 C     2012      4     8

数据

df1 <- structure(list(CODE = c(1L, 12L, 8L), NAME = c("A", "B", "C"), 
    M_2010_1 = c(10L, 11L, 9L), M_2011_1 = c(11L, 13L, 2L), M_2012_1 = c(10L, 
    15L, 4L), M_2010_3 = c(9L, 15L, 2L), M_2011_3 = c(10L, 14L, 
    8L), M_2012_3 = c(13L, 11L, 8L)), class = "data.frame", row.names = c(NA, 
-3L))