Separate a column into new columns based on the number of leading spaces

Question

These reports are coming from quickbooks, downloaded as Excel files. Notice that the left column is this nested hierarchy based on the left spacing.

I need to separate Description column into separate columns based on the number of leading spaces on the left.

As I've been working with financial reports recently, these are super common and extremely difficult to work with. Is there a package or function for importing this type of data?

Here is example reproducible input dataframe:

df1 <- structure(list(Description = c("asset", " current asset", "   bank acc", 
                                      "    banner", "    clearing",
                                      "   total bank accounts",
                                      " total current assets"),
                 Total = c(NA, NA, NA, 10L, 20L, 30L, 30L)),
            .Names = c("Description", "Total"), 
            class = "data.frame", 
            row.names = c(NA, -7L))

Answer 1

You can try tidyxl and unpivotr for these Excel wrangling tasks. Here are the docs:

• unpivotr: https://github.com/nacnudus/unpivotr
• tidyxl: https://nacnudus.github.io/tidyxl/

Here's a nice tutorial: https://blog.davisvaughan.com/2018/02/16/tidying-excel-cash-flow-spreadsheets-in-r/