Converting nested lists and dictionary to Dataframe?

Question

I have a dictionary with thousand keys something as follows:

 my_dictionary:
                {'key1':[['ft1',[2,4,12,2]],['ft2',[0,3,3,1]],'key2':[['ft1',[5,0,2,9]],['ft2',[10,39,3,2]]}

Now, I want to convert this dictionary into a dataframe, where the keys should be a specific column, and the features (ft1, ft2, ..) and their values converted to different column as well. So my desired dataframe should be like this:

 my_new_dataframe:
             ID, ft1_sig,ft1_med,ft1_les,ft1_non,ft2_sig,ft2_med,ft2_les,ft2_non,... 
             key1    2       4      12      2       0       3       3.     1
             key2    5.      0.      2.     9.      10.     39     3.     2
             ...
             keyn.  ..       ..      ..     ..

Richard K Yu · Accepted Answer · 2019-05-07 18:17:10Z

I attempted a solution at this, but it requires that each of the keys (i.e. key1, key2, etc.) contains the ft attribute you want in a dictionary. Also, are you missing a "]" for the original list? It was mismatched when I pasted into my interpreter.

import pandas as pd

#added method to change your original dictionary to one that I can manipulate with the method below.

#If you compare the values of new_dict and data using ==, it returns true.
my_dictionary = {'key1':[['ft1',[2,4,12,2]],['ft2',[0,3,3,1]]],'key2':[['ft1',[5,0,2,9]],['ft2',[10,39,3,2]]]}

new_dict ={}
for element in my_dictionary:
    print(element)
    print(my_dictionary[element])
    new_dict[element] = dict(my_dictionary[element])
    print(new_dict)

data = {
    'key1':{
        'ft1':[2,4,12,2],
        'ft2':[0,3,3,1]
        },
    'key2':{
        'ft1':[5,0,2,9],
        'ft2':[10,39,3,2]
        }
    }

keys = list(data.keys())

df = pd.DataFrame.from_dict(data).T

df2 = pd.DataFrame(df.ft1.values.tolist()).add_prefix('ft1_')
df3 = pd.DataFrame(df.ft2.values.tolist()).add_prefix('ft2_')
df4 = pd.merge(df2,df3,left_index=True,right_index=True)
df4.index=keys

print(df4)

Here is the output:

jose_bacoy · Accepted Answer · 2019-05-07 18:24:04Z

I added more data in your example to show that the script will be flexible if new features or rows (keys) are added.

Collect all features in a list (colname)
Get only the number list
Assign new column names
Create a function that will return each items in the number list
Use apply function to create new columns
Remove/drop the temp columns

Here it is

mydict = {'key1':[['ft1',[2,4,12,2]],['ft2',[0,3,3,1]],['ft3',[0,3,3,1]]],
          'key2':[['ft1',[5,0,2,9]],['ft2',[10,39,3,2]],['ft3',[0,3,3,1]]]
         ,'key3':[['ft1',[5,0,2,9]],['ft2',[10,39,3,2]],['ft3',[0,3,3,1]]]} 
df = pd.DataFrame(mydict).T 
colname = [df[c][0][0] for c in df]
df = df.applymap(lambda c: c[1])
df.reset_index(level=0, inplace=True)
df.columns=['ID'] + colname
s=['_sig','_med','_les','_non']
def f(x):
    return pd.Series([x[0], x[1], x[2], x[3]])
for col in colname:
    df[[col+'_sig', col+'_med', col+'_les', col+'_non']]= df[col].apply(lambda x: f(x))
df.drop(colname, axis=1, inplace=True)
df

Result:

     ID ft1_sig ft1_med ft1_les ft1_non ft2_sig ft2_med ft2_les ft2_non ft3_sig ft3_med ft3_les ft3_non
0   key1    2    4  12  2    0   3  3   1   0   3   3   1
1   key2    5    0   2  9   10  39  3   2   0   3   3   1
2   key3    5    0   2  9   10  39  3   2   0   3   3   1

Collectives™ on Stack Overflow

Converting nested lists and dictionary to Dataframe?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related