Long to Wide Data with Tidyr

Long to wide data with tidyR?

Since tidyr 1.0.0 you can do the following:

library(tidyr)

df = data.frame(name=c("A","A","B","B"),
                group=c("g1","g2","g1","g2"),
                V1=c(10,40,20,30),
                V2=c(6,3,1,7))

pivot_wider(df, names_from = "group", values_from = c("V1", "V2"), names_sep = ".")
#> # A tibble: 2 x 5
#>   name  V1.g1 V1.g2 V2.g1 V2.g2
#>   <fct> <dbl> <dbl> <dbl> <dbl>
#> 1 A        10    40     6     3
#> 2 B        20    30     1     7

^{Created on 2019-09-14 by the reprex package (v0.3.0)}

Convert long data to wide in R

You can do this with pivot_wider from the tidyr package:

df |>
    dplyr::arrange(status) |> # Not necessary if order of columns not important
    tidyr::pivot_wider(
        names_from = status,
        values_from = time,
        names_prefix = "time_status"
    )

# # A tibble: 3 x 3
#     day time_status0 time_status1
#   <dbl>        <dbl>        <dbl>
# 1    24         2340        22313
# 2    25         2219        22106
# 3    26         2401        30192

Go from long to wide using tidyr's pivot_wider

Get data in long format first so that values of one and two are in same column and then cast it into wide format.

library(tidyr)

df %>%
  pivot_longer(cols = -fi) %>%
  pivot_wider(names_from = fi, values_from = value)

# name      A     B     C     D     E     F     G     H     I     J
#  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 one    0.5    1.4  0.89   1.4  1.45  1.25  1.45   1.4  1.4   1.5 
#2 two    0.75   1.6  1.05   1.6  1.45  1.05  1.65   1.5  1.55  1.65

With data.table :

library(data.table)
setDT(df)
dcast(melt(df, id.vars = 'fi'), variable ~fi, value.var = 'value')

Long to wide conversion using tidyr for grouped data

With tidyverse you can do the following. Enumerate the cases for each ID and then pivot_wider to put into wide format.

library(tidyverse)

df %>%
  group_by(ID) %>%
  mutate(N = row_number()) %>%
  pivot_wider(id_cols = ID, names_from = N, values_from = c(Case, Case_date))

Output

     ID Case_1 Case_2 Case_3 Case_date_1 Case_date_2 Case_date_3
  <dbl> <chr>  <chr>  <chr>  <chr>       <chr>       <chr>      
1     1 A      B      C      1-Sep       2-Sep       3-Sep      
2     2 D      NA     NA     4-Sep       NA          NA         
3     3 E      F      NA     5-Sep       6-Sep       NA     

dplyr and tidyr: convert long to wide format and arrange columns

We can change it to factor with levels specified

df %>%
  mutate(Factor = factor(Factor, levels = paste0("TK", 1:12))) %>%
  spread(Factor, Power)

---

Or make it more dynamic, we extract the non-numeric and numeric part into separate columns ('Factor1', 'Factor2'), change the 'Factor' to factor with levels specified by pasteing the sequence of min to max values in 'Factor2' with that of the first character value in 'Factor1', remove the 'Factor1' and 'Factor2', and spread.

library(tidyr)
res <- df %>%
         extract(Factor, into = c("Factor1", "Factor2"), "(\\D+)(\\d+)",
                                   remove = FALSE, convert=TRUE) %>% 
         mutate(Factor = factor(Factor, levels = paste0(Factor1[1], 
                              min(Factor2):max(Factor2)))) %>% 
         select(-Factor1, -Factor2) %>% 
         spread(Factor, Power)
head(res, 2)
#  Customer Rate TK1 TK2 TK3 TK4 TK5 TK6 TK7 TK8 TK9 TK10 TK11 TK12
#1       W1    6   5   0   0   0   1   0   0   0   0    0    0    0
#2       W2    3   0   1   5   3   0   0   0   0   0    0    0    0

Reshaping multiple long columns into wide column format in R

One option is the function pivot_wider() from the tidyr package:

df.wide <- tidyr::pivot_wider(df2,
                              names_from = c("Type", "Dept", "Year"),
                              values_from = "Salary",
                              values_fn = {mean})

This should get you the desired result.

How to reshape data from long to wide format

Using reshape function:

reshape(dat1, idvar = "name", timevar = "numbers", direction = "wide")

Long to wide format using variable names

We can usepivor_longer %>% pivot_wider. separateis not needed if we set the appropriate parameters to pivor_longer.

library(tidyr)

dataset %>%
        pivot_longer(cols = matches('time\\d+$'), names_to = c('sport', 'time'), names_pattern = '(.*)\\.(.*)') %>%
        pivot_wider(names_from = sport, values_from = value)

# A tibble: 15 × 5
      id time  basketball volleyball vollyeball
   <dbl> <chr>      <dbl>      <dbl>      <dbl>
 1     1 time1          2          2         NA
 2     1 time2          3          3         NA
 3     1 time3          1         NA          1
 4     2 time1          5          3         NA
 5     2 time2          4          4         NA
 6     2 time3          8         NA          8
 7     3 time1          4          4         NA
 8     3 time2          5          3         NA
 9     3 time3          4         NA         12
10     4 time1          3          0         NA
11     4 time2          3          1         NA
12     4 time3          3         NA          2
13     5 time1          3          1         NA
14     5 time2          2          3         NA
15     5 time3          1         NA          3

---

Related Topics