Extract List in Column Pandas Dataframe

Question

please help..

file.json
[
{"fullname": "mona", "phones": [{"phone": "21323131"}], "areas": [{"area": "Texas"}, {"area": "New York"}] }, 
{"fullname": "joni", "phones": [{"phone": "546465464"},{"phone": "45345353"}], "areas": [{"area": "California"},{"area": "San Jose"}] }
]

i have dataframe like this

import pandas as pd

df = pd.read_json('file.json')

print(df.head(2))

Output:

fullname   phones                                          Areas
mona       [{'phone': '21323131'}]                         [{'area': 'Texas'}, {'area': 'New York'}]   
joni       [{'phone': '546465464'},{'phone': '45345353'}]  [{'area': 'California'},{'area': 'San Jose'}]

how to extract dataframe to be like this?

fullname   phone        Areas
mona       21323131     Texas, New York 
joni       546465464    California, San Jose
joni       45345353     California, San Jose

Can you paste your file.json contents in the question? It will be easier. — NYC Coder
– NYC Coder, Commented Oct 24, 2020 at 15:06
@NYCCoder, sorry. I have fixed the sample json contents. please check my friend — Hendra
– Hendra, Commented Oct 24, 2020 at 15:33

Shubham Sharma · Accepted Answer · 2020-10-24 16:06:49Z

2

Let's try explode along with Series.str.get:

s = df['areas'].explode().str.get('area').groupby(level=0).agg(', '.join)
d = df.explode('phones').assign(areas=s, phones=lambda x: x['phones'].str.get('phone'))

print(d)

  fullname     phones                 areas
0     mona   21323131       Texas, New York
1     joni  546465464  California, San Jose
1     joni   45345353  California, San Jose

edited Oct 24, 2020 at 16:06

answered Oct 24, 2020 at 16:00

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Riccardo Bucco · Accepted Answer · 2020-10-24 16:01:09Z

1

Here is a possible solution:

import json
import pandas as pd

with open('file.json') as f:
    data = json.load(f)

exploded_data = [{'fullname': x['fullname'],
                  'phone': p['phone'],
                  'area': a['area']}
                 for x in data for p, a in zip(x['phones'], x['areas'])]

df = pd.DataFrame(exploded_data)

answered Oct 24, 2020 at 16:01

Riccardo Bucco

15.5k4 gold badges29 silver badges57 bronze badges

Collectives™ on Stack Overflow

Extract List in Column Pandas 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