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