I have a pandas dataframe, one of the columns has a series in there. The structure is as follows:
Date Col1
2022-01-02 'Amt_Mean 2022.0\nAmt_Med 5.0\nAmt_Std 877.0\ndtype: float64'
2022-01-03 'Amt_Mean 2025.0\nAmt_Med 75.0\nAmt_Std 27.0\ndtype: float64'
I want to reshape this such that I get the following output
Date Amt_Mean Amt_Med Amt_Std
2022-01-02 2022.0 5.0 877.0
2022-01-03 2025.0 75.0 27.0
How can I achieve this? I tried df['Col1'][0][1] which gives me the first amount and I can potentially for loop it, but seems there should be a more easy (pythonic) way of doing this.
Thanks!
>Solution :
Some string processing to convert each string into a dict, convert those to a dataframe, and concatenate that with the original:
new_df = pd.DataFrame([dict(re.split(r'\s+', y) for y in x.split('\n')[:-1]) for x in df['Col1']])
df = pd.concat([df.drop('Col1', axis=1), new_df], axis=1)
Output:
>>> df
Date Amt_Mean Amt_Med Amt_Std
0 2022-01-02 2022.0 5.0 877.0
1 2022-01-03 2025.0 75.0 27.0