how to split a nested dictionary inside a column of a dataframe into new rows?

user_12977233 0
Viewed 445

I have a dataframe :

Col1 Col2 Col3
01   ABC  {'link':'http://smthing1}
02   DEF  {'link':'http://smthing2}

I need to split col3 into new rows: expected output dataframe :

Col1 Col2 Col3
01   ABC  'http://smthing1'
02   DEF  'http://smthing2'

This doesnt seem to work :

df= df.apply(pd.Series)
pandas dataframe dictionary python
2 Answers

Use Series.str.get, but first convert to dictionaries if necessary:

#converting to dicts
#import ast
#df['Col3'] = df['Col3'].apply(ast.literal_eval)
df['Col3'] = df['Col3'].str.get('link')
user_29010020

If you like long code:

import pandas as pd

my_list = [[1,   'ABC',  {'link':'http://smthing1'}],  # list 
[2,   'DEF',  {'link':'http://smthing2'}]]

df = pd.DataFrame(my_list, columns=['col1', 'col2', 'col3']) #convert to pandas dataframe

convert_col3_to_dict = dict(df['col3']) #convert string to dict 

list1 = []
list2 = []

for i in range(len(my_list)):
    elements = my_list[i][:2]
    #remove empty list
    elements = [x for x in elements if x != []]
    # print(elements)
    list1.append(elements)

for key, val in convert_col3_to_dict.items(): #Iterating through the col3
for k,v in val.items(): #iterating through values in col3
    # print([v]) # printing the value
    list2.append([v])

new_list = []

for f, b in zip(list1, list2):
    # print(f, b)
    tem_list = f + b
    new_list.append(tem_list)

new_df = pd.DataFrame(new_list, columns=['col1', 'clo2', 'col3'])

print(new_df)

Result:

   col1 clo2             col3
0     1  ABC  http://smthing1
1     2  DEF  http://smthing2
user_110956440
Related