# Processing JSON from a .txt file and converting to a DataFrame

**URL:** <https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119>\
**Category:** New to Julia\
**Tags:** dataframes, json3\
**Created:** [May 14, 2021, 3:34am UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119 "2021-05-14T03:34:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![clibassi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/clibassi/32/12856_2.png) [@clibassi](https://discourse.julialang.org/u/clibassi)\
**Post date:** [May 14, 2021, 3:34am UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/1 "2021-05-14T03:34:20Z")

</div>

Hi all -

I’m just looking for some insight into why the below code is returning a dataframe containing just the first line of my json file. If you’d like to try working with the file I’m working with, [you can download the aminer\_papers\_0.zip from the Microsoft Open Academic Graph site](https://www.microsoft.com/en-us/research/project/open-academic-graph/), I’m using the first file in that group of files.

```julia
using JSON3, DataFrames, CSV
file_name = "path/aminer_papers_0.txt"
json_string = read(file_name, String)
js = JSON3.read(json_string)
df = DataFrame([js])

```

The resulting DataFrame has just one line, but the column titles are correct, as is the first line. To me the mystery is why the rest isn’t getting processed. I think I can rule out that read() is only reading the first JSON object, because I can index into the resulting object and see many JSON objects:

 ![image](https://global.discourse-cdn.com/julialang/original/3X/3/3/33786bbe1c4e2d0aebad71724c54d41bd47bc743.png)

My first guess was maybe the newline \n was causing escape issues, and tried to use chomp to get rid of them, but couldn’t get it to work.

Anyway - any help would be greatly appreciated!

EDIT: To include a link to just the single file I’ve been trying to process: [Dropbox - File Deleted - Simplify your life](https://www.dropbox.com/s/mfv8e7uc2267786/aminer_papers_0.txt?dl=0)

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 14, 2021, 3:27pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/2 "2021-05-14T15:27:24Z")

</div>

I can’t help with this specific problem. But have you checked out `JSONTables.jl`? Could be of use.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [May 14, 2021, 3:34pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/3 "2021-05-14T15:34:54Z")

</div>

Already asked - the JSON is nested. You have a link to the file here [Processing JSON from a .txt file and converting to a DataFrame in Julia - Stack Overflow](https://stackoverflow.com/questions/67535869/processing-json-from-a-txt-file-and-converting-to-a-dataframe-in-julia) (it is 9GB, I do not have enough RAM to process it)

---

<div class="post-metadata">

**Author:** ![Tomas\_Pevny](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tomas_pevny/32/25466_2.png) [@Tomas\_Pevny](https://discourse.julialang.org/u/Tomas_Pevny)\
**Post date:** [May 14, 2021, 5:49pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/4 "2021-05-14T17:49:46Z")

</div>

It seems to be nested but not so terribly. If you want to train a classifier, you might be interested in [GitHub - CTUAvastLab/JsonGrinder.jl: Towards more automatic processing of structured data](https://github.com/CTUAvastLab/JsonGrinder.jl)

---

<div class="post-metadata">

**Author:** ![clibassi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/clibassi/32/12856_2.png) [@clibassi](https://discourse.julialang.org/u/clibassi)\
**Post date:** [May 14, 2021, 6:40pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/6 "2021-05-14T18:40:31Z")

</div>

So I got a very useful answer over on SO: [Processing JSON from a .txt file and converting to a DataFrame in Julia - Stack Overflow](https://stackoverflow.com/questions/67535869/processing-json-from-a-txt-file-and-converting-to-a-dataframe-in-julia)

It seems that the key is passing the jsonlines=true argument JSON3.read() command. My only challenge now is that the the conversion to DataFrame is taking a really long time (granted, it’s a very big JSON file) - I’m at a couple of hours now with it still running. Maybe that’s just the best I can hope for, but I am thinking maybe conversion from JSONTable will be faster?

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [May 14, 2021, 7:08pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/7 "2021-05-14T19:08:16Z")

</div>

JSONTables seems to complain after some long minutes about input data that cannot be considered a table:

```julia
using JSONTables, DataFrames

json_source = read("aminer_papers_0.txt", String);
jst = jsontable(json_source)
# df = DataFrame(jst)

ERROR: ArgumentError: input `JSON3.Object` must only have `JSON3.Array` values to be considered a table
Stacktrace:
 [1] jsontable(x::JSON3.Object{Base.CodeUnits{UInt8, String}, Vector{UInt64}})      
   @ JSONTables C:\Users\jrafa\.julia\packages\JSONTables\g5bSA\src\JSONTables.jl:26
 [2] jsontable(source::String)
   @ JSONTables C:\Users\jrafa\.julia\packages\JSONTables\g5bSA\src\JSONTables.jl:15
 [3] top-level scope
   @ REPL[4]:100: 

```

---

<div class="post-metadata">

**Author:** ![kmundnic](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@kmundnic](https://discourse.julialang.org/u/kmundnic)\
**Post date:** [May 14, 2021, 7:35pm UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/8 "2021-05-14T19:35:42Z")

</div>

Is a solution using Julia strictly necessary? If not, an option is to use `jq` to convert the JSONL file to a CSV file and then read the CSV file directly. In my experience, this is _much_ faster when using large files.

The first line (parsed) is:

```julia
{
  "id": "53a7258520f7420be8b514a9",
  "title": "Semantic Wikipedia.",
  "authors": [
    {
      "name": "Max Völkel",
      "id": "53f47915dabfaefedbbb728f"
    },
    {
      "name": "Markus Krötzsch",
      "id": "53f44a27dabfaedf435dbf2e"
    },
    {
      "name": "Denny Vrandecic",
      "id": "5433f551dabfaebba5832602"
    },
    {
      "name": "Heiko Haller",
      "id": "53f322dddabfae9a84460560"
    },
    {
      "name": "Rudi Studer",
      "id": "53f556b9dabfaea7cd1d5e32"
    }
  ],
  "venue": {
    "raw": "WWW",
    "id": "547ffa8cdabfaebedf84f229"
  },
  "year": 2006,
  "n_citation": 639,
  "page_start": "585",
  "page_end": "594",
  "lang": "en",
  "volume": "",
  "issue": "",
  "url": [
    "http://doi.acm.org/10.1145/1135777.1135863"
  ]
}

```

You can use `jq` to translate the nested JSONL like this (here, creating several rows for the first JSON line), including the nested values in authors as different rows:

```julia
head -n 1 aminer_papers_0.txt | jq -r '.id as $id | .title as $title | .year as $year |
       .authors[] | [$id, $title, .name, .id, $year] | @csv'

```

which yields

```julia
"53a7258520f7420be8b514a9","Semantic Wikipedia.","Max Völkel","53f47915dabfaefedbbb728f",2006
"53a7258520f7420be8b514a9","Semantic Wikipedia.","Markus Krötzsch","53f44a27dabfaedf435dbf2e",2006
"53a7258520f7420be8b514a9","Semantic Wikipedia.","Denny Vrandecic","5433f551dabfaebba5832602",2006
"53a7258520f7420be8b514a9","Semantic Wikipedia.","Heiko Haller","53f322dddabfae9a84460560",2006
"53a7258520f7420be8b514a9","Semantic Wikipedia.","Rudi Studer","53f556b9dabfaea7cd1d5e32",2006

```

Of course, you can have the entries you want saved into each row by saving more variables in the command above by using `$`.

Processing the complete file in my laptop this way just took 280.69s, and then reading it using `CSV.read` took only 16s (including compilation time):

```julia
      Row │ 53a7258520f7420be8b514a9 Semantic Wikipedia. Max Völkel 53f47915dabfaefedbbb72 ⋯
          │ String String? String? Union{Missing, String} ⋯
──────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────
        1 │ 53a7258520f7420be8b514a9 Semantic Wikipedia. Markus Krötzsch 53f44a27dabfaedf435dbf ⋯
        2 │ 53a7258520f7420be8b514a9 Semantic Wikipedia. Denny Vrandecic 5433f551dabfaebba58326
        3 │ 53a7258520f7420be8b514a9 Semantic Wikipedia. Heiko Haller 53f322dddabfae9a844605
        4 │ 53a7258520f7420be8b514a9 Semantic Wikipedia. Rudi Studer 53f556b9dabfaea7cd1d5e
        5 │ 53a7280320f7420be8ba5e96 Parsing. Ralph Grishman missing ⋯
    ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋱
 20843090 │ 53e99e61b7602d97027281bd Subject: On "the System of Crimi… WANG Bin 54408976dabfae7f9b33f6
 20843091 │ 53e99e61b7602d97027281be Spectroscopic and Theoretical St… Peng Chen 54457221dabfae862da1cd
 20843092 │ 53e99e61b7602d97027281be Spectroscopic and Theoretical St… Kiyoshi Fujisawa 53f42c20dabfaee0d9ae9b
 20843093 │ 53e99e61b7602d97027281be Spectroscopic and Theoretical St… Edward I. Solomon 5487a490dabfae8a11fb3c ⋯
                                                                                 2 columns and 20843084 rows omitted

```

I’m missing the header here, this was just an example of what can be achieved.

---

<div class="post-metadata">

**Author:** ![clibassi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/clibassi/32/12856_2.png) [@clibassi](https://discourse.julialang.org/u/clibassi)\
**Post date:** [May 15, 2021, 2:06am UTC](https://discourse.julialang.org/t/processing-json-from-a-txt-file-and-converting-to-a-dataframe/61119/9 "2021-05-15T02:06:35Z")

</div>

This is really great - I never knew about jq. Thanks!