Python Pandas read_csv not importing correctly

Question

I have a .xls file that looks similar to this...

Value of Construction Put in Place...
(Millions of Dollars....)
Blank Row
Date    Total_Construction Total Residential Total Nonresidential...Columns 
Dec-15  1,116,570          435,454           681,217 
Nov-15  1,115,966          432,295           683,671
Oct-15  1,122,749          431,164           691,585   
.
.
.

I am trying to import the file to get the following:

Date    Total_Construction Total Residential Total Nonresidential 
Dec-15  1,116,570          435,454           681,217 
Nov-15  1,115,966          432,295           683,671
Oct-15  1,122,749          431,164           691,585   
.
.
.

With the following code:

for chunk in pandas.read_csv('/PATH/totsatime.xls',
                 names      = ['Date', 'Total Residential', 'Total Nonresidential'],
                 header     = 4,
                 chunksize  = 1,
                 skiprows   = range(1, 4),
                 thousands  = ','):

    if chunk['Date'] == 'Dec-01':
        break

    else:
        df = pandas.DataFrame(chunk)

However, I end up with the following:

Date             Total Residential     Total Nonresidential
Lodging          NaN                   NaN
Office          NaN                   NaN
Commercial      NaN                   NaN
Health care     NaN                   NaN

The dates end up being formatted from the columns that I am not importing. Any advice would greatly be appreciated.

Thank you in advance.

I guess you should try read_excel:pandas.pydata.org/pandas-docs/stable/generated/… — alpert
– alpert, Commented Apr 27, 2016 at 19:10
also forget chunks and names etc... Just read_excel and it will probably do the right thing.. — dartdog
– dartdog, Commented Apr 27, 2016 at 19:17

mh00h · Accepted Answer · 2016-04-27 19:09:53Z

5

Don't use read_csv to import an xls file. Use read_excel. See http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html

answered Apr 27, 2016 at 19:09

mh00h

1,8663 gold badges27 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Python Pandas read_csv not importing correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related