pandas pivot table convert to defaultdict(list)

Question

I use pandas pivot table. I need convert data from pivot table to defaultdict(list)

here is my pivot table data to dict (df.to_dict()):

{
    'count': {
        ('formation1', 'position1'): 2,
        ('formation1', 'position2'): 1
    },
    'personData.employeeContract.edges': {
        ('formation1','position1'): 1,
        ('formation1', 'postition2'): 0
    },
    'total_count': {
        ('formation1', 'position1'): 2,
        ('formation1', 'position2'): 1
    },
    'count_with_contract': {
        ('formation1', 'position1'): 1,
        ('formation1', 'position2'): 0
    },
    'percent': {
        ('formation1', 'position1'): 0.5,
        ('formation1', 'position2'): 0.0
    }
}

I need transfer data above to below:

{
    'formation1': [{
            'position1': {
                'total_count': 2.0
                'count_with_contract': 1.0
                'percent': 0.0
                
            } {
                'position2': {
                    'total_count': 1.0
                    'count_with_contract': 0.0
                    'percent': 0.0
                }
            ]

        }
    }

How can i do it?

Answer 1

First filter columns names which need and then in dict comprehension create nested dictionaries:

df1 = df[['total_count','count_with_contract','percent']]

d = {i:[g.reset_index(level=0, drop=True).to_dict('index')] for i,g in df1.groupby(level=0)}
print (d)
{'formation1':[{'position1': {'total_count': 2, 'count_with_contract': 1, 'percent': 0.5}, 
                'position2': {'total_count': 1, 'count_with_contract': 0, 'percent': 0.0}}]}

Solution with defaultdict (output is a bit different):

from collections import defaultdict

df1 = df[['total_count','count_with_contract','percent']]
print (df1)

d = defaultdict(list)
for (f, pos), x in df1.T.items():
    d[f].append({pos: x.to_dict()})
    
print (d)
defaultdict(<class 'list'>, 
{'formation1': [{'position1': {'total_count': 2.0, 'count_with_contract': 1.0, 'percent': 0.5}}, 
                {'position2': {'total_count': 1.0, 'count_with_contract': 0.0, 'percent': 0.0}}]})