CODEWITHSUNDEEP
excelpython-3.xcsvpandas
user3062459
user3062459

Reputation: 1631

Read from CSV and store in Excel tabs

I am reading multiple CSVs (via URL) into multiple Pandas DataFrames and want to store the results of each CSV into separate excel worksheets (tabs). When I keep writer.save() inside the for loop, I only get the last result in a single worksheet. And when I move writer.save() outside the for loop, I only get the first result in a single worksheet. Both are wrong.

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()

How can I get all three results in three separate worksheets?

Upvotes: 2

Views: 185

Answers (1)

Parfait
Parfait

Reputation: 107567

You are re-initializing the writer object with each loop. Simply initialize it once before for and save document once after the loop. Also, in read_csv() line, you should be reading in the request content, not the URL (i.e., statement) saved in dictionary:

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()

Upvotes: 2

Related Questions