Posts tagged ‘Pandas’

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)

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination. Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

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")

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination. Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

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

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination. Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website