Merge multiple rows of dataframe together if followed by an empty row in R

Question

I have the following dataframe:

location <- "https://www.mofa.go.jp/announce/info/conferment/pdfs/2013_sp.pdf"
out <- tabulizer::extract_tables(location)
final <- do.call(rbind, out)
final <- as.data.frame(final) %>%
  janitor::row_to_names(row_number = 2) %>%
  janitor::clean_names()

Unfortunately, due to extraction issues with tabulizer::extract_table (see this thread), the dataframe is unclean.

A data point spans over multiple rows, followed by empty rows (rows 20 and 26 in the screenshot):

Is it possible to automatically merge mutliple rows together into a single row if they have an empty row afterwards (or if there is no row afterwards, as in the last row of the dataframe)?

In other words, rows 13-19 should form a single row, and rows 21-25 should also serve as a single row. The columns are correct.

I would be grateful for your help!

Answer 1

Data is messy because you can have empty rows between same group (rows 126 and 127). I've defined starting of a group when decoration != "". It would be easier to define groups with nationality because it has ( in it (problem are people from Taiwan).

library(tidyverse)
library(data.table)

tidyPage <- function(dt){
  
  setDT(dt)
  dt <- dt[, map(.SD, as.character)]
  dt[, flag := !decoration == ""]
  dt <- dt[which.max(flag):.N]
  dt[, group := rleid(flag)]
  dt[flag == TRUE, flag := c(TRUE, rep(FALSE, .N - 1)), by = group]
  dt[, group := cumsum(flag)]
  
  split(dt, dt$group) %>%
    map_dfr(~map_chr(select(.x, -flag, -group), str_c, collapse = " ")) %>%
    mutate(across(where(is.character), str_squish))
}

location <- "https://www.mofa.go.jp/announce/info/conferment/pdfs/2013_sp.pdf"
out <- tabulizer::extract_tables(location) %>%
  map(~
    as.data.frame(.x) %>%
      janitor::row_to_names(row_number = 2) %>%
      janitor::clean_names()
  ) %>%
  map_dfr(tidyPage)