CODEWITHSUNDEEP
pythonpandas
AbdurRehman Khan
AbdurRehman Khan

Reputation: 925

Pandas won't separate columns in my comma separated .txt file

I'm aware this has been asked so many times, but it's left me really scratching my head. I have a .txt file which looks like:

"0,1,2,3,4, ....63,0,1,2,3,4.....63"

"0,1,2,3,4, ....63,0,1,2,3,4.....63"

"0,1,2,3,4, ....63,0,1,2,3,4.....63"

and so on for multiple rows. So that's 64+64 = 128 columns separated by commas, while each row is enclosed in double quotes.

I have used the commands:

#Used this initially
df = pd.read_csv('test_data.txt')
#Used this after reading more stackoverflow answers
df = pd.read_csv('test_data.txt', header = None, sep=",", delimiter=',', quotechar='"', index_col = None)

I know sep and delimiter are the same parameters, but I tried both out anyway, I shouldn't have to specify these either because pandas chooses commas by default. After this I'm just using:

df.head()

And it outputs:

                                                 0
0   0.00,1.00,2.00,3.00,4.00,5.00,6.00,7.00,8.00,9...

1   0.00,1.00,2.00,3.00,4.00,5.00,6.00,7.00,8.00,9...

2   0.00,1.00,2.00,3.00,4.00,5.00,6.00,7.00,8.00,9...

3   0.00,1.00,2.00,3.00,4.00,5.00,6.00,7.00,8.00,9...

4   0.00,1.00,2.00,3.00,4.00,5.00,6.00,7.00,8.00,9...

It just reads it all as the one column, please advise on how I can read all 128 columns.

Upvotes: 0

Views: 3933

Answers (1)

zipa
zipa

Reputation: 27869

This will get you to desired result:

df = pd.read_csv('test_data.txt', header=None)
df = pd.DataFrame(df[0].str.split(',').tolist())

So this will read your file, that has each row wrapped with quote marks, and pack it into single column.

Then, you split that column by comma and construct new dataframe from the results.

Upvotes: 2

Related Questions