reading last column from dataframe is creating issue

Question

I am trying to read last column in Dataframe with column name but its erroring out
Below is my code

f = open('E:\Downloads\Goods&Transit.csv', 'rU')
readfile = csv.reader(f, delimiter = ';')
input_file = [list(line) for line in readfile]
headers = input_file.pop(0)
df = pd.DataFrame(list(input_file), columns= headers)
print df['Receivables']
f.close()

When i print Receivables column - its throwing key error (but dataframe has that column)

My csv file

print headers:

  ['PO Number', 'PO Type', 'Location', 'Vendor Number', 'Product ID', 'Planned Delivery Date', 'Receivables

csv file in text format
PO Number;PO Type;Location;Vendor Number;Product ID;Planned Delivery Date;Receivables
7000002174;DP06;1006;0000010055;P26220C00000E10;31.05.2014;205.000-
7000001994;DP06;1006;0000010662;P60514X00000010;06.04.2014;8000.000
7600000238;IM06;1006;0000020257;R87M45X06000020;30.04.2014;4350.000
7600000238;IM06;1006;0000020257;R87M47F06000020;15.04.2014;2700.000

There is single quote for Receivables column is that causing any issue
Any help will be much appreciated

Are you sure the headers output hasn't been truncated? It doesn't look like the output from a print() call with a list as its argument. — holdenweb
– holdenweb, Commented Apr 25, 2018 at 10:28
why don't you directly read csv into dataframe? df = pd.read_csv("yourfile.csv") — akshat
– akshat, Commented Apr 25, 2018 at 10:28

SpghttCd · Accepted Answer · 2018-04-25 12:26:27Z

1

I'd recommend to use the csv-reader of Pandas directly:

filename = 'E:\Downloads\Goods&Transit.csv'
pd.read_csv(filename, sep= ';')

I have to admit: with the way via open and lists and lists of lists I get errors, too...

But the straightforward pandas way works like a charm:

s = 'PO Number;PO Type;Location;Vendor Number;Product ID;Planned Delivery Date;Receivables\n 7000002174;DP06;1006;0000010055;P26220C00000E10;31.05.2014;205.000-\n7000001994;DP06;1006;0000010662;P60514X00000010;06.04.2014;8000.000\n 7600000238;IM06;1006;0000020257;R87M45X06000020;30.04.2014;4350.000\n 7600000238;IM06;1006;0000020257;R87M47F06000020;15.04.2014;2700.000'

from io import StringIO
import pandas as pd

df = pd.read_csv(StringIO(s), sep=';')

df.head()
Out[1]: 
    PO Number PO Type  Location  Vendor Number       Product ID  \
0  7000002174    DP06      1006          10055  P26220C00000E10   
1  7000001994    DP06      1006          10662  P60514X00000010   
2  7600000238    IM06      1006          20257  R87M45X06000020   
3  7600000238    IM06      1006          20257  R87M47F06000020   

  Planned Delivery Date Receivables  
0            31.05.2014    205.000-  
1            06.04.2014    8000.000  
2            30.04.2014    4350.000  
3            15.04.2014    2700.000  

df.columns
Out[2]: 
Index(['PO Number', 'PO Type', 'Location', 'Vendor Number', 'Product ID',
   'Planned Delivery Date', 'Receivables'],
  dtype='object')

df['Receivables']
Out[3]: 
0    205.000-
1    8000.000
2    4350.000
3    2700.000
Name: Receivables, dtype: object

edited Apr 25, 2018 at 12:26

answered Apr 25, 2018 at 10:28

SpghttCd

10.9k2 gold badges23 silver badges28 bronze badges

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

3 Comments

nick Over a year ago

Thanks @SpghttCd In my case i need to have list and df. Even if i read directly am unable to read last column from df

SpghttCd Over a year ago

This is strange. Could you please post the first 3 lines of your csv in text format (e.g. from a simple text Editor), not as an Excel-screenshot. Secondly, what is the return of df.head() or df.columns, as you said, your df has a column called 'Receivables'

SpghttCd Over a year ago

Sorry, I meant completely in text format - not only from a text editor as source for another screenshot, but inserted as text charcters here in the post, so that others can Play around with it and see, if there's anything wrong at this point... However, looking at this screenshot, I hardly can imagine, that pd.read_csv() would make any problems here...

Collectives™ on Stack Overflow

reading last column from dataframe is creating issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related