Pandas: create dictionary from hierarchical data

Question

Say I have the following dataframe df:

      A            B       
0     mother1      NaN
1     NaN          child1
2     NaN          child2
3     mother2      NaN
4     NaN          child1
5     mother3      NaN
6     NaN          child1
7     NaN          child2
8     NaN          child3

How could you turn this into a dictionary that yields:

results={'mother1':['child1','child2'],'mother2':['child1'],'mother3':['child1','child2','child3']}

My take on it:

import pandas as pd
import numpy as np

results={}

for index1,row1 in df.iterrows():
    if row1['A'] is not np.nan:
        children=[]
        for index2,row2 in df.iterrows():
            if row2['B'] is not np.nan:
                children.append(row2['B'])
        results[row1['A']]=children

However, the result is wrong:

In[1]: results
Out[1]: 
{'mother1': ['child1', 'child2', 'child1', 'child1', 'child2', 'child3'],
 'mother2': ['child1', 'child2', 'child1', 'child1', 'child2', 'child3'],
 'mother3': ['child1', 'child2', 'child1', 'child1', 'child2', 'child3']}

sjw · Accepted Answer · 2018-05-14 09:54:15Z

3

Here's one way:

df['A'].fillna(method='ffill', inplace=True)

Giving:

         A       B
0  mother1     NaN
1  mother1  child1
2  mother1  child2
3  mother2     NaN
4  mother2  child1
5  mother3     NaN
6  mother3  child1
7  mother3  child2
8  mother3  child3

Then drop the child NAs:

df.dropna(subset=['B'], inplace=True)

Giving:

         A       B
1  mother1  child1
2  mother1  child2
4  mother2  child1
6  mother3  child1
7  mother3  child2
8  mother3  child3

You can then use groupby and a dictionary comprehension to get you to the final result:

results = {k: v['B'].tolist() for k, v in df.groupby('A')}

Results:

{'mother1': ['child1', 'child2'],
 'mother2': ['child1'],
 'mother3': ['child1', 'child2', 'child3']}

answered May 14, 2018 at 9:54

sjw

6,5512 gold badges30 silver badges41 bronze badges

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

1 Comment

FaCoffee Over a year ago

It works and it is much quicker than the nested for loop I have come up with. I believe this can be extended to situations where we have grandparents->parents->children.

Collectives™ on Stack Overflow

Pandas: create dictionary from hierarchical data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related