I have data like sampledata reproduced using the code below:
###producing sample data
sampledatain1 <- data.frame(in_1=c(1,2,3,4,5),in_2=c("A","B","C","D","E"),stringsAsFactors = F)
sampledatain2 <- data.frame(in_1=c(6,7,8,9,10),in_2=c("F","G","H","I","J"),stringsAsFactors = F)
sampledatain3 <- data.frame(in_1=c(99,98,97,96,95),in_2=c("Z","Y","X","W","V"),stringsAsFactors = F)
sampledata <- data.frame(row = 1:3,
colmerge1 = c("MA","MB","MC"),
colmerge2 = -99:-97,
stringsAsFactors = FALSE)
sampledata$df <- list(sampledatain1,sampledatain2,sampledatain3)
I want to flatten all the nested dataframes and combine them so the result looks like desired_data:
###reproducing desired data
library(data.table)
sampledatain1$row <- 1
sampledatain1$colmerge1 <- "MA"
sampledatain1$colmerge2 <- -99
sampledatain2$row <- 2
sampledatain2$colmerge1 <- "MB"
sampledatain2$colmerge2 <- -98
sampledatain3$row <- 3
sampledatain3$colmerge1 <- "MC"
sampledatain3$colmerge2 <- -97
desired_data <- rbindlist(list(sampledatain1,sampledatain2,sampledatain3))
How can I do this using a function to produce desired_data from sampledata?
The thing you're looking for is unnest() (unnest_longer() then unpack()/unnest_wider() works too):
> tidyr::unnest(sampledata, df)
# A tibble: 15 × 5
row colmerge1 colmerge2 in_1 in_2
<int> <chr> <int> <dbl> <chr>
1 1 MA -99 1 A
2 1 MA -99 2 B
3 1 MA -99 3 C
4 1 MA -99 4 D
5 1 MA -99 5 E
6 2 MB -98 6 F
7 2 MB -98 7 G
8 2 MB -98 8 H
9 2 MB -98 9 I
10 2 MB -98 10 J
11 3 MC -97 99 Z
12 3 MC -97 98 Y
13 3 MC -97 97 X
14 3 MC -97 96 W
15 3 MC -97 95 V
To convert it to a data.table, you can use as.data.table()
For more information on unnest(), read the documentation.