Python Pandas reading CSV issue

Question

I am trying to read an unstructured CSV file without any header, using Pandas. The number of columns differ in different rows and there is no clear upper limit for the num of columns. Right now it is 10 but it will increase to maybe 15.

Example CSV file content:

a;b;c
a;b;c;d;e;;;f
a;;
a;b;c;d;e;f;g;h;;i
a;b;
....

Here is how I read it using Python Pandas:

pd.DataFrame(pd.read_csv(path, sep=";", header=None, usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
                                            names=['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'],
                                            nrows=num_of_rows + 1))

However this produces FutureWarning: Defining usecols with out of bounds indices is deprecated and will raise a ParserError in a future version. warning message. And I don't want my code to stop working in the future because of this reason.

My question is that is there a way to read such an unstructured CSV file using Pandas (or any other equivalently fast library) in a future-safe way?

Corralien · Accepted Answer · 2023-01-26 09:52:34Z

1

You can use:

# choose a bad seperator
df = (pd.read_csv('data.csv', sep='@', header=None).squeeze()
        .str.split(';', expand=True).fillna(''))

df.columns = [chr(65+c) for c in df.columns]  # or whatever you want
print(df)

# Output
   A  B  C  D  E  F  G  H I  J
0  a  b  c                    
1  a  b  c  d  e        f     
2  a                          
3  a  b  c  d  e  f  g  h    i
4  a  b

Update

Other possibility:

df = (pd.read_csv('data.csv', sep='@', header=None).squeeze()
        .str.replace(r';{2,}', ';')
        .str.split(';', expand=True).fillna(''))
df.columns = [chr(65+c) for c in df.columns]  # or whatever you want
print(df)

# Output
   A  B  C  D  E  F  G  H  I
0  a  b  c                  
1  a  b  c  d  e  f         
2  a                        
3  a  b  c  d  e  f  g  h  i
4  a  b

edited Jan 26, 2023 at 9:52

answered Jan 26, 2023 at 9:46

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

2 Comments

JollyRoger Over a year ago

Adding 65 to each char doesn't seem to be effective tbh, but it solves the problem I mentioned. So thank you.

Corralien Over a year ago

Glad to read that :-) Use what you want to rename columns, it was just for demo.

Collectives™ on Stack Overflow

Python Pandas reading CSV issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related