Exporting All Data in a Loop to Excel in Python

Question

I am trying to write a list of data to an excel spreadsheet. I have 34 columns in excel that I need to input that data into. Column 1 is city1, column 2 is city 2, etc...

This is the script I use to retrieve all the data I need for the spreadsheet.

#!usr/bin/env python

import urllib
from datetime import datetime
from datetime import timedelta

date = datetime.now()
date1 = date + timedelta(days=1)
date2 = date + timedelta(days=2)
date3 = date + timedelta(days=3)
date4 = date + timedelta(days=4)
date5 = date + timedelta(days=5)
date6 = date + timedelta(days=6)

class city :
        def __init__(self, city_name, link) :
                self.name = city_name
                self.url = link
                self.high0 = 0
                self.high1 = 0
                self.high2 = 0
                self.high3 = 0
                self.high4 = 0
                self.high5 = 0
                self.high6 = 0
                self.high7 = 0
                self.low1 = 0
                self.low2 = 0
                self.low3 = 0
                self.low4 = 0
                self.low5 = 0
                self.low6 = 0
                self.low7 = 0

        def retrieveTemps(self) :
                filehandle = urllib.urlopen(self.url)

                # get lines from result into array
                lines = filehandle.readlines()

                # (for each) loop through each line in lines
                line_number = 0 # a counter for line number
                for line in lines:
                        line_number = line_number + 1 # increment counter

                        # find string, position otherwise position is -1

                        position0 = line.rfind('title="{}"'.format(date.strftime("%A")))
                        position1 = line.rfind('title="{}"'.format(date1.strftime("%A")))
                        position2 = line.rfind('title="{}"'.format(date2.strftime("%A")))
                        position3 = line.rfind('title="{}"'.format(date3.strftime("%A")))
                        position4 = line.rfind('title="{}"'.format(date4.strftime("%A")))
                        position5 = line.rfind('title="{}"'.format(date5.strftime("%A")))
                        position6 = line.rfind('title="{}"'.format(date6.strftime("%A")))
                        if position0 > 0 :
                                self.high0 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low1 = lines[line_number + 18].split('&')[0].split('>')[-1]

                        if position1 > 0 :
                                self.high1 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low2 = lines[line_number + 19].split('&')[0].split('>')[-1]

                        if position2 > 0 :
                                self.high2 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low3 = lines[line_number + 19].split('&')[0].split('>')[-1]

                        if position3 > 0 :
                                self.high3 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low4 = lines[line_number + 19].split('&')[0].split('>')[-1]

                        if position4 > 0 :
                                self.high4 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low5 = lines[line_number + 19].split('&')[0].split('>')[-1]

                        if position5 > 0 :
                                self.high5 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.low6 = lines[line_number + 19].split('&')[0].split('>')[-1]
                                self.low7 = lines[line_number + 19].split('&')[0].split('>')[-1]

                        if position6 > 0 :
                                self.high6 = lines[line_number + 4].split('&')[0].split('>')[-1]
                                self.high7 = lines[line_number + 4].split('&')[0].split('>')[-1]

                                break # done with loop, break out of it

                filehandle.close()

#BRITISH COLUMBIA CITIES

def getCities():

        c1 = city('Prince George', 'http://www.weatheroffice.gc.ca/city/pages/bc-79_metric_e.html')
        c2 = city('Kamloops', 'http://www.weatheroffice.gc.ca/city/pages/bc-45_metric_e.html')
        c3 = city('Blue River', 'http://www.weatheroffice.gc.ca/city/pages/bc-22_metric_e.html')
        c4 = city('High Level', 'http://www.weatheroffice.gc.ca/city/pages/ab-24_metric_e.html')
        c5 = city('Peace River', 'http://www.weatheroffice.gc.ca/city/pages/ab-25_metric_e.html')
        c6 = city('Jasper', 'http://www.weatheroffice.gc.ca/city/pages/ab-70_metric_e.html')
        c7 = city('Edmonton', 'http://www.weatheroffice.gc.ca/city/pages/ab-50_metric_e.html')
        c8 = city('Calgary', 'http://www.weatheroffice.gc.ca/city/pages/ab-52_metric_e.html')

#SASKATCHEWAN CITIES

        c9 = city('Biggar', 'http://www.weatheroffice.gc.ca/city/pages/sk-2_metric_e.html')
        c10 = city('Saskatoon', 'http://www.weatheroffice.gc.ca/city/pages/sk-40_metric_e.html')
        c11 = city('Melville', 'http://www.weatheroffice.gc.ca/city/pages/sk-8_metric_e.html')
        c12 = city('Canora', 'http://www.weatheroffice.gc.ca/city/pages/sk-3_metric_e.html')
        c13 = city('Yorkton', 'http://www.weatheroffice.gc.ca/city/pages/sk-33_metric_e.html')

#MANITOBA CITIES

        c14 = city('Winnipeg', 'http://www.weatheroffice.gc.ca/city/pages/mb-38_metric_e.html')
        c15 = city('Sprague', 'http://www.weatheroffice.gc.ca/city/pages/mb-23_metric_e.html')

#ONTARIO CITIES

        c16 = city('Thunder Bay', 'http://www.weatheroffice.gc.ca/city/pages/on-100_metric_e.html')
        c17 = city('Sioux Lookout', 'http://www.weatheroffice.gc.ca/city/pages/on-135_metric_e.html')
        c18 = city('Armstrong', 'http://www.weatheroffice.gc.ca/city/pages/on-111_metric_e.html')
        c19 = city('Hornepayne', 'http://www.weatheroffice.gc.ca/city/pages/on-78_metric_e.html')
        c20 = city('Sudbury', 'http://www.weatheroffice.gc.ca/city/pages/on-40_metric_e.html')
        c21 = city('South Parry', 'http://www.weatheroffice.gc.ca/city/pages/on-103_metric_e.html')
        c22 = city('Toronto', 'http://www.weatheroffice.gc.ca/city/pages/on-143_metric_e.html')
        c23 = city('Kingston', 'http://www.weatheroffice.gc.ca/city/pages/on-69_metric_e.html')
        c24 = city('Cornwall', 'http://www.weatheroffice.gc.ca/city/pages/on-152_metric_e.html')

#QUEBEC CITIES

        c25 = city('Montreal', 'http://www.weatheroffice.gc.ca/city/pages/qc-147_metric_e.html')
        c26 = city('Quebec', 'http://www.weatheroffice.gc.ca/city/pages/qc-133_metric_e.html')
        c27 = city('La Tuque', 'http://www.weatheroffice.gc.ca/city/pages/qc-154_metric_e.html')
        c28 = city('Saguenay', 'http://www.weatheroffice.gc.ca/city/pages/qc-166_metric_e.html')
        c29 = city('Riviere-du-loup', 'http://www.weatheroffice.gc.ca/city/pages/qc-108_metric_e.html')

#NOVA SCOTIA CITIES

        c30 = city('Truro', 'http://www.weatheroffice.gc.ca/city/pages/ns-25_metric_e.html')
        c31 = city('Halifax', 'http://www.weatheroffice.gc.ca/city/pages/ns-19_metric_e.html')

#NEW BRUNSWICK CITIES

        c32 = city('Edmundston', 'http://www.weatheroffice.gc.ca/city/pages/nb-32_metric_e.html')
        c33 = city('Moncton', 'http://www.weatheroffice.gc.ca/city/pages/nb-36_metric_e.html')
        c34 = city('Sarnia', 'http://www.weatheroffice.gc.ca/city/pages/on-147_metric_e.html')

        cities = []
        cities.append(c1)
        cities.append(c2)
        cities.append(c3)
        cities.append(c4)
        cities.append(c5)
        cities.append(c6)
        cities.append(c7)
        cities.append(c8)
        cities.append(c9)
        cities.append(c10)
        cities.append(c11)
        cities.append(c12)
        cities.append(c13)
        cities.append(c14)
        cities.append(c15)
        cities.append(c16)
        cities.append(c17)
        cities.append(c18)
        cities.append(c19)
        cities.append(c20)
        cities.append(c21)
        cities.append(c22)
        cities.append(c23)
        cities.append(c24)
        cities.append(c25)
        cities.append(c26)
        cities.append(c27)
        cities.append(c28)
        cities.append(c29)
        cities.append(c30)
        cities.append(c31)
        cities.append(c32)
        cities.append(c33)
        cities.append(c34)

return (cities)

I then run this script to export all the data to an excel file:

#!/usr/bin/env python

from xlutils.copy import copy
from xlrd import open_workbook

import canada

#import os

#os.chdir("/data/ops/Ops Documents/MexTemps")


cities = canada.getCities()

for c in cities :
        c.retrieveTemps()

## 
# writing to excel
##
file_name = 'fcst_hilo_TEST.xls'
new_file_name = 'fcst_hilo.xls'
row = 1

# column constants
high0_col = 1
low1_col = 2
high1_col = 3
low2_col = 4
high2_col = 5
low3_col = 6
high3_col = 7
low4_col = 8
high4_col = 9
low5_col = 10
high5_col = 11
low6_col = 12
high6_col = 13
low7_col = 14
high7_col = 15

workbook_file = None
try :
        # currently xlwt does not implement this option for xslx files
        workbook_file = open_workbook(file_name, formatting_info=True)
except :
        workbook_file = open_workbook(file_name)

workbook = copy(workbook_file)
sheet = workbook.get_sheet(0)

for c in cities :
        sheet.write(row, high0_col, c.high0)
        sheet.write(row, low1_col, c.low1)
        sheet.write(row, high1_col, c.high1)
        sheet.write(row, low2_col, c.low2)
        sheet.write(row, high2_col, c.high2)
        sheet.write(row, low3_col, c.low3)
        sheet.write(row, high3_col, c.high3)
        sheet.write(row, low4_col, c.low4)
        sheet.write(row, high4_col, c.high4)
        sheet.write(row, low5_col, c.low5)
        sheet.write(row, high5_col, c.high5)
        sheet.write(row, low6_col, c.low6)
        sheet.write(row, high6_col, c.high6)
        sheet.write(row, low7_col, c.low7)
        sheet.write(row, high7_col, c.high7)

workbook.save(new_file_name)

When I run this script it inputs all the data for the last city (c34) into the first row (which is supposed to be for c1, the first city). It is running through the loop in the first script, but only exporting the last line it encounters into the excel spreadsheet. Any ideas on how to get it to enter the data for everything in the loop? Thanks!

EDIT:

The last thing I need to do is assign each city to a separate column. For example: "city1" would need to go to column 2, "city2" to column 4, "city3" to column 8, etc... Any ideas?

Please isolate the problem. Also numbered variables means you should be using a more complex data structure, probably a list — Marcin
– Marcin, Commented Sep 18, 2013 at 19:05
I am going to guess that this would help: for row, c in enumerate(cities, start=1): It does not look like you are incrementing row in the loop, so this would fix that. — hughdbrown
– hughdbrown, Commented Sep 18, 2013 at 19:10
I am not sure what the problem is. The script runs successfully. However, it only fills in the data for the last city it encounters and not all of them. I wish I could explain it more clearly. I need to write the data for each city (c1,c2,etc...) in a corresponding column. For example: all the data for c1 would go in column 1, c2 in column 2, etc. What I get is all the data for c34 (the last city in the loop) in column 1. Hope this clarifies it up at least a little. — hunter21188
– hunter21188, Commented Sep 18, 2013 at 19:14
@hughdbrown I deleted some lines in the above code that I have commented out. One was #Ontario Cities 2. But thanks! lol — hunter21188
– hunter21188, Commented Sep 18, 2013 at 19:15

hughdbrown · Accepted Answer · 2013-09-18 19:28:54Z

This will help:

class city(object):
    def __init__(self, city_name, link) :
        self.name = city_name
        self.url = link
        self.high0 = 0
        self.high1 = 0
        self.high2 = 0
        self.high3 = 0
        self.high4 = 0
        self.high5 = 0
        self.high6 = 0
        self.high7 = 0
        self.low1 = 0
        self.low2 = 0
        self.low3 = 0
        self.low4 = 0
        self.low5 = 0
        self.low6 = 0
        self.low7 = 0

    def retrieveTemps(self) :
        filehandle = urllib.urlopen(self.url)

        # get lines from result into array
        lines = filehandle.readlines()
        filehandle.close()

        # (for each) loop through each line in lines
        for line_number, line in enumerate(lines, start=1):
            # find string, position otherwise position is -1
            position0 = line.rfind('title="{}"'.format(date.strftime("%A")))
            position1 = line.rfind('title="{}"'.format(date1.strftime("%A")))
            position2 = line.rfind('title="{}"'.format(date2.strftime("%A")))
            position3 = line.rfind('title="{}"'.format(date3.strftime("%A")))
            position4 = line.rfind('title="{}"'.format(date4.strftime("%A")))
            position5 = line.rfind('title="{}"'.format(date5.strftime("%A")))
            position6 = line.rfind('title="{}"'.format(date6.strftime("%A")))

            if position0 > 0 :
                self.high0 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low1 = lines[line_number + 18].split('&')[0].split('>')[-1]

            if position1 > 0 :
                self.high1 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low2 = lines[line_number + 19].split('&')[0].split('>')[-1]

            if position2 > 0 :
                self.high2 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low3 = lines[line_number + 19].split('&')[0].split('>')[-1]

            if position3 > 0 :
                self.high3 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low4 = lines[line_number + 19].split('&')[0].split('>')[-1]

            if position4 > 0 :
                self.high4 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low5 = lines[line_number + 19].split('&')[0].split('>')[-1]

            if position5 > 0 :
                self.high5 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.low6 = lines[line_number + 19].split('&')[0].split('>')[-1]
                self.low7 = lines[line_number + 19].split('&')[0].split('>')[-1]

            if position6 > 0 :
                self.high6 = lines[line_number + 4].split('&')[0].split('>')[-1]
                self.high7 = lines[line_number + 4].split('&')[0].split('>')[-1]
                break # done with loop, break out of it

    def getCities():
        return [
            #BRITISH COLUMBIA CITIES
            city('Prince George', 'http://www.weatheroffice.gc.ca/city/pages/bc-79_metric_e.html'),
            city('Kamloops', 'http://www.weatheroffice.gc.ca/city/pages/bc-45_metric_e.html'),
            city('Blue River', 'http://www.weatheroffice.gc.ca/city/pages/bc-22_metric_e.html'),

            # Alberta
            city('High Level', 'http://www.weatheroffice.gc.ca/city/pages/ab-24_metric_e.html'),
            city('Peace River', 'http://www.weatheroffice.gc.ca/city/pages/ab-25_metric_e.html'),
            city('Jasper', 'http://www.weatheroffice.gc.ca/city/pages/ab-70_metric_e.html'),
            city('Edmonton', 'http://www.weatheroffice.gc.ca/city/pages/ab-50_metric_e.html'),
            city('Calgary', 'http://www.weatheroffice.gc.ca/city/pages/ab-52_metric_e.html'),

            #SASKATCHEWAN CITIES

            city('Biggar', 'http://www.weatheroffice.gc.ca/city/pages/sk-2_metric_e.html'),
            city('Saskatoon', 'http://www.weatheroffice.gc.ca/city/pages/sk-40_metric_e.html'),
            city('Melville', 'http://www.weatheroffice.gc.ca/city/pages/sk-8_metric_e.html'),
            city('Canora', 'http://www.weatheroffice.gc.ca/city/pages/sk-3_metric_e.html'),
            city('Yorkton', 'http://www.weatheroffice.gc.ca/city/pages/sk-33_metric_e.html'),

            #MANITOBA CITIES

            city('Winnipeg', 'http://www.weatheroffice.gc.ca/city/pages/mb-38_metric_e.html'),
            city('Sprague', 'http://www.weatheroffice.gc.ca/city/pages/mb-23_metric_e.html'),

            #ONTARIO CITIES

            city('Thunder Bay', 'http://www.weatheroffice.gc.ca/city/pages/on-100_metric_e.html'),
            city('Sioux Lookout', 'http://www.weatheroffice.gc.ca/city/pages/on-135_metric_e.html'),
            city('Armstrong', 'http://www.weatheroffice.gc.ca/city/pages/on-111_metric_e.html'),
            city('Hornepayne', 'http://www.weatheroffice.gc.ca/city/pages/on-78_metric_e.html'),
            city('Sudbury', 'http://www.weatheroffice.gc.ca/city/pages/on-40_metric_e.html'),
            city('South Parry', 'http://www.weatheroffice.gc.ca/city/pages/on-103_metric_e.html'),
            city('Toronto', 'http://www.weatheroffice.gc.ca/city/pages/on-143_metric_e.html'),
            city('Kingston', 'http://www.weatheroffice.gc.ca/city/pages/on-69_metric_e.html'),
            city('Cornwall', 'http://www.weatheroffice.gc.ca/city/pages/on-152_metric_e.html'),
            city('Sarnia', 'http://www.weatheroffice.gc.ca/city/pages/on-147_metric_e.html'),

            #QUEBEC CITIES

            city('Montreal', 'http://www.weatheroffice.gc.ca/city/pages/qc-147_metric_e.html'),
            city('Quebec', 'http://www.weatheroffice.gc.ca/city/pages/qc-133_metric_e.html'),
            city('La Tuque', 'http://www.weatheroffice.gc.ca/city/pages/qc-154_metric_e.html'),
            city('Saguenay', 'http://www.weatheroffice.gc.ca/city/pages/qc-166_metric_e.html'),
            city('Riviere-du-loup', 'http://www.weatheroffice.gc.ca/city/pages/qc-108_metric_e.html'),

            #NOVA SCOTIA CITIES

            city('Truro', 'http://www.weatheroffice.gc.ca/city/pages/ns-25_metric_e.html'),
            city('Halifax', 'http://www.weatheroffice.gc.ca/city/pages/ns-19_metric_e.html'),

            #NEW BRUNSWICK CITIES

            city('Edmundston', 'http://www.weatheroffice.gc.ca/city/pages/nb-32_metric_e.html'),
            city('Moncton', 'http://www.weatheroffice.gc.ca/city/pages/nb-36_metric_e.html'),
        ]

And you definitely need this:

for row, c in enumerate(cities, start=1):

It does not look like you are incrementing row in the loop, so this would fix that.

I can't see what you've changed in your first block of code (there's simply too many lines to do a diff by eye). Can you put some text around it, explaining why you're including it in the answer? The second code block is exactly what is needed though!
@Blckknght - He cleaned up the code so I could get rid of all the cities.append() lines I had in there.
This question may or may not be related to the above code. But is there a way to have "city1" export to column 1, "city2" to column3, "city3" to column 6, etc... Basically, the cities are not all in consecutive columns and I need to specifically assign each city a column.

Blckknght · Accepted Answer · 2013-09-18 20:13:01Z

I'd initially answered this on your previous question (where I'd read enough of your comments to see the underlying issue, but not the one that said you were going to ask a separate question). The second code block of hughdbrown's answer shows the key insight, that you should use an enumerate call to handle your row value. Here's a further suggestion I had:

You might also make your column copying code simpler if you used a loop, using another enumerate and the getattr function:

column_names = ["high0", "low1", "high1", "low2", "high2",
                "low3", "high3", "low4", "high4", "low5",
                "high5", "low6", "high6", "low7", "high7"]

# ...

for row, city in enumerate(cities, start=1):
    for column, col_name in enumerate(column_names, start=1):
        sheet.write(row, column, getattr(city, col_name))

Edit: A further improvement might be to make the city instances directly iterable, so you don't need to jump through hoops with getattr. Here's one crude way to do that:

class city:     # note, if you're using Python 2, you should inherit from object
    # other stuff

    def __iter__(self):
        return iter([self.high0, self.low1, self.high1, self.low2, self.high2,
                     self.low3, self.high3, self.low4, self.high4, self.low5,
                     self.high5, self.low6, self.high6, self.low7, self.high7])

A better solution would be to do away with the named attributes, and instead use a list as the underlying data structure in the class. That's a bit more complicated, at least, if you still need to be able to use the attribute syntax:

class city:
    def __init__(city_name, link) :
        self.name = city_name
        self.url = link
        self.data = [0] * 15

    def _attr_index(self, name):
        """Translate an name into an integer index, or raise an exception"""
        if name.startswith("high") and len(name) == 5:
            n = int(name[-1])
            if 0 <= n <= 7:
                return n*2 # even indexes 0-14
        if name.startswith("low") and len(name) == 4:
            n = int(name[-1])
            if 1 <= n <= 7:
                return n*2 - 1 # odd indexes 1-13
        raise ValueError()

    def __getattr__(self, name):
        try:
            return self.data[self._attr_index(name)]
        except ValueError:
            return super().__getattr__(name) # should raise an AttributeError

    def __setattr__(self, name, value):
        try:
            self.data[self._attr_index(name)] = value
        except ValueError:
            super().__setattr__(name, value) # will do a regular assignemnt

    def __iter__(self):
        return iter(self.data)

    # other stuff

Using either of those, the code in your script would become very simple:

for row, city in enumerate(cities, start=1):
    for column, value in enumerate(city, start=1):
        sheet.write(row, column, value)

Awesome. Thank you @Blckknght. That makes it much cleaner and works perfectly.

Collectives™ on Stack Overflow

Exporting All Data in a Loop to Excel in Python

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related