split multiple columns by separator pandas

Question

How to split every column by ":", using pandas

    ,0,1,2,3,4
0,MMSI : 222111345,Country : Singa,Ship name : xxxVessel,Call sign : abcd,IMO number : 12345
1,MMSI : 222111345,Country : Singa,Ship name : xxxVessel,Call sign : abcd,IMO number : 12345
2,MMSI : 222111345,Country : Singa,Ship name : xxxVessel,Call sign : abcd,IMO number : 12345
3,MMSI : 222111345,Country : Singa,Ship name : xxxVessel,Call sign : abcd,IMO number : 12345

I tryed df3 = df3[df3.columns[0]].str.split(":",expand=True) but doesn´t work

Why dont you use separator as df = pd.read_csv("test_data1", header=None, sep=":") while you reading the file itself? — Karn Kumar
– Karn Kumar, Commented Jul 2, 2021 at 10:07

Anurag Dabas · Accepted Answer · 2021-07-02 10:59:39Z

1

You can try via concat() and list comprehension:

df=pd.concat([df3[x].str.split(':',expand=True) for x in df3],axis=1)

Note: If there are integer and float values in columns then:

cols=df3.columns[df3.dtypes=='O']
#Filtered out columns that are of type Object    
df=pd.concat([df3[x].str.split(':',expand=True) for x in cols],axis=1)

Note:

If you are reading csv file then you can also try this :

df3=pd.read_csv("filename.csv", sep=",|:").reset_index()

edited Jul 2, 2021 at 10:59

answered Jul 2, 2021 at 8:18

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

split multiple columns by separator pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related