Reading and writing multiple dictionaries to csv file vertically

user_17209577 0
Viewed 184

I got multiple dictionaries in json format 'some.json' looking like these

{
  "my_dict" : {
    'key1': 'value1', 
    'key2': 'value2', 
    'key3': 'value3'
  },
  "my_dict2" : {
    'key8': 'value8', 
    'key9': 'value9', 
    'key10': 'value10'
  }
}

Both key and value are strings. I would like to export it to csv format vertically then read it back to json file. For example, when I add an item to my_dict on csv format then it would also be added on the json file.

Required output

my_dict
key1,value1
key2,value2
key3,value3
my_dict2
key8,value8
key9,value9
key10,value10

I got so far a solution, but the problem is, the name of the dictionaries are not written on the csv file so can't be read back to json file

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)
pandas csv dictionary python json
3 Answers

Create new file with file.write for names of dict:

import json

with open('some.json') as f:    
    d = json.load(f)  

#sample
d = { "my_dict" : { 'key1': 'value1', 'key2': 'value2', 'key3': 'value3'}, 
      "my_dict2" : { 'key8': 'value8', 'key9': 'value9', 'key10': 'value10'}}
     
with open("some1.csv", 'w') as f:
     for k, v in d.items():
         f.write(k + '\n')
         for k1, v1 in v.items():
             f.write(f"{k1},{v1}\n")

my_dict
key1,value1
key2,value2
key3,value3
my_dict2
key8,value8
key9,value9
key10,value10

And for read back:

df = pd.read_csv("some1.csv", names=['a','b']) 

m = df['b'].isna()
df['new'] = df['a'].where(m).ffill()
s = df[~m].set_index(['new','a'])['b']

d = {level: s.xs(level).to_dict() for level in s.index.levels[0]}
print (d)
{'my_dict': {'key1': 'value1', 'key2': 'value2', 'key3': 'value3'},
 'my_dict2': {'key8': 'value8', 'key9': 'value9', 'key10': 'value10'}}

EDIT:

If format should be changed:

d = { "my_dict" : { 'key1': 'value1', 'key2': 'value2', 'key3': 'value3'}, 
      "my_dict2" : { 'key8': 'value8', 'key9': 'value9', 'key10': 'value10'}}

with open("some1.csv", 'w') as f:
     for k, v in d.items():
         for k1, v1 in v.items():
             f.write(f"{k},{k1},{v1}\n")
             
my_dict,key1,value1
my_dict,key2,value2
my_dict,key3,value3
my_dict2,key8,value8
my_dict2,key9,value9
my_dict2,key10,value10

s = pd.read_csv("some1.csv", names=['b'], squeeze=True)
print (s)
my_dict   key1      value1
          key2      value2
          key3      value3
my_dict2  key8      value8
          key9      value9
          key10    value10
Name: b, dtype: object


d = {level: s.xs(level).to_dict() for level in s.index.levels[0]}
print (d)
{'my_dict': {'key1': 'value1', 'key2': 'value2', 'key3': 'value3'},
 'my_dict2': {'key8': 'value8', 'key9': 'value9', 'key10': 'value10'}}
user_29010020

I fixed your some.json file:

{
  "my_dict": {
    "key1": "value1", 
    "key2": "value2", 
    "key3": "value3"
  },
  "my_dict2": {
    "key8": "value8", 
    "key9": "value9", 
    "key10": "value10"
  }
}

Now you can convert your json file into a csv file:

pd.read_json('some.json', orient='index').stack().to_csv('some.csv', header=False)

Your some.csv file looks like:

my_dict,key1,value1
my_dict,key2,value2
my_dict,key3,value3
my_dict2,key8,value8
my_dict2,key9,value9
my_dict2,key10,value10

Note: with the above format, you can easily use your csv file in Excel if you need it.

The rollback operation:

d = pd.read_csv('some.csv', header=None, index_col=0).groupby(0, sort=False) \
      .apply(lambda x: {k: v for k, v in zip(x[1], x[2])}).to_dict()
print(d)

# Output:
{'my_dict': {'key1': 'value1', 'key2': 'value2', 'key3': 'value3'},
 'my_dict2': {'key8': 'value8', 'key9': 'value9', 'key10': 'value10'}}
user_152399510

A little hacky but produces exactly the input you are after:

[f'{col}\n{df[col].dropna().to_csv(header=False)}' for col in df.columns)]

You can either ''.join(...) and then write in a single go or add mode='a', to to_csv so that the output is appended directly to some file.

user_21536360
Related