CODEWITHSUNDEEP
pythonpandastxt
Tobitor
Tobitor

Reputation: 1508

Reading in text file with time column which is separated by commas?

I have a txt-file with data that looks like this

A,B,C,Time
xyz,1,MN,14/11/20 17:20:08,296000000
tuv,0,ST,30/12/20 11:11:18,111111111

I read the data in using this code:

df = pd.read_csv('path/to/file',delimiter=',')

Because of my time column it does not work correctly because Time is separated through a comma. How can I solve this and how can I make it work even in the case that I have multiple columns with such a time format?

I would like to get a datframe which looks like this:

 A B C Time
 xyz 1 MN 14/11/20 17:20:08,296000000
 tuv 0 ST 30/12/20 11:11:18,111111111

Thanks a lot!

Upvotes: 0

Views: 437

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

Use reset_index() method,apply() method and drop() method:

df=df.reset_index()
df['Time']=df[['C','Time']].astype(str).apply(','.join,1)
df=df.drop(columns=['C'])
df.columns=['A','B','C','Time']

Now If you print df you will get desired output:

    A       B   C   Time
0   xyz     1   MN  14/11/20 17:20:08,296000000
1   tuv     0   ST  30/12/20 11:11:18,111111111

Now If you wish to convert it back to txt file then use:

df.to_csv('filename.txt',sep='|',index=False)

Note: you can't use ',' and ' ' as sep parameter because it creates the same problem when you try to load your txt/csv file

Upvotes: 1

Related Questions