时间戳

首页 > 解决方案 > 将多个长到宽的转换合并为一个

r - 将多个长到宽的转换合并为一个

问题描述

我需要将多列从长转换为宽，下面的步骤是我目前的做法。有没有办法将其推广到任意数量的列？在我当前的用例中，聚合和强制转换总是在var_1下面的代码中的相同变量上，最好但不是必要的解决方案将允许我var_1用于强制转换City和var_2强制转换Month。我对data.table,tidyr或基本 R 解决方案很好。谢谢

library(data.table)
library(tidyverse)
set.seed(100)
dat=data.frame(G1=sample(LETTERS[1:3],200,replace=TRUE),
               G2=sample(LETTERS[10:13],200,replace=TRUE),
               G3=sample(LETTERS[16:18],200,replace=TRUE),
               Month=rep(month.name[1:4],50),
               City=rep(c("LA","NYC","Charlotte","Bloomington"),50),
               var_1=sample.int(200,200),var_2=sample.int(200,200))

cityWide=dcast(setDT(dat), G1+G2+G3~ City,value.var="var_1",fill = 0L,fun.aggregate = sum)
monthWide=dcast(setDT(dat), G1+G2+G3~ Month,value.var="var_1",fill = 0L,fun.aggregate = sum)

wideData=cityWide %>% inner_join(monthWide,by=c("G1","G2","G3"))

标签： r

解决方案

也许我们可以使用tidyverse

library(dplyr)
library(tidyr)
dat %>%
    group_by(G1, G2, G3, City) %>% 
    mutate(Sum_var1 = sum(var_1, na.rm = TRUE)) %>% 
    group_by(G1, G2, G3, Month) %>% 
    mutate(SumMonth_var1 = sum(var_1, na.rm = TRUE)) %>% 
    ungroup %>%
    select(G1, G2, G3, Month, City, Sum_var1, SumMonth_var1) %>% 
    distinct %>%
    pivot_wider(names_from = c(Month, City), values_from = c(Sum_var1, SumMonth_var1))

推荐阅读