从CSV读取并存储在Excel选项卡中
问题描述:
我正在将多个CSV(通过URL)读取到多个Pandas DataFrame中,并希望将每个CSV的结果存储到单独的Excel工作表(选项卡)中。当我在for循环中保留writer.save()
时,我只在单个工作表中获得最后一个结果。当我将writer.save()
移动到for循环之外时,我只在单个工作表中获得第一个结果。两者都是错误的。从CSV读取并存储在Excel选项卡中
import requests
import pandas as pd
from pandas import ExcelWriter
work_statements = {
'sheet1': 'URL1',
'sheet2': 'URL2',
'sheet3': 'URL3'
}
for sheet, statement in work_statements.items():
writer = pd.ExcelWriter('B.xlsx', engine='xlsxwriter')
r = requests.get(statement) # go to URL
df = pd.read_csv(statement) # read from URL
df.to_excel(writer, sheet_name= sheet)
writer.save()
我怎样才能在三个单独的工作表中得到所有三个结果?
答
您正在重新初始化每个循环的对象writer
。只需在for
之前初始化一次,并在循环后保存一次文档。此外,在read_csv()
行,你应该在请求内容阅读,而不是URL(即statement
)保存在词典:
writer = pd.ExcelWriter('B.xlsx', engine='xlsxwriter')
for sheet, statement in work_statements.items():
r = requests.get(statement) # go to URL
df = pd.read_csv(r.content) # read from URL
df.to_excel(writer, sheet_name= sheet)
writer.save()
这个工作!谢谢 – user3062459