Combining data frames horizontally in R with names

Question

I have a set of 12 data frames (DF1, DF2,... DF12) with a similar format like this:

> DF1
# A tibble: 12 x 6
   month     mean  max   min   sd        n
   <chr>     <chr> <chr> <chr> <chr> <int>
 1 January   1.22    x     x     x      x
 2 February  2.23    x     x     x      x
 3 March     4.86    x     x     x      x
 4 April     7.82    x     x     x      x
 5 May        -      -     -     -      x
 6 June       -      -     -     -      x
 7 July       -      -     -     -      x
 8 August     -      -     -     -      x
 9 September  -      -     -     -      x
10 October    -      -     -     -      x
11 November   -      -     -     -      x
12 December   -      -     -     -      x

The data frames are always similar (12X6) and the column names too (month, mean, max, min, sd, n). How can I do to combine the 12 data frames horizontally to obtain something like this (with the names of the DF above) :

                   "name of the DF1"                           "name of the DF2"                 "name of the DF3"             ....
   month     mean.x max.x min.x sd.x n.x   mean.y       max.y       min.y sd.y n.y
1  January      -     -     -    -   -    -           -           -    -   -
2  February     -     -     -    -   -    -           -           -    -   -
3  March        -     -     -    -   -    -           -           -    -   -
4  April        -     -     -    -   -    -           -           -    -   -
5  May          -     -     -    -   -    -           -           -    -   -
6  June         -     -     -    -   -    -           -           -    -   -
7  July         -     -     -    -   -    -           -           -    -   -
8  August       -     -     -    -   -    -           -           -    -   -
9  September    -     -     -    -   -    -           -           -    -   -
10 October      -     -     -    -   -    -           -           -    -   -
11 November     -     -     -    -   -    -           -           -    -   -
12 December     -     -     -    -   -    -           -           -    -   -

I have a list containing my 12 data frames :

var_names <- ls(envir = globalenv(), pattern = "^Final_analysis")
DF<- mget(var_names)

Hello :) I am wondering why you want to acheive this nice human-readible display in R. To perform further analysis, I think this is not recommended (see r4ds.had.co.nz/tidy-data.html). If you want a "nice looking document" I suggest you have a look to Rmarkdown and kable() like functions. — Paul

milanmlft milanmlft · Accepted Answer · 2020-06-16T09:51:37

As @Eyayaw mentioned, it's not possible to have the DF names appear above the column names.

However, we can get around this by pasting the DF names to the column names before combining everything with dplyr::bind_cols() as suggested by @grouah

One approach to do this using the dplyr and purrr packages:

library(dplyr)
library(purrr)

# Create NAMED list of dataframes
df_list <- list("mtcars1" = mtcars, "mtcars2" = mtcars, "mtcars3" = mtcars)
# Add name of df to colnames
df_list <- imap(df_list, function(df, name) {
    colnames(df) <- paste(colnames(df), name, sep = "_")
    df
})
# Check result
map(df_list, colnames)
#> $mtcars1
#>  [1] "mpg_mtcars1"  "cyl_mtcars1"  "disp_mtcars1" "hp_mtcars1"   "drat_mtcars1"
#>  [6] "wt_mtcars1"   "qsec_mtcars1" "vs_mtcars1"   "am_mtcars1"   "gear_mtcars1"
#> [11] "carb_mtcars1"
#> 
#> $mtcars2
#>  [1] "mpg_mtcars2"  "cyl_mtcars2"  "disp_mtcars2" "hp_mtcars2"   "drat_mtcars2"
#>  [6] "wt_mtcars2"   "qsec_mtcars2" "vs_mtcars2"   "am_mtcars2"   "gear_mtcars2"
#> [11] "carb_mtcars2"
#> 
#> $mtcars3
#>  [1] "mpg_mtcars3"  "cyl_mtcars3"  "disp_mtcars3" "hp_mtcars3"   "drat_mtcars3"
#>  [6] "wt_mtcars3"   "qsec_mtcars3" "vs_mtcars3"   "am_mtcars3"   "gear_mtcars3"
#> [11] "carb_mtcars3"

# bind_cols() everything together
combined <- bind_cols(df_list)
colnames(combined)
#>  [1] "mpg_mtcars1"  "cyl_mtcars1"  "disp_mtcars1" "hp_mtcars1"   "drat_mtcars1"
#>  [6] "wt_mtcars1"   "qsec_mtcars1" "vs_mtcars1"   "am_mtcars1"   "gear_mtcars1"
#> [11] "carb_mtcars1" "mpg_mtcars2"  "cyl_mtcars2"  "disp_mtcars2" "hp_mtcars2"  
#> [16] "drat_mtcars2" "wt_mtcars2"   "qsec_mtcars2" "vs_mtcars2"   "am_mtcars2"  
#> [21] "gear_mtcars2" "carb_mtcars2" "mpg_mtcars3"  "cyl_mtcars3"  "disp_mtcars3"
#> [26] "hp_mtcars3"   "drat_mtcars3" "wt_mtcars3"   "qsec_mtcars3" "vs_mtcars3"  
#> [31] "am_mtcars3"   "gear_mtcars3" "carb_mtcars3"

^{Created on 2020-06-16 by the reprex package (v0.3.0)}

As an example I just used the mtcars data 3 times in the DF list, but this should work for any named list of data frames which have the same number of rows (number of columns can be different).

Combining data frames horizontally in R with names

3 Answers