Pandas – filtrowanie, grupowanie, eksport do Excela

Na podstawie pliku listy tankowań pobranego w formacie csv przedstawię podstawowe możliwości biblioteki Pandas. We wcześniejszym >>artykule<< opisałem parsowanie za pomocą modułu csv z biblioteki standardowej Pythona.

Plik csv jest plikiem tekstowym, w którym wartości poszczególnych kolumn są oddzielone delimiterem, najczęściej znakiem przecinka. W tym konkretnym przypadku, w pobranym pliku raportu listy tankowań, wartości są oddzielone od siebie znakiem średnika.

Nagłówek danych ma postać: Dane Kontrahenta;Imie;Nazwisko;Numer Korekty;Numer WZ;Data;Godzina;Licznik;Stacja;Numer Rejestracyjny;Numer Karty;Nazwa towaru;VAT procent;Cena bazowa;Cena netto;Cena brutto;Wartość rabatu;Ilość;Netto;VAT;Brutto. Kolejne wiersze zawierają wiersze z danymi dotyczącymi kolejnych tankowań.

Aby wczytać pobrany plik csv o nazwie report.csv musimy utworzyć obiekt ramki danych, gdzie pierwszym argumentem jest ścieżka do pliku, który chcemy wczytać, a argument sep określa rodzaj separatora, tzn.

import pandas as pd

df = pd.read_csv('path/to/report.csv', sep=';')

▣ Na podstawie utworzonego obiektu możemy np. określić ilość poszczególnych wierszy danych np. ilość tankowań z podziałem na różne paliwa tzn.

refuel_amount = df['Nazwa towaru'].value_counts()

Przykładowy wynik :

OLEJ NAPĘDOWY                     466
OPŁATA ZA WYDANIE KARTY FLOTA      1
Name: Nazwa towaru, dtype: int64

▣ Jeśli chcemy np. określić średnią cenę netto dla paliwa zawierającego w nazwie łańcuch: ‘napędowy’ to musimy zastosować filtrowanie tzn.

filter_ = df['Nazwa towaru'].str.lower().str.contains('napędowy')

result = df[filter_]

(Oryginalnie zawartość kolumny Nazwa towaru była pisana dużymi literami, stąd zastosowanie metody lower()). W wyniku zastosowania filtra ramka danych o nazwie result posiada tylko dane związane z wybranym paliwem. Niestety, dane wpisane w kolumnie Cena netto posiadają części dziesiętne oddzielone za pomocą przecinka a nie kropki. Aby dokonać konwersji wszystkich danych z kolumny Cena netto zastosuję metodę replace() a następnie konwersję wartości z kolumny na typ float tzn.

result['Cena netto'] = result['Cena netto'].str.replace(',', '.')

result['Cena netto'] = result['Cena netto'].astype(float)

Średnia cena paliwa dla kolumny Cena netto ramki result utworzonej w efekcie zastosowania filtra:

result['Cena netto'].mean()       # wylicza średnią cenę danego paliwa

Analogicznie stosując funkcje min(), max() możemy wyliczyć najniższą lub najwyższą cenę paliwa w danym okresie dla wybranego paliwa.

▣ Jeśli chcemy obliczyć średnią cenę dla różnych rodzajów paliw z kolumny Nazwa towaru to musimy zastosować grupowanie. Utworzę zmienną fuel przechowującą unikalne wartości z kolumny Nazwa towaru tzn.

fuel = df.groupby(['Nazwa towaru']                # obiekt DataFrameGroupBy

fuel['Cena netto'].mean()        # Zwraca średnią cenę netto dla każdego rodzaju paliwa

W powyższym przykładzie także należy mieć na uwadze, aby kolumna Cena netto zawierała elementy typu float .

▣ Jeśli chcielibyśmy przeszukiwać dane na podstawie daty to można zauważyć, że w pobranym pliku csv mamy dwie kolumny odpowiedzialne za przechowywanie daty i godziny. Są to odpowiednio kolumna Data oraz kolumna Godzina. Dane o wpisach do rejestru tankowań są posortowane, ale jeśli chcielibyśmy znaleźć np. tankowania z określonego zakresu dat lub np. średnią cenę z określonego zakresu dat, to należałoby zamienić wartości z tych obu kolumn na obiekt typu datetime. W poniższym przykładzie utworzę nową kolumnę, która będzie przechowywać wartość typu datetime dla każdego wiersza ramki danych.

df['datetime'] = df['Data'] + ' ' + df['Godzina']    # na razie kolumna z wartościami tekstowymi typu object 

df['datetime'] = df['datetime'].apply(lambda x: datetime.strptime(x, '%Y.%m.%d %H:%M:%S'))

Dla każdej wartości wiersza kolumny datetime zostaje zastosowana funkcja strptime(), która zamienia łańcuch znaków mający postać np. 2020.04.20 22:20:10 w obiekt typu datetime zgodnie z formatem podanym jako drugi argument funkcji.

Wartości z kolumny datetime mogą posłużyć jako index ramki, co ułatwi wyszukiwanie za pomocą wycinków (slices) tzn.

df.set_index('datetime', inplace=True)  # argument inplace=True nadpisuje ramkę df

Jeśli chcemy np. wyświetlić wiersze z kolumn Nazwa towaru oraz Cena netto dla wierszy z zakresu dat np. od 15 do 20 kwietnia 2020 to możemy napisać:

df.loc['2020-04-15':'2020-04-20', ['Nazwa towaru','Cena netto']]

▣ Jeśli chcemy wyświetlić maksymalną i minimalne cenę wszystkich rodzajów paliw z pobranego raportu w tygodniach objętych raportem, to możemy użyć metodę resample():

fuel = df.groupby(['Nazwa towaru'])  # utworzenie grupy
  # z wszystkich rodzajów paliw 
  # występujących w kolumnie 'Nazwa towaru' (unikalne wartości)

fuel['Cena netto'].resample('W').agg(['max', 'min'])  

W powyższym przykładzie utworzyłem najpierw zmienną przechowującą obiekt DataFrameGroupBy, przechowującą unikalne wartości z wierszy tankowań. Następnie dla każdego unikalnego elementu wyświetlam wartości ceny minimalnej i maksymalnej zwracane przez metody min() oraz max(), ale wyniki są wyświetlane dla okresów tygodniowych (argument ‘W’ – week). Analogicznie dla interwałów dwutygodniowych wystarczy jako argument użyć ‘2W’.

Przykładowy wynik :

		                max	min
Nazwa towaru	 datetime		
OLEJ NAPĘDOWY 	2020-04-05	3.39	3.24
                2020-04-19	3.20	2.98
                2020-05-03	2.99	2.87
                2020-05-17	3.11	2.94
                2020-05-31	3.21	3.11
OPŁATA ZA WYDANIE KARTY 2020-05-19 4.07	4.07

▣ Jeśli chcielibyśmy wyświetlić dane dotyczące okresów tygodniowych, ale dla wartości wierszy z kolumny ‘Cena netto’ interesuje nas, jaka w tych tygodniach jest maksymalna i minimalna cena netto, a dla kolumny ‘Ilość’, reprezentującej ilość zatankowanego paliwa chcielibyśmy uzyskać informację o ilości zakupionego paliwa w danym tygodniu:

result = df.resample('W').agg({'Cena netto':['min', 'max'], 'Ilość':'sum'})

W powyższym przykładzie stosujemy funkcję agregującą agg(), która jako argument pobiera słownik, w którym indeksami są nazwy kolumn, a wartościami nazwy funkcji, które chcemy zastosować.

Przykładowy wynik :

	          Cena netto	Ilość
                min	max	sum
datetime			
2020-04-05	3.01	3.11	2969.00
2020-04-12	3.08	3.15	3019.54
2020-04-19	2.88	3.05	3808.03
2020-04-26	2.81	4.02	3902.50
2020-05-03	2.78	2.89	3808.00
2020-05-10	2.74	2.95	4409.00
2020-05-17	2.95	3.05	4008.04
2020-05-24	3.02	3.15	4684.05
2020-05-31	3.12	3.18	4055.00

▣ Zapis lub odczyt z plików Excela:

df = pd.read_excel('path/to/file.xlsx')  # odczyt z pliku file.xlsx

df.to_excel('path/to/file.xlsx')  # zapis do pliku file.xlsx

Aby odczytać/zapisać dane z konkretnego arkusza pliku programu Excel, to jako drugi parametr należy podać argument sheet_name

df.to_excel('path/to/file.xlsx', sheet_name='Arkusz_1')    # zapisuje dane z ramki
 # do pliku file.xlsx w arkuszu o nazwie Arkusz_1

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *