Extract list of dictionaries from dataframe column

user_11836796 0
Viewed 34

Dataframe is like that

dataframe

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?

pandas dataframe dictionary python
1 Answers

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
user_100359850
Related