Folien zum Data Science Meetup vom 29.01.2016

Hier meine Folien zum Data Science Meetup vom 29.01.2016.

Pandas (PDF)

Zeilen kombinieren mit pandas

Vor einiger Zeit hatte ich eine Excel-Datei zu bearbeiten, in der in einer Spalte die Spaltennamen, in einer anderen die korrespondieren Werte standen. Immer drei Zeilen bildeten den eigentlichen Datensatz. Mit wenigen Zeilen Pandas und cleverer Adressierung der Ergebnis-Zelle.

Spaltenname Wert
ColA Andi
ColB Berni
ColC Cesar
ColA Dorian
ColB Ernest
ColC Frank

 

import pandas as pd
 
# Lade die Daten
daten = pd.read_excel('combine.xlsx')
# Erstelle leeren Dataframe mit den Spaltennamen aus den Excelzeilen 
verarbeitet = pd.DataFrame(columns=['ColA','ColB','ColC'])
 
# Iteriere über die Daten
for i, row in daten.iterrows():
    # ganzzahliges Teilen, um die Zeile zu bestimmen
    # in die die Zelle gehört, Spalte ergibt sich aus dem Wert in 'Spalte'
    verarbeitet.loc[i // 3,row['Spalte']] = row['Wert']
 
print(verarbeitet)
ColA ColB ColC
0 Andi Berni Cesar
1 Dorian Ernest Frank

Nachtrag: Stephan vom Kölner Data Science Meetup hat mir noch einen alternativen Weg gezeigt:

import pandas as pd
 
data = {'A': ["cola", "colb", "colc", "cola", "colb", "colc"], "B": [1, 2, 3, 4, 5, 6]}
data = pd.DataFrame(data)
gb = data.groupby('A')
res = pd.DataFrame()
for key in gb.groups:
    res[key] = gb.get_group(key)['B'].values.flatten()
 
print(res)

Spendenbescheinigungen erstellen mit LaTeX und pandas

Nach Buchungen auswerten mit Python Pandas hier der zweite Artikel zum Thema pandas.

Ausgangspunkt bilden die Formulare der Finanzdirektion, die ich in LaTeX umgesetzt habe; ihr findet sie unter https://github.com/UweZiegenhagen/spendenquittungen-mit-latex.

Wenn man dieses Formular mit entsprechenden Jinja2 Variablen anreichert (dazu später mehr), erhält man Sammelbestaetigung_Geldzuwendung (PDF) (TeX Code)

Die Aufgabe, die sich jetzt für das automatische Befüllen mittels pandas stellt, ist die folgende:

  1. Lies die Stammdaten ein und bereite sie auf
  2. Lies die Buchungen (aus Quicken 2015) ein und bereite sie auf
  3. Befüll das entsprechende LaTeX-Template
  4. Erzeuge die fertige PDF Datei

Hier der Quellcode nebst Erläuterung:

  • jinja2 nutzen wir als Template-Engine, os für die pdflatex-Aufrufe, codecs für ein wenig UTF8-Gewusel
  • cleanPLZ und prepareAddress sind einfache Hilfsfunktionen. Die erste reinigt die PLZ (pandas nimmt an, das hier Floats drinstehen), die zweite erzeugt den Adressstring für die Ausgabe
  • kardinal erzeugt das Zahlwort, siehe dazu den verlinkten Beitrag vom Finanzamt.
  • Richtig los geht es ab „##################### Prepare Stammdaten #####################“:
    • Ich lade die Stammdaten in einen Dataframe und ersetzte fehlende Werte durch “
    • Die PLZ wird bereinigt (geht sicher auch einfacher), ehemalige Mitglieder (mit Status ‚E‘) werden entfernt
    • Die Buchungen werden geladen, fehlende Werte durch 0 bzw. “ ersetzt.
    • Relevant sind nur Buchungstypen wie Mitgliedsbeitrag und Aufnahmegebühr, alles andere wird entfernt.
    • Die Jinja2 Komponente wird konfiguriert
    • Für jeden Stammdatensatz werden die Stammdaten aufbereitet und die Buchungen eingesammelt.
    • Ein wenig Python/pandas Magie bereitet die daten auf und schreibt alles in das Template
    • Dieses Template wird dann in eine TeX Datei geschrieben und nach PDF übersetzt.

Hier der Link zur Zip-Datei mit allen benötigten Dateien: AlleDateien

import pandas as pd # pandas selbst
 
import jinja2
import os
import codecs
 
def cleanPLZ(stringToClean):
    return stringToClean.replace('.0','')
 
# Eine Funktion, die die Adresse vorbereitet
# keine überflüssigen Leerzeichen, wenn Feld nicht gefüllt ist
def prepareAddress(id, vorname, name, strasse, plz, ort):
    address = '' # + str(id) + ': '
    if len(vorname)==0:
        address = address + name
    else:
        address = address + vorname + ' ' + name
    if len(strasse)>0:
        address = address + ", " + strasse    
    if len(plz)>0:
        address = address + ", " + plz + ' ' + ort
    return address
 
# Zerlege die Gesamtsumme in einzelne Bestandteile, um Zahlwort auszugeben
# Siehe http://www.steuer-schutzbrief.de/fileadmin/downloads/BMF-Schreiben/BMF-Schreiben-Zuwendungsbestaetigung-2012-08-30.pdf
def kardinal(summenstring,separator,indicator):
	zahlen = {"1" : "Eins", "2":"Zwei", "3":"Drei", "4":"Vier","5":"Fünf","6":"Sechs","7":"Sieben","8":"Acht","9":"Neun","0":"Null"}
	zahlwort = ''
	zahl = summenstring.split(',')[0]
	for i in zahl:
		zahlwort = zahlwort + zahlen[i]+ separator
	return indicator + separator + zahlwort + indicator
 
# Diverse Konfigurationsvariablen
# http://chrisalbon.com/python/pandas_list_unique_values_in_column.html
# Set ipython's max row display
pd.set_option('display.max_row', 10000)
# Set iPython's max column width to 50
pd.set_option('display.max_columns', 50)
# A set number format to 2 digits
pd.set_option('display.float_format', lambda x: '%.2f' % x)
# http://stackoverflow.com/questions/20625582/how-to-deal-with-this-pandas-warning
# komische Fehlermeldung beim Drop von Spalten loswerden
pd.options.mode.chained_assignment = None  # default='warn'
 
##################### Prepare Stammdaten ##################### 
# lade Daten
stammdaten = pd.read_excel('Stammdaten.xlsx', 'Tabelle1')
 
# Remove NaN values by " for strings
stammdaten['Vorname'].fillna(value='',inplace=True)
stammdaten['Name'].fillna(value='',inplace=True)
stammdaten['Adresszusatz'].fillna(value='',inplace=True)
stammdaten['Strasse'].fillna(value='',inplace=True)
stammdaten['PLZ'].fillna(value='',inplace=True)
stammdaten['Ort'].fillna(value='',inplace=True)
stammdaten['eMail'].fillna(value='',inplace=True)
stammdaten['Mitgliedsart'].fillna(value='',inplace=True)
 
# convert PLZ to string
# apply str function first, then run cleanPLZ on the string
stammdaten['PLZ']= stammdaten.PLZ.apply(str)
stammdaten['PLZ']= stammdaten.PLZ.apply(cleanPLZ)
 
# entferne ehemalige Mitglieder
stammdaten  = stammdaten[stammdaten.Mitgliedsart != 'E']
 
################################ Prepare Buchungen
# lade die Buchungen
buchungen = pd.read_excel('Buchungen.xlsx', 'Tabelle1')
buchungen[['Klasse']] = buchungen[['Klasse']].astype(str)
buchungen[['Betrag']] = buchungen[['Betrag']].astype(float)
 
# Change format of 'Buchungstag' to datetime
buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True)
 
# Remove NaN values by " for strings or 0 for numbers 
buchungen['Vorgang'].fillna(value=0,inplace=True)
buchungen['Empfänger'].fillna(value='',inplace=True)
buchungen['Verwendungszweck'].fillna(value='',inplace=True)
buchungen['Kategorie'].fillna(value='',inplace=True)
buchungen['Klasse'].fillna(value='',inplace=True)
buchungen['Relevant']=True
buchungen.Kategorie.str.match('^Aufnahmegebühr|Zweckspende|Mitgliedsbeitrag|Spende$')
 
# entferne irrelevante Buchungen
buchungen = buchungen[buchungen.Relevant != False]
 
# http://stackoverflow.com/questions/20937538/how-to-display-pandas-dataframe-using-a-format-string-for-columns
#pd.options.display.float_format = '{:,.2f} EUR'.format
 
class CommaFloatFormatter:
    def __mod__(self, x):
        return str(x).replace('.',',')
 
latex_jinja_env = jinja2.Environment(
    block_start_string = '\BLOCK{',
    block_end_string = '}',
    variable_start_string = '\VAR{',
    variable_end_string = '}',
    comment_start_string = '\#{',
    comment_end_string = '}',
    line_statement_prefix = '%-',
    line_comment_prefix = '%#',
    trim_blocks = True,
    autoescape = False,
    loader = jinja2.FileSystemLoader(os.path.abspath('.'))
)
 
# Laden des Templates aus einer Datei
template = latex_jinja_env.get_template('Sammelbestaetigung_Geldzuwendung.tex')
 
for index, row in stammdaten.iterrows():
    print("ID:",row["ID"])
    address = prepareAddress(row["ID"],row['Vorname'],row['Name'],row['Strasse'],row['PLZ'],row['Ort'])
    print(address)
    beitraege = buchungen[buchungen.Klasse.str.contains('^' +  str(row["ID"]) + '$')]
    beitraege.drop('Klasse',axis=1,inplace=True)
    beitraege.drop('Verwendungszweck',axis=1,inplace=True)
    beitraege.drop('Relevant',axis=1,inplace=True)
    beitraege.drop('Empfänger',axis=1,inplace=True)
    beitraege.drop('Konto',axis=1,inplace=True)
    beitraege.drop('Vorgang',axis=1,inplace=True)
    gesamtsumme = beitraege.sum()[0]
 
    beitraege['Buchungstag'] = beitraege['Buchungstag'].apply(lambda x: x.strftime('%d-%m-%Y'))
    texbuchungen = beitraege.applymap(lambda x: str(x).replace('.',',0')).to_latex(index=False)    
    texbuchungen = beitraege.to_latex(index=False)    
    summe = str(gesamtsumme).replace('.',',0') + ' EUR'
    # kardinal(summe,'-','xxx')
    dokument = template.render(Spender=address, ID=row['ID'],Summe=summe,kardinal=kardinal(summe,'-','xxx'),Buchungen=texbuchungen)
    with codecs.open(''+str(row['ID']) + ".tex", "w","utf-8") as letter:
        letter.write(dokument);
        letter.close();
        os.system("pdflatex -interaction=batchmode " + str(row['ID']) + ".tex")
 
os.system("del *.log")
os.system("del *.aux")

Buchungen auswerten mit Python Pandas

Ich bin ehrenamtlich Schatzmeister der Dingfabrik Köln e.V., als solcher muss ich auch schauen, welches Mitglied denn die Beiträge bezahlt hat. Mit Hilfe von Pandas lassen sich die Buchungsdaten sehr elegant aufbereiten.

Ausgangspunkt sind die folgenden Excel-Dateien

Mitglieder.xlsx

Enthält die Mitgliedsnummer, den Namen sowie die Mitgliedsart (F für Fördermitglied, O für Ordentlich, E für Ehemalig, etc.)

Klasse Name Mitgliedsart
1 Max Mustermann O

Buchungen.xlsx

Die Buchungen wurden aus Quicken 2015 nach Excel exportiert, die Datei muss leider noch manuell bearbeitet werden, da der Excel-Export von Quicken nicht sonderlich schön ist. Die relevante Kategorie ist in diesem Beispiel „Mitgliedsbeitrag“, Klasse enthält die zugewiesene Mitgliedsnummer.

Buchungstag Konto Vorgang Empfänger Verwendungszweck Kategorie Klasse Betrag
04.01.2016 Firmengirokonto Köln 3763   Buchungstext bla, bla Mitgliedsbeitrag 1 23,00

Diese beiden Dateien können wir jetzt mit Pandas verarbeiten.

import pandas as pd
import numpy as np
import time as t
 
# Anpassungen an der Pandas-Ausgabe
pd.set_option('display.float_format', lambda x: '%.2f' % x)
# http://stackoverflow.com/questions/11707586/python-pandas-widen-output-display
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
 
# lies die Stammdaten aus Excel
mitglieder = pd.read_excel('Mitglieder.xlsx', 'Tabelle1')
# konvertier die Mitgliedsnummer in einen String
mitglieder['Klasse'] = mitglieder['Klasse'].apply(int).apply(str)
# Ehemalige Mitglieder interessieren nicht
mitglieder = mitglieder[mitglieder.Mitgliedsart.str.contains('A|F|O')]
 
# lies die Buchungen ein
buchungen = pd.read_excel('Buchungen.xlsx', 'Sheet')
# entferne alle Zeilen, die keinen 'Mitgliedbeitrag' enthalten
buchungen = buchungen[buchungen.Kategorie.str.contains('Mitgliedsbeitrag')]
 
# entferne Zeilen ohne Betrag oder Klasse
buchungen = buchungen[np.isfinite(buchungen['Betrag'])]
buchungen = buchungen[np.isfinite(buchungen['Klasse'])]
 
# Konvertiere den Buchungstag in ein Pandas-Datum
buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True,format='%d.%m.%Y') 
 
# Füge neue Spalten für Quartal und Monat hinzu
buchungen['Quartal'] = buchungen['Buchungstag'].dt.quarter
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Wandle 'Klasse' von Float => Integer => String 
buchungen['Klasse'] = buchungen['Klasse'].apply(int).apply(str) 
 
# Führe einen right join durch, wir wollen auch die Datensätze von Mitgliedern haben, die noch aktiv sind, aber noch nichts bezahlt haben
buchungen = pd.merge(buchungen,mitglieder, how='right', on=['Klasse', 'Klasse'])
 
# Durch den right Join haben wir jetzt einige NaN (Not a Number) Zellen, die wir durch 0.0 ersetzen
# Dadurch erzeugen wir Dummy Datensätze, die aber nicht stören
buchungen['Betrag'].fillna(value=0,inplace=True)
buchungen['Buchungstag'].fillna(value=pd.Timestamp('20160101'),inplace=True)
# Einen Eintrag für die Monatsspalte nehmen wir auch vor, darüber wird pivotisiert
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Erstellung der Pivot-Table
pivotTable = pd.pivot_table(buchungen,dropna=False,margins=True,index=['Name'],values=['Betrag'],columns=['Monat'],aggfunc=np.sum)
 
print(pivotTable)
 
# Optional: Rausschreiben der Tabelle nach Excel
#pivotTable.to_excel('pivotisiert.xlsx')

Das Ergebnis, hier anonymisiert und bei Betrag nur jeweils 1.0 eingetragen, sieht dann so aus:

pivot

Excel und Python Win32COM

Hier ein kleines Beispiel, wie man mittels COM eine Excel-Mappe aus Python heraus befüllt und die Excel-Instanz beendet.

from win32com import client
 
# http://stackoverflow.com/questions/10221150/cant-close-excel-completely-using-win32com-on-python
def close_excel_by_force(excel):
    import win32process
    import win32gui
    import win32api
    import win32con
    import time
 
    # Get the window's process id's
    hwnd = excel.Hwnd
    t, p = win32process.GetWindowThreadProcessId(hwnd)
    # Ask window nicely to close
    win32gui.PostMessage(hwnd, win32con.WM_CLOSE, 0, 0)
    # Allow some time for app to close
    time.sleep(10)
    # If the application didn't close, force close
    try:
        handle = win32api.OpenProcess(win32con.PROCESS_TERMINATE, 0, p)
        if handle:
            win32api.TerminateProcess(handle, 0)
            win32api.CloseHandle(handle)
    except:
        pass
 
# Öffne Referenz zu Excel
excel = client.Dispatch("Excel.Application")
 
# Wir wollen sehen, was geschieht
excel.Visible= True
 
# Öffne eine bestehende Arbeitsmappe (im selben Verzeichnis)
wb = excel.Workbooks.Open('C:/Users/john/doe/win32test.xlsx')
 
# Wähle das erste Arbeitsblatt aus
ws = wb.Worksheets(1)
 
# Wähle alle Zellen aus
excel.Cells.Select()
# Entferne den Zellschutz
excel.Selection.Locked = False
# Wähle die Spalten B bis E aus
excel.Range("B:E").Select()
# setze den Zellschutz
excel.Selection.Locked = True
 
# Schütze das Sheet mit einem Passwort
ws.Protect("password", True, True)
 
# Speichere ab
wb.Save()
 
# Schließe die Mappe
wb.Close()
 
# Close Excel
excel.Quit()
wb = None
wbs = None
 
close_excel_by_force(excel)

EPUBs erstellen mit tex4ebook

Es gibt ein relativ neues LaTeX-Paket zum Erzeugen von EPUBs aus LaTeX heraus, hier ein kurzes Beispiel:

\documentclass[ngerman]{article}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{babel}
\usepackage{csquotes}
\usepackage{xcolor}
 
\title{Testdokument für tex4ebook}
\author{Uwe Ziegenhagen}
 
\begin{document}
\maketitle
\tableofcontents
 
\section{Hallo}
 
Hallo Welt
 
\section{Welt}
 
\end{document}

In diesem einfachen Beispiel hat das generierte EPUB noch Probleme:

  • Umlaute sind kaputt
  • Das Inhaltsverzeichnis befindet sich hinter dem eigentlichen Text

In den nächsten Artikeln werde ich schauen, was mit dem Paket geht und wo seine Grenzen liegen.

Org-Mode export and upload from the command line

The following is a little snippet to export an Emacs org mode file to HTML and to upload it to the server via FTP. Remark: It’s better to use scp for the upload!

G:\Programme\emacs-24.3\bin\emacs.exe --batch -l "C:\Users\Uwe\.emacs" --visit "nameoftheorgfile.org" --funcall org-html-export-to-html

@echo off
echo user Myusername> ftpcmd.dat
echo MyPassword>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo put nameoftheorgfile.html>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat servername
del ftpcmd.dat

SVN auf dem Synology

Bislang habe ich Subversion immer auf einem externen Virtual-Root Server (1und1) betrieben, da ich aber auch ein Synology NAS habe, kann ich dies auch zuhause erledigen.

Zur Datensicherung der Repositories nutze ich ein Shellskript, das die jeweils aktuellste Version eines Repos in eine Datei dumpt, die dann über Amazon S3 in der Cloud gesichert wird.

Der SVN-Server wurde über den App-Store des NAS heruntergeladen und installiert

SVN Server

Im Aufgabenplaner wird das Dump-Skript nachts aufgerufen

syno-02

Das Skript dumpt jeweils die HEAD-Version in die entsprechende Datei

NAS> more svnbackupper.sh
#!/bin/ash
for i in $( ls /volume2/svn/); do
 svnadmin dump /volume2/svn/$i -rHEAD > /volume1/backups/synosvnbackups/$i.dump
 done

Emacs: Encoding resetten

In der letzten Zeit hatte ich öfter das Problem, dass Emacs meine org-mode Datei mit dem falschen Encoding öffnet und alle Umlaute kaputt sind. Die Lösung findet sich auf Superuser:

M-x revert-buffer-with-coding-system

M-x set-buffer-file-coding-system

Ergänzung: Ich habe meine .emacs Datei wie folgt erweitert:

;; UTF-8 as default encoding
(set-language-environment "UTF-8")

Satzspiegel konstruieren mit Geogebra

Hier ein Beispiel, wie man mit Geogebra Satzspiegel entwerfen kann.

Satzspiegel

satz