CODEWITHSUNDEEP
pythonpandasdataframe
Kumar AK
Kumar AK

Reputation: 1037

Export DataFrame of dictionaries to JSON file

have_df = pd.DataFrame({'User':['101','101','101'],'json_text':["""{"president":{"name": "Zaphod Beeblebrox","species": "Betelgeusian"}}""","""{"president":{"name": "Zaphod Beeblebrox","species": "Betelgeusian"}}""",'blank']})

and I want this export pipe delimited file and tried this:

have_df.to_csv('have_df.csv',sep="|")

when I opened and see the pipe file the there's an extra double quotes around the json text values like this:

"{""president"":{""name"": ""Zaphod Beeblebrox"",""species"": ""Betelgeusian""}}"

how to remove this extra double quotes programmatic way? Thanks

Upvotes: 2

Views: 1662

Answers (2)

cs95
cs95

Reputation: 402333

Eval your JSON data first, and then save to csv:

import json

(have_df.json_text
        .replace('blank', "None")
        .apply(ast.literal_eval)
        .to_csv('file.csv', sep='|')
)

file.csv

0|{'president': {'name': 'Zaphod Beeblebrox', 'species': 'Betelgeusian'}}
1|{'president': {'name': 'Zaphod Beeblebrox', 'species': 'Betelgeusian'}}
2|

Upvotes: 2

Rakesh
Rakesh

Reputation: 82765

You have json string convert it using json.loads and should fix your issue

Ex:

import pandas as pd
import json

def converttojson(val):
    try:
        return json.loads(val)
    except:
        return val

have_df = pd.DataFrame({'User':['101','101','101'],'json_text':["""{"president":{"name": "Zaphod Beeblebrox","species": "Betelgeusian"}}""","""{"president":{"name": "Zaphod Beeblebrox","species": "Betelgeusian"}}""",'blank']})
have_df["json_text"] = have_df["json_text"].apply(converttojson)
have_df.to_csv(filename,sep="|")
  • Note -- You can use have_df["json_text"].apply(json.loads) directly. But i have used converttojson because you have blank in your sample data

Upvotes: 2

Related Questions