split the text with random spaces-CodePudding

I have a field with random spaces

ID    field1
1     C 2205  123 ABC
2     B  111 345  DDD

I would like to split field1 into multiple columns. there are double spaces or single spaces.

I tried below line and it gives me the array.

new=df["field1"].str.split(':')

print(new):
[C, 2205,  123, ABC]
[B,  111, 345,  DDD]

I also tried with this line but my column can have single space or double space. ts just random.

df["field1"].str.split(" ", n = 1, expand = True)

how can I get the output like below?

ID  col1  col2  col3  col4
1   C     2205  123   ABC
2   B     111   345   DDD

CodePudding user response：

Try this:

df = df.set_index('ID')
df['field1'].str.split('\s ', expand=True).add_prefix('col')

Output:

   ID col0  col1 col2 col3
0   1    C  2205  123  ABC
1   2    B   111  345  DDD