宽到长数据转换多列 [英] Wide to long data transformation multiple columns

问题描述

我有用于公司数据的宽格式数据框 df_wide

I have data frame df_wide for company data in a wide format

    df_wide <- data.frame(Company=c('CompanyA','CompanyB', 'CompanyC'),
             Industry=c('Manufacturing', 'Telecom', 'Services'),
             Sales.2015=c('100', '500', '1000'), 
             Sales.2016=c('110', '550', '1100'), 
             Sales.2017=c('120', '600', '1200'),
             EBITDA.2015=c('10', '50', '100'), 
             EBITDA.2016=c('11', '55', '110'),
             EBITDA.2017=c('12', '60', '120'))

        Company      Industry Sales.2015 Sales.2016 Sales.2017 EBITDA.2015 EBITDA.2016 EBITDA.2017
    1 CompanyA Manufacturing        100        110        120          10          11          12
    2 CompanyB       Telecom        500        550        600          50          55          60
    3 CompanyC      Services       1000       1100       1200         100         110         120

并且我希望将数据转换为像 df_long 这样的长格式

And I wish to transform the data into a long format like df_long

    df_long <- data.frame(Company=c('CompanyA', 'CompanyA', 'CompanyA', 'CompanyB', 'CompanyB','CompanyB','CompanyC','CompanyC', 'CompanyC'),
              Industry=c('Manufacturing','Manufacturing','Manufacturing','Telecom','Telecom','Telecom','Services','Services','Services'),
              Year=c('2015','2016','2017','2015','2016','2017','2015','2016','2017'),
              Sales=c('100','110','120','500', '550','600','1000','1100','1200'),
              EBITDA=c('10','11','12','50','55','60','100','110','120'))

       Company      Industry Year Sales EBITDA
    1 CompanyA Manufacturing 2015   100     10
    2 CompanyA Manufacturing 2016   110     11
    3 CompanyA Manufacturing 2017   120     12
    4 CompanyB       Telecom 2015   500     50
    5 CompanyB       Telecom 2016   550     55
    6 CompanyB       Telecom 2017   600     60
    7 CompanyC      Services 2015  1000    100
    8 CompanyC      Services 2016  1100    110
    9 CompanyC      Services 2017  1200    120

我尝试过使用 pivot_longer 并且它只使用一个变量就可以正常工作，但是在尝试同时调整销售额和 EBITDA 时却很挣扎.

I have tried with pivot_longer and it works fine with just one variable but struggling when trying to pivot both Sales and EBITDA.

    df_long2 <- df_wide %>% pivot_longer(cols = starts_with("Sales"),
                                 names_to = "Year",
                                 values_to = "Sales")

推荐答案

使用 pivot_longer

tidyr::pivot_longer(df_wide, 
                   cols = -c(Company, Industry), 
                   names_to = c(".value", "Year"),
                   names_sep = "\\.") %>% type.convert()

#  Company  Industry       Year Sales EBITDA
#  <fct>    <fct>         <int> <int>  <int>
#1 CompanyA Manufacturing  2015   100     10
#2 CompanyA Manufacturing  2016   110     11
#3 CompanyA Manufacturing  2017   120     12
#4 CompanyB Telecom        2015   500     50
#5 CompanyB Telecom        2016   550     55
#6 CompanyB Telecom        2017   600     60
#7 CompanyC Services       2015  1000    100
#8 CompanyC Services       2016  1100    110
#9 CompanyC Services       2017  1200    120

这篇关于宽到长数据转换多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！