Split/Parse Values in One Column and create multiple Columns in Python-CodePudding

I have a column in which multiple fields are concatenated, they are delimited by . and : Example: Order ID:0001ACW120I .Record ID:01160000000UAxCCW .Type:Small .Amount:4596.35 .Booked Date 2021-06-14

I have tried the following:

df["Details"].str.split(r" .|:", expand=True)

But I lose the Decimal and the Amount doesn't Match.

I want to parse the Details Column to: |Details |Order ID |Record ID |Type |Amount |Booked Date | |-------------------------------------------------------------------------------------------------------|---------------|-----------------------|-------|---------------|---------------| |Order ID:0001ACW120I .Record ID:01160000000UAxCCW .Type:Small .Amount:4596.35 .Booked Date 2021-06-14 |0001ACW120I |01160000000UAxCCW |Small |4596.35 |2021-06-14 |

Thank you for your help and guidance

CodePudding user response：

Hope this will give you the solution you want.

Original Data:

df = pd.DataFrame({'A': ['Order ID:0001ACW120I .Record ID:01160000000UAxCCW .Type:Small .Amount:4596.35  .Booked Date 2021-06-14']})

Replacing . with : & then splitting with :

df = df['A'].replace(to_replace ='\s[.]', value = ':', regex = True).str.split(':', expand = True)

Final dataset. Rename the columns.

print(df)