Pivot wider to one row in R

user_4578075 0
Viewed 297

Here is the sample code that I am using

  library(dplyr)

  naics <- c("000000","000000",123000,123000)
  year <- c(2020,2021,2020,2021)
  January <- c(250,251,6,9)
  February <- c(252,253,7,16)
  March <- c(254,255,8,20)

  sample2 <- data.frame (naics, year, January, February, March)

Here is the intended result

                 Jan2020        Feb2020    March2020      Jan2021        Feb2021   March2021   
 000000            250             252          254          251            253           255
 123000             6                7           8            9              16            20

Is this something that is done with pivot_wider or is it more complex?

tidyr dplyr pivot r
3 Answers

We use pivot_wider by selecting the values_from with the month column, names_from as 'year' and then change the column name format in names_glue and if needed convert the 'naics' to row names with column_to_rownames (from tibble)

library(tidyr)
library(tibble)
pivot_wider(sample2, names_from = year, values_from = January:March, 
      names_glue = "{substr(.value, 1, 3)}{year}")%>% 
   column_to_rownames('naics')

-output

       Jan2020 Jan2021 Feb2020 Feb2021 Mar2020 Mar2021
000000     250     251     252     253     254     255
123000       6       9       7      16       8      20
user_37322710

With reshape function from BaseR,

reshape(sample2, dir = "wide", sep="",
        idvar = "naics", 
        timevar = "year",
        new.row.names = unique(naics))[,-1]

#        January2020 February2020 March2020 January2021 February2021 March2021
# 000000         250          252       254         251          253       255
# 123000           6            7         8           9           16        20
user_72243540

This takes a longer route than @akrun's answer. I will leave this here in case it may help with more intuition on the steps being taken. Otherwise, @akrun's answer is more resource efficient.

sample2 %>% 
   tidyr::pivot_longer(-c(naics, year), names_to = "month", 
                       values_to = "value") %>% 
   mutate(Month=paste0(month, year)) %>% 
   select(-year, - month) %>% 
   tidyr::pivot_wider(names_from = Month,values_from = value)


   # A tibble: 2 x 7
  naics  January2020 February2020 March2020 January2021 February2021
  <chr>        <dbl>        <dbl>     <dbl>       <dbl>        <dbl>
1 000000         250          252       254         251          253
2 123000           6            7         8           9           16
# ... with 1 more variable: March2021 <dbl>
user_103237980
Related