How to split a column in many different columns?

Question

I have a dataset and in one of it columns I have many values that I want to convert to new columns:

"{'availabilities': {'bikes': 4, 'stands': 28, 'mechanicalBikes': 4, 'electricalBikes': 0, 'electricalInternalBatteryBikes': 0, 'electricalRemovableBatteryBikes': 0}, 'capacity': 32}"

I tried to use str.split() and received the error because of the patterns.

bikes_table_ready[['availabilities',
                   'bikes',
                   'stands',
                   'mechanicalBikes',
                   'electricalBikes',
                   'electricalInternalBatteryBikes',
                   'electricalRemovableBatteryBikes',
                   'capacity']]= bikes_table_ready.totalStands.str.extract('{.}', expand=True)

ValueError: pattern contains no capture groups

Which patterns should I use to have it done?

Answer 1

IIUC, use ast.literal_eval with pandas.json_normalize.

With a dataframe df with two columns (id) and the column to be splitted (col), it gives this :

import ast
​
df["col"] = df["col"].apply(lambda x: ast.literal_eval(x.strip('"')))
​
out = df.join(pd.json_normalize(df.pop("col").str["availabilities"]))

# Output :

print(out.to_string())
      id  bikes  stands  mechanicalBikes  electricalBikes  electricalInternalBatteryBikes  electricalRemovableBatteryBikes
0  id001      4      28                4                0                               0                                0