How do I specify pivot_wider for an entire dataframe?

user_12317572 0
Viewed 418

I am able to pivot_wider for a specific column using the following:

new_df <- pivot_wider(old_df, names_from = col10, values_from = value_col, values_fn = list)

I would like to pivot_wider with every column in a dataframe (minus an id column). What is the best way to do this? Should I use a loop or is there a way that this function takes the whole dataframe?

To clarify, using the below sample dataframes, I am able to go from old_df to new_df using the pivot_wider function I listed above. I would like to now go from old_df2 to new_df2.

old_df <- structure(list(id = c("1", "1", "2"), col10 = c("yellow", 
"green", "green"), value_col = c("1", "1", "1")), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))

old_df2 <- structure(list(id = c("1", "1", "2"), col10 = c("yellow", 
"green", "green"), col11 = c("dog", 
"cat", "dog"), value_col = c("1", "1", "1")), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))

new_df <- pivot_wider(old_df, names_from = col10, values_from = value_col, values_fn = list)

new_df2 <- structure(list(id = c("1", "2"), yellow = c("1", "NULL"), green = c("1", "1"), dog = c("1", "1"), cat = c("1", "NULL")), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"))
tidyr reshape dataframe pivot r
3 Answers

If you would like to have separate column names for each value between these two columns (or any number of columns) you first need to use pivot_longer to put all the column names into a single column and then use pivot_wider to spread them:

library(tidyr)

old_df2 %>%
  pivot_longer(!c(id, value_col), names_to = "Cols", values_to = "vals") %>%
  pivot_wider(names_from = vals, values_from = value_col) %>%
  select(-Cols) %>%
  group_by(id) %>%
  summarise(across(everything(), ~ sum(as.numeric(.x), na.rm = TRUE)))

# A tibble: 2 x 5
  id    yellow   dog green   cat
  <chr>  <dbl> <dbl> <dbl> <dbl>
1 1          1     1     1     1
2 2          0     1     1     0
user_143145200

Update 1

As per your update, here comes with a data.table option

dcast(
  melt(setDT(old_df),
    id.var = "id",
    measure.vars = patterns("^col\\d+")
  ),
  id ~ value,
  fun.aggregate = length,
  fill = NA
)

which gives

   id cat dog green yellow
1:  1   1   1     1      1
2:  2  NA   1     1     NA

Are you looking for something like below?

reshape(
  transform(
    old_df,
    q = ave(id, id, FUN = seq_along)
  ),
  direction = "wide",
  idvar = "id",
  timevar = "q"
)

The output is

  id col10.1 col11.1 value_col.1 col10.2 col11.2 value_col.2
1  1  yellow     dog           1   green     cat           1
3  2   green     dog           1    <NA>    <NA>        <NA>
user_121587570

You could combine those columns and unnest them followed by pivot_wider:

library(tidyr)
library(dplyr)

old_df2 <- structure(list(id = c("1", "1", "2"), col10 = c("yellow", 
                                                           "green", "green"), col11 = c("dog", 
                                                                                        "cat", "dog"), value_col = c("1", "1", "1")), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))


old_df2 %>% 
  mutate(new_col = strsplit(paste(col10, col11, sep = "_"), "_"), .keep = "unused") %>% 
  unnest(new_col) %>% 
  pivot_wider(names_from = new_col, values_from = value_col)

#> # A tibble: 2 x 5
#>   id    yellow dog   green cat  
#>   <chr> <chr>  <chr> <chr> <chr>
#> 1 1     1      1     1     1    
#> 2 2     <NA>   1     1     <NA>

Created on 2021-08-25 by the reprex package (v2.0.1)

user_125052510
Related