How to expand array type column in Pandas to individual columns

user_10722752 0
Viewed 61

I have a df, sample data looks like below:

df

user    collaborators
A       {'first_name' : 'jack', 'last_name' : 'abc', 'id' : 'a12@email.com'}, {'first_name' : 'john', 'last_name' : 'pqr', 'id' : 'd23@email.com'}
B       {'first_name' : 'tom', 'last_name' : 'pwc', 'id' : 'd99@email.com'}
C       {'first_name' : 'jill', 'last_name' : 'def', 'id' : 'x45@email.com'}, {'first_name' : 'jill', 'last_name' : 'def', 'id' : 'x45@email.com'}

I need to get the first_name, last_name and id in individual columns. Expected Output:

enter image description here

I first tried to remove the square brackets using:

df['collaborators'].str.strip('{}')
df['collaborators'].str.replace('[{}]', '')

But I got only NaN as the result, not sure why. I thought of exploding the column after converting it to a list and extracting first_name, last_name and id. But all this looks like lot of steps. Could someone please let me know if there's a shorter way.

Updated DF:

enter image description here

I used to_list to convert the three columns into list:

df['first_name'] = df['first_name'].to_list()
df['last_name'] = df['last_name'].to_list()
df['id'] = df['id'].to_list()

I then tried below code from SO:

 df.set_index('collaborators').apply(lambda x:
    x.apply(pd.Series).stack()).reset_index().drop('level_1', 1)

But it didn't work for me.

pandas python
2 Answers

Use ast.literal_eval with add [] for lists of DataFrames, so possible use DataFrame.explode with json_normalize and last append to original with remove helper column new in DataFrame.pop:

import ast

df['new'] = df['collaborators'].apply(lambda x: ast.literal_eval('[' + x + ']'))

df1 = df.explode('new').reset_index(drop=True)

df = df1.join(pd.json_normalize(df1.pop('new')))
print (df)

  user                                      collaborators first_name  \
0    A  {'first_name' : 'jack', 'last_name' : 'abc', '...       jack   
1    A  {'first_name' : 'jack', 'last_name' : 'abc', '...       john   
2    B  {'first_name' : 'tom', 'last_name' : 'pwc', 'i...        tom   
3    C  {'first_name' : 'jill', 'last_name' : 'def', '...       jill   
4    C  {'first_name' : 'jill', 'last_name' : 'def', '...       jill   

  last_name             id  
0       abc  a12@email.com  
1       pqr  d23@email.com  
2       pwc  d99@email.com  
3       def  x45@email.com  
4       def  x45@email.com
user_29010020

Use:

(df[0]+",").apply(eval).explode().apply(pd.Series)

Output:

enter image description here

Complete code:

data="""{'first_name' : 'jack', 'last_name' : 'abc', 'id' : 'a12@email.com'},  {'first_name' : 'john', 'last_name' : 'pqr', 'id' : 'd23@email.com'}
{'first_name' : 'tom', 'last_name' : 'pwc', 'id' : 'd99@email.com'}
{'first_name' : 'jill', 'last_name' : 'def', 'id' : 'x45@email.com'},  {'first_name' : 'jill', 'last_name' : 'def', 'id' : 'x45@email.com'}"""
df = pd.DataFrame([x for x in data.split('\n')])#
(df[0]+",").apply(eval).explode().apply(pd.Series)
user_72816750
Related