How to extract dictionary and sub dictionary

user_13721610 0
Viewed 147

Here is an original dataframe of 2 rows consisting of ID and ColumnA. Some row may have one detail.

ID  ColumnA
1   {'1': {'Order': '0', 'Result': ''},
     '2': {'Order': 'Yellow', 'Result': 'Red'},
     '3': {'Order': 'Clear', 'Result': 'Tight'},
     '4': {'Order': '1.000-1.030', 'Result': '1.015'}}
2   {'1': {'Order': '0', 'Result': '1.015'},
     '4': {'Order': '1.000-1.030', 'Result': '2.4'},
     '5': {'Order': '6.0-7.0', 'Result': ''},
     '6': {'Order': 'Negative', 'Result': 'Negative'},
     '7': {'Order': 'Negative', 'Result': 'Negative'},
     '8': {'Order': 'Negative', 'Result': 'Positive'},
     '9': {'Order': 'Negative', 'Result': ''}}

I want to extract from ColumnA to new dataframe

ID Column_ID       Column_Order        ColumnD_Result
1  1               0               
1  2               Yellow              Red
1  3               Clear               Tight
1  4               1.000-1.030         1.015
2  1               0                   1.015
2  4               1.000-1.030         2.4
2  5               6.0-7.0                 
2  6               Negative            Negative
2  7               Negative            Negative
2  8               Negative            Positive
2  9               Negative

How to write the extraction of dictionary?

pandas dataframe dictionary python
1 Answers

Extraction by looping on dictionary items:

import pandas as pd

data = [

    ['1', {'1': {'Order': '0', 'Result': ''},
     '2': {'Order': 'Yellow', 'Result': 'Red'},
     '3': {'Order': 'Clear', 'Result': 'Tight'},
     '4': {'Order': '1.000-1.030', 'Result': '1.015'}}],

    ['2', {'1': {'Order': '0', 'Result': '1.015'},
     '4': {'Order': '1.000-1.030', 'Result': '2.4'},
     '5': {'Order': '6.0-7.0', 'Result': ''},
     '6': {'Order': 'Negative', 'Result': 'Negative'},
     '7': {'Order': 'Negative', 'Result': 'Negative'},
     '8': {'Order': 'Negative', 'Result': 'Positive'},
     '9': {'Order': 'Negative', 'Result': ''}}]]

df = pd.DataFrame(data, columns=['ID', 'ColumnA'])

dfColumnA = pd.DataFrame([], columns=['ID', 'Column_ID', 'Column_Order', 'ColumnD_Result'])

i = 0
for index, row in df.iterrows():
    dictColumA = row['ColumnA']
    for column_ID, v in dictColumA.items():
        dfColumnA.loc[i] = [row['ID'], column_ID, v['Order'], v['Result']]
        i += 1

print(dfColumnA)

Output:

   ID Column_ID Column_Order ColumnD_Result
0   1         1            0               
1   1         2       Yellow            Red
2   1         3        Clear          Tight
3   1         4  1.000-1.030          1.015
4   2         1            0          1.015
5   2         4  1.000-1.030            2.4
6   2         5      6.0-7.0               
7   2         6     Negative       Negative
8   2         7     Negative       Negative
9   2         8     Negative       Positive
10  2         9     Negative
user_141488640
Related