1
$\begingroup$

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) enter image description here

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

enter image description here

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?

$\endgroup$
1
$\begingroup$

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",
$\endgroup$
0
0
$\begingroup$

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)
$\endgroup$
6
  • $\begingroup$ Well, isn't it what I have just answered (and demonstrated, and justified)? $\endgroup$ – desertnaut Oct 26 '20 at 22:36
  • $\begingroup$ Well, did you see, that I just answered 3 Minutes after your answer? what do you think does it take to answer a question? $\endgroup$ – jottbe Oct 27 '20 at 0:12
  • $\begingroup$ It can take some time - in my case, downloading the data, locating the documentation, replicate the error, correcting it, copying & pasting the stuff from my notebook & from the docs here, doing the same with OP's data (to justify the error). What did it take for you? Longer than 3 minutes? And when you realized that there was already an answer...? $\endgroup$ – desertnaut Oct 27 '20 at 0:21
  • $\begingroup$ Then I left my answer here, since I invested the time. Do you have a problem with this, or why else are we having this discussion? $\endgroup$ – jottbe Oct 27 '20 at 0:23
  • 1
    $\begingroup$ Well, since you invested that much time, nope, suit yourself... $\endgroup$ – desertnaut Oct 27 '20 at 0:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.