Merge rows upwards, while missing values in cells of column

user_12847169 0
Viewed 75

I have some data read in from a poorly formatted pdf table, where the cells sometimes spans across several pages. This has left me with a dataframe that looks similar to this:

company_name <- c("company_a", NA, "company_a", "company_b", "company_b", NA)
text <- c("some_text", "text that should be in the above cell","some_text",  "some_text", "some_text","text that should be in the above cell")
more_text <- c("some_text", "text that should be in the above cell", "some_text",  "some_text", "some_text","text that should be in the above cell")
df <- data.frame(company_name, text, more_text)
company_name text more_text
company_a some_text some_text
NA text that should be in the above cell text that should be in the above cell
company_a some_text some_text
company_b some_text some_text
company_b some_text some_text
NA text that should be in the above cell text that should be in the above cell

How could I merge the rows that have a missing value where "company_name" should be, so it looks more like this and also loop it over all rows that start with NA:

company_name text more_text
company_a some_text + text that should be in the above cell some_text + text that should be in the above cell
company_a some_text some_text
company_b some_text some_text
company_b some_text + text that should be in the above cell some_text + text that should be in the above cell

I've tried the unheadr package, but I can't seem to figure out the correct function to use.

Edit: re-did the example for more clarity

na rows r merge
1 Answers

We create a logical column based on the NA elements (ind), then create the 'grp' by converting the 'ind' or (|) the lead of that column to numeric index with rleid, use fill to replace the NA elements with the previous non-NA in 'company_name, then use the grouping column and summarise across the other columns by pasteing the elements together

library(dplyr)
library(tidyr)
library(stringr)
library(data.table)
df %>% 
   mutate(ind = is.na(company_name), 
       grp = rleid(ind|lead(ind))) %>%
    fill(company_name) %>% 
    group_by(company_name, grp) %>% 
    summarise(across(contains('text'), str_c, collapse=" + "), .groups = 'drop') %>% 
    select(-grp)
# A tibble: 4 x 3
#  company_name text                                              more_text                                        
#  <chr>        <chr>                                             <chr>                                            
#1 company_a    some_text + text that should be in the above cell some_text + text that should be in the above cell
#2 company_a    some_text                                         some_text                                        
#3 company_b    some_text                                         some_text                                        
#4 company_b    some_text + text that should be in the above cell some_text + text that should be in the above cell

data

df <- data.frame(company_name = company_a, text, more_text)
user_37322710
Related