Normalizing JSON in Python

Question

I have a JSON response which is:

{"Neighborhoods":[    
{"Name":"Project A",
    "Balcony":false,
    "Sauna":false,
    "ProjectIds":["f94d25e2-3709-42bc-a4a2-bf8e073e9790","b106b4f1-32b9-4fc2-b2b3-55a7e5348c24"],
    "NextViewing":null,
    "Location":{"Lat":52.484295,"Lon":13.5058143},
    "SalesStatus":"ForSale",
    "TypeOfContract":7},
    {"Name"

I then use pd.json_normalize(Response,'Neighborhoods') for normalizing. The Location part is then flattened out as I want, as two columns "Location.Lat" and "Location.Lon". My issue is "ProjectIds" which I get in one column as

['f94d25e2-3709-42bc-a4a2-bf8e073e9790', 'b106b4f1-32b9-4fc2-b2b3-55a7e5348c24']

But I would like to have it without '[] and the space in the middle. So that the output would be

f94d25e2-3709-42bc-a4a2-bf8e073e9790,b106b4f1-32b9-4fc2-b2b3-55a7e5348c24

SeaBean · Accepted Answer · 2021-04-25 11:25:38Z

1

You can use .str.join() to convert the list of strings into comma separated string, as follows:

df['ProjectIds'] = df['ProjectIds'].str.join(',')

Demo

Response ={"Neighborhoods":[    
 {"Name":"Project A",
"Balcony":'false',
"Sauna":'false',
"ProjectIds":["f94d25e2-3709-42bc-a4a2-bf8e073e9790","b106b4f1-32b9-4fc2-b2b3-55a7e5348c24"],
"NextViewing":'null',
"Location":{"Lat":52.484295,"Lon":13.5058143},
"SalesStatus":"ForSale",
"TypeOfContract":7}]}

df = pd.json_normalize(Response,'Neighborhoods') 

df['ProjectIds'] = df['ProjectIds'].str.join(',')




print(df)

        Name Balcony  Sauna                                                                 ProjectIds NextViewing SalesStatus  TypeOfContract  Location.Lat  Location.Lon
0  Project A   false  false  f94d25e2-3709-42bc-a4a2-bf8e073e9790,b106b4f1-32b9-4fc2-b2b3-55a7e5348c24        null     ForSale               7     52.484295     13.505814

edited Apr 25, 2021 at 11:25

answered Apr 25, 2021 at 11:00

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

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

Comments

Shubham Periwal · Accepted Answer · 2021-04-25 10:37:36Z

1

Use ",".join() on the projectIds to convert them to string from list before you pass it to json_nornalize

answered Apr 25, 2021 at 10:37

Shubham Periwal

2,2582 gold badges10 silver badges29 bronze badges

1 Comment

NewDev Over a year ago

Thanks Shubham! Can you please explain it in a bit more detail. How should I use join()?

Aditya · Accepted Answer · 2021-04-25 10:48:49Z

1

The way you can solve this is by using ','.join() on the ProjectIds column:

data ={"Neighborhoods":[    
 {"Name":"Project A",
"Balcony":'false',
"Sauna":'false',
"ProjectIds":["f94d25e2-3709-42bc-a4a2-bf8e073e9790","b106b4f1-32b9-4fc2-b2b3-55a7e5348c24"],
"NextViewing":'null',
"Location":{"Lat":52.484295,"Lon":13.5058143},
"SalesStatus":"ForSale",
"TypeOfContract":7}]}

df = pd.json_normalize(data['Neighborhoods'])
df['ProjectIds'] = df['ProjectIds'].apply(lambda x: ','.join(x))

edited Apr 25, 2021 at 10:48

answered Apr 25, 2021 at 10:41

Aditya

1,3771 gold badge9 silver badges19 bronze badges

3 Comments

NewDev Over a year ago

Thanks! Will try it.The last df you have alone on the last row, I assume this should be deleted?

Aditya Over a year ago

@NewDev Please mark as solved if it solved your problem. Thanks

NewDev Over a year ago

Can I mark two posts as solved? Then I will do so. Your post was very similar to an earlier response which also solved my problem.

Collectives™ on Stack Overflow

Normalizing JSON in Python

3 Answers 3

Demo

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Demo

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related