时间戳

首页 > 解决方案 > 按键/值转置多列并将它们组合起来

r - 按键/值转置多列并将它们组合起来

问题描述

我有以下数据框：

date <- c(1,2,3,4,5,6,7)
value1 <-c(10,20,30,0,0,0,0)
key1 <- c(100,200,300,0,0,0,0)
value2 <- c(0,0,0,0,40,30,20)
key2 <- c(0,0,0,0,400,500,600)
df <- data.frame(date,value1,key1,value2,key2)

它看起来像这样：

date value1 key1 value2 key2
1    10     100  0      0
2    20     200  0      0
3    30     300  0      0
4    0      0    0      0   
5    0      0    40     400   
6    0      0    30     500    
7    0      0    20     600

我想像这样转换它：

date 100    200  300  400  500  600
1    10     0    0    0    0    0
2    0      20   0    0    0    0
3    10     0    30   0    0    0
4    10     0    0    0    0    0   
5    10     0    0    40   0    0   
6    10     0    0    0    30   0    
7    10     0    0    0    0    20

我可以像这样手动转置它们：

library(tidyr)
numbers_transpose <- df %>% spread(key = key1, value = value1)
numbers_transpose <- numbers_transpose %>% spread(key = key2, value = value2)

但我有 50 对，并希望将其自动化。如何以简单的方式做到这一点？

标签： r

解决方案

我们可以将其转换为“长”格式，pivot_longer然后将其重新整形

library(dplyr)
library(tidyr)
df %>%
    pivot_longer(cols = -date, names_to = c('.value', 'grp'), 
             names_sep= "(?<=[a-z])(?=[0-9])", values_drop_na = TRUE) %>%
    filter(key != 0) %>%
    pivot_wider(names_from = key, values_from = value,
           values_fill = list(value = 0)) %>%
    select(-grp)
# A tibble: 6 x 7
#   date `100` `200` `300` `400` `500` `600`
#  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1     1    10     0     0     0     0     0
#2     2     0    20     0     0     0     0
#3     3     0     0    30     0     0     0
#4     5     0     0     0    40     0     0
#5     6     0     0     0     0    30     0
#6     7     0     0     0     0     0    20

推荐阅读