First steps with Excel & Python [PL] PART4

Czy można wyobrazić sobie coś bardziej pięknego niż dostawianie w raporcie kolejnych kolumn, które z minuty na minutę przybliżają Cię do oddania raportu na czas? Jestem pewien, że można dodać do tego jeszcze…

przede wszystkim wzrost własnych umiejętności, które będą nie tylko Cię rozwijać, ale jeszcze przynosić konkretną satysfakcję mierzoną liczbą uśmiechów do monitora za każdym razem, gdy uda Ci się rozwiązać mały puzzel, z których składa się Twój raport.

Dziś właśnie miałem taki dzień- właściwie muszę przyznać, że kolejno toczone batalie nad materiałem, który publikuję dziś na blogu, dały mi ostatnio bardzo dużo frajdy.

Nie tylko musiałem powalczyć ze swoimi dotychczasowymi nawykami excelowego wyjadacza, ale również bardzo mocno pomyśleć jak zrobić to inaczej. Okazuje się, że programowanie jest całkowicie innym rozwiązaniem problemów, z którymi my Analitycy danych bujamy się na co dzień. Klasyczne zadania, jak dodanie kolumny, usuniecie wiersza, skopiowanie zakresu, wklejenie go w inne miejsce w dokumencie wydaję się być trywialną codziennością. Cóż, z mojej obecnej perspektywy w Pythonie  jest to oczywiście równie proste, ale mogę tak powiedzieć tylko dlatego, że oddałem dużo czasu na poznanie każdej linijki swojego lub czyjegoś kodu, który musiałem przeanalizować aby cokolwiek dziś napisać. Przychodzę do Was zatem z bardzo konkretnym rozwiązaniem, konkretnego problemu, który rozwiązałem na dwa sposoby: w znanym nam i lubianym Excelu oraz w Pythonie.

Na początek jedna duża uwaga- zapoznałem Was tu First steps with Excel & Python [PL] PART1 z narzędziem Jupyter Notebook a to tylko dlatego, że mam przyjemność korzystać z takiego środowiska w moim biznesowym otoczeniu. Z perspektywy tych kilku projektów, które zrealizowałem na potrzeby bloga uważam, że zdecydowanie lepszym narzędziem do nauki oraz programowania w Pythonie jest IDE PyCharm, który w wersji Community możecie pobrać za darmo tu PyCharm. PyCharm jest bardziej intuicyjny, pozwala kontrolować wersje programu dzięki połączeniu z GitHubem, na którym możecie trzymać swoje repozytoria kodu oraz ma fajne funkcje jak: autouzupełnianie, podpowiedzi, dobry dostęp do kodów źródłowych Pythona i jeszcze pewnie dodatkowo 50 innych zalet, których nie znam. Polecam zatem zmienić środowisko pracy, naprawdę usprawnia to robotę. Poniżej plan jazdy na dziś, który opisuję z perspektywy Excela:

Na podstawie naszego pliku MOCK_DATA_1 dodamy do niego kolejne kolumny:

  • Techniczna_1, w której została wyciągnięta informacja, w jakiej domenie użytkownik posiada adres mailowy
  • Wyszukiwanie_1, w której na podstawie kolumny Techniczna_1 wyszukamy (jak dobrze pamiętasz z ostatniej mojej obietnicy, będziemy korzystać z ukochanej funkcji WYSZUKAJ.PIONOWO) informację z arkusza MAPP_1 z kolumny ‘Zgoda’, gdzie dodałem informację, czy dana domena posiada zgodę (np. na przetwarzanie danych z tytułu RODO, to w końcu takie popularne ostatnio)
  • Techniczna_2, w której określiłem typ zapisu IP, czyli skonwertowałem zapis IP 111.11.11.111 do „3znaki.2znaki.2znaki.3znaki”. Z tej formuły excelowskiej zostały wytypowane unikatowe wartości i zmapowane w arkuszu MAPP_2
  • Wyszukiwanie_2, w której wyszukaliśmy na podstawie kolumny Techniczna_2 zmapowaną wartość typu IP użytkownika
  • Wyszukiwanie_3, w której w prosty sposób wyszukuje polskiego odpowiednika angielskiego Gender.

W naszym zadaniu mamy zatem 2 różne kolumny obliczeniowe wyciągające informacje z pliku z fejkowymi danymi oraz mappingi, z których wyszukujemy 3 razy konkretne informacje. Jest to zatem dość ciekawe zadanie: czas wykonania w Excelu 15 minut, czas nauki i wykonania w Pythonie (naprawdę) kilka godzin. Wniosek: zrobiłem to za Ciebie w Pythonie i oszczędzasz kilka godzin szukania rozwiązań, jeśli uczysz się tak samo jak ja.

Do pobrania rozwiązanie w Excelu:

Do dzieła!!

Dane do pobrania przed wykonaniem ćwiczenia w Pythonie:

  • Plik MOCK_DATA_1.csv
  • Plik WYSZUKIWANIE_MAPP1.csv
  • Plik WYSZUKIWANIE_MAPP2.csv
  • Plik WYSZUKIWANIE_MAPP3.csv

Zacznijmy od rozwiązania w Excelu:

  • Formuła, za pomocą której wyciągnięta została domena użytkownika na podstawie podanego maila to:

=PODSTAW($D2;LEWY($D2;ZNAJDŹ(“@”;$D2;1));””)

Rozwiązanie jest proste: w komórce, w której jest mail zamiast fragmentu od pierwszego lewego znaku do znaku małpy „@” podstawiliśmy znak pusty. Dzięki temu z michalfedorczy@gmail.com otrzymaliśmy gmail.com

  • Formuła, za pomocą której wyszukaliśmy zgodę dla domeny jest poniżej:

=WYSZUKAJ.PIONOWO($G2;MAPP_1!$A:$B;2;0)

Chciałbym dodać: to chyba nie wymaga wytłumaczenia- jeżeli ktokolwiek by go potrzebował, zapraszam do pytania w komentarzu.

  • Funkcja, za pomocą której skonwertowane zostały numery IP to:

=PODSTAW(ZNAJDŹ(“.”;$F2)-1&” znaki.”&ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2));””;1))-1&” znaki.”&ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2)-1+ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2));””;1))-1+2);””;1))-1&” znaki.”&DŁ(PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2)-1+ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2));””;1))-1+ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2)-1+ZNAJDŹ(“.”;PODSTAW($F2;LEWY($F2;ZNAJDŹ(“.”;$F2));””;1))-1+2);””;1))-1+3);””;1))&” znaki”;” “;”_”)

I to jest coś pięknego, co właśnie wzbudza uśmiech na twarzy. Taka formuła poprawnie przekonwertowała wszystkie IP z mojej listy. Krótkie wyjaśnieniem jej funkcjonowania: znajdź numer miejsca w ciągu znaków, w którym znajduje się kropka, ustal numer miejsca kropki. Następnie, podstaw za wszystko co jest przed pierwszą kropka znak pusty i zrób ponownie ustalenie w którym miejscu znajduje się kropka w tym nowym ciągu, itd. I tu właśnie widać idealnie: gdyby IP miał numer: 45.78979.78977.97.89797979.789797979.7789797.98797…7878.8787.87878, taka logika tworzenia funkcji by nie przeszła, nie byłaby możliwa. Trzeba zatem te powtarzalne kroki spisać za pomocą jakiejś iteracji: pracując w Excelu można oczywiście to zrobić w VBA. Na tym blogu dowiesz się jak robić takie rzeczy w Pythonie i zrobisz to razem ze mną od zera, gdyż nie jestem expertem. Ale funkcja ładna, co nie?

  • Funkcja, za pomocą której udało się dodać typ IP znajduje się poniżej;

=WYSZUKAJ.PIONOWO($I2;MAPP_2!$A:$B;2;0)

  • Funkcja, która przypisała Kobietę do Female i Mężczyznę do Male znajduje się poniżej:

=WYSZUKAJ.PIONOWO($E2;MAPP_3!$A:$B;2;0)

Oczywiście, powyższe funkcje nie są trudne. Trudna natomiast byłaby implementacja bardziej zaawansowanych funkcji w Pythonie na tym etapie mojej wiedzy, nie przeciążam się zatem i już przystępuję do wyjaśnienia, jak zrobiłem to w Pythonie.

Jedna mała uwaga w tym miejscu- naprawdę można to zrobić na kilkanaście sposobów, zatem feel free aby zaproponować swój własny kod w komentarzu.

Na początek, przede wszystkim wgrywamy dane:

import pandas as pd

# Definicja lokalizacji plików oraz nazwy kolumn w plikach

lokalizacja1 = ‘C://Users/micha/Dropbox/skillbrix/First_steps_in_Excel/MOCK_DATA_1.csv’
kolumny1 = [‘ID’, ‘imie’, ‘nazwisko’, ’email’, ‘plec’, ‘IP’]

lokalizacja2 = ‘C://Users/micha/Dropbox/skillbrix/First_steps_in_Excel/WYSZUKIWANIE_MAPP1.csv’
kolumny2 = [‘techniczna_1’, ‘zgoda’]

lokalizacja3 = ‘C://Users/micha/Dropbox/skillbrix/First_steps_in_Excel/WYSZUKIWANIE_MAPP2.csv’
kolumny3 = [‘techniczna_2’, ‘typIP’]

lokalizacja4 = ‘C://Users/micha/Dropbox/skillbrix/First_steps_in_Excel/WYSZUKIWANIE_MAPP3.csv’
kolumny4 = [‘gender’, ‘plec’]

# Pobranie wcześniej zdefiniowanych plików csv.

dane = pd.read_csv(lokalizacja1, delimiter=’;’, encoding=’utf-8′, names=kolumny1, skiprows=1)
mapp1 = pd.read_csv(lokalizacja2, delimiter=’;’, encoding=’latin’, names=kolumny2, skiprows=1)
mapp2 = pd.read_csv(lokalizacja3, delimiter=’;’, encoding=’utf-8′, names=kolumny3, skiprows=1)
mapp3 = pd.read_csv(lokalizacja4, delimiter=’;’, encoding=’utf-8′, names=kolumny4, skiprows=1)

Mam tutaj sam do siebie apel, albo prośbę do Was o dobrą radę- jak zrobić, aby pobrać dane również z formatu xlsx? Wrzucę to sobie w listę rzeczy toDo.

Podejdźmy odważnie do zadania numer nr 1, czyli musimy do naszego zbioru danych dodać kolumnę z domeną, a potem na podstawie tego dograć zgodę na przetwarzanie tej domeny, która została określona w pliku MAPP1.

Poniżej przedstawiam Wam cały kod wraz z komentarzem:

# *******************************************************************
# ZADANIE1
# Dogranie do pliku MOCK_DATA_1 kolumny: techniczna_1, wyszukiwanie_1
# *******************************************************************

# Krok 1: stworzenie tabeli array1 z wymiarem zgodnym z wielkością ‘dane’
array1 = [None] * len(dane.email)

# Krok 2: wydzielenie oddzielnej kolumny z mailami ‘data’ do obliczeń
dane_techniczna_1 = dane[’email’]


# Krok 3: stworzenie funkcji splittującej
def domainsplit(x):
    try:
        return x.split(‘@’)[1]
    except:
        return “not a domain”


# Krok 4: wykonanie operacyji przypisania do array1 odpowiednich wartości z dane_techniczna_1
for i in range(len(array1)):
    array1[i] = domainsplit(dane_techniczna_1[i])

# Krok 5: konwersja array1 do DataFrame i przypisanie kolumny techniczna_1 do ‘dane’
techniczna_1 = pd.DataFrame(array1)
dane[‘techniczna_1’] = techniczna_1

# Krok 6: przypisanie do domeny danych o zgodach zgodnie z plikiem MAPP_1 oraz zmiana nazwy kolumny ‘zgoda’ na kolumnę ‘wyszukiwanie_1’
dane = pd.merge(dane, mapp1, on=’techniczna_1′)

dane.rename(columns={‘zgoda’: ‘wyszukiwanie_1’}, inplace=True)

Na podstawie tej metodologii zrobimy to samo z zadaniem numer 2, w którym główną zmianą będzie przede wszystkim stworzenie silnika, który przemieli numer IP i pokaże nam odpowiedni, zdefiniowany w Excelu typ. W poniższym kodzie znajdziecie całe rozwiązanie tego problemu:

# # *******************************************************************
# # ZADANIE2
# # Dogranie do pliku MOCK_DATA_1 kolumny: techniczna_2, wyszukiwanie_2
# # *******************************************************************


# Krok 1: wydzielenie kolumny IP ze zbioru danych
dane_techniczna_2 = dane[‘IP’]

# Krok 2: utworzenie tabeli, w której uzupełnimy typ IP po wykonaniu obliczeń:
array2 = [None] * len(dane_techniczna_2)

# Krok 3: wykonanie obliczeń dla każdego elementu tabeli dane_techniczna_2 i przypisanie ich do tabeli array2

for i in range(len(array2)):
    array2[i] = dane_techniczna_2[i]

# Krok 4: wykonanie obliczeń dla każdego elementu tabeli array2 i tymczasowe przypisanie ich do arr

arr = [‘.’.join([str(len(x))+’_znaki’ for x in i.split(‘.’)])  for i in array2]

# Krok 5: konwersja arr do DataFrame i przypisanie kolumny techniczna_2 do ‘dane’
dane_techniczna_2 = pd.DataFrame(arr)
dane[‘techniczna_2’] = dane_techniczna_2

# Krok 6: przypisanie do typu IP odpowiednio zdefiniowanego numeru z pliku MAPP3 oraz zmiana nazwy kolumny
dane = pd.merge(dane, mapp2, on=’techniczna_2′)

dane.rename(columns={‘typIP’: ‘wyszukiwanie_2’}, inplace=True)

Najtrudniejszym elementem tego zadania było stworzenie maszynki, która przemieli w odpowiednim formacie numery IP na liczbę znaków- tak samo jak w Excelu, było to duże wyzwanie.

Ostatnim zadaniem na dziś jest stworzenie kolumny, w której zgodnie z mappingiem3 zmienimy angielskie nazwy płci na polskie nazwy, wszystko do osiągnięcia poniższym kodem:

# *******************************************************************
# ZADANIE3
# Dogranie do pliku MOCK_DATA_1 kolumny: wyszukiwanie_3
# *******************************************************************

# Krok 1: przypisanie do zbioru ‘dane’ informacji o płci w języku polskim

dane = pd.merge(dane, mapp3, on=’plec’)
dane.rename(columns={‘plecPL’: ‘wyszukiwanie_3’}, inplace=True)

Finalnie, abyśmy mogli wspólnie otworzyć szampana potrzebujemy jeszcze jednej, bardzo ważnej rzeczy: funkcji zapisu. W dzisiejszym ćwiczeniu możemy to wykonać za pomocą poniższego kodu.

# Zapisanie prac do pliku “dane.xlsx”
lokalizacja5 = ‘C://Users/micha/Dropbox/skillbrix/First_steps_in_Excel/dane.xlsx’
writer = pd.ExcelWriter(lokalizacja5, engine=’xlsxwriter’)
dane.to_excel(writer, sheet_name=’ARKUSZ1′)
writer.save()

Rezultatem wszystkich operacji będzie plik dane.xlsx, który powinien wyglądać tak:

Rozpoczęcie poniedziałku taką dawką kodu może przytłoczyć. Nie ma natomiast powodu, aby przestawać dążyć do bardziej optymalnych rozwiązań problemów. Prędzej czy później, z pozoru szybsze rozwiązanie w Excelu skończy się wraz z końcem zdolności obliczeniowych Waszych arkuszy: sam nie raz stanąłem przed ścianą, za którą nie byłem w stanie przemycić ani jednej formuły więcej, mimo tego, że bardzo bym chciał. Mam nadzieję, że ruszyłem Wasze poranne umysły do pracy!

Miłego poniedziałku!

Leave a Reply

Your email address will not be published. Required fields are marked *