Melt dataframes of two different lists into one list of data frames in r

1
votes

I have two lists of dataframes: list1 and list2. Below is a sample data frame from list1 (df1) and list2 (df2):

> print(df1)

         Moment.ext_multi.lane   Moment.ext_single.lane   Moment.int_multi.lane  Moment.int_single.lane
Baseline   0.7109148                  0.5367121               0.5874249               0.3718993
Sample1    0.7109148                  0.5367121               0.5874249               0.3718993
Sample2    0.7109148                  0.5367121               0.5874249               0.3718993
Sample3    0.7109148                  0.5367121               0.5874249               0.3718993
Sample4    0.7109148                  0.5367121               0.5874249               0.3718993
Sample5    0.7109148                  0.5367121               0.5874249               0.3718993
Sample6    0.7109148                  0.5367121               0.5874249               0.3718993
Sample7    0.7109148                  0.5367121               0.5874249               0.3718993
Sample8    0.7109148                  0.5367121               0.5874249               0.3718993
Sample9    0.7109148                  0.5367121               0.5874249               0.3718993
Sample10   0.7109148                  0.5367121               0.5874249               0.3718993
AASHTO     0.7550000                  NA                      0.6640000               0.4310000
Mean       0.7109148                  0.5367121               0.5874249               0.3718993

> print(df2)

         Shear.ext_multi.lane   Shear.ext_single.lane   Shear.int_multi.lane  Shear.int_single.lane
Baseline   0.7109148                  0.5367121               0.5874249               0.3718993
Sample1    0.7109148                  0.5367121               0.5874249               0.3718993
Sample2    0.7109148                  0.5367121               0.5874249               0.3718993
Sample3    0.7109148                  0.5367121               0.5874249               0.3718993
Sample4    0.7109148                  0.5367121               0.5874249               0.3718993
Sample5    0.7109148                  0.5367121               0.5874249               0.3718993
Sample6    0.7109148                  0.5367121               0.5874249               0.3718993
Sample7    0.7109148                  0.5367121               0.5874249               0.3718993
Sample8    0.7109148                  0.5367121               0.5874249               0.3718993
Sample9    0.7109148                  0.5367121               0.5874249               0.3718993
Sample10   0.7109148                  0.5367121               0.5874249               0.3718993
AASHTO     0.7550000                  NA                      0.6640000               0.4310000
Mean       0.7109148                  0.5367121               0.5874249               0.3718993

I want to merge the two lists into a new list of data frames and remove all rown is all dataframes with rownames that are called "Mean": list3.

Then I would like to melt the data of the list such that the dataframes in the new list have 4 columns.

The first columns is Source and if the rownames of the originals lists list1 and list 2 are "Sample1" to "Sample10", then the Source indicates Samples, if the rowname is "baseline" then Source indicates Baseline, and if the row name is "AASHTO" then Source indicates AASHTO as well.

The second column is Type and is extracting the end of the column names (removing "Moment." and "Shear." from the begining and ".lane" from the end).

The third column is Moment and includes the values of list1.

The fourth column is Shear and includes the values of list1.

The expected sample dataframe (df3) from final list list3 is:

> print(df2)
     Source        Type           Shear          Moment
1   Baseline     ext_multi      0.5367121      0.5874249
2   Baseline     ext_single     0.5367121      0.5874249    
3   Baseline     int_multi      0.5367121      0.5874249
4   Baseline     int_single     0.5367121      0.5874249
5   AASHTO       ext_multi      0.5367121      0.5874249
6   AASHTO       ext_single     0.5367121      0.5874249    
7   AASHTO       int_multi      0.5367121      0.5874249
8   AASHTO       int_single     0.5367121      0.5874249
9   AASHTO       int_single     0.5367121      0.5874249
5   Sample       ext_multi      0.5367121      0.5874249
6   Sample       ext_single     0.5367121      0.5874249    
7   Sample       int_multi      0.5367121      0.5874249
8   Sample       int_single     0.5367121      0.5874249
9   Sample       int_single     0.5367121      0.5874249
... continues
2
rdataframe

2 Answers

1
votes

We can use pivot_longer to reshape to 'long' format in both the list elements, then use map2 to loop over the corresponding elements of both lists and do a join

lst1new <-  map(lst1, ~
              .x %>% 
                 rownames_to_column("Source") %>% 
                 pivot_longer(cols = -Source, names_to = 'Type', 
                   values_to = 'Moment') %>% 
                 mutate(Type = str_replace(Type, '^\\w+\\.([^.]+)\\..*', '\\1')))

lst2new <-  map(lst2, ~
       .x %>% 
          rownames_to_column("Source") %>% 
          pivot_longer(cols = -Source, names_to = 'Type',
                values_to = 'Shear') %>%
          mutate(Type = str_replace(Type, '^\\w+\\.([^.]+)\\..*', '\\1')))

map2(lst1new, lst2new, full_join)
#[[1]]
# A tibble: 52 x 4
#   Source   Type       Moment Shear
# * <chr>    <chr>       <dbl> <dbl>
# 1 Baseline ext_multi   0.711 0.711
# 2 Baseline ext_single  0.537 0.537
# 3 Baseline int_multi   0.587 0.587
# 4 Baseline int_single  0.372 0.372
# 5 Sample1  ext_multi   0.711 0.711
# 6 Sample1  ext_single  0.537 0.537
# 7 Sample1  int_multi   0.587 0.587
# 8 Sample1  int_single  0.372 0.372
# 9 Sample2  ext_multi   0.711 0.711
#10 Sample2  ext_single  0.537 0.537
# … with 42 more rows

#[[2]]
# A tibble: 52 x 4
#   Source   Type       Moment Shear
# * <chr>    <chr>       <dbl> <dbl>
# 1 Baseline ext_multi   0.711 0.711
# 2 Baseline ext_single  0.537 0.537
# 3 Baseline int_multi   0.587 0.587
# 4 Baseline int_single  0.372 0.372
# 5 Sample1  ext_multi   0.711 0.711
# 6 Sample1  ext_single  0.537 0.537
# 7 Sample1  int_multi   0.587 0.587
# 8 Sample1  int_single  0.372 0.372
# 9 Sample2  ext_multi   0.711 0.711
#10 Sample2  ext_single  0.537 0.537
# … with 42 more rows

If we need to remove the numbers in 'Sample'

map2(lst1new, lst2new, ~ full_join(.x, .y) %>%
                         mutate(Source = str_remove(Source, "\\d+$")))

data

lst1 <- list(structure(list(Moment.ext_multi.lane = c(0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.755, 0.7109148), Moment.ext_single.lane = c(0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, NA, 0.5367121), Moment.int_multi.lane = c(0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.664, 0.5874249), 
    Moment.int_single.lane = c(0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.431, 0.3718993)), class = "data.frame", row.names = c("Baseline", 
"Sample1", "Sample2", "Sample3", "Sample4", "Sample5", "Sample6", 
"Sample7", "Sample8", "Sample9", "Sample10", "AASHTO", "Mean"
)), structure(list(Moment.ext_multi.lane = c(0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.755, 0.7109148), Moment.ext_single.lane = c(0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, NA, 0.5367121), Moment.int_multi.lane = c(0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.664, 0.5874249), 
    Moment.int_single.lane = c(0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.431, 0.3718993)), class = "data.frame", row.names = c("Baseline", 
"Sample1", "Sample2", "Sample3", "Sample4", "Sample5", "Sample6", 
"Sample7", "Sample8", "Sample9", "Sample10", "AASHTO", "Mean"
)))

lst2 <- list(structure(list(Shear.ext_multi.lane = c(0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.755, 0.7109148), Shear.ext_single.lane = c(0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, NA, 0.5367121), Shear.int_multi.lane = c(0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.664, 0.5874249), 
    Shear.int_single.lane = c(0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.431, 0.3718993)), class = "data.frame", row.names = c("Baseline", 
"Sample1", "Sample2", "Sample3", "Sample4", "Sample5", "Sample6", 
"Sample7", "Sample8", "Sample9", "Sample10", "AASHTO", "Mean"
)), structure(list(Shear.ext_multi.lane = c(0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 0.7109148, 
0.7109148, 0.7109148, 0.7109148, 0.755, 0.7109148), Shear.ext_single.lane = c(0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 0.5367121, 
0.5367121, 0.5367121, 0.5367121, 0.5367121, NA, 0.5367121), Shear.int_multi.lane = c(0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.5874249, 
0.5874249, 0.5874249, 0.5874249, 0.5874249, 0.664, 0.5874249), 
    Shear.int_single.lane = c(0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 0.3718993, 
    0.3718993, 0.3718993, 0.431, 0.3718993)), class = "data.frame", row.names = c("Baseline", 
"Sample1", "Sample2", "Sample3", "Sample4", "Sample5", "Sample6", 
"Sample7", "Sample8", "Sample9", "Sample10", "AASHTO", "Mean"
)))
1
votes

data.table

library(data.table)
merge(
  melt(df1, id.vars="Source",
       measure = patterns(m="^Moment.*"), value.name = "Moment", variable.name = "Type",
       variable.factor = FALSE, value.factor = FALSE)[, Type := gsub("^Moment\\.(.*)\\.lane", "\\1", Type) ],
  melt(df2, id.vars="Source",
       measure = patterns(m="^Shear.*"), value.name = "Shear", variable.name = "Type",
       variable.factor = FALSE, value.factor = FALSE)[, Type := gsub("^Shear\\.(.*)\\.lane", "\\1", Type) ],
  by = c("Source", "Type")
)
#       Source       Type    Moment     Shear
#  1:   AASHTO  ext_multi 0.7550000 0.7550000
#  2:   AASHTO ext_single        NA        NA
#  3:   AASHTO  int_multi 0.6640000 0.6640000
#  4:   AASHTO int_single 0.4310000 0.4310000
#  5: Baseline  ext_multi 0.7109148 0.7109148
#  6: Baseline ext_single 0.5367121 0.5367121
#  7: Baseline  int_multi 0.5874249 0.5874249
#  8: Baseline int_single 0.3718993 0.3718993
#  9:     Mean  ext_multi 0.7109148 0.7109148
# 10:     Mean ext_single 0.5367121 0.5367121
# 11:     Mean  int_multi 0.5874249 0.5874249
# 12:     Mean int_single 0.3718993 0.3718993
# 13:  Sample1  ext_multi 0.7109148 0.7109148
# 14:  Sample1 ext_single 0.5367121 0.5367121
# 15:  Sample1  int_multi 0.5874249 0.5874249
# 16:  Sample1 int_single 0.3718993 0.3718993
# 17: Sample10  ext_multi 0.7109148 0.7109148
# 18: Sample10 ext_single 0.5367121 0.5367121
# 19: Sample10  int_multi 0.5874249 0.5874249
# 20: Sample10 int_single 0.3718993 0.3718993
# 21:  Sample2  ext_multi 0.7109148 0.7109148
# 22:  Sample2 ext_single 0.5367121 0.5367121
# 23:  Sample2  int_multi 0.5874249 0.5874249
# 24:  Sample2 int_single 0.3718993 0.3718993
# 25:  Sample3  ext_multi 0.7109148 0.7109148
# 26:  Sample3 ext_single 0.5367121 0.5367121
# 27:  Sample3  int_multi 0.5874249 0.5874249
# 28:  Sample3 int_single 0.3718993 0.3718993
# 29:  Sample4  ext_multi 0.7109148 0.7109148
# 30:  Sample4 ext_single 0.5367121 0.5367121
# 31:  Sample4  int_multi 0.5874249 0.5874249
# 32:  Sample4 int_single 0.3718993 0.3718993
# 33:  Sample5  ext_multi 0.7109148 0.7109148
# 34:  Sample5 ext_single 0.5367121 0.5367121
# 35:  Sample5  int_multi 0.5874249 0.5874249
# 36:  Sample5 int_single 0.3718993 0.3718993
# 37:  Sample6  ext_multi 0.7109148 0.7109148
# 38:  Sample6 ext_single 0.5367121 0.5367121
# 39:  Sample6  int_multi 0.5874249 0.5874249
# 40:  Sample6 int_single 0.3718993 0.3718993
# 41:  Sample7  ext_multi 0.7109148 0.7109148
# 42:  Sample7 ext_single 0.5367121 0.5367121
# 43:  Sample7  int_multi 0.5874249 0.5874249
# 44:  Sample7 int_single 0.3718993 0.3718993
# 45:  Sample8  ext_multi 0.7109148 0.7109148
# 46:  Sample8 ext_single 0.5367121 0.5367121
# 47:  Sample8  int_multi 0.5874249 0.5874249
# 48:  Sample8 int_single 0.3718993 0.3718993
# 49:  Sample9  ext_multi 0.7109148 0.7109148
# 50:  Sample9 ext_single 0.5367121 0.5367121
# 51:  Sample9  int_multi 0.5874249 0.5874249
# 52:  Sample9 int_single 0.3718993 0.3718993
#       Source       Type    Moment     Shear

Data

df1 <- read.table(header=T, stringsAsFactors=F, text="
         Moment.ext_multi.lane   Moment.ext_single.lane   Moment.int_multi.lane  Moment.int_single.lane
Baseline   0.7109148                  0.5367121               0.5874249               0.3718993
Sample1    0.7109148                  0.5367121               0.5874249               0.3718993
Sample2    0.7109148                  0.5367121               0.5874249               0.3718993
Sample3    0.7109148                  0.5367121               0.5874249               0.3718993
Sample4    0.7109148                  0.5367121               0.5874249               0.3718993
Sample5    0.7109148                  0.5367121               0.5874249               0.3718993
Sample6    0.7109148                  0.5367121               0.5874249               0.3718993
Sample7    0.7109148                  0.5367121               0.5874249               0.3718993
Sample8    0.7109148                  0.5367121               0.5874249               0.3718993
Sample9    0.7109148                  0.5367121               0.5874249               0.3718993
Sample10   0.7109148                  0.5367121               0.5874249               0.3718993
AASHTO     0.7550000                  NA                      0.6640000               0.4310000
Mean       0.7109148                  0.5367121               0.5874249               0.3718993")
df1$Source <- rownames(df1)
rownames(df1) <- NULL
setDT(df1)

df2 <- read.table(header=T, stringsAsFactors=F, text="
         Shear.ext_multi.lane   Shear.ext_single.lane   Shear.int_multi.lane  Shear.int_single.lane
Baseline   0.7109148                  0.5367121               0.5874249               0.3718993
Sample1    0.7109148                  0.5367121               0.5874249               0.3718993
Sample2    0.7109148                  0.5367121               0.5874249               0.3718993
Sample3    0.7109148                  0.5367121               0.5874249               0.3718993
Sample4    0.7109148                  0.5367121               0.5874249               0.3718993
Sample5    0.7109148                  0.5367121               0.5874249               0.3718993
Sample6    0.7109148                  0.5367121               0.5874249               0.3718993
Sample7    0.7109148                  0.5367121               0.5874249               0.3718993
Sample8    0.7109148                  0.5367121               0.5874249               0.3718993
Sample9    0.7109148                  0.5367121               0.5874249               0.3718993
Sample10   0.7109148                  0.5367121               0.5874249               0.3718993
AASHTO     0.7550000                  NA                      0.6640000               0.4310000
Mean       0.7109148                  0.5367121               0.5874249               0.3718993")
df2$Source <- rownames(df2)
rownames(df2) <- NULL
setDT(df2)