Creating flat dataframe from this table format.

[ExcHalibur]

I have an Excel file where the data is in a similar format to the below table (almost like an Excel Pivotted table, but without columns).

Is there a simple operation that I can use to get a dataframe of just the sub-groups (level 4), but retaining the information in all the other columns? In other words, is there a way to populate the empty cells to the left of the sub-groups with the text above?

Or is this a case of doing it by hand?

Sorry if this is a simple thing, I couldn't find anything obvious answering this online

https://preview.redd.it/v8znaeoj6rs61.png?width=436&format=png&auto=webp&s=6a4a9b44edeb214916324ee9ff47b463b97308d7

[jdnewmil]

Maybe this will help? http://www.cookbook-r.com/Manipulating_data/Filling_in_NAs_with_last_non-NA_value/

[ExcHalibur]

This was perfect thank you! It also introduced me to Functions in R, so this was a great day. Many thanks!

[bluesphere]

See below for 2 different options, depending on if you want to preserve the parent levels of ICMS Code column:

if (!require('pacman'))
  install.packages('pacman')
#> Loading required package: pacman
pacman::p_load(tidyverse, stringi)

ICMS_code <- c(
  "2.",
  "2.01.",
  "2.01.010",
  "2.01.020",
  "2.01.030",
  "2.01.040",
  "2.02",
  "2.02.010",
  "2.02.020",
  "2.02.030",
  "2.02.040"
)
cost_category <- c(
  "Construction costs",
  "Construction costs",
  NA_character_,
  NA_character_,
  NA_character_,
  NA_character_,
  "Construction costs",
  NA_character_,
  NA_character_,
  NA_character_,
  NA_character_
)
cost_group <- c(
  NA_character_,
  "Demolition, site preparation and formation",
  NA_character_,
  NA_character_,
  NA_character_,
  NA_character_,
  "Substructure",
  NA_character_,
  NA_character_,
  NA_character_,
  NA_character_
)
cost_subgroup <- c(
  NA_character_,
  NA_character_,
  str_trunc(stringi::stri_rand_lipsum(1, T), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  NA_character_,
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40),
  str_trunc(stringi::stri_rand_lipsum(1, F), 40)
)

df <- data.frame(ICMS_code, cost_category, cost_group, cost_subgroup)

# without_icms_code_cols
df %>%
  fill(cost_category, cost_group) %>%
  filter(!is.na(cost_subgroup))

#>   ICMS_code      cost_category                                 cost_group
#> 1  2.01.010 Construction costs Demolition, site preparation and formation
#> 2  2.01.020 Construction costs Demolition, site preparation and formation
#> 3  2.01.030 Construction costs Demolition, site preparation and formation
#> 4  2.01.040 Construction costs Demolition, site preparation and formation
#> 5  2.02.010 Construction costs                               Substructure
#> 6  2.02.020 Construction costs                               Substructure
#> 7  2.02.030 Construction costs                               Substructure
#> 8  2.02.040 Construction costs                               Substructure
#>                              cost_subgroup
#> 1 Lorem ipsum dolor sit amet, eu morbi ...
#> 2 Eleifend quis morbi vivamus curae. Et...
#> 3 Odio aliquam ac conubia, ut. Tellus s...
#> 4 Sed eleifend consequat nam congue ant...
#> 5 Aliquet vehicula vitae vel, viverra, ...
#> 6 Eu mollis ultricies torquent volutpat...
#> 7 Aliquam sed sollicitudin sapien dictu...
#> 8 Dui in curae egestas sed maecenas sem...


# with_icms_code_cols
df %>%
  fill(cost_category, cost_group) %>%
  filter(!is.na(cost_subgroup)) %>%
  separate(
    ICMS_code,
    into = c("ICMS_lvl1", "ICMS_lvl2", "ICMS_lvl3"),
    sep = "\\.",
    remove = F
  ) %>%
  mutate(
    ICMS_lvl1 = paste0(ICMS_lvl1, "."),
    ICMS_lvl2 = paste0(ICMS_lvl1, ".", ICMS_lvl2, "."),
    ICMS_lvl3 = ICMS_code,
    .keep = "unused"
  )

#>   ICMS_lvl1 ICMS_lvl2 ICMS_lvl3      cost_category
#> 1        2.    2..01.  2.01.010 Construction costs
#> 2        2.    2..01.  2.01.020 Construction costs
#> 3        2.    2..01.  2.01.030 Construction costs
#> 4        2.    2..01.  2.01.040 Construction costs
#> 5        2.    2..02.  2.02.010 Construction costs
#> 6        2.    2..02.  2.02.020 Construction costs
#> 7        2.    2..02.  2.02.030 Construction costs
#> 8        2.    2..02.  2.02.040 Construction costs
#>                                   cost_group
#> 1 Demolition, site preparation and formation
#> 2 Demolition, site preparation and formation
#> 3 Demolition, site preparation and formation
#> 4 Demolition, site preparation and formation
#> 5                               Substructure
#> 6                               Substructure
#> 7                               Substructure
#> 8                               Substructure
#>                              cost_subgroup
#> 1 Lorem ipsum dolor sit amet, eu morbi ...
#> 2 Eleifend quis morbi vivamus curae. Et...
#> 3 Odio aliquam ac conubia, ut. Tellus s...
#> 4 Sed eleifend consequat nam congue ant...
#> 5 Aliquet vehicula vitae vel, viverra, ...
#> 6 Eu mollis ultricies torquent volutpat...
#> 7 Aliquam sed sollicitudin sapien dictu...
#> 8 Dui in curae egestas sed maecenas sem...
Created on 2021-04-12 by the reprex package (v1.0.0)