Create a dataframe in R-CodePudding

I have a text file where each row of the data is structured as:

[
 id,
"Field1",
"Field2",
"Field3"
]

For example:

[
 1,
"Name1",
"Adress1",
"Website 1"
] 
[
 2,
"Name2",
"Adress2",
"Website 2"
]

In R I'd like to convert this to a data frame:

ID   Field1  Field2  Field3
1    Name1   Adress1 Website1
2    Name2   Adress2 Website2

How can this be done?

Also, curious about what sort of structure this is?

CodePudding user response：

Another option might be using jsonlite (borrowing data from @akrun's answer)

library(jsonlite)
type.convert(
    as.data.frame(
        do.call(
            rbind,
            lapply(strsplit(s, "\\s\n(?=\\[)", perl = TRUE)[[1]], fromJSON)
        )
    ),
    as.is = TRUE
)

and we obtain

  V1    V2      V3        V4
1  1 Name1 Adress1 Website 1
2  2 Name2 Adress2 Website 2

CodePudding user response：

We may use reticulate

library(reticulate)
df1 <- setNames( do.call(rbind.data.frame, 
   lapply(py_eval(gsub("\\]\n\\[", "],[", str1)), unlist)),
     c("id", "Field1", "Field2", "Field3"))

-output

> df1
  id Field1  Field2    Field3
1  1  Name1 Adress1 Website 1
2  2  Name2 Adress2 Website 2

data

str1 <- "[\n 1,\n\"Name1\",\n\"Adress1\",\n\"Website 1\"\n] \n[\n 2,\n\"Name2\",\n\"Adress2\",\n\"Website 2\"\n]"

CodePudding user response：

Another possible solution, based on tidyverse:

library(tidyverse)

df <- read.csv(text = '[
 1,
"Name1",
"Adress1",
"Website 1"
] 
[
 2,
"Name2",
"Adress2",
"Website 2"
]', row.names = NULL, strip.white = T)

df %>% 
  select(x = row.names) %>% 
  filter(!str_detect(x, "^\\[|^\\]")) %>% 
  mutate(name = rep(c("ID", "Field1", "Field2", "Field3"), 2)) %>% 
  pivot_wider(values_from = x, values_fn = list) %>% 
  unnest(everything())

#> # A tibble: 2 × 4
#>   ID    Field1 Field2  Field3   
#>   <chr> <chr>  <chr>   <chr>    
#> 1 1     Name1  Adress1 Website 1
#> 2 2     Name2  Adress2 Website 2