Export danych z pliku excel (xlsx) do formatu csv

Format danych csv  – „Comma Separated Values” jest jednym podstawowych formatów danych używany jako wsad w większości znanych programów czy systemów. Dane są przetrzymywane w formie tekstowej, gdzie każde pole jest oddzielane przez wskazany separator. W niniejszym artykule omówimy skrypt w języku Python.

Celem przedstawionego kodu jest eksport wybranych wartości z wybranych kolumn do formatu CSV  z ostatnio zmodyfikowanego pliku Excel w zadanej lokalizacji. Oznacza to, że skrypt potrzebuje przeszukać dany folder, ustalić ostatnio zmodyfikowany plik, pobrać interesujące dane, sparsować dane, i na koniec zapisać plik csv lokalizacji wyjściowej.

Jako zestaw danych zostaną posłużone historyczne wyniki matur ze strony https://stat.gov.pl/

Format danych

Wykorzystane dane są w postaci prostej tabelki w pliku xlsx.

Biblioteki

Na potrzeby niniejszego projektu będą potrzebne biblioteki

• pandas – do parsowania plików xlsx
• glob – listowanie plików
• os – pozyskiwanie daty modyfikacji

import pandas as pd         
import glob                 
import os                 

Ostatnio modyfikowany plik Excel

Zanim przejdziemy do ustalenia ostatniego pliku, pewne informacje najlepiej będzie zapisać w formie zmiennych.

Następnie tabela z wynikami znajduje się w zakładce ‘dane’, którą przypiszemy do stałej SHEET_NAME. Kolejnym krokiem jest pobranie listy plików xlsx i przypisanie do files_list. Oczywiście każdy powinien podać ścieżkę właściwą według swoich potrzeb.

SHEET_NAME = 'dane'
files_list = glob.glob(r'E:\Blogowanie\Python\XLSXdoCSV\Dane\*.xlsx')

Jeżeli pliki znajdują się w lokalizacji sieciowej to mogą zostać wykorzystane przez inne osoby. Czyli istnieje szansa uwzględnienia na liście plików tymczasowych, które niekoniecznie są tym czego potrzebujemy. Pliki takie zobaczymy po odznaczeniu opcji ‘Ukryj chronione pliki systemu operacyjnego (zalecane)’.

Do rozwiązania tego problemu wykorzystamy listę plików, ale po wykluczeniu plików tymczasowych. Możemy ją zbudować dodając warunek na pominiecie plików z tyldą w nazwie. Ponadto Do ustalenia ostatnio modyfikowanego pliku posłużymy się metodą getmtime. Alternatywą może być również sortowanie po dacie utworzenia pliku poprzez getctime.

files_list_exclude_temp = []
for x in files_list:
    if '~' not in x:
        files_list_exclude_temp.append(x)
latest_file = max(files_list_exclude_temp, key=os.path.getmtime)

Parsowanie danych

Na początek wykonamy parsowanie pliku excel dzięki bibliotece pandas do zmiennej xl.

xl = pd.ExcelFile(latest_file)

Kolejno dodamy zmienną określającą istnienie szukanej zakładki w pliku. W naszym przypadku wiemy, że zakładka istnieje ale w praktyce jest to dobra metoda na obsłużenie jednego z możliwych do wystąpienia błędów.

exists = 0
for sheet in xl.sheet_names:
    if sheet == SHEET_NAME:
        exists = 1

if exists==0:
    print("Brak danych")
else:

Dalej parsujemy zakładki z danymi. Parametr header pozwala na ustawienie wiersza z nagłówkiem licząc od zera. W naszym przypadku będzie to wartość domyślna – 0.

    df_results = xl.parse(SHEET_NAME,header=0)

Przeważnie dane, które chcemy wyeksportować wymagają dodatkowych transformacji i zawierają nadmiar informacji. Tak i tutaj następną z możliwości jest wykluczenie wybranych kolumn. W tym przypadku stworzymy listę z położeniem tychże kolumn i następnie usuniemy je z df_results poprzez metodę drop.

    columns_count = 0
    columns_deleted_list = []
    for header in df_results.columns.values:
        if header.upper() in ('NAZWA_ZMIENNEJ','TYP_INFORMACJI_Z_JEDNOSTKA_MIARY','FLAGA'):
            columns_deleted_list.append(columns_count)
        columns_count+=1
    df_results = df_results.drop(df_results.columns[columns_deleted_list], axis=1)

Opcjonalnie można również usunąć wiersze, w których występuje chociaż jedno pole bez wartości.

    df_results = df_results.dropna()

Następnie będzie stworzenie pustej listy, do której będą dodawane wartości pól do eksportu oraz przypisanie ilości wiersz do zmiennej pomocniczej. Do tego możemy również utworzyć zmienną pomocniczą rows z wierszami poprzez metodę shape[0]. Gdybyśmy chcieli odnieść się do kolumn wystarczy wykorzystać shapte[1].

    value = []
    rows= df_results.shape[0]

Pola takie jak rodzaj egzaminu, poziom egzaminu czy płeć są polami z bardzo wąskim zakresem wartości i w zasadzie reprezentują skale nominalne. W ten sposób wartości te można traktować jako etykiety. Dlatego też w tym przypadku można zastąpić takie pola wartościami liczbowymi, które będą identyfikatorami pól. W przypadku potrzeby przywrócenia oryginalnych wartości można wykorzystać prosty słownik do przypisania pól tekstowych.

Zdefiniowanie zmiennych pomocniczych z wartościami domyślnymi

    exam_type = 0   #ustny
    exam_level = 0  #nie dotyczy
    gender = 0      #kobiety

Kolejno już podczas sprawdzania poszczególnych wierszy następuje przypisanie pozostałych wartości

    for i in range(rows):
        #for j in range(1,columns):
        if df_results.iloc[i,0] == 'ustny': exam_type = 1
        if df_results.iloc[i,1] == 'podstawowy': 
            exam_level = 1
        elif df_results.iloc[i,1] == 'rozszerzony':
            exam_level = 2
        if df_results.iloc[i,3] == 'mężczyźni': gender = 1

Jeżeli interesują nas jedynie wybrane dane można dodać warunki na wykluczenie poszczególnych kategorii lub wartości. W poniższym przypadku pominięte zostały wartości  zerowa, oraz puste. Ponadto w polu płeć wykluczona została kategoria ogółem.

        if df_results.iloc[i,5] not in (0,'') and df_results.iloc[i,3] not in ('ogółem'):
            values.append((df_results.iloc[i,2],exam_type, exam_level,gender,df_results.iloc[i,4] , df_results.iloc[i,5]  ))

Ostatnim krokiem jest już tylko parsowanie danych z tabeli i zapis do formatu csv.

    df_final_results = pd.DataFrame(values,columns = ["Przedmiot", "Rodzaj","Poziom", "Plec","Rok","Wynik"])
    df_final_results.to_csv(r'E:\Blogowanie\Python\XLSXdoCSV\Wyniki\matury.csv',sep=';',encoding='utf-16')

Konkludując cały kod będzie wyglądał jak poniżej

import pandas as pd         #parsowanie plików xlsx 
import glob                 #listowanie plików
import os                   #pozyskanie daty modyfikacji pliku


#Wartości stałe
SHEET_NAME = 'dane'

#Pobieranie najnowszego pliku
files_list = glob.glob(r'E:\Blogowanie\Python\XLSXdoCSV\Dane\*.xlsx')
files_list_exclude_temp = []
for x in files_list:
    if '~' not in x:
        files_list_exclude_temp.append(x)

latest_file = max(files_list_exclude_temp, key=os.path.getmtime)

xl = pd.ExcelFile(latest_file)

exists = 0
for sheet in xl.sheet_names:
    if sheet == SHEET_NAME:
        exists = 1

if exists==0:
    print("Brak danych")
else:

    #Ladowanie pliku
    df_results = xl.parse(SHEET_NAME,header=0)

    columns_count = 0
    columns_deleted_list = []
    for header in df_results.columns.values:
        if header.upper() in ('NAZWA_ZMIENNEJ','TYP_INFORMACJI_Z_JEDNOSTKA_MIARY','FLAGA'):
            columns_deleted_list.append(columns_count)
        columns_count+=1
    df_results = df_results.drop(df_results.columns[columns_deleted_list], axis=1)

    df_results = df_results.dropna();

    values = []
    rows= df_results.shape[0]

    exam_type = 0   #ustny
    exam_level = 0  #nie dotyczy
    gender = 0      #kobiety

    for i in range(rows):
        if df_results.iloc[i,0] == 'ustny': exam_type = 1
        if df_results.iloc[i,1] == 'podstawowy': 
            exam_level = 1
        elif df_results.iloc[i,1] == 'rozszerzony':
            exam_level = 2
        if df_results.iloc[i,3] == 'mężczyźni': gender = 1

        if df_results.iloc[i,5] not in (0,'') and df_results.iloc[i,3] not in ('ogółem'):
            values.append((df_results.iloc[i,2],exam_type, exam_level,gender,df_results.iloc[i,4] , df_results.iloc[i,5]  ))
    
    df_final_results = pd.DataFrame(values,columns = ["Przedmiot", "Rodzaj","Poziom", "Plec","Rok","Wynik"])
    df_final_results.to_csv(r'E:\Blogowanie\Python\XLSXdoCSV\Wyniki\matury.csv',sep=';',encoding='utf-16')

    print(str(df_final_results.shape[0]) + ' lines exported')