sheets of Excel Workbook from a URL into a `pandas.DataFrame`

Question

After looking at different ways to read an url link, pointing to a .xls file, I decided to go with using xlrd.

I am having a difficult time converting a 'xlrd.book.Book' type to a 'pandas.DataFrame'

I have the following:

import pandas
import xlrd 
import urllib2

link ='http://www.econ.yale.edu/~shiller/data/chapt26.xls'
socket = urllib2.urlopen(link)

#this line gets me the excel workbook 
xlfile = xlrd.open_workbook(file_contents = socket.read())

#storing the sheets
sheets = xlfile.sheets()

I want to tak the last sheet of sheets and import as a pandas.DataFrame, any ideas as to how I can accomplish this? I've tried, pandas.ExcelFile.parse() but it wants a path to an excel file. I can of certainly save the file to memory and then parse (using tempfile or something), but I'm trying to follow pythonic guidelines and use functionality likely already written into pandas.

Any guidance is greatly appreciated as always.

DSM · Accepted Answer · 2013-03-23 16:00:15Z

28

You can pass your socket to ExcelFile:

>>> import pandas as pd
>>> import urllib2
>>> link = 'http://www.econ.yale.edu/~shiller/data/chapt26.xls'
>>> socket = urllib2.urlopen(link)
>>> xd = pd.ExcelFile(socket)
NOTE *** Ignoring non-worksheet data named u'PDVPlot' (type 0x02 = Chart)
NOTE *** Ignoring non-worksheet data named u'ConsumptionPlot' (type 0x02 = Chart)
>>> xd.sheet_names
[u'Data', u'Consumption', u'Calculations']
>>> df = xd.parse(xd.sheet_names[-1], header=None)
>>> df
                                   0   1   2   3         4
0        Average Real Interest Rate: NaN NaN NaN  1.028826
1    Geometric Average Stock Return: NaN NaN NaN  0.065533
2              exp(geo. Avg. return) NaN NaN NaN  0.067728
3  Geometric Average Dividend Growth NaN NaN NaN  0.012025

answered Mar 23, 2013 at 16:00

DSM

355k67 gold badges606 silver badges504 bronze badges

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

Comments

Jean-François Corbett · Accepted Answer · 2018-01-02 15:48:10Z

11

You can pass a URL to pandas.read_excel():

import pandas as pd

link ='http://www.econ.yale.edu/~shiller/data/chapt26.xls'
data = pd.read_excel(link,'sheetname')

edited Jan 2, 2018 at 15:48

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Jan 2, 2018 at 15:28

aghazaly

1111 silver badge3 bronze badges

Collectives™ on Stack Overflow

sheets of Excel Workbook from a URL into a `pandas.DataFrame`

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related