Pandas: how to reorganize data in dictionaries

user_12425142 0
Viewed 72

I have a dataframe object structured flat like this (actually I have more than 6 variables)

 
   VAR1  VAR2 VAR3 VAR4 VAR5 VAR6
0     1     2    3    4    5    6 
1     2     4    6    8   10   12
2     3     6    9   12   15   18
3     4     8   12   16   20   24
4     5    10   15   20   25   30
5     6    12   18   24   30   36

However, in order to get compatibility with other applications I would like to have a structure like this

 
           NEW1                                             NEW2                                     NEW3 
0   {"id":{"VAR1": 1}}      {"AA":{"VAR2": 2, "VAR3": 3}, "CC":{"BB":{"VAR4": 4, "VAR5": 5}}}      {"TS": 6}
1   {"id":{"VAR1": 2}}      {"AA":{"VAR2": 4, "VAR3": 6}, "CC":{"BB":{"VAR4": 8, "VAR5":10}}}      {"TS":12}
2   {"id":{"VAR1": 3}}      {"AA":{"VAR2": 6, "VAR3": 9}, "CC":{"BB":{"VAR4":12, "VAR5":15}}}      {"TS":18}
3   {"id":{"VAR1": 4}}      {"AA":{"VAR2": 8, "VAR3":12}, "CC":{"BB":{"VAR4":16, "VAR5":20}}}      {"TS":24}
4   {"id":{"VAR1": 5}}      {"AA":{"VAR2":10, "VAR3":15}, "CC":{"BB":{"VAR4":20, "VAR5":25}}}      {"TS":30}
5   {"id":{"VAR1": 6}}      {"AA":{"VAR2":12, "VAR3":18}, "CC":{"BB":{"VAR4":24, "VAR5":30}}}      {"TS":36}

Is there any easy way to achieve this result? I have tried to use df.to_dict("index"), but it groups all the variables together while I need to split the dict into "subdictionaries" and associate them to these variables "AAA", "BBB", "id", "TS"

thank you for the tips and suggestions

pandas dictionary python
1 Answers

Create dictionaries defining the relationships of variables to other labels

replace = {'VAR6': 'TS'}
graph = {
    'VAR1': 'id', 'VAR2': 'AA', 'VAR3': 'AA',
    'VAR4': 'BB', 'VAR5': 'BB',
    'BB': 'CC',
    'id': 'NEW1', 'AA': 'NEW2', 'CC': 'NEW2', 'TS': 'NEW3'}

def traverse(k, d):
    path = []
    while k in d:
        k = d[k]
        path.append(k)
    return path

dat = {}
for i, rec in zip(df.index, df.to_dict('records')):
    for k, v in rec.items():
        k = replace.get(k, k)
        cur = dat
        path = traverse(k, thing)
        cur = dat.setdefault(path.pop(), {}).setdefault(i, {})
        while path:
            cur = cur.setdefault(path.pop(), {})
        cur[k] = v


pd.DataFrame(dat)

                  NEW1                                                                      NEW2        NEW3
0  {'id': {'VAR1': 1}}      {'AA': {'VAR2': 2, 'VAR3': 3}, 'CC': {'BB': {'VAR4': 4, 'VAR5': 5}}}   {'TS': 6}
1  {'id': {'VAR1': 2}}     {'AA': {'VAR2': 4, 'VAR3': 6}, 'CC': {'BB': {'VAR4': 8, 'VAR5': 10}}}  {'TS': 12}
2  {'id': {'VAR1': 3}}    {'AA': {'VAR2': 6, 'VAR3': 9}, 'CC': {'BB': {'VAR4': 12, 'VAR5': 15}}}  {'TS': 18}
3  {'id': {'VAR1': 4}}   {'AA': {'VAR2': 8, 'VAR3': 12}, 'CC': {'BB': {'VAR4': 16, 'VAR5': 20}}}  {'TS': 24}
4  {'id': {'VAR1': 5}}  {'AA': {'VAR2': 10, 'VAR3': 15}, 'CC': {'BB': {'VAR4': 20, 'VAR5': 25}}}  {'TS': 30}
5  {'id': {'VAR1': 6}}  {'AA': {'VAR2': 12, 'VAR3': 18}, 'CC': {'BB': {'VAR4': 24, 'VAR5': 30}}}  {'TS': 36}
user_23366540
Related