CODEWITHSUNDEEP
pythonjsonpandascsv
iamgroot
iamgroot

Reputation: 85

Reading and writing out a dictionary to csv file in python (or json)

I have a similar problem to this question here. But I got a json file that has to be converted to csv file through python. The json file is basically multiple python dictionary (example):

{
  "d" : {
    'a': 'one',
    'b': 'two',
    'c': 'three'
   },

 "z" : {
    'j': 'eleven',
    'k': 'twenty',
    'l': 'forty'
  }
}

I would like to write these dictionaries out to a csv file one after another (in a same column), in the format where each line contains the string key, followed by the string value.

I would also like to be able to read them all back into json file

I have tried to use the solution but I got an error saying that dict_items has no values

with open('some.json') as file:
    data = json.load(file)
    data_item = data["d"].items()

    df = pd.DataFrame.from_dict(data_item, orient="index")
    df.to_csv("data.csv")

This is the expected output:

expected output

any help would be appreciated.

Upvotes: 1

Views: 327

Answers (2)

Martin Evans
Martin Evans

Reputation: 46759

You could use bfill() to combine all the columns and then just write the first column as the CSV output. This approach should work regardless of how many columns you have, for example:

import pandas as pd

with open('some.json') as f_input:
    df = pd.read_json(f_input)

df = df.bfill(axis='columns')
df.iloc[:, 0].to_csv('some.csv', encoding='utf-8', header=False)

So if your JSON file looks like:

{
  "d" : {
    "a": "one",
    "b": "two",
    "c": "three"
   },

 "z" : {
    "j": "eleven",
    "k": "twenty",
    "l": "forty"
  },

 "a" : {
    "x": "fifty",
    "y": "sixty",
    "z": "seventy"
  }
}

Your CSV file would be:

a,one
b,two
c,three
j,eleven
k,twenty
l,forty
x,fifty
y,sixty
z,seventy

Upvotes: 2

Sina Qahremani
Sina Qahremani

Reputation: 134

if you want to write one of the dictionaries, remove .items() and that's it.

Upvotes: 0

Related Questions