Pandas loads a csv file incorrectly, but without throwing an error [closed]

Question

So I tried loading some data through pandas to practice manipulating it, but I ran into a slight problem. Basically, the pandas loads the data improperly. And it does so consistently. Let me show you what I mean.

This is a snapshot of the dataset (a csv file)

I think you can more or less see how it looks here. However, when I load it in Pandas, I get this:

As you can see there's a clear distortion. For one thing, the Date column has been pushed from the first column in the spreadsheet to the second in the csv. Its values have also been replaced with what looks to me like a copy of the "Travels" column. Moving on, you can also see that the column I'm interested in ("Total Direct Remittances") is totally incorrect. In the spreadsheet it starts with 342, but when loaded it starts with 23423, and the rest of its values are completely off.

Here's the code:

import pandas as pd

data = pd.read_csv("/home/user/Downloads/IntPayments26102020.csv")

You can get a copy of the data here. Any ideas guys?

Answer 1

You need to read the file with the index_col=False argument:

# wrong way:
df = pd.read_csv("IntPayments26102020.csv")
print(df.head())
# result :
                  Date     Travels  ...  Debt Services And Payments  Total
10/12/2003   234234.00     23424.0  ...                3.574570e+05    NaN
1/12/2005        12.00         2.0  ...                1.160000e+02    NaN
4/28/2006   7119901.48   9950000.0  ...                3.887479e+08    NaN
5/2/2006        346.00    345435.0  ...                4.632060e+05    NaN
5/12/2006    152204.22  11500000.0  ...                4.743224e+08    NaN

[5 rows x 11 columns]


# correct way:
df = pd.read_csv("IntPayments26102020.csv", index_col=False)
print(df.head())
# result :

         Date     Travels  ...  Debt Services And Payments         Total
0  10/12/2003   234234.00  ...                    23423.00  3.574570e+05
1   1/12/2005       12.00  ...                       21.00  1.160000e+02
2   4/28/2006  7119901.48  ...                 45602245.83  3.887479e+08
3    5/2/2006      346.00  ...                    34534.00  4.632060e+05
4   5/12/2006   152204.22  ...                 71560790.20  4.743224e+08

[5 rows x 11 columns]

From the docs:

index_col : int, str, sequence of int / str, or False, default None

Column(s) to use as the row labels of the DataFrame, either given as string name or column index. If a sequence of int / str is given, a MultiIndex is used.

Note: index_col=False can be used to force pandas to not use the first column as the index, e.g. when you have a malformed file with delimiters at the end of each line.

From where you can arguably guess why the default setting index_col=None will not work with your data - your rows are also separated by comma ,:

"Date","Travels","Inward Money Transfers","Cash Sales to BDC And Banks","Letters of Credit","Total Direct Remittances","Remittances to Travelex","Remittances to Amex","WDAS","Debt Services And Payments","Total"
"10/12/2003","234234","23424","23423","2342","342","23423","3423","23423","23423","357457",
"1/12/2005","12","2","12","12","21","12","12","12","21","116",

Answer 2

Your columns are shifted, because pandas interprets the first column as the index. If you want to avoid this, you can just load the data like this:

df= pd.read_csv('IntPayments26102020.csv', index_col=False)