OE1IAH.at

Logfile Auswertung mit Excel

Es gibt überaus viele Programme zum Analysieren von ADIF oder CSV Infos aus Logdateien. Viele davon sind relativ unflexibel oder kosten doch einiges an Geld. Ähnliches gibt für die Onlinedienste mancher WEB Seiten. Viele Funk Amateure haben PCs ohnehin zur Verfügung daher die Idee diese gleicht dafür zu benutzen.

Ich verwende dazu diverse Komponenten aus dem Microsoft Office Bereich. Die Kernkomponente dafür ist Excel. Für den Beginn zeige ich hier Möglichkeiten ohne etwas zu Programmieren. es reicht wenn man mit der Maus klicken kann.

Die Kernkomponente im Excel sind die Pivot Tabellen. Das ist eine Standardfunktion mit der man sehr einfach und flexibel große Datenmengen Filtern Gruppieren und verdichten kann. Die Daten selbst können eine Excel Tabelle, eine externe Datei oder Datenbank sein. Hier werde ich der Einfachheit halber die Daten in das Excel importieren.

Ornitologen stehen mit Libre Office ebenfalls solche Werkzeuge zur Verfügung. Die Besprechung hier erfolgt auf Basis von Excel weil es einfach mehr Verbreitung hat und bei den komplexeren Funktionen, die ich auch nutze, wesentlich mehr kann.

Log Analyses via Excel

There are several programs available to analyze ADIF or CSV files. Most of them are inflexible and may cost some money as well. Similar to some online services. Most HAMs have personal computers, so I want to introduce the idea to use existing tools to run over the files.

I use some components out of the Microsoft Office arsenal to analyze the log. The core component I will use is Excel. To start with it I will not use any programming, just point and click as every user is able to do.

There is one core component which might not be so common for many users it is the Pivot Table. This is a standard tool which allows to summarize, group and filter large number of data. The source of data might be a Excel Table or external sources like other files or databases. To keep the things simple I will import the log data into Excel to avoid unnecessary complexity.

Ornithologist have with Libre Office a similar solution in Calc. I discuss here only Excel as it is much more popular in the field. The more complex solutions, which I intend to present as well, are only available in Excel.

Import der Daten

Die einfachste Methode ist eine CVS oder TXT Datei einzulesen. Diese Datei Formate sind Tabellen deren Elemente durch ein Trennzeichen wie "," ";" oder <TAB> geteilt werden. Üblicherweise sollte es ausreichen die Datei mit Excel zu öffnen um eine Tabelle zu erhalten. Es gibt ein häufiges Problem mit dem Trennzeichen bei CSV Dateien. Gute Programme lesen das Trennzeichen aus den Sparacheinstellungen des PC aus. Funktioniert das nicht dann kann man mit einem Texteditor den Trenner mittels globalem Ersetzen austauschen. Die TXT Dateien haben ein <TAB> als Trenner da gibt es das Problem nicht.

Ich habe die CSV Datei mittels Export aus meinem Logging Programm HAM Office erzeugt. Die bekannten ADIF Dateien sind in einem XML Format, das kann man mit Excel AddOns auch öffnen, will ich hier aber nicht beschreiben. Falls das Logging-Programm keinen Export kann gibt es Online Services die ADIF in andere Formate wandeln können.

 

Importing Data

The easiest way is to use a CSV File or TXT File. These file formats have a table of values each element separated by a "," ";" or TAB. Usually it is enough to open the file with Excel. There is one common issue on CSV is the delimiter a "," or a ";" this depends on local file settings. Usually when you export on a PC a well written program will use that Info from the environment. If that does not match just use an editor like notepad to replace the delimiter character. The TXT Files have a <TAB> as delimiter.

I generated the CSV via a export out of my logging SW Ham Office. The well-known ADIF file format is XML. There are add on's available for Excel to read that. To keep things simple I don't want to describe that. If your logger does not offer an export you may use online services to convert ADIF to CSV or TXT.

Das Ergebnis ist eine große Tabelle die noch recht unübersichtlich ist. Wesentlich ist die erste Zeile in der stehen die Spaltenüberschriften. Diese benötigen wir später zum Auswählen der Felder.

The result is a big table which does not show much. It is important to have header lines. They are necessary to select the data in the next step.

Wählen Sie nun alle Daten aus die Sie analysieren wollen und wählen Sie im Excel Menu Band die Pivot Tabelle aus. Ich empfehle das in einem neuen Tabellenblatt zu erzeugen.

Next select all data you want to analyze. Select PivotTable in the manu band and create a new one. I recommend in a new tab.

Rechts erscheint das Eigenschaften-Fenster in dem man mit ziehen und Fallenlassen die Felder zuordnet. Zum Beispiel die Bänder in das reihen Feld und als "Wertefeld" das Call. Augenblicklich erscheint statt dem Pivot Tabellen Platzhalter die Auswertung, Calls aufsummiert nach Bändern

When the PivotTable is created, you get a properties field on the right side. Just drag and drop the bands into the rows field and the call into the values field. Immediately the dummy Pivot graphic is replaces by a table presenting the summarized calls grouped by bands.

Wenn man dann die verdichteten Daten hat, kann man diese mit wenigen Klicks in eine Grafik verwandeln. Dazu wählt man einfach die Pivot Tabelle aus und klickt im Menu Band auf eines der Grafiksymbole. Es gibt da unzählige Varianten an Grafiken die je nach gesuchter Info eingestellt werden können. Man kann Stunden verschwenden um mit den Möglichkeiten zu spielen.

Now as you have the concentrated data it is easy to generate graphichs to have a look on the info. Just select the pivot table and klick one of the graphics buttons in the menu strip. Threr are various variants, and when you have them you can customize them. You spend easily hours to pay here.

Mit wenigen Mausklicks lässt sich die Grafik noch etwas attraktiver gestalten. Alles nur mit Bordmitteln von Excel da ist nichts mit externen Tools bearbeitet.

With a few mouse clicks the graphic is modifies to look better. Everythin only done with Excel internal tools, there is no graphics editor involved

In der zweiten Grafik sind die Frequenzen umgeordnet. Das geht einfach indem man in der Grafik die Beschriftung unter den Säulen anklickt und im Kontzext menu die Reihenfolge verändert.

The second picture has a resorted list of the frequencies. This is done by klicking on the column labels next right mouse in the menue you can reorder the values.

Auf ähnliche Art kann man auch mehrere Dinge verknüpfen wie Monate und Wochentag. Hier wurde aber ein kleiner Trick angewendet, im Excel habe ich eine weitere Spalte gemacht in der der Wochentag ermittelt wird. Das geschieht indem das Datum mit der Formel "=WEEKDAY(B2)" in deutscher Umgebung lautet der Formelbegriff "=WOCHENTAG(B2)" in einen Wochentag umgewandelt wird und in dieser Hilfsspalte abgelegt wird. B2 wäre somit für die 2. Teile. Diese Formel kopiert man dann die Spalte nach unten, excel repariert den Verweis automatisch auf B3, B4 B5 und so weiter.

This allows to combine multiple fields as month and weekday. Here I dis a little trick. I did a extra column. This column has a formula which calculated the weekday based on the date. The formula "=WEEKDAY(B2)" is used for this. The pivot table uses this calculated column. B2 is the reference to the line 2. Just copy that cell formula down, Excel is clever enough to fix the reference so ut will go like B3, B4, B5 downwards.

Das Loch in der Grafik hat folgenden Grund: ich habe mein Call Ende Juni 2019 bekommen, zum Zeitpunkt der Erstellung Anfang Mai 2020 fehlen einfach fast 2 Monate.
Man sieht deutlich die erwartete höhere Aktivität an Wochenenden. Ostermontag ist im April deutlich zu erkennen, da scheine ich ganz fleißig gewesen zu sein, deutliche mehr als an sonstigen Montagen.

The hole in the graphic has the following reason: I got my call end of June 2019. The report was done begin of May 2020 so there are almost 2 months missing.
As you can see on weekends There is more activity than on weekdays. Easter Monday in April is quite visible compared to other mondays.

Noch eine Auswertung Calls pro Land

Another one alls by country

Wenns schlecht lesbar ist hilft immer ein wenig das ganze zu Verschlimmbessern indem man zumindest eine Achse logarithmisch macht. Schaut gleich viel technischer aus und Logarithmen sind für uns Funker ohnehin geläufig. Die kleinen und großen Werte sind so besser erkennbar.

When data is unreadable screwed up it always helps to set one scale to logarithmic at least some times this really helps to read the values. Looks much better now especially small and big values. Logarithmic scales are not so unpopular in HAM radio anyways.

Auswertung der Zertifikatvergabe

Schließlich noch eine Auswertung wann wurden die Zertifikate ausgestellt. Es scheint das Q1 2020 war durch die Lockdown sehr betriebsam am Funk und in Sachen Zertifikate überaus ertragreich

Analysis of issued awards

Analysis about issued certificates. As you can see the Corvic19 lockdown allowed me to word some additional awards during that period