Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

split a series into multiple pandas columns

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!

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading