[FIXED] How to convert a list with nested dictionary into dataframe to save as a csv?

Issue

I have the following list:

a = [{'cluster_id': 0, 'points': [{'id': 1, 'name': 'Alice', 'lat': 52.523955, 'lon': 13.442362}, {'id': 2, 'name': 'Bob', 'lat': 52.526659, 'lon': 13.448097}]}, {'cluster_id': 0, 'points': [{'id': 1, 'name': 'Alice', 'lat': 52.523955, 'lon': 13.442362}, {'id': 2, 'name': 'Bob', 'lat': 52.526659, 'lon': 13.448097}]}, {'cluster_id': 1, 'points': [{'id': 3, 'name': 'Carol', 'lat': 52.525626, 'lon': 13.419246}, {'id': 4, 'name': 'Dan', 'lat': 52.52443559865125, 'lon': 13.41261723049818}]}, {'cluster_id': 1, 'points': [{'id': 3, 'name': 'Carol', 'lat': 52.525626, 'lon': 13.419246}, {'id': 4, 'name': 'Dan', 'lat': 52.52443559865125, 'lon': 13.41261723049818}]}]

I would like to convert this list into a dataframe with the following columns:

1. cluster_id
2. id
3. name
4. lat
5. lon

to save it as a csv. I tried a couple of solutions which I found like:

pd.concat([pd.DataFrame(l) for l in a],axis=1).T

But it didn't work as I expected.

What is the mistake I am doing?

Thanks

Solution

You can use pd.json_normalize

df = pd.json_normalize(a, record_path='points', meta='cluster_id')

print(df)

   id   name        lat        lon cluster_id
0   1  Alice  52.523955  13.442362          0
1   2    Bob  52.526659  13.448097          0
2   1  Alice  52.523955  13.442362          0
3   2    Bob  52.526659  13.448097          0
4   3  Carol  52.525626  13.419246          1
5   4    Dan  52.524436  13.412617          1
6   3  Carol  52.525626  13.419246          1
7   4    Dan  52.524436  13.412617          1

Answered By - Ynjxsjmh