creating dataframe using list of dictionaries

Question

I have a list of dictionary as follows

test_lst = [{'col1': 'https://link1.com', 'col2':['data1', 'data2', 'data3']},
            {'col1': 'https://link2.com', 'col2':['data3', 'data4', 'data5']},
            {'col1': 'https://link3.com', 'col2':['data6', 'data7', 'data8']}]

I want to create a dataframe using this list. The dataframe should be as follows

    col1                 col2
0   https://link1.com    data1
1   https://link1.com    data2
2   https://link1.com    data3
3   https://link2.com    data3
4   https://link2.com    data4
5   https://link2.com    data5
6   https://link3.com    data6
7   https://link3.com    data7
8   https://link3.com    data8

But passing test_lst directly to pd.DataFrame seems to create the dataframe as follows

    col1                col2
0   https://link.com    [data1, data2, data3]
1   https://link.com    [data3, data4, data5]
2   https://link.com    [data6, data7, data8]

This is my code

test_lst = [{'col1': 'https://link1.com', 'col2':['data1', 'data2', 'data3']},
            {'col1': 'https://link2.com', 'col2':['data3', 'data4', 'data5']},
            {'col1': 'https://link3.com', 'col2':['data6', 'data7', 'data8']}]

df = pd.DataFrame(test_lst)

What am I doing wrong?

jezrael · Accepted Answer · 2021-05-31 06:12:12Z

Use DataFrame.explode is simpliest solution:

df = pd.DataFrame(test_lst).explode('col2')
print (df)
    
                col1   col2
0  https://link1.com  data1
0  https://link1.com  data2
0  https://link1.com  data3
1  https://link2.com  data3
1  https://link2.com  data4
1  https://link2.com  data5
2  https://link3.com  data6
2  https://link3.com  data7
2  https://link3.com  data8

Or create one element lists with scalars like col1 and then flatten with zip_longest, last forward filling missing values:

from  itertools import zip_longest

test_lst = [{k: v if isinstance(v, list) else [v] for k, v in x.items()} for x in test_lst]
   
L = [y for x in test_lst for y in zip_longest(*x.values())]

df = pd.DataFrame(L, columns=test_lst[0].keys()).ffill()
print (df)
                col1   col2
0  https://link1.com  data1
1  https://link1.com  data2
2  https://link1.com  data3
3  https://link2.com  data3
4  https://link2.com  data4
5  https://link2.com  data5
6  https://link3.com  data6
7  https://link3.com  data7
8  https://link3.com  data8

Collectives™ on Stack Overflow

creating dataframe using list of dictionaries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related