Complex Long-Wide Dataset to Long Dataset in R

I have a complex dataset that looks like this:

df1 <- tibble::tribble(~"Canada > London",	~"",	~"Notes",	~"United Kingdom > London",	~"",	~"",
"Restaurant",	"Price",	"Range",	"Restaurant",	"Price",	"Range",
"Fried beef",	"27",	"25-30",	"Fried beef",	"29",	"25 - 35",
"Fried potato",	"5",	"3 - 8",	"Fried potato",	"8",	"3 - 8",
"Bar",	"Price",	"Range",	"Price",	"Range",	"",
"Beer Lager",	"5",	"4 - 8",	"Beer Lager",	"6",	"4 - 8",
"Beer Dark",	"4",	"3 - 7",	"Beer Dark",	"5",	"3 - 7")

Or, for visual representation:

It is long in parameters (like Beer Lager, Beer Dark, ....) and wide by the data input (many wide elements like Canada > London, or United Kingdom > London).

The desired output would be two datasets that should look like this:

1. The first dataset (the Values):

2. The second dataset (the Ranges):

Any suggestions would be much appreciated

Your data is neither wide nor long but is a messy data table which needs some cleaning to convert it to tidy data. Afterwards you could get your desired tables using tidyr::pivot_wider:

library(dplyr)
library(tidyr)
library(purrr)

tidy_data <- function(.data, cols) {
  .data <- .data[cols]
  place <- names(.data)[[1]]
  
  .data |> 
    rename(product = 1, price = 2, range = 3) |> 
    filter(!price %in% c("Price", "Range")) |>
    mutate(place = place)
}

df1_tidy <- purrr::map_dfr(list(1:3, 4:6), tidy_data, .data = df1)

df1_tidy |> 
  select(place, product, price) |> 
  pivot_wider(names_from = product, values_from = price)
#> # A tibble: 2 × 5
#>   place                   `Fried beef` `Fried potato` `Beer Lager` `Beer Dark`
#>   <chr>                   <chr>        <chr>          <chr>        <chr>      
#> 1 Canada > London         27           5              5            4          
#> 2 United Kingdom > London 29           8              6            5

df1_tidy |> 
  select(place, product, range) |> 
  pivot_wider(names_from = product, values_from = range, names_glue = "{product} Range")
#> # A tibble: 2 × 5
#>   place                   `Fried beef Range` Fried potato Rang…¹ Beer …² Beer …³
#>   <chr>                   <chr>              <chr>               <chr>   <chr>  
#> 1 Canada > London         25-30              3 - 8               4 - 8   3 - 7  
#> 2 United Kingdom > London 25 - 35            3 - 8               4 - 8   3 - 7  
#> # … with abbreviated variable names ¹​`Fried potato Range`, ²​`Beer Lager Range`,
#> #   ³​`Beer Dark Range`

I agree with @stefan. You actually have 4 tables, or 2 depending on how you look at it. Here is an implementation of 2 functions that start the cleaning and formatting process. The first split the dfs by row and the second function splits them by column. After that it is easier to format, clean, and merge the dfs into 1.

library(tidyverse)

df0 = tibble::tribble(~"Canada > London",  ~"",    ~"Notes",   ~"United Kingdom > London", ~"",    ~"",
                       "Restaurant",   "Price",    "Range",    "Restaurant",   "Price",    "Range",
                       "Fried beef",   "27",   "25-30",    "Fried beef",   "29",   "25 - 35",
                       "Fried potato", "5",    "3 - 8",    "Fried potato", "8",    "3 - 8",
                       "Bar",  "Price",    "Range",    "Price",    "Range",    "",
                       "Beer Lager",   "5",    "4 - 8",    "Beer Lager",   "6",    "4 - 8",
                       "Beer Dark",    "4",    "3 - 7",    "Beer Dark",    "5",    "3 - 7")


split_rows = function(df){
  
  # breaks of sub-dfs within original df
  df_breaks = df[,2] == "Price"
  df_breaks = (1:length(df_breaks))[df_breaks]
  df_breaks
  
  
  
  # list to populate in loop with sub-dfs
  df_list = c()
  
  for(i in 1:length(df_breaks)){
    
    # get start of sub-df
    start = df_breaks[i]
    
    # get end of sub-df
    if(i == length(df_breaks)){
      end = nrow(df) # if its the last set it to the last row of the original df
    }
    else{
      end = df_breaks[i+1]-1 # else, set it to the next start - 1
    }
    
    # subset df
    df_temp = df[start:end,]
    
    # first row as header
    colnames(df_temp) = df_temp[1,]
    df_temp = df_temp[-1,]
    
    # append to df_list
    df_list = append(df_list,list(df_temp))
  }
  
  return(df_list)
}
split_cols = function(df_list,second_df_col_start = 4){
  df_list = lapply(df_list, function(df){
    df1 = df[,1:(second_df_col_start-1)]
    df2 = df[,second_df_col_start:ncol(df)]
    return(list(df1,df2))
    })
  
  return(df_list)
}

output = split_rows(df0) %>% 
  split_cols()

output:

[[1]]
[[1]][[1]]
# A tibble: 2 × 3
  Restaurant   Price Range
  <chr>        <chr> <chr>
1 Fried beef   27    25-30
2 Fried potato 5     3 - 8

[[1]][[2]]
# A tibble: 2 × 3
  Restaurant   Price Range  
  <chr>        <chr> <chr>  
1 Fried beef   29    25 - 35
2 Fried potato 8     3 - 8  


[[2]]
[[2]][[1]]
# A tibble: 2 × 3
  Bar        Price Range
  <chr>      <chr> <chr>
1 Beer Lager 5     4 - 8
2 Beer Dark  4     3 - 7

[[2]][[2]]
# A tibble: 2 × 3
  Price      Range ``   
  <chr>      <chr> <chr>
1 Beer Lager 6     4 - 8
2 Beer Dark  5     3 - 7