Pandas read csv is shifting columns

user_7777625 0
Viewed 9151

I'm trying to create a dataframe of a csv file that has 4 empty columns. When I open it on LibreOffice or Excel it correctly identifies the empty columns. However, opening with pd.read_csv() ends up shifting the columns' values by one.

How can I solve this? It seems like a problem with pandas read_csv() method.

My code is really standard:

import pandas as pd
df = pd.DataFrame.read_csv('csv_file.csv', sep=',')
df.head()

I changed the headers and used this:

df = pd.DataFrame.read_csv('csv_file.csv', sep=',', index_col=False).

This solved the problem, but what in my previous headers was causing this?

pandas csv python
4 Answers

The problems occurs if your line ends with an delimiter (here comma[,]), which creates an empty cell generally not visible in MS Excel. If your csv line looks like this:

1,2282816,102.97245065789474,2432,0.8333333333333334,0.1388888888888889,certain,

then modify it to:

1,2282816,102.97245065789474,2432,0.8333333333333334,0.1388888888888889,certain

and pd.read_csv(fileName) will work fine.

user_111176820

I had a similar problem. Here is how I have solved it:

  1. Opened excel file with google spreadsheet on google drive
  2. Downloaded spread sheet as csv file
  3. Read the csv file via pandas.read_csv('filename', sep=',', index_col=False))

Problem resolved.

user_116874860

I encountered the same problem. Try writing headings on top of each column if there are none. This time, read_csv() also reads the headings and lists them.
After that convert the data frame to an array by 

df=df.values

and the headings are gone.

user_102316300
Related