0

In a previous post (How do I read multiple JSON structures contained in one file?) I have asked about an uncommon data structure (or at least uncommon for R)

I have a txt file with this structure:

identifier ### part A ### part B

A simplification of the 1st line of my real data would be this 

1 ### [{"X": "1", "Y": "2", "Z": "3"}, {"X": "4", "Y": "5", "Z": "6"}] ### [{"X": "7", "Y": "8", "Z": "9"}, {"X": "10", "Y": "11", "Z": "12"}, {"X": "13", "Y": "14", "Z": "15"}]

This structure comes from public data.

I have used this

setwd("/myfolder")

library(stringi)
library(purrr)
library(jsonlite)

raw <- readLines("myfile.txt")

raw <- gsub("^.\\###", " ", raw)

PartB <- gsub("^.*\\]\\###\\[", "", raw)
PartB <- paste0("[", PartB)

PartB <- stri_replace_first_regex(PartB, "\\###", "") %>% 
  map_df(fromJSON)

save(fundamento, file = "PartB.RData")

PartA <- gsub(pattern = "(.*\\###)(.*)(\\###.*)", "\\2", raw)

PartA <- stri_replace_first_regex(concepto, "\\###", "") %>% 
  map_df(fromJSON)

save(PartA, file = "PartA.RData")

And that creates two data frames

PartA

X Y Z
1 2 3
4 5 6

PartB

X  Y  Z
7  8  9
10 11 12
13 14 15

I have tried to obtain something like this

PartA

identifier part X Y Z
1          A    1 2 3
1          A    4 5 6

PartB

identifier part X  Y  Z
1          B    7  8  9
1          B    10 11 12
1          B    13 14 15

Any idea would be helpful. Many thanks in advance.

Community
  • 1
  • 1
pachadotdev
  • 3,345
  • 6
  • 33
  • 60
  • You just want to add a `part` and `identifier` column do each data.frame? And the value of those columns are the same all the way down? – Señor O Sep 13 '16 at 14:13
  • likewise I want to add a column that for each element of line 1 (in the txt) it puts 1 1 1 2 2 2 2 2 3 3 3 etc – pachadotdev Sep 13 '16 at 15:40

1 Answers1

1

Try this:

library(stringr)
library(tidyjson)
library(purrr)
library(dplyr)

line1 <- '### [{"X": "1", "Y": "2", "Z": "3"}, {"X": "4", "Y": "5", "Z": "6"}] ### [{"X": "7", "Y": "8", "Z": "9"}, {"X": "10", "Y": "11", "Z": "12"}, {"X": "13", "Y": "14", "Z": "15"}]'
line2 <- '### [{"X": "2", "Y": "3", "Z": "4"}, {"X": "5", "Y": "6", "Z": "7"}] ### [{"X": "8", "Y": "9", "Z": "10"}, {"X": "11", "Y": "12", "Z": "13"}, {"X": "14", "Y": "15", "Z": "16"}]'

raw <- c(line1, line2)

cleanup_line <- function(line, id) {

  line %>% gsub('^### ', '', .) %>% str_split('###') %>%
    flatten_chr %>%
    gather_array %>%
    spread_all %>%
    mutate(identifier = id, part = letters[document.id]) %>%
    select(identifier, part, X, Y, Z) %>%
    tbl_df

}

map2_df(raw, seq_along(raw), cleanup_line)

where you will need the dev version of tidyjson to use spread_all, otherwise you can use the more verbose spread_values from the CRAN version.

jeremy stanley
  • 126
  • 2
  • this is clever !! very clever !! I did change that a bit and I tried it for the 1st 10,000 lines and this doesn't crash my computer compared to `paste0 + weird "tricks"` – pachadotdev Sep 17 '16 at 06:05