Python i Excel. Odsłona druga.

Muszę przyznać, że dzisiejszy wpis jest chyba najbardziej przykrym tekstem, który przyszło mi na tym blogu popełnić. Bynajmniej jednak nie o jakieś wyjątkowo bolesne treści chodzi, ale wrażenia jakie mi towarzyszyły w trakcie przygotowań do jego napisania. Nie wiem czy bardziej się to brało z faktu, że nie udało mi się znaleźć lub chociaż podkraść komuś jakiegoś fajnego pomysłu na ćwiczenie do wykonania i prezentacji, czy samo w sobie było to wyjątkowo nużące zagadnienie. Zapewne jedno i drugie. Dlatego, by nie przedłużać tych katuszy przejdźmy do omówienia ćwiczenia, a następnie jego realizacji.

Co będziemy robić

Ze strony https://data.london.gov.uk/dataset/global-city-population-estimates można pobrać plik, który zawiera listę miast, które w 2014 roku miały co najmniej 300 tysięcy mieszkańców. Nas będzie interesować będą jednak aglomeracje powyżej o zaludnieniu powyżej miliona i to w roku 2020, czyli w tym wypadku do czynienia będziemy z danymi dopiero prognozowanymi, ale na potrzeby tego ćwiczenia przyjmijmy, że są to informacje bieżące, choć nie ma to większego znaczenia. Dla tej wprzódy wyselekcjonowanej grupy będziemy chcieli sprawdzić, ile takich aglomeracji przypada na dany kraj oraz ile łącznie (w ramach danego państwa) ludzi zamieszkuje te miejscowości. Do wykonania mamy zatem następujące kroki:

A. pobieramy i ładujemy plik do pamięci komputera,

B. wybieramy arkusz, gdzie znajdują się interesujące nas dane (w pliku są 4 “zakładki”),

C. wykonujemy iterację przez wiersze tego arkusza w poszukiwaniu tych rekordów, gdzie liczebność wynosi od miliona w górę,

D. dla każdego z Państw, które posiada choć jedno takie miasto, liczymy ile jest takich aglomeracji oraz ile osób w nich zamieszkuje łącznie,

E. to wszystko wrzucamy do słownika, który następnie wyświetlimy w konsoli.

Zanim pójdziemy dalej istotna uwaga. Dla Openpyxl nie są wspierane starze arkusze kalkulacyjne z rozszerzeniem xls, dlatego wcześniej należy ten plik skonwertować do wersji wspieranej przez najnowszego Office’a.

Realizacja

Zaczynamy od załadowania modułu Openpyxl, a następnie pobrania pliku xlsx oraz utworzenia obiektu odpowiadającego arkuszowi “CITIES-OVER-300K”, bo to on zawiera szukane przez nas dane.

import openpyxl 
skoroszyt = 'global-city-population-estimates.xlsx'
arkusz = 'CITIES-OVER-300K' 
wb = openpyxl.load_workbook(skoroszyt)
ws = wb[arkusz]

W kolejnym kroku tworzymy pusty słownik o dowolnej nazwie (u mnie stanęło na “topCities”). Do niego będziemy wstawiali nasze dane przy pomocy pętli for, gdzie kluczem będzie nazwa poszczególnych krajów (kolumna B w arkuszu Excela) zaś wartością zagnieżdżony słownik o kluczach “citySum” oraz . Zanim jednak do tego przejdziemy słowo o tym jak pobierać można pobierać przy pomocy modułu Openpyxl wartości z poszczególnych komórek. Dla kogoś kto choć trochę bawił się VBA nie będzie zaskoczonym, że można to robić na różne sposoby. Ja wybrałem metodę cell i atrybut value. W przypadku tej pierwszej zasada jest prosta, chcąc wskazać interesującą nas komórkę podajemy jej położenie w postaci dwóch cyfr, które odpowiadają wierszowi i kolumnie. Czyli dla A1 będzie to cell(1,1), dla A3 cell(3,1), zaś dla B7 cell(7,2). Uzbrojeni w tę wiedzę idziemy dalej, czyli dorzucamy następujący kod, który następnie krótko omówię.

topCities ={} 
for x in range (2, ws.max_row+1):   
    if int(ws.cell(x, 22).value) >= 1000:       
        countryName = ws.cell(x,2).value       
        population = ws.cell(x, 22).value               
        topCities.setdefault(countryName, {'citySum': 0, 'populationSum' : 0})       
        topCities[countryName]['citySum'] += 1       
        topCities[countryName]['populationSum'] += int(population)

W pierwszym wierszu tworzymy słownik, który następnie zasilimy danymi. Potem rozpoczynamy interację poprzez wiersze przy pomocy pętli for i funkcji range. Zaczynamy od 2-go rekordu, ponieważ pierwszy zawiera nazwy kolumn, zaś długość zakresu wyznaczamy przy pomocy metody max_row, która zwraca liczbę zapisanych wierszy. Ponieważ ostatni wiersz nas również interesuje, to podnosimy tę liczbę (jeśli ktoś nie pamięta, to range nie bierze pod uwagę ostatniej liczby przy wyznaczaniu zakresu). W praktyce pętla wykona się 1692 razy.

Następnie dodajemy warunek, który mówi o tym, że dla danego wiersza w kolumnie “V” wartość ma być równa większa tysiąc, który tutaj – naszym arkuszu – odpowiada właśnie milionowi (tysiąc tysięcy). Dla każdego wiersza zaś pobieramy nazwę kraju oraz populację dla aglomeracji, o której w nim jest mowa i przypisujemy je do zmiennych. Pierwsza z nich (countryName) będzie stanowić klucz naszego słownika, której wartością będzie kolejny słownik o dwóch kluczach, czyli citySum oraz populationSum.

Następnie zaczynamy od dodania pierwszego klucza do naszego słownika, ale używamy do tego metody setdafault, która pozwala przed jego utworzeniem sprawdzić czy przypadkiem nie ma już klucza o takiej nazwie i wówczas nie będzie próbowała go dodać ponownie. Pozwoli to nam uniknąć ciągłego nadpisywania klucza w przypadku, gdy dla danego kraj występuje więcej niż jeden raz (czytaj: na jego terytorium dostępne jest więcej niż jedno miasto o liczebności przekraczającej milion). Potem następuje zliczanie wystąpień danego kraju (czytaj: liczby miast w nim leżących i spełniających ten warunek) oraz sumowanie ludności, która mieszka w tych aglomeracjach. No i gotowe.

Na koniec możemy sobie wyświetlić tę listę w konsoli, choć do tego celu sugerowałbym użycie modułu pprint, który pozwala słowniki zagnieżdżone prezentować w nieco bardziej czytelny sposób.  Niżej widać wyimek dla krajów zaczynających się na P. Jak widać kraje azjatyckie biją nas na głowę pod tym względem.

{…

’Pakistan’: {’citySum’: 11, 'populationSum’: 49126},

’Panama’: {’citySum’: 1, 'populationSum’: 1853},

’Paraguay’: {’citySum’: 1, 'populationSum’: 2608},

’Peru’: {’citySum’: 1, 'populationSum’: 10755},

’Philippines’: {’citySum’: 4, 'populationSum’: 17821},

’Poland’: {’citySum’: 1, 'populationSum’: 1751},

’Portugal’: {’citySum’: 2, 'populationSum’: 4333},

…}