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
>Solution :
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