CODEWITHSUNDEEP
pythonjsonpandasdataframe
Sevval Kahraman
Sevval Kahraman

Reputation: 1301

How to create a single json file from two DataFrames?

I have two DataFrames, and I want to post these DataFrames as json (to the web service) but first I have to concatenate them as json.

#first df
input_df = pd.DataFrame()
input_df['first'] = ['a', 'b']
input_df['second'] = [1, 2]

#second df
customer_df = pd.DataFrame()
customer_df['first'] = ['c']
customer_df['second'] = [3]

For converting to json, I used following code for each DataFrame;

df.to_json(
        path_or_buf='out.json',
        orient='records',  # other options are (split’, ‘records’, ‘index’, ‘columns’, ‘values’, ‘table’)
        date_format='iso',
        force_ascii=False,
        default_handler=None,
        lines=False, 
        indent=2    
    )

This code gives me the table like this: For ex, input_df export json

[
  {
    "first":"a",
    "second":1
  },
  {
    "first":"b",
    "second":2
  }
]

my desired output is like that:

{
  "input": [
    {
      "first": "a",
      "second": 1
    },
    {
      "first": "b",
      "second": 2
    }
  ],
  "customer": [
    {
      "first": "d",
      "second": 3
    }
  ]
}

How can I get this output like this? I couldn't find the way :(

Upvotes: 0

Views: 64

Answers (1)

user7864386
user7864386

Reputation:

You can concatenate the DataFrames with appropriate key names, then groupby the keys and build dictionaries at each group; finally build a json string from the entire thing:

out = (
       pd.concat([input_df, customer_df], keys=['input', 'customer'])
       .droplevel(1)
       .groupby(level=0).apply(lambda x: x.to_dict('records'))
       .to_json()
      )

Output:

'{"customer":[{"first":"c","second":3}],"input":[{"first":"a","second":1},{"first":"b","second":2}]}'

or a dict by replacing the last to_json() to to_dict().

Upvotes: 1

Related Questions