CODEWITHSUNDEEP
pythonpandas
Honza
Honza

Reputation: 139

Python 3 level dictionary into dataframe

In python, I have a dictionary that looks like this.

{
cat1:
  { subcat2: [1,2,3],
    subcat3: [4,5,6]
  }
cat2:
  {
  subcat3:[7,8,9]
  }
}

I would want to transform it into pandas dataframe with a structure

Cat Subcat Value
1 2 1
1 2 2
1 3 4

etc. Is there any nicer way to do this than iterating through everything?

Upvotes: 1

Views: 126

Answers (2)

voidpointercast
voidpointercast

Reputation: 185

I think iterating is the easiest option, something like:

DataFrame(
    dict(category=category, subcategory=subcategory, value=value)
    for (category, subcategories) in sample.items()
    for subcategory, values in subcategories.items()
    for value in values
)

where sample is your dictionary

Upvotes: 0

jezrael
jezrael

Reputation: 863401

You can use nested list comprehension for list of tuples here and then pass to DataFrame constructor:

d = {
'cat1':
  { 'subcat2': [1,2,3],
    'subcat3': [4,5,6]
  },
'cat2':
  {
  'subcat3':[7,8,9]
  }
}
    
a = [(k, k1, x) for k, v in d.items() for k1, v1 in v.items() for x in v1]
print(a)
[('cat1', 'subcat2', 1), ('cat1', 'subcat2', 2),
 ('cat1', 'subcat2', 3), ('cat1', 'subcat3', 4),
 ('cat1', 'subcat3', 5), ('cat1', 'subcat3', 6),
 ('cat2', 'subcat3', 7), ('cat2', 'subcat3', 8), 
 ('cat2', 'subcat3', 9)]


df=pd.DataFrame(a, columns=['Cat','Subcat','Value'])
print (df)
    Cat   Subcat  Value
0  cat1  subcat2      1
1  cat1  subcat2      2
2  cat1  subcat2      3
3  cat1  subcat3      4
4  cat1  subcat3      5
5  cat1  subcat3      6
6  cat2  subcat3      7
7  cat2  subcat3      8
8  cat2  subcat3      9

Upvotes: 1

Related Questions