CODEWITHSUNDEEP
pythonpandasdataframe
Kuroskai
Kuroskai

Reputation: 27

Python Pandas dataframes from dictioniaries containing dictionaries

I have the following Problem: I'm analysing experimental data with the pandas dataframes and because the data in itself is a bit complicated in the end I have something like this as a result which is a dictionary containing dictionaries:

    data = {aa: {1: 4, 2: 19, 3: 70, 4: 20}, bb: {1: 9, 2: 3, 3: 65, 4: 20}}

When trying to get a pandas dataframe out of it like this:

    df = pd.DataFrame(list(data.items()), columns=['code', 'week1_2'])

The result I am getting looks like this:

    0   ah03Di  {'1': 11, '2': 11, '3': 12, '4': 14}
    1   an02Ka     {'1': 6, '2': 11, '3': 7, '4': 9}

But what I rather want (or need) is something like this

    0   ah03Di  11 
    1   ah303Di 11
    2   ah03Di  12
    3   ah03Di  14
    4   an02ka  6
    5   an02Ka  11
    6   an02ka  7
    7   an02ka  9

Is there a simple way to do this?

Upvotes: 1

Views: 55

Answers (3)

piRSquared
piRSquared

Reputation: 294258

pd.DataFrame(data).pipe(
    lambda d: pd.DataFrame(dict(
        code=np.tile(d.columns, len(d)),
        week1_2=d.values.ravel()
    ))
)

  code  week1_2
0   aa        4
1   bb        9
2   aa       19
3   bb        3
4   aa       70
5   bb       65
6   aa       20
7   bb       20

Upvotes: 1

ALollz
ALollz

Reputation: 59549

import pandas as pd  
pd.DataFrame(data).stack().reset_index()

   level_0 level_1   0
0        1      aa   4
1        1      bb   9
2        2      aa  19
3        2      bb   3
4        3      aa  70
5        3      bb  65
6        4      aa  20
7        4      bb  20

For your names and just those two columns:

(pd.DataFrame(data).stack().reset_index().drop(columns='level_0')
  .rename(columns={'level_1': 'code', 0: 'week1_2'}))
#  code  week1_2
#0   aa        4
#1   bb        9
#2   aa       19
#3   bb        3
#4   aa       70
#5   bb       65
#6   aa       20
#7   bb       20

Upvotes: 3

jezrael
jezrael

Reputation: 862661

Use list comprehension for triples if performance is important:

data = {'aa': {1: 4, 2: 19, 3: 70, 4: 20}, 'bb': {1: 9, 2: 3, 3: 65, 4: 20}}

L = sorted([(k,k1,v1) for k,v in data.items() for k1,v1 in v.items()], 
            key=lambda x: (x[0], x[1]))
print (L)
[('aa', 1, 4), ('aa', 2, 19), ('aa', 3, 70), ('aa', 4, 20), 
 ('bb', 1, 9), ('bb', 2, 3), ('bb', 3, 65), ('bb', 4, 20)]

df = pd.DataFrame(L, columns=list('abc'))

Or concat with Series contructor:

df = pd.concat({k: pd.Series(v) for k, v in data.items()}).reset_index()
df.columns = list('abc')

print (df)
    a  b   c
0  aa  1   4
1  aa  2  19
2  aa  3  70
3  aa  4  20
4  bb  1   9
5  bb  2   3
6  bb  3  65
7  bb  4  20

If need only 2 columns:

L = sorted([(k,v1) for k,v in data.items() for k1,v1 in v.items()], 
            key=lambda x: (x[0], x[1]))
print (L)
[('aa', 4), ('aa', 19), ('aa', 20), ('aa', 70),
 ('bb', 3), ('bb', 9), ('bb', 20), ('bb', 65)]

df = pd.DataFrame(L, columns=list('ab'))

df = (pd.concat({k: pd.Series(v) for k, v in data.items()})
        .reset_index(level=1, drop=True)
        .reset_index())
df.columns = ['a','b']
print (df)
    a   b
0  aa   4
1  aa  19
2  aa  70
3  aa  20
4  bb   9
5  bb   3
6  bb  65
7  bb  20

Upvotes: 2

Related Questions