Skip to content
Advertisement

Normalizing JSON in Python

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

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement