Dataframe to Dictionary including List of dictionaries

user_14090465 0
Viewed 119

I am trying to convert below dataframe to dictionary. I want to group via column A and take a list of common sequence. for e.g.

Example 1:

    n1 v1  v2 
2    A  C   3
3    A  D   4
4    A  C   5
5    A  D   6

Expected output:

{'A': [{'C':'3','D':'4'},{'C':'5','D':'6'}]}

Example 2:

n1   n2  v1  v2 
s1    A  C   3
s1    A  D   4
s1    A  C   5
s1    A  D   6
s1    B  P   6
s1    B  Q   3

Expected Output:

{'s1': {'A': [{'C': 3, 'D': 4}, {'C': 5, 'D': 6}], 'B': {'P': 6, 'Q': 3}}}

so basically C and D are repeating as a sequence,I want to club C and D in one dictionary and make a list of if it occurs multiple times.

Please note (Currently I am using below code):

def recur_dictify(frame):
    if len(frame.columns) == 1:
        if frame.values.size == 1: return frame.values[0][0]
        return frame.values.squeeze()
    grouped = frame.groupby(frame.columns[0])
    d = {k: recur_dictify(g.iloc[:,1:]) for k,g in grouped}
    return d

This returns :

{s1 : {'A': {'C': array(['3', '5'], dtype=object), 'D': array(['4', '6'], dtype=object),'B':{'E':'5','F':'6'}}

Also, there can be another series of s2 having E,F,G,E,F,G repeating and some X and Y having single values

pandas dataframe list dictionary python
2 Answers

Lets create a function dictify which create a dictionary with top level keys from name column and club's the repeating occurrences of values in column v1 into different sub dictionaries:

from collections import defaultdict

def dictify(df):
    dct = defaultdict(list)
    for k, g in df.groupby(['n1', df.groupby(['n1', 'v1']).cumcount()]):
        dct[k[0]].append(dict([*g[['v1', 'v2']].values]))
    return dict(dct)

dictify(df)

{'A': [{'C': 3, 'D': 4}, {'C': 5, 'D': 6}]}

UPDATE:

In case there can be variable number of primary grouping keys i.e. [n1, n2, ...] we can use a more generic method:

def update(dct, keys, val):
    k, *_ = keys
    dct[k] = update(dct.get(k, {}), _, val) if _ \
        else [*np.hstack([dct[k], [val]])] if k in dct else val
    return dct

def dictify(df, keys):
    dct = dict()
    for k, g1 in df.groupby(keys):
        for _, g2 in g1.groupby(g1.groupby('v1').cumcount()):
            update(dct, k, dict([*g2[['v1', 'v2']].values]))

    return dict(dct)

dictify(df, ['n1', 'n2'])

{'s1': {'A': [{'C': 3, 'D': 4}, {'C': 5, 'D': 6}], 'B': {'P': 6, 'Q': 3}}}
user_128331660

Here is a simple one line statement that solves your problem:

def df_to_dict(df):
    return {name: [dict(x.to_dict('split')['data'])
                   for _, x in d.drop('name', 1).groupby(d.index // 2)]
            for name, d in df.groupby('name')}

Here is an example:

df = pd.DataFrame({'name': ['A'] * 4,
                   'v1': ['C', 'D'] * 2,
                   'v2': [3, 4, 5, 6]})
print(df_to_dict(df))

Output:

{'A': [{'C': 3, 'D': 4}, {'C': 5, 'D': 6}]}
user_52961060
Related