import warnings
import warnings
warnings.filterwarnings('ignore')
import sqlite3
import os
import numpy as np
import pandas as pd
os.chdir('/home/jarekj/Dropbox/dydaktyka/programowanie4/powiatData')
Pandas to biblioteka zarządzania danymi uporządkowanymi w założeniu przypominająca działanie struktury danych dataFrame znanej z języka R i innych systemów obliczeniowych. Z założenia pandas umożliwia w prosty sposób bezpośrednie pobieranie danych z różnych źródeł, w tym:
Możliwe jest również przekształcenie do formatu dataFrame z innych struktur danych języka Python: słowników oraz macierzy numpy. Ta druga metoda może być wykorzystana do poboru dowolnego typu danych które można w wyniku przekształcić do formatu tabelarycznego. To drugie rozwiązanie zostanie wykorzystane do importu danych rastrowych do formatu DataFreame poprzez interface GDAL.
Dane tekstowe lub strukturyzowane dane binarne jak na przykład arkusz Excela importowane są w całości odpowiednim pleceniem: pd.read_csv() lub read_excel()co może być problemem w przypadku dużych zbiorów danych, jeżeli nie potrzebujemy ich w całości. W przypadku importu danych SQL możemy importować wyniki zapytań SQL co oznacza że już na etapie importu możemy wybrać jakie kolumny i rekordy zostaną zaimportowane a nawet łączyć dane z różych tabel przy pomocy odpowiednich klauzul SQL. Import danych z bazy danych wymaga załadowania odpowiedniego sterownika dostępu do danego silnika. Popularne formaty takie jak SQLite, MySQL, MSSQL są dostępne z poziomu biblioteki standardowej.
W analizie danych interesują nas wyłącznie atrybuty (część nieprzestrzenna) pliku wektorowego. Z tego powodu możemy uprościć proces importu danych potraktowanie pliku geoprzestrzennego jako zwykłej bazy danych. W ramach przykładu zaimportujemy dane pochodzace z pliku wektorowego przechowywanego w formacie SQLite/Spatialite. Niestety język Python nie wspiera komunikacji z plikami dbf. Z tego powodu należy unikać stosowania formatu shapefile. Również istnieją problemy z zarządzaniem (modyfikowaniem) plików w formacie Geospatial Package - gpkg. Do analizy dużych zbiorów danych najlepiej stosować dane w formacie postgreSQL/Postgis, lub - jeżeli zależny nam na niezależności od serwera bazodanowego z formatu SQLite/Spatialite. W ramach kursu będziemy korzystać z formatu SQlite, który jest obsługiwany przez bibliotekę standardową Pythona oraz nie wymaga stosowania żadnych dodatkowych narzedzi (serwera SQL)
Po załadowaniu biblioteki sqlite3 oraz ustawieniu katalogu roboczego, proces importu obejmuje następujące kroki:
pandas.read_sql()Baza danych znajduje się w pliku database.db a tabela zawierająca dane ma nazwę centroids.
database = 'database.db' #1A
table = 'centroids' #1B
conn=sqlite3.connect(database) #2A
cur = conn.cursor() #2B
query = "SELECT * FROM {0};".format(table)#3
data = pd.read_sql_query(query,conn)#4
Po wykonaniu funkcji zostanie utworzona struktura danych DataFrame, która jest natywnym rozwiązaniem biblioteki pandas. Struktura jest wzorowana na modelu danych języka R i porządkuje dane w kolumnach - każda określonego typu oraz rekordach zawierających wartości dla każdej kolumny. Strukturę danych można podejrzeć wypisując po prostu nazwę DataFrame, a w celu ograniczenia ilości wierszy możemy wywołać funkcję .head, która zwróci nam ograniczoną liczbę wierszy.
data.head(10)
Do opisu dataFrame mozna wykorzystać też inne funkcje:
df.head() # pierwsze 5 wierszy df.tail() # ostatnie pięć wierszy df.sample(5) # pięć losowych wierszy df.shape # liczba wierszy i kolumn w formie krotki (rows,cols) df.describe() # podstawowe miary dataFrame df.info() # informacje o typach danych i zajmowanej pamięci
data.info()
data.describe()
Przegląd struktury ramki danych wskazuje że w danych znajduje się kolumna GEOMETRY, która zawiera dane binarne, do których nie mamy dostępu na tym etapie. Są one nieprzydatne w analizie i w przypadku dużego zbioru danych mogą zajmować duży obszar pamięci i długi czas importu. W związku z typ proces importu należy ograniczyć jedynie do danych atrybutowych.
Niestety, język SQL uniemożliwia wskazania negatywnej listy kolumn, kolumny które chcemy importować należy wymienić jawnie. Przy dużej liczbie kolumn może to być jednak kłopotliwe. Z tego powodu usunięcie kolumny geometrii z importu wymaga kilku nietrywialnych kroków:
cur = conn.cursor()
cl = cur.execute("PRAGMA table_info('%s')" % table).fetchall() #1
cl
cl = list(zip(*cl))[1] #2
columns = list(cl) # konwersja z krotki na listę
columns.remove('GEOMETRY') # usunięcie geometrii
columns
query = "SELECT {0} FROM {1};".format(",".join(columns),table) #3
query
Tk zdefiniowany zestaw poleceń zamknąć w postaci pojedynczej funkcji. Funkcja dodatkowo spraawdza czy podana tabel znajdue się w bazie danych, oraz sprawdza czy kolumna geometrii znajduje się w bazie danych.
def importSQliteAttributes(conn,table,geom='GEOMETRY'):
cur = conn.cursor()
result = cur.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() # sprawdzenie czy podana tabela znajduje się w bazie danych
all_tables = list(zip(*result))[0]
if not table in all_tables:
return None
cl = cur.execute("PRAGMA table_info('%s')" % table).fetchall()
cl = list(zip(*cl))
cl = list(cl[1])
columns = cl
if geom in columns:
columns.remove(geom)
query = "SELECT {0} FROM {1};".format(",".join(columns),table)
data = pd.read_sql_query(query,conn)
return data
conn=sqlite3.connect(database)
data = importSQliteAttributes(conn,table)
data.head()
Dodawanie kolumn do bazy danych jest o tyle skomplikowane, że musimy zmodyfikować posiadaną tabelę zawierającą geometrię. Poprzez interface pandas nie jest możliwe dodanie kolumny bezpośrednio. Procedura ta jest niezbędna, gdyż wyniki przetwarzania danych chcemy wykorzystywać w systemach informacji geograficznej.
Do bazy danych dodamy kolumnę zawierającą wartości losowe, wygenerowane przy pomocy funkcji np.random.randomint o długości równej liczbie wierszy dataFrame (#1). Następnie z tej tabeli wyodrębniamy dwie kolumny: dodaną kolumnę oraz kolumnę _gisfid
data.shape
data['result'] = np.random.randint(0,10,size=data.shape[0])
results = data[['ogc_fid','result']]
results.head()
Procedura ta wymaga jednak kilku nietrywialnych kroków. Na tym etapie jedynie zostanie przedstawiona mechanika dodawania kolumn, która będzie następnie wykorzystywana do prezentacji wyników zaawansowanego przetwarzania danych.
results.to_sql('tmp', conn, if_exists='replace', index=True) #1
conn.execute('ALTER TABLE centroids ADD COLUMN results integer') #2A
query = 'UPDATE centroids SET results = (SELECT result FROM tmp WHERE tmp.ogc_fid = centroids.ogc_fid) WHERE results IS NULL'
conn.execute(query) #3
conn.commit() #3 każda transakcja zmieniająca zawartość tabeli wymaga zamknięcia transkacji
conn.execute('drop table tmp') #4
Całość też można zamknąć w formie funkcji o dość skomplikowanycm nagłówku (cztery obowiązkowe pola)
def addColumn(frame,column,dbase,table,datatype='integer',fid='ogc_fid'):
tmp = frame[[fid,column]] # tabela tymczasowa
conn=sqlite3.connect(dbase)
frame.to_sql('tmp', conn, if_exists='replace', index=True) #1
conn.execute('ALTER TABLE {0} ADD COLUMN {1} {2}'.format(table,column,datatype)) #2
query = 'UPDATE {0} SET {1} = (SELECT {1} FROM tmp WHERE tmp.{2} = centroids.{2}) WHERE {1} IS NULL'.format(table,column,fid)
conn.execute(query) #3
conn.commit() #3
conn.execute('drop table tmp') #4
W poniższej części zostaną przedstawione wybrane funkcje biblioteki pandas, które będą wykorzystywane w dalszej części kursu. Biblioteka jest bardzo złożona i rozbudowana, szczegółowy opis funkcji biblioteki znajduje się w dokumentacji i licznych podręcznikach.
import sqlite3
os.chdir('/home/jarekj/Dropbox/dydaktyka/programowanie4/powiatData')
database = 'database.db' #1A
table = 'centroids' #1B
conn=sqlite3.connect(database) #2A
cur = conn.cursor() #2B
query = "SELECT * FROM {0};".format(table)#3
dt = pd.read_sql_query(query,conn)#4
Wybór kolumn i wierszy na podstawie nazwy i kryterium statystycznego
Jeżeli chcemy wybrać niektóre kolumny w podstaci nowej dataFrame używamy listy kolumn, które chcemy wybrać. Selekcję wierszy na postawie kryterium wykonujemy przy pomocy kryterium można wykonać na zasadzie porównania zwracającego serię True/False, a następnie użycie takiego wektora jako selekcji. Alternatywnie można zastosować metodę query().
selector = dt['aprod']>50000
dt[['powiat','aprod']][selector]
dt[['powiat','aprod']].query('aprod>50000')
Alternatywnie, jeżeli chcemy usunąć pojedyncze kolumny z wielu, możemy użyć metody drop(), wskazując jednoczesnie axis=1, jako informację że usuwamy kolumny. Zarówno selekcja jak i usuwanie ma charakter tymczasowy, jeżeli wyników nie zapiszemy w nowej dataFrame. Należy zaznaczyć, że kwestia ekstrakcji części danych (selekcja/usuwanie) nie jest gramatycznie spójna.
dt.drop(['aprod','prod','pprod'],axis=1)
Wybór kolumn i wierszy na podstawie pozycji
Jeżeli do wyboru wierszy i kolumn chcemy użyć informacji o ich pozycji musimy zastosować operator iloc, na pierwszym miejscu podajemy listę lub przedział wierszy a następnie listę lub przedział kolumn. Jeżeli dla jednej z osi chcemy pobrać cały zakres używamy operatora zasięgu [;].
dt.iloc[2:21,[1,3,7]]
Wartości unikalne i sortowanie
Pozyskanie wartości unikalnych dla danej kolumny odbywa się za pomocą funkcji drop_duplicates(). W przypadku większej ilości kolumn usuwane będą duplikaty połączeń a nie pojedynczych wartości. Do posortowania wartości w data.frame służy metoda sort_values(). Wymaga wskazania kolumn według których wykonywane jest sortwanie i jego kierunek. W poniższym przykładzie połączono usuwanie duplikatów i sortowanie danych.
dt['woj'].drop_duplicates().sort_values()
Selekcja danych na podstawie funkcji języka Python
W przypadku, gdy zadanie nie jest możliwe do zrealizowania na podstawie dialektu SQL, można do wykonania selekcji zastosować bezpośrednio funkcje języka Python. Na przykład w celu wyboru powiatów miejskich - których nazwy zaczynają się z dużej litery, można zamienić nazwę powiatu "jak w tytule" a następnie porównać z oryginalną nazwą. W ten sposób wybierzemy tylko te, których nazwy piasane są z wielkiej litery. Tak utworzony selector (True/False) można wykorzystać do wyboru wierszy z data.frame.
selector = dt.powiat.str.title()==dt.powiat
dt.loc[selector,'powiat']
Dodanie kolumny i tymczasowe dodanie kolumny
Jeżeli chcemy wybrać kolumnę poprzez nazwę wystarczy podać jej nazwę w nawiasach kwadratowych. Jeżeli do kolumny przypiszemy nową wartość, serię danych lub tablicę numpy o długości równej kolumnie jej wartość zostanie zastąpiona. Jeżeli kolumna nie istnieje zostanie utworzona. Typ danych zostanie wybrany automatycznie na podstawie przypisanej wartości. Poprzez metodę assign() można tymczasowo dodać kolumnę na czas wykonywania obliczeń. Jeżeli dodaną tę metodę kolumnę chcemy zachować, należy utworzyć nową dataFrame lub nadpisać istniejącą.
dt.assign(ludnosc=dt.aprod + dt['prod'] + dt.pprod).head(10)
dt['ludnosc'] = (dt.aprod + dt['prod'] + dt.pprod)
Grupowanie i statystki podsumowujące
Pandas umożliwia wyliczanie statystyk grupujących na podstawie kolumn zawierających nieunikalne wartości (kategoryzacyjne). Służą do tego dwie metody: groupby(), która wskazuje kolumnę grupującą oraz metody agg() wskazująca jakie funkcje statystyczne będą wyliczane dla grup. Jeżli wynik grupowania chcemy ograniczyć do wybranych kolumn należy użyć słownika zawierającą nazwę kolumny i wyliczane funkcje.
dt.groupby('woj').agg(['mean','std'])
dt.groupby('woj').agg({'aprod':['mean','std']})
Zmodyfikowanie zawartości kolumny
Jeżeli chcemy zmodyfikować zawartość kolumny jest to połączone najczęściej ze zmianą jej typu. W poniższym przykładzie...
wojs = {16:'OPOLSKIE',
26:'ŚWIĘTOKRZYSKIE',
4:'KUJAWSKO-POMORSKIE',
14:'MAZOWIECKIE',
22:'POMORSKIE',
24:'ŚLĄSKIE',
28:'WARMIŃSKO-MAZURSKIE',
32:'ZACHODNIOPOMORSKIE',
2:'DOLNOŚLĄSKIE',
30:'WIELKOPOLSKIE',
10:'ŁÓDZKIE',
20:'PODLASKIE',
12:'MAŁOPOLSKIE',
8:'LUBUSKIE',
18:'PODKARPACKIE',
6:'LUBELSKIE'}
dt['nazwoj'] = 't'
for key in wojs:
dt.loc[dt.woj==key,'nazwoj'] =wojs[key]
dt = dt.drop(['woj','GEOMETRY'],axis=1)
dt.rename(columns={'nazwoj':'woj'},inplace=True)
Co to jest pickle czy warto stosować różnice z Python2
#import pickle
#pickle.dump(dt,open("dta.p","wb+"))