Extract list of dictionaries from dataframe column

Dataframe is like that

and I want to extract that row values into same dataframe.

df["row"][0] values like

[{‘acceleration’: 0,
‘distance’: 0.917,
‘energy_cost’: 4.644,
‘estimated_energy’: 76.323,
‘half’: 1,
‘metabolic_power’: 17.043,
‘pos_x’: -27.14,
‘pos_y’: 50.03,
‘speed’: 3.67,
‘video_second’: 5.0},
{‘acceleration’: 0,
‘distance’: 0.848,
‘energy_cost’: 4.647,
‘estimated_energy’: 70.546,
‘half’: 1,
‘metabolic_power’: 15.753,
‘pos_x’: -27.988,
‘pos_y’: 50.022,
‘speed’: 3.39,
‘video_second’: 5.25},
{‘acceleration’: 0,
‘distance’: 0.848,
‘energy_cost’: 3.978,
‘estimated_energy’: 60.39,
‘half’: 1,
‘metabolic_power’: 13.485,
‘pos_x’: -28.835,
‘pos_y’: 50.015,
‘speed’: 3.39,
‘video_second’: 5.5},
{‘acceleration’: 0,
‘distance’: 0.848,
‘energy_cost’: 4.647,
‘estimated_energy’: 70.546,
‘half’: 1,
‘metabolic_power’: 15.753,
‘pos_x’: -29.682,
‘pos_y’: 50.008,
‘speed’: 3.39,
‘video_second’: 5.75},
{‘acceleration’: 0,
‘distance’: 0.848,
‘energy_cost’: 4.647,
‘estimated_energy’: 70.546,
‘half’: 1,
‘metabolic_power’: 15.753,
‘pos_x’: -30.53,
‘pos_y’: 50.0,
‘speed’: 3.39,
‘video_second’: 6.0},
{‘acceleration’: 0.445,
‘distance’: 0.959,
‘energy_cost’: 4.647,
‘estimated_energy’: 79.805,
‘half’: 1,
‘metabolic_power’: 17.82,
‘pos_x’: -31.345,
‘pos_y’: 50.505,
‘speed’: 3.835,
‘video_second’: 6.25},
{‘acceleration’: 0,
‘distance’: 0.959,
‘energy_cost’: 5.91,
‘estimated_energy’: 101.505,
‘half’: 1,
‘metabolic_power’: 22.666,
‘pos_x’: -32.16,
‘pos_y’: 51.01,
‘speed’: 3.835,
‘video_second’: 6.5},
{‘acceleration’: 0.0,
‘distance’: 0.959,
‘energy_cost’: 4.647,
‘estimated_energy’: 79.805,
‘half’: 1,
‘metabolic_power’: 17.82,
‘pos_x’: -32.975,
‘pos_y’: 51.515,
‘speed’: 3.835,
‘video_second’: 6.75},
{‘acceleration’: 0,
‘distance’: 0.959,
‘energy_cost’: 4.644,
‘estimated_energy’: 79.761,
‘half’: 1,
‘metabolic_power’: 17.81,
‘pos_x’: -33.79,
‘pos_y’: 52.02,
‘speed’: 3.835,
‘video_second’: 7}]

Desired dataframe is like id, number, acc, distance, … and sorted with video second.

How can I do that?

>Solution :

Try:

from ast import literal_eval

# if not converted already, apply ast.literal_eval
df["row"] = df["row"].apply(literal_eval)

df = df.explode("row")
df = pd.concat([df, df.pop("row").apply(pd.Series)], axis=1).sort_values(
    "video_second"
)

print(df)

Prints:

    id  number  acceleration  distance  energy_cost  estimated_energy  half  metabolic_power   pos_x   pos_y  speed  video_second
0  340       6         0.000     0.917        4.644            76.323   1.0           17.043 -27.140  50.030  3.670          5.00
0  340       6         0.000     0.848        4.647            70.546   1.0           15.753 -27.988  50.022  3.390          5.25
0  340       6         0.000     0.848        3.978            60.390   1.0           13.485 -28.835  50.015  3.390          5.50
0  340       6         0.000     0.848        4.647            70.546   1.0           15.753 -29.682  50.008  3.390          5.75
0  340       6         0.000     0.848        4.647            70.546   1.0           15.753 -30.530  50.000  3.390          6.00
0  340       6         0.445     0.959        4.647            79.805   1.0           17.820 -31.345  50.505  3.835          6.25
0  340       6         0.000     0.959        5.910           101.505   1.0           22.666 -32.160  51.010  3.835          6.50
0  340       6         0.000     0.959        4.647            79.805   1.0           17.820 -32.975  51.515  3.835          6.75
0  340       6         0.000     0.959        4.644            79.761   1.0           17.810 -33.790  52.020  3.835          7.00