Select columns in tibble with $ in name (from a json object)

[williaml]

I have got a tibble that comes from manipulating some json output. How would I select some of the columns below like title$id, $value etc.

For example, this does not work:

df %>% 
  select(id, date, title$id, $value)

# A tibble: 5 x 12
  id     url     heading    category title$id $value      date    page pageSequence relevance$score $value  snippet                    troveUrl        edition
  <chr>  <chr>   <chr>      <chr>    <chr>    <chr>       <chr>  <int> <chr>        <chr>           <chr>   <chr>                      <chr>           <chr>  
1 19706~ /newsp~ "MURDER! ~ Article  319      Leader (Me~ 1908-~    51 51           183.23116       very r~ "\"Mr. Hayes, there's an ~ https://trove.~ NA     
2 13396~ /newsp~ "MURDER! ~ Article  466      The World'~ 1908-~     3 3            183.22223       very r~ "\"Mr. Hayes, there's an ~ https://trove.~ NA     
3 25364~ /newsp~ "MURDER! ~ Article  1464     Sydney Pun~ 1878-~     7 7            182.75418       very r~ "MR. PUNCH disclaims the ~ https://trove.~ NA     
4 17612~ /newsp~ "Murder, ~ Article  840      The Telegr~ 1909-~     6 6            172.47327       very r~ "A remarkable delusion ha~ https://trove.~ NA     
5 17950~ /newsp~ "MURDER O~ Article  840      The Telegr~ 1914-~     7 7            172.47327       very r~ "Sara Gindotti, a young g~ https://trove.~ NA 


df <- structure(list(id = c("197068265", "133966969", "253642282", 
"176123761", "179503947"), url = c("/newspaper/197068265", "/newspaper/133966969", 
"/newspaper/253642282", "/newspaper/176123761", "/newspaper/179503947"
), heading = c("MURDER! MURDER!", "MURDER! MURDER!", "MURDER! MURDER!!", 
"Murder, \"Wilful Murder.\"", "MURDER ON MURDER."), category = c("Article", 
"Article", "Article", "Article", "Article"), title = structure(list(
    id = c("319", "466", "1464", "840", "840"), value = c("Leader (Melbourne, Vic. : 1862 - 1918, 1935)", 
    "The World's News (Sydney, NSW : 1901 - 1955)", "Sydney Punch (NSW : 1864 - 1888)", 
    "The Telegraph (Brisbane, Qld. : 1872 - 1947)", "The Telegraph (Brisbane, Qld. : 1872 - 1947)"
    )), class = "data.frame", row.names = c(NA, -5L)), date = c("1908-09-05", 
"1908-09-19", "1878-03-16", "1909-06-07", "1914-02-19"), page = c(51L, 
3L, 7L, 6L, 7L), pageSequence = c("51", "3", "7", "6", "7"), 
    relevance = structure(list(score = c("183.23116", "183.22223", 
    "182.75418", "172.47327", "172.47327"), value = c("very relevant", 
    "very relevant", "very relevant", "very relevant", "very relevant"
    )), class = "data.frame", row.names = c(NA, -5L)), snippet = c("\"Mr. Hayes, there's an inventor downstairs want's to see you.\" \"What is his Invention?\" \"He has a heating apparatus for hen coops that", 
    "\"Mr. Hayes, there's an inventor downstairs wants to see you.\" \"What is his invention?\" \"He has a heating apparatus for hen-coops", 
    "MR. PUNCH disclaims the slightest sympathy with the dastardly roughs who created the disturbance in Hyde Park last Sunday afternoon, and hustled Pastor Allen and his friends off the ground. He trusts sincerely that the ringleaders may be brought to justice and made an", 
    "A remarkable delusion has settled on the minds of not a few individuals in this community to the effect that there is a difference between the terms of", 
    "Sara Gindotti, a young girl, was shot dead on 15th December, in her father's house ut Aquita, Spain, while dancing at her birthday party. Ernesto Ugaldi,"
    ), troveUrl = c("https://trove.nla.gov.au/ndp/del/article/197068265?searchTerm=murder", 
    "https://trove.nla.gov.au/ndp/del/article/133966969?searchTerm=murder", 
    "https://trove.nla.gov.au/ndp/del/article/253642282?searchTerm=murder", 
    "https://trove.nla.gov.au/ndp/del/article/176123761?searchTerm=murder", 
    "https://trove.nla.gov.au/ndp/del/article/179503947?searchTerm=murder"
    ), edition = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

[andresrcs]

@williaml

df %>% 
  select(id, date, title$id, $value)

Non-syntactic variable names must be surrounded among backticks

df %>% 
    select(date, `title$id`, `$value`)

[williaml]

Thanks. I tried that earlier though and it didn't work.

> df %>% 
+   select(id, date, `title$id`, `$value`)
Error: Can't subset columns that don't exist.
x Column `title$id` doesn't exist.

[williaml]

There is some good information here and I think I should just further unnest(), then select.

Something like this, then select the columns:

Nested data: JSON to tibble

library(tidyverse)
purrr::map_if(df, is.data.frame, list) %>% 
as_tibble() %>% 
  unnest(cols = c(title, relevance), names_repair = "unique") %>% 
  janitor::clean_names() %>% 
  select(id_1, date, id_5, value_6)