CODEWITHSUNDEEP
pythonpandasdataframe
baxx
baxx

Reputation: 4725

Convert JSON structure to dataframe in Python with Pandas

Given the following data: 

d = {"a": {1.0: "this", 2.0: "that"}, "b": {1.0: "here", 2.0: "there"}}

How would one create dataframe: 

  var  code   name
0   a   1.0   this
1   a   2.0   that
2   b   1.0   here
3   b   2.0  there

Upvotes: 0

Views: 88

Answers (2)

jezrael
jezrael

Reputation: 863291

I suggest use nested list comprehension for list of tuples and pass to DataFrame constructor:

L = [(k, k1, v1) for k, v in d.items() for k1, v1 in v.items()]
df = pd.DataFrame(L, columns=['var', 'code', 'name'])
print (df)
  var  code   name
0   a   1.0   this
1   a   2.0   that
2   b   1.0   here
3   b   2.0  there

Alternative solution with DataFrame constructor, DataFrame.rename_axis, reshape by DataFrame.unstack and Series.reset_index:

df = (pd.DataFrame(d)
        .rename_axis(index='code', columns='var')
        .unstack()
        .reset_index(name='name'))
print (df)
  var  code   name
0   a   1.0   this
1   a   2.0   that
2   b   1.0   here
3   b   2.0  there

Upvotes: 3

yatu
yatu

Reputation: 88285

We can construct a dataframe from the dictionary, stack and reset_index:

df = pd.DataFrame(d).stack()
df.index =  df.index.set_names(['var', 'code'])
df.reset_index(name='name')

print(df)

   var code   name
0  1.0    a   this
1  1.0    b   here
2  2.0    a   that
3  2.0    b  there

Upvotes: 2

Related Questions