how to split a dataframe by specific rows in r

Question

I have a data look like this:

data <- structure(list(A = c("1", "1", "1", "A", "10", "10", "B", "200"), B = c("2", "2", "2", "B", "20", "20", "C", "300"), C = c("3","3", "3", "C", "30", "30", "D", "400"), D = c("4", "4", "4", "D", "40", "40", NA, NA)), row.names = c(NA, -8L), class = c("tbl_df","tbl", "data.frame"))
data

> data
# A tibble: 8 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 1     2     3     4    
2 1     2     3     4    
3 1     2     3     4    
4 A     B     C     D    
5 10    20    30    40   
6 10    20    30    40   
7 B     C     D     NA   
8 200   300   400   NA    

It was wrong bind by rows and I wanted to split the data into 3 sub data(d1, d2 and d3) such like this:

NOTE: In my real situation, d1, d2 and d3 have different nrow(). I set nrow(d1) = 3, nrow(d2) = 2 and nrow(d3) = 1 just for simplify the question in this example.

d1 <- data.frame(A = rep(1,3),  B = rep(2,3),   C = rep(3,3),   D = rep(4,3))
d2 <- data.frame(A = rep(10,2), B = rep(20,2),  C = rep(30,2),  D = rep(40,2))
d3 <- data.frame(        B = 200, C = 300, D = 400)

> d1
  A B C D
1 1 2 3 4
2 1 2 3 4
3 1 2 3 4
> d2
   A  B  C  D
1 10 20 30 40
2 10 20 30 40
> d3
    B   C   D
1 200 300 400

And then I could bind them correctly using bind_rows from dplyr

bind_rows(d1, d2, d3) %>% as_tibble()

# A tibble: 6 x 4
      A     B     C     D
  <dbl> <dbl> <dbl> <dbl>
1     1     2     3     4
2     1     2     3     4
3     1     2     3     4
4    10    20    30    40
5    10    20    30    40
6    NA   200   300   400

The problem is that I am troubled by how to get the d1, d2 and d3 from data.

Any help will be highly appreciated!

Answer 1

Here is a tidyverse solution.

process_df takes a data frame and sets the column names and removes the first row.

process_df <- function(df, ...) {
  df %>%
    set_names(slice(., 1)) %>%
    select(which(!is.na(names(.)))) %>%
    slice(-1)
}

Add a header row that just contains the column names.

Use rowwise() and c_across() to get the values of all columns by row. Use this to identify which rows are header rows.

group_map will apply a function over each group and bind_rows will combine the results.

data %>%
  add_row(!!!set_names(names(.)), .before = 1) %>%
  rowwise() %>%
  mutate(
    group = all(is.na(c_across()) | c_across() %in% names(.))
  ) %>%
  ungroup() %>%
  mutate(group = cumsum(group)) %>%
  group_by(group) %>%
  group_map(process_df) %>%
  bind_rows()
#> # A tibble: 6 x 4
#>   A     B     C     D    
#>   <chr> <chr> <chr> <chr>
#> 1 1     2     3     4    
#> 2 1     2     3     4    
#> 3 1     2     3     4    
#> 4 10    20    30    40   
#> 5 10    20    30    40   
#> 6 NA    200   300   400 

---

Explanation of the usage of !!! in new_row

set_names(names(.)) creates a named vector that represents the row we want to add. However, add_row doesn't accept a named vector - it wants the values to be specified as arguments.

Here is a simplified example.

new_row <- c(speed = 1, dist = 2)

add_row doesn't accept a named vector, so this doesn't work.

cars %>% add_row(new_row, .before = TRUE)
# (Error)

!!! will unpack the vector as arguments to the function.

cars %>% add_row(!!!new_row, .before = TRUE)
# (Works)

!!! above essentially results in this:

cars %>% add_row(speed = 1, dist = 2, .before = TRUE)

Answer 2

Base R solution:

Map(function(x){setNames(data.frame(t(x[,2, drop = FALSE])), x[,1])[,!is.na(x[,1])]},
  split.default(cbind(X0 = names(df), data.frame(t(df))), c(0, seq_len(nrow(df)) %/% 2)))

Including pushing separate data.frames to Global Environment:

list2env(setNames(Map(function(x){setNames(data.frame(t(x[,2, drop = FALSE])), x[,1])[,!is.na(x[,1])]},
  split.default(cbind(X0 = names(df), data.frame(t(df))), c(0, seq_len(nrow(df)) %/% 2))),
    paste0('d', seq_len(ceiling(nrow(df) / 2)))), .GlobalEnv)

Tidyverse Solution:

library(tidyverse)
df %>%
  rbind(names(df), .) %>%
  split(cumsum(seq_len(nrow(.)) %% 2)) %>%
  Map(function(x){setNames(x[2,], x[1,])[,complete.cases(t(x))]}, .) %>%
  set_names(str_c('d', names(.))) %>%
  list2env(., .GlobalEnv)

Note solution adjusted to reflect edit to the question:

rdf <- type.convert(data.frame(t(rbind(names(df), df))))

Map(function(x){
  y <- setNames(t(x[,-1, drop = FALSE]), x[,1]); y[,!is.na(colSums(y))]
}, split.default(rdf, cumsum(!sapply(rdf, is.integer))))

New solution including push to Global Env:

rdf <- type.convert(data.frame(t(rbind(names(df), df))))

dflist <- Map(function(x) {
  y <-
    setNames(t(x[, -1, drop = FALSE]), x[, 1])
  y[, !is.na(colSums(y))]
}, split.default(rdf, cumsum(!sapply(rdf, is.integer))))

list2env(setNames(dflist, paste0('d', names(dflist))), .GlobalEnv)

Adjusted Tidyverse solution:

df %>%
  rbind(names(.), .) %>%
  t() %>%
  data.frame() %>% 
  type.convert() %>%
  split.default(cumsum(!sapply(., is.integer))) %>%
  Map(function(x){
    y <- setNames(t(x[,-1, drop = FALSE]), x[,1])
    data.frame(y[,!is.na(colSums(y)), drop = FALSE])}, .) %>%
  set_names(str_c('d', names(.))) %>%
  list2env(., .GlobalEnv)

Data:

df <- structure(list(A = c("1", "A", "10", "B", "200"), B = c("2", "B", "20", "C", "300"), C = c("3", "C", "30", "D", "400"), D = c("4","D", "40", NA, NA)), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"))

Updated Data:

df <- structure(list(A = c("1", "1", "1", "A", "10", "10", "B", "200"), B = c("2", "2", "2", "B", "20", "20", "C", "300"), C = c("3","3", "3", "C", "30", "30", "D", "400"), D = c("4", "4", "4", "D", "40", "40", NA, NA)), row.names = c(NA, -8L), class = c("tbl_df","tbl", "data.frame"))

Answer 3

Does this work:

data
# A tibble: 5 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 1     2     3     4    
2 A     B     C     D    
3 10    20    30    40   
4 B     C     D     NA   
5 200   300   400   NA   
data <- rbind(LETTERS[1:4],data)
data
# A tibble: 6 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 A     B     C     D    
2 1     2     3     4    
3 A     B     C     D    
4 10    20    30    40   
5 B     C     D     NA   
6 200   300   400   NA   
split(data, rep(1:ceiling(nrow(data)/2), each = 2))
$`1`
# A tibble: 2 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 A     B     C     D    
2 1     2     3     4    

$`2`
# A tibble: 2 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 A     B     C     D    
2 10    20    30    40   

$`3`
# A tibble: 2 x 4
  A     B     C     D    
  <chr> <chr> <chr> <chr>
1 B     C     D     NA   
2 200   300   400   NA