Parsing json into pandas dataframe

Question

{
  "type_a": {
    "2015-08-07": {
      "is_available": false, 
      "variable": 0.282
    }, 
    "2015-08-23": {
      "is_available": false, 
      "variable": 0.296
    },
    "2017-03-28": {
      "is_available": false, 
      "variable": 0.524
    }
  }, 
  "type_b": {
    "2015-06-27": {
      "is_available": true, 
      "variable": 0.038
    }, 
    "2015-07-30": {
      "is_available": true, 
      "variable": 0.035
    },
    "2017-04-27": {
      "is_available": true, 
      "variable": 0.158
    }
  }
}

I would like to load the above json into a pandas dataframe and make both is_available and variable column names, so eventual structure looks like so:

             data_source      is_available     variable
2015-08-07     type_a            false           0.282
2015-08-23     type_a            false           0.296
2017-03-28     type_a            false           0.524
2015-06-27     type_b            true            0.038  
2015-07-30     type_b            true            0.035
2015-04-27     type_b            true            0.158

currently, I do this:

pd.read_json(json)

Does it not break on the use of true and false? Because that's not python syntax — TLOwater
– TLOwater, Commented May 5, 2017 at 14:49

zipa · Accepted Answer · 2017-05-05 15:06:06Z

2

If I assume your data is called data, this will do it:

import pandas as pd

df = pd.DataFrame.from_dict({(i, j): data[i][j] for i in data.keys() for j in data[i].keys()}, orient='index')
df['data_source'] = df.index.droplevel(level=1)
df.index = df.index.droplevel(level=0)

answered May 5, 2017 at 15:06

zipa

28k6 gold badges45 silver badges62 bronze badges

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

Collectives™ on Stack Overflow

Parsing json into pandas dataframe

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