Create list of dataframes based on columns of dataframes in another list R

user_16870234 0
Viewed 65

I have a list of dataframes that look like this:

df_type1 <- data.frame(unit_name=c("A", "B", "C", "D"),
                   year_2014=c(653, 49, 563, 239),
                   year_2015=c(29, 63, 4879, 34),
                   year_2016=c(349, 340, 1, 47))

df_type2 <- data.frame(unit_name=c("A", "B", "C", "D"),
                   year_2014=c(573, 59, 563, 2999),
                   year_2015=c(39, 6390, 479, 98),
                   year_2016=c(5649, 640, 890, 12))

list_types <- list(df_type1, df_type2)

Here, values of units A to D correspond to a year.

From this list, I want to obtain a new list where each dataframe corresponds to the values of each separate year for the same units A to D. These new dataframes should look like this:

df_2014 <- data.frame(unit_name=c("A", "B", "C", "D"),
                      type1=c(653, 49, 563, 239),  # these are the values of the column year_2014 in df_type1
                      type2=c(573, 59, 563, 2999))

df_2015 <- data.frame(unit_name=c("A", "B", "C", "D"),
                      type1=c(29, 63, 4879, 34),
                      type2=c(39, 6390, 479, 98))

df_2016 <- data.frame(unit_name=c("A", "B", "C", "D"),
                      type1=c(349, 340, 1, 47),
                      type2=c(5649, 640, 890, 12))
dataframe r list
4 Answers

Something like?:

s <- c("type1", "type2")
lapply(2:4, function(i) cbind(list_types[[1]][1], setNames(sapply(list_types, `[`, i), s)))
#[[1]]
#  unit_name type1 type2
#1         A   653   573
#2         B    49    59
#3         C   563   563
#4         D   239  2999
#
#[[2]]
#  unit_name type1 type2
#1         A    29    39
#2         B    63  6390
#3         C  4879   479
#4         D    34    98
#
#[[3]]
#  unit_name type1 type2
#1         A   349  5649
#2         B   340   640
#3         C     1   890
#4         D    47    12

Or a little bit nicer and creating the data.frames.

. <- do.call(cbind, list_types)
i <- unique(grep("year", names(.), value = TRUE))
i <- setNames(i, sub("year", "df", i))
s <- paste0("type", seq_len(length(list_types)))
lapply(i, function(i) {
  cbind(.[1], setNames(.[i == names(.)], s))
})
#$df_2014
#  unit_name type1 type2
#1         A   653   573
#2         B    49    59
#3         C   563   563
#4         D   239  2999
#
#$df_2015
#  unit_name type1 type2
#1         A    29    39
#2         B    63  6390
#3         C  4879   479
#4         D    34    98
#
#$df_2016
#  unit_name type1 type2
#1         A   349  5649
#2         B   340   640
#3         C     1   890
#4         D    47    12
user_104885040

This should give:

list_types |> 
      purrr::map_dfr(~.x, .id = "TYPE") |> 
      tidyr::pivot_longer(!c(TYPE, unit_name )) |> 
      tidyr::pivot_wider(names_from  = TYPE, names_prefix = "type") |>  
      split(~name)

$year_2014
# A tibble: 4 × 4
  unit_name name      type1 type2
  <chr>     <chr>     <dbl> <dbl>
1 A         year_2014   653   573
2 B         year_2014    49    59
3 C         year_2014   563   563
4 D         year_2014   239  2999

$year_2015
# A tibble: 4 × 4
  unit_name name      type1 type2
  <chr>     <chr>     <dbl> <dbl>
1 A         year_2015    29    39
2 B         year_2015    63  6390
3 C         year_2015  4879   479
4 D         year_2015    34    98

$year_2016
# A tibble: 4 × 4
  unit_name name      type1 type2
  <chr>     <chr>     <dbl> <dbl>
1 A         year_2016   349  5649
2 B         year_2016   340   640
3 C         year_2016     1   890
4 D         year_2016    47    12
user_82826740

Here is a data.table option using rbindlist + melt + dcast + split

library(data.table)

split(
    dcast(
        melt(
            setDT(
                rbindlist(
                    Map(cbind,
                        type = paste0("type", seq_along(list_types)),
                        list_types
                    )
                )
            ),
            c("type", "unit_name"),
            variable.name = "year"
        ), unit_name + year ~ type
    ),
    by = "year"
)

which gives

$year_2014
   unit_name      year type1 type2
1:         A year_2014   653   573
2:         B year_2014    49    59
3:         C year_2014   563   563
4:         D year_2014   239  2999

$year_2015
   unit_name      year type1 type2
1:         A year_2015    29    39
2:         B year_2015    63  6390
3:         C year_2015  4879   479
4:         D year_2015    34    98

$year_2016
   unit_name      year type1 type2
1:         A year_2016   349  5649
2:         B year_2016   340   640
3:         C year_2016     1   890
4:         D year_2016    47    12
user_121587570 
   > # here we create a list with unique name of each df to use later with bind_rows
> 
> list_types <- list(df_type1 = df_type1, df_type2 = df_type2)
> 
> # Then we bind the rows for the dataframes since they have the same columns
> # Also using .id = "type" to give a variable name to each df
> 
> master_df <-  bind_rows(list_types, .id = "type")
> 
> head(master_df)
      type unit_name year_2014 year_2015 year_2016
1 df_type1         A       653        29       349
2 df_type1         B        49        63       340
3 df_type1         C       563      4879         1
4 df_type1         D       239        34        47
5 df_type2         A       573        39      5649
6 df_type2         B        59      6390       640
> 
> # we expand the df to make columns again for each df mapped to each year
> 
> expanded_df <- master_df %>%
+   pivot_wider(names_from = type, values_from = starts_with("year"))
> 
> head(expanded_df)
# A tibble: 4 x 7
  unit_name year_2014_df_type1 year_2014_df_type2 year_2015_df_type1 year_2015_df_type2
  <chr>                  <dbl>              <dbl>              <dbl>              <dbl>
1 A                        653                573                 29                 39
2 B                         49                 59                 63               6390
3 C                        563                563               4879                479
4 D                        239               2999                 34                 98
# ... with 2 more variables: year_2016_df_type1 <dbl>, year_2016_df_type2 <dbl>
> 
> # Then we get separate df for each year !
> 
> df_2014 <- expanded_df %>% select(unit_name, starts_with("year_2014"))
> df_2015 <- expanded_df %>% select(unit_name, starts_with("year_2015"))
> df_2016 <- expanded_df %>% select(unit_name, starts_with("year_2016"))
> 
> # we can easily clean their names:
>   
> names(df_2014) <- str_remove (names(df_2014), "year_2014")
> names(df_2015) <- str_remove (names(df_2015), "year_2015")
> names(df_2016) <- str_remove (names(df_2016), "year_2016")
> 
> df_2014
# A tibble: 4 x 3
  unit_name `_df_type1` `_df_type2`
  <chr>           <dbl>       <dbl>
1 A                 653         573
2 B                  49          59
3 C                 563         563
4 D                 239        2999
> 
> df_2015
# A tibble: 4 x 3
  unit_name `_df_type1` `_df_type2`
  <chr>           <dbl>       <dbl>
1 A                  29          39
2 B                  63        6390
3 C                4879         479
4 D                  34          98
> 
> df_2016
# A tibble: 4 x 3
  unit_name `_df_type1` `_df_type2`
  <chr>           <dbl>       <dbl>
1 A                 349        5649
2 B                 340         640
3 C                   1         890
4 D                  47          12
user_193358040
Related