CODEWITHSUNDEEP
rjoinmergedata.tablerows
Miko
Miko

Reputation: 506

Merge / Join data.tables per row

I have the following data tables and I would like to make a single data table out of all three.

library(dplyr)
set.seed(123)

dt.Ger <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Germany = rnorm(365, 2, 1), check.names = FALSE)
dt.Aut <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Austria = rnorm(365, 4, 2), check.names = FALSE)
dt.Den <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Denmark = rnorm(365, 3, 1), check.names = FALSE)

dt.Ger <- dt.Ger %>%
  mutate(month = format(date, '%b'), 
         date = format(date, '%d')) %>%
  tidyr::pivot_wider(names_from = date, values_from = Germany)

dt.Aut <- dt.Aut %>%
  mutate(month = format(date, '%b'), 
         date = format(date, '%d')) %>%
  tidyr::pivot_wider(names_from = date, values_from = Austria)

dt.Den <- dt.Den %>%
  mutate(month = format(date, '%b'), 
         date = format(date, '%d')) %>%
  tidyr::pivot_wider(names_from = date, values_from = Denmark)

Now I would like to link all tables together, i.e. first dt.Ger, then possibly add two empty lines and then append dt.Aut, now add again two empty lines and finally add dt.Den. Ideally, it would be great if Germany were the first headline, then Austria (in the second empty line before dt.Aut) and then Denmark (in the second empty line before dt.Den).

So that I only have a single table as a return. This table should look something like this (I only did it with SnippingTool, so it only serves to explain):

enter image description here

EDIT: Using

l <- list(dt.Ger, dt.Aut, dt.Den)
l.result <- rbindlist(l)

yields to:

enter image description here

And I want to get an extra space/line/row (at the red parts) where Germany, Austria and Denmark is written.

Upvotes: 0

Views: 182

Answers (1)

ismirsehregal
ismirsehregal

Reputation: 33540

I'm still not sure, what you are trying to achive - for me it seems you are better of working with a list of data.tables.

Furthermore, I switched to using dcast instead of pivot_wider so you can drop tidyr / dplyr.

However, here is an approach inserting NAs inbetween the different data.tables using rbindlist:

library(data.table)
set.seed(123)

dt.Ger <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Germany = rnorm(365, 2, 1), check.names = FALSE)
dt.Aut <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Austria = rnorm(365, 4, 2), check.names = FALSE)
dt.Den <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                     Denmark = rnorm(365, 3, 1), check.names = FALSE)

# or rather date  ~ month?
dt.Ger[, c("month", "date") := list(format(date, '%b'), format(date, '%d'))]
dt.Ger <- dcast(dt.Ger, month ~ date, value.var = "Germany")

dt.Aut[, c("month", "date") := list(format(date, '%b'), format(date, '%d'))]
dt.Aut <- dcast(dt.Aut, month ~ date, value.var = "Austria")

dt.Den[, c("month", "date") := list(format(date, '%b'), format(date, '%d'))]
dt.Den <- dcast(dt.Den, month ~ date, value.var = "Denmark")

# use a list of data.tables:
recommended <- list(Germany = dt.Ger, Austria = dt.Aut, Denmark = dt.Den)

DT <- rbindlist(list(data.table(month = c("", "Germany")), dt.Ger, data.table(month = c("", "Austria")), dt.Aut, data.table(month = c("", "Denmark")), dt.Den), fill = TRUE) # [, V1 := NULL]
DT[,(names(DT)):= lapply(.SD, as.character), .SDcols = names(DT)]
for (j in seq_len(ncol(DT))){
  set(DT, which(is.na(DT[[j]])), j, "")
}

print(DT)

Upvotes: 2

Related Questions