Pandas reads all columns into a single column-CodePudding

I am trying to load a txt file separated with semicolons ';', but when using pandas' read_csv it puts the two columns into a single column. The .txt file looks as follows:

Profile 1;Profile 1;
x;y;
[m];[m];
0;3.4467541e-010;
1.0053956e-007;3.5615887e-010;
2.0107912e-007;5.7246124e-010;
3.0161869e-007;6.8413124e-010;
4.0215825e-007;7.3333242e-010;
...
5.2280572e-006;1.9645013e-009;
5.3285968e-006;1.8215396e-009;

My attempt takes the form of:

df = pd.read_csv('textfile.txt', sep=';', header=2)

The output from this is:

data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 54 entries, 0 to 53
Data columns (total 1 columns):
 #   Column                Non-Null Count  Dtype
---  ------                --------------  -----
 0   [m]     [m]           54 non-null     object
dtypes: object(1)
memory usage: 560.0  bytes

I have also tried other ways with using "with open()", but with no luck either.

CodePudding user response：

As @Reihaneh Kouhi reported the problem is the last semicolon creating an extra empty column and the first and the third row (to skip).
I tried this locally and it should work.
I just skip the rows and then delete the last column

import pandas as pd

table = pd.read_csv("table.csv", sep=";",  skiprows=[0,2], skipinitialspace=True, engine='python')
table = table.iloc[:, :-1]
print(table)

CodePudding user response：

Your separator is a semicolon, and after the second column you have another semicolon. This is why this problem happens. Just get rid of the last semicolon in each row and run the code. I am not sure, but this works for me.