python - 如何将 Excel 文件从付费专区下载到 pandas 数据框中？

Question

我有一个需要登录才能访问数据的网站。

import pandas as pd
import requests

r = requests.get(my_url, cookies=my_cookies) # my_cookies are imported from a selenium session.
df = pd.io.excel.read_excel(r.content, sheetname=0)

回复:

IOError: [Errno 2] No such file or directory: 'Ticker\tAction\tName\tShares\tPrice\...

显然，str 被处理为文件名称。有没有办法将其作为文件处理？或者我们可以将 cookies 传递给 pd.get_html 吗？

编辑:经过进一步处理，我们现在可以看到这实际上是一个 csv 文件。下载的文件内容为:

In [201]: r.content
Out [201]: 'Ticker\tAction\tName\tShares\tPrice\tCommission\tAmount\tTarget Weight\nBRSS\tSELL\tGlobal Brass and Copper Holdings Inc\t400.0\t17.85\t-1.00\t7,140\t0.00\nCOHU\tSELL\tCohu Inc\t700.0\t12.79\t-1.00\t8,953\t0.00\nUNTD\tBUY\tUnited Online Inc\t560.0\t15.15\t-1.00\t-8,484\t0.00\nFLXS\tBUY\tFlexsteel Industries Inc\t210.0\t40.31\t-1.00\t-8,465\t0.00\nUPRO\tCOVER\tProShares UltraPro S&P500\t17.0\t71.02\t-0.00\t-1,207\t0.00\n'

请注意，它是制表符分隔的。仍在尝试:

# csv version 1
df = pd.read_csv(r.content) 
# Returns error, file does not exist. Apparently read_csv() is also trying to read it as a file.

# csv version 2
fh = io.BytesIO(r.content)
df = pd.read_csv(fh) # ValueError: No columns to parse from file.

# csv version 3
s = StringIO(r.content)
df = pd.read_csv(s)
# No error, but the resulting df is not parsed properly; \t's show up in the text of the dataframe.

Answer 1

只需将文件内容包装在 BytesIO 中:

with io.BytesIO(r.content) as fh:
    df = pd.io.excel.read_excel(fh, sheetname=0)