CODEWITHSUNDEEP
rdplyrtidyverse
DanG
DanG

Reputation: 741

Append several tables into one with one different column in R

I have a data frame after making several tables I would like to create a data frame that combines all tables into one data frame in order to export to excel. The only issue is the first variable is different in each table so bind_rows will not work.

Dummy sample data:

df1 = data.frame(Id = c(11:16), date = seq(as.Date("2015-01-01"),as.Date("2015-01-6"),1))
df2 = data.frame(HH_size = c(1:6 ), date = seq(as.Date("2015-01-01"),as.Date("2015-01-6"),1) )

let's say I made these tables

df11<- df1 %>%
  dplyr::group_by(date) %>%
  count(Id) %>%
  tidyr::spread(date,n)

df22<- df2 %>%
  dplyr::group_by(date) %>%
  count(HH_size) %>%
  tidyr::spread(date,n)


df11

         Id `2015-01-01` `2015-01-02` `2015-01-03` `2015-01-04` `2015-01-05` `2015-01-06`
       <int>        <int>        <int>        <int>        <int>        <int>        <int>
1         11            1           NA           NA           NA           NA           NA
2         12           NA            1           NA           NA           NA           NA
3         13           NA           NA            1           NA           NA           NA
4         14           NA           NA           NA            1           NA           NA
5         15           NA           NA           NA           NA            1           NA
6         16           NA           NA           NA           NA           NA            1

This will not work

list <- c("df11" , "df22")
list %>% map_df(bind_rows)
Error: Argument 1 must have names

here is my desired output:

   label      cat `2015-01-01` `2015-01-02` `2015-01-03` `2015-01-04` `2015-01-05` `2015-01-06`

   Id         11            1           NA           NA           NA           NA           NA
   Id         12           NA            1           NA           NA           NA           NA
   Id         13           NA           NA            1           NA           NA           NA
   Id         14           NA           NA           NA            1           NA           NA
   Id         15           NA           NA           NA           NA            1           NA
   Id         16           NA           NA           NA           NA           NA            1
HH_size       1            1           NA           NA           NA           NA           NA
HH_size       2           NA            1           NA           NA           NA           NA
HH_size       3           NA           NA            1           NA           NA           NA
HH_size       4           NA           NA           NA            1           NA           NA
HH_size       5           NA           NA           NA           NA            1           NA
HH_size       6           NA           NA           NA           NA           NA            1

Upvotes: 0

Views: 76

Answers (2)

AnilGoyal
AnilGoyal

Reputation: 26218

This will serve your purpose.

  • . in dplyr/magrittr means result upto previous pipe. So names(.)[1] took out the name of first column and mutated it into a new column named label
  • Then again you needed first column back as cat. So I mutated a column cat with .x[[1]] which is first column of every iterated value passed on. I think you may also use . instead of .x as value just prior to pipe is .x only.
  • unselect first column
  • rearrange placement of these columns as desired.
map_df(list(df11, df22), ~.x %>% 
         mutate(label = names(.)[1],
                cat = .x[[1]]) %>% 
         select(-1) %>% 
         select(label, cat, everything()))

# A tibble: 12 x 8
   label     cat `2015-01-01` `2015-01-02` `2015-01-03` `2015-01-04` `2015-01-05` `2015-01-06`
   <chr>   <int>        <int>        <int>        <int>        <int>        <int>        <int>
 1 Id         11            1           NA           NA           NA           NA           NA
 2 Id         12           NA            1           NA           NA           NA           NA
 3 Id         13           NA           NA            1           NA           NA           NA
 4 Id         14           NA           NA           NA            1           NA           NA
 5 Id         15           NA           NA           NA           NA            1           NA
 6 Id         16           NA           NA           NA           NA           NA            1
 7 HH_size     1            1           NA           NA           NA           NA           NA
 8 HH_size     2           NA            1           NA           NA           NA           NA
 9 HH_size     3           NA           NA            1           NA           NA           NA
10 HH_size     4           NA           NA           NA            1           NA           NA
11 HH_size     5           NA           NA           NA           NA            1           NA
12 HH_size     6           NA           NA           NA           NA           NA            1

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 388817

Put all the dataframes in a list and then you can do :

library(tidyverse)

list_df <- lst(df1, df2)

map_df(list_df, ~{
  col <- names(.x)[1]
  .x %>%
  count(.data[[col]], date) %>%
  pivot_wider(names_from = date, values_from = n) %>%
  mutate(label = col) %>%
  rename_with(~'cat', 1)
  })

#     cat `2015-01-01` `2015-01-02` `2015-01-03` `2015-01-04` `2015-01-05` `2015-01-06` label  
#   <int>        <int>        <int>        <int>        <int>        <int>        <int> <chr>  
# 1    11            1           NA           NA           NA           NA           NA Id     
# 2    12           NA            1           NA           NA           NA           NA Id     
# 3    13           NA           NA            1           NA           NA           NA Id     
# 4    14           NA           NA           NA            1           NA           NA Id     
# 5    15           NA           NA           NA           NA            1           NA Id     
# 6    16           NA           NA           NA           NA           NA            1 Id     
# 7     1            1           NA           NA           NA           NA           NA HH_size
# 8     2           NA            1           NA           NA           NA           NA HH_size
# 9     3           NA           NA            1           NA           NA           NA HH_size
#10     4           NA           NA           NA            1           NA           NA HH_size
#11     5           NA           NA           NA           NA            1           NA HH_size
#12     6           NA           NA           NA           NA           NA            1 HH_size

Upvotes: 2

Related Questions