Pandas dataframe to JSONL (JSON Lines) conversion

Question

I need to convert pandas data frame to JSONL format. I couldn't find a good package to do it and tried to implement myself, but it looks a bit ugly and not efficient.

For example, given a pandas df:

        label      pattern
  0      DRUG      aspirin
  1      DRUG    trazodone
  2      DRUG   citalopram

I need to convert to txt file of the form:

{"label":"DRUG","pattern":[{"lower":"aspirin"}]}
{"label":"DRUG","pattern":[{"lower":"trazodone"}]}
{"label":"DRUG","pattern":[{"lower":"citalopram"}]}

I tried with to_dict('records'), but I'm missing [ ] and nested 'lower' key.

df.to_dict('record')

creates:

[{'label': 'DRUG', 'pattern': 'aspirin'},
 {'label': 'DRUG', 'pattern': 'trazodone'},
 {'label': 'DRUG', 'pattern': 'citalopram'}]

I thought about converting the 'pattern' columns and include nested 'lower'?

UPD

So far, I succeeded to convert 'pattern' into list:

df_new = pd.concat((df[['label']], df[['pattern']].apply(lambda x: x.tolist(), axis=1)), axis=1)
df_new.columns = ['label', 'pattern']
df_new.head()

The result:

    label   pattern
0   DRUG    [aspirin]
1   DRUG    [trazodone]
2   DRUG    [citalopram]

and then:

df_new.to_dict(orient='records')

[{'label': 'DRUG', 'pattern': ['aspirin']},
 {'label': 'DRUG', 'pattern': ['trazodone']},
 {'label': 'DRUG', 'pattern': ['citalopram']}]

UPD 2

Eventually, I managed to get what I want, but in the most non-pythonic way.

df_1 = pd.DataFrame(df[['pattern']].apply(lambda x: {'lower': x[0]}, axis=1))
df_1.columns = ['pattern']

df_fin = pd.concat((df[['label']], df_1[['pattern']].apply(lambda x: x.tolist(), axis=1)), axis=1)
df_fin.columns = ['label', 'pattern']
df_fin.to_json(orient='records')

 '{'label': 'DRUG', 'pattern': [{'lower': 'aspirin'}]}
  {'label': 'DRUG', 'pattern': [{'lower': 'trazodone'}]}
  {'label': 'DRUG', 'pattern': [{'lower': 'citalopram'}]}'

Any chance you can show a neat solution?

Pandas DataFrame.to_json may be what you're looking for. Orient='records'. pandas.pydata.org/pandas-docs/stable/generated/… — Michael B
– Michael B, Commented Aug 9, 2018 at 20:24
@MichaelB, thanks, I tried, but it does not create '[ ]' after "pattern". Basically, 'pattern' values should be a list. — Arnold Klein
– Arnold Klein, Commented Aug 9, 2018 at 20:32

kmsquire · Accepted Answer · 2018-10-17 18:56:09Z

73

In versions of Pandas > 0.19.0, DataFrame.to_json has a parameter, lines, that will write out JSONL format.

Given that, a more succinct version of your solution might look like this:

import pandas as pd

data = [{'label': 'DRUG', 'pattern': 'aspirin'},
        {'label': 'DRUG', 'pattern': 'trazodone'},
        {'label': 'DRUG', 'pattern': 'citalopram'}]
df = pd.DataFrame(data)

# Wrap pattern column in a dictionary
df["pattern"] = df.pattern.apply(lambda x: {"lower": x})

# Output in JSONL format
print(df.to_json(orient='records', lines=True))

Output:

{"label":"DRUG","pattern":{"lower":"aspirin"}}
{"label":"DRUG","pattern":{"lower":"trazodone"}}
{"label":"DRUG","pattern":{"lower":"citalopram"}}

answered Oct 17, 2018 at 18:56

kmsquire

1,49213 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Alexander Over a year ago

Thank you very much for this solution. I was grappling in the dark with the jsonlines and json libraries while this is a lot cleaner.

Brendan Over a year ago

Since I've accidentally bumped into this answer a few times over the years and not had any luck: this does not work as expected with complex/nested data. It won't error, but instead will output struct field values without their keys, which is not very helpful if you were expecting something more like what happens when you do df.write.json(...) in spark

Haha TTpro · Accepted Answer · 2023-01-12 03:50:15Z

8

Very short code that should work for easy coping-pasting.

output_path = "/data/meow/my_output.jsonl"

with open(output_path, "w") as f:
    f.write(df_result.to_json(orient='records', lines=True, force_ascii=False))

If you are using jupyter notebook, you should use with open(output_path, "w") as f instead of f = open(output_path, "w") to make sure file is saved (correctly close) and ready to read in next cell.

answered Jan 12, 2023 at 3:50

Haha TTpro

5,5867 gold badges52 silver badges79 bronze badges

Comments

Jakub Kukul · Accepted Answer · 2023-08-07 13:03:33Z

4

To write to a file I have modified the last line of @kmsquire
# Output in JSONL format

import pandas as pd

data = [{'label': 'DRUG', 'pattern': 'aspirin'},
        {'label': 'DRUG', 'pattern': 'trazodone'},
        {'label': 'DRUG', 'pattern': 'citalopram'}]
df = pd.DataFrame(data)

# Wrap pattern column in a dictionary
df["pattern"] = df.pattern.apply(lambda x: {"lower": x})

# Output in JSONL format into a file 
f=open('records.jsonl', 'w')
print(df.to_json(orient='records', lines=True),file=f, flush=False)

edited Aug 7, 2023 at 13:03

Jakub Kukul

15k4 gold badges61 silver badges60 bronze badges

answered Mar 29, 2022 at 22:23

Abdurrahman Adebiyi

4984 silver badges12 bronze badges

1 Comment

Philippe Remy Over a year ago

Do not forget to close your file handler f with f.close()

Collectives™ on Stack Overflow

Pandas dataframe to JSONL (JSON Lines) conversion

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related