Concatenating JSON output for multiple dataframes from pd.to_json()

Question

I want to combine several dataframes and write to a singular JSON file.

import json 
import pandas as pd 

df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [10, 11, 12]})
df2 = pd.DataFrame({'C': [1, 2, 3], 'D': [10, 11, 12]})

with open('df1_df2.json', 'w') as js:
    for df in [df1, df2]:
        df.set_index(df.columns[0], inplace=True)
        parsed = json.loads(df.to_json())
        print(json.dumps(parsed, indent=4))


>>>
{
    "B": {
        "1": 10,
        "2": 11,
        "3": 12
    }
}
{
    "D": {
        "1": 10,
        "2": 11,
        "3": 12
    }
}

As the above would not make a valid json file, I am not sure the best way to write these json objects to a file. The name of the main keys (df1, df2) can be anything. The format I want is:

{
   "df1":{
      "B":{
         "1":10,
         "2":11,
         "3":12
      }
   },
   "df2":{
      "D":{
         "1":10,
         "2":11,
         "3":12
      }
   }

Elbek · Accepted Answer · 2021-03-11 09:31:42Z

2

You can convert dataframe to dictionary than combine dictionaries to one object, then dump to json

import json 
import pandas as pd 

df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [10, 11, 12]})
df2 = pd.DataFrame({'C': [1, 2, 3], 'D': [10, 11, 12]})

combined = {"df1": df1.to_dict(), "df2": df2.to_dict()}
print(json.dumps(combined, indent=4))

This generates:

{
    "df1": {
        "A": {
            "0": 1,
            "1": 2,
            "2": 3
        },
        "B": {
            "0": 10,
            "1": 11,
            "2": 12
        }
    },
    "df2": {
        "C": {
            "0": 1,
            "1": 2,
            "2": 3
        },
        "D": {
            "0": 10,
            "1": 11,
            "2": 12
        }
    }
}

answered Mar 11, 2021 at 9:31

Elbek

6461 gold badge4 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Concatenating JSON output for multiple dataframes from pd.to_json()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related