someRandomGuy someRandomGuy - 3 years ago 124
Python Question

Pandas create a df from dict of dict of lists

I've a data structure which is dynamically populated, so number of keys and sub-keys are unknown. I want to convert it into a Pandas df. The structure looks like this

datastore = {
"user1":{
"time1":[1,2,3,4],
"time2":[5,6,7,8],
"time3":[1,2,3,4] },
"user2":{
"time1":[1,2,3,4],
"time2":[5,6,7,8] }
}


A dict of dicts with lists for value

I want to convert it into pandas df like this

index users times x y z k
0 user1 time1 1 2 3 4
1 user1 time2 5 6 7 8
2 user1 time3 1 2 3 4
3 user2 time1 1 2 3 4
4 user2 time2 5 6 7 8
....


I've tried pd.DataFrame(dict), from_dict method but couldn't get it to work. Any help would be appreciated.

EDIT: Sorry about the syntax error, fixed

Answer Source

Here's an approach

datastore = {
"user1":{
    "time1":[1,2,3,4], 
    "time2":[5,6,7,8], 
    "time3":[1,2,3,4] },
"user2":{ 
    "time1":[1,2,3,4], 
    "time2":[5,6,7,8]}
}

We can use pd.DataFrame() with the dict then stack() it then reset_index() it

df = pd.DataFrame(datastore).stack().reset_index()
print(df)
  level_0 level_1             0
0   time1   user1  [1, 2, 3, 4]
1   time1   user2  [1, 2, 3, 4]
2   time2   user1  [5, 6, 7, 8]
3   time2   user2  [5, 6, 7, 8]
4   time3   user1  [1, 2, 3, 4]

Now we 'split' the list in 0 with an apply of pd.Series and then join that back to level_1 and level_2. Some column renaming and we're done

df = df[['level_1', 'level_0']].join(df[0].apply(pd.Series))
df.columns = ['users', 'times', 'x', 'y', 'z', 'k']
print(df)
   users  times  x  y  z  k
0  user1  time1  1  2  3  4
1  user2  time1  1  2  3  4
2  user1  time2  5  6  7  8
3  user2  time2  5  6  7  8
4  user1  time3  1  2  3  4
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download