CODEWITHSUNDEEP
rdplyrdata.tablereshapereshape2
Faryan
Faryan

Reputation: 409

Convert Data frame with different date in list table into single dataframe using R

I want to make table in list into single dataframe. I have data here

datex <- c("2018/01/01","2018/01/02","2018/01/03")
x1 <- c(101,102,103); varx1 <- data.frame(x1,datex)
datex <- c("2018/01/01","2018/01/02","2018/01/03","2018/01/04","2018/01/05")
x2 <- c(10,11,12,13,14); varx2 <- data.frame(x2,datex)
datex <- c("2018/01/01")
x3 <- c(1000); varx3 <- data.frame(x3,datex)
combination <- list(varx1,varx2,varx3)
combination

i want to have the result with "NULL" or "NA" like this

datex <- c("2018/01/01","2018/01/02","2018/01/03","2018/01/04","2018/01/05")
x1 <- c(101,102,103,"NULL","NULL")
x2 <- c(10,11,12,13,14)
x3 <- c(1000,"NULL","NULL","NULL","NULL")
answer <- data.frame(datex, x1,x2,x3)
answer

Need help please!

Upvotes: 1

Views: 246

Answers (1)

akrun
akrun

Reputation: 886968

We can use Reduce with merge from base R. The missing values will be NA instead of NULL

Reduce(function(...) merge(..., by = 'datex', all = TRUE), combination)
#       datex  x1 x2   x3
#1 2018/01/01 101 10 1000
#2 2018/01/02 102 11   NA
#3 2018/01/03 103 12   NA
#4 2018/01/04  NA 13   NA
#5 2018/01/05  NA 14   NA

A similar option with tidyverse would be

library(tidyverse)
combination %>%
      reduce(full_join, by = 'datex') %>%
      select(datex, everything())
#      datex  x1 x2   x3
#1 2018/01/01 101 10 1000
#2 2018/01/02 102 11   NA
#3 2018/01/03 103 12   NA
#4 2018/01/04  NA 13   NA
#5 2018/01/05  NA 14   NA

If we really need NULL, it can be placed within a list (not recommended)

combination %>%
      reduce(full_join, by = 'datex') %>%
      select(datex, everything()) %>%
      mutate_at(vars(matches('x\\d+')), funs(replace(., is.na(.), list(NULL))))
#    datex   x1 x2   x3
#1 2018/01/01  101 10 1000    
#2 2018/01/02  102 11 NULL
#3 2018/01/03  103 12 NULL
#4 2018/01/04 NULL 13 NULL
#5 2018/01/05 NULL 14 NULL

Upvotes: 4

Related Questions