Statistieken uit een whatsapp-groep

Voor een project met een aantal studenten maakten we in het tweede semester van 2016/2017 gebruik van whatsapp voor de onderlinge communicatie. Gedurende dat semester werd er behoorlijk wat heen en weer gewhatsappt, maar toen het project eenmaal voltooid was (het had een behoorlijk strakke deadline in het weekend van 22 juli) droogde die stroom snel op. Omdat ik het jammer zou vinden als al die data verloren zou gaan, besloot ik de hele conversatie naar mezelf te mailen en eens aan een eenvoudige statistische analyse te onderwerpen.

Om de data aan jezelf te mailen kun je gebruik maken van de optie ‘Exporteer chat’ van whatsapp, die je te zien krijgt wanneer je op de gegevens van de whatsapp-groep zelf klikt. Helemaal onderaan, onder de lijst van de deelnemers, zie je deze optie (zie figuur).

De data die je dan opgestuurd krijgt, heeft een eenvoudig formaat: datum en tijd, afzender, en tekst – allemaal gescheiden door een dubbele punt gevolgd door een spate ': '. Op zich zou dit dus eenvoudig in mysql te laden moeten zijn. Hiervoor maakte ik even een tabel chat met drie corresponderende kolommen (genaamd wanneer, wie en wat) en probeerde de data in te laden:

load data local infile 
'/Users/bart/Desktop/wttv/wttv_chat.txt' 
into table chat fields terminated by ': ';

Dit gaf nog wel een zooi errors en warnings, maar belangrijker was dat bij inspectie bleek dat er in het datum-veld dingen als '2024-07-17 17:47:52' terecht waren gekomen. Op de corresponderende regel in de data zelf stond '24-07-17 17:47:52' en uit de context kon ik achterhalen dat dit een bericht betrof dat op 24 juli verstuurd was. Blijkbaar is het datum-formaat dus DD-MM-YY HH:MM:SS.

Het zou natuurlijk een optie zijn geweest om deze data in Excel op te ruimen, maar via deze link op stackoverflow kwam ik er achter dat het ook (eenvoudiger) via mysql zelf kon.

select str_to_date('24-07-17 17:47:52', '%d-%m-%Y %k:%i:%s');
+-------------------------------------------------------+
| str_to_date('24-07-17 17:47:52', '%d-%m-%Y %k:%i:%s') |
+-------------------------------------------------------+
| 2017-07-24 17:47:52                                   |
+-------------------------------------------------------+
1 row in set (0,00 sec)

Dus

load data local infile 
'/Users/bart/Desktop/wttv/wttv_chat.txt' 
into table chat fields terminated by ': ' 
(@var1, wie, wat) set wanneer=str_to_date(@var1, '%d-%m-%Y %k:%i:%s');

Query OK, 1619 rows affected, 271 warnings (0,03 sec)
Records: 1619  Deleted: 0  Skipped: 0  Warnings: 271

Een laatste probleem, wat ook al die foutmeldingen en warnings aan het begin had veroorzaakt, was dat er berichten in de data stonden waar een nieuwe regel in voorkwam; berichten zoals de onderstaande (ik heb de namen van de studenten in deze blog verzonnen):

11-07-17 11:52:34: Henk de Boer: I think it’s really important to see you all this Wednesday at 15:30
We’ll discuss the following:
- Decoration, fencing (Jan)
- Tents! Who has a tent, who needs one?
- What kind of tools does each group needs, how are we going to get them?
- Transportation
- Schedule at WTTV

Het mooiste is om even een scriptje te maken dat over alle regels van het bestand heengaat en dit soort regels tot één bericht samenvoegt: op die manier voorkom je vuile data en hou je de statistieken het meest accuraat. Om dit te bewerkstelligen kunnen eenvoudig kijken of de regel begint met twee cijfers (die van de datum namelijk); als dat niet het geval is, betreft het hoogstwaarschijnlijk een stuk tekst die over de regel is gelopen. Het onderstaande python-scriptje doet precies dat:

buffer = [];
previous_line = '';

with open('wttv_chat.txt') as f:
  for line in f:
    line = line.rstrip()
    if (line[:2].isdigit()):
      print(previous_line + ' '.join(c for c in buffer))
      previous_line = line
      buffer = []

    else:
      buffer.append(line) #buffering lines that belong to the same message

print(line) #otherwise the last line won't be printed.

Het enige probleem is dat er tijdens het festival op een gegeven moment een rooster werd verspreid waarin tijden staan waarop mensen aanwezig moesten zijn. Deze begonnen natuurlijk weer wel met twee cijfers aan het begin van de regel:

20-07-17 12:03:22: Karel de Graaf: VRIJDAG AANWEZIG  14.00 - 1.00
14.00 - 17.00 die-en-die studenten
17.00 - 20.00 andere studenten
20.00 - 22.00 nog een groepje
11.00 - 14.00 enzovoort

Dit gebeurde twee of drie keer, dus ik had het met de hand aan kunnen passen, maar programmatisch is natuurlijk altijd beter. Eenvoudig de check op regel 7 in het python-scriptje uitbreiden met het streepje dat na de timestamp komt:

if (line[:2].isdigit() and line[2]=='-'):

Er kwamen nu bij het laden nog wel een paar waarschuwingen, maar dat bleken opmerkingen te zijn van mensen die aan de groep waren toegevoegd – die konden we dus eenvoudig negeren. Voor de goede orde halen we die (en vergelijkbare) zooi er even uit:

delete from chat where wat is null;

Nu was de data alleszins acceptabel om de statistieken eruit te halen.

STATISTIEKEN
De meest eenvoudige query is natuurlijk om te kijken hoeveel appjes er in totaal zijn verstuurd
Hoeveel appjes zijn er in totaal verstuurd?

mysql> select count(*) from chat;
+----------+
| count(*) |
+----------+
|     3032 |
+----------+
1 row in set (0,00 sec)

Een ander interessant gegeven, dat eveneens makkelijk te achterhalen is, is wie er het meeste appt:

mysql> select count(*) as tot, wie from chat group by wie order by tot desc;

Wie heeft er hoeveel afbeeldingen en hoeveel video’s aan de groep toegevoegd?

mysql> select wie, count(*) from chat where wat like '%afbeelding%' group by wie order by wie;
mysql> select wie, count(*) from chat where wat like '%video%' group by wie order by wie;

Omdat dit een groep zeer gemotiveerde studenten betrof, was ik ook wel nieuwsgierig of je iets van die motivatie terug kon zien in de momenten waarop zoal berichten werden verstuurd. Omdat deze chat maar een half jaar heeft bestaan (ok, hij bestaat nog steeds, maar de analyse gaat over het voorbije half jaar), kon ik eenvoudig een check doen op de weeknummers:

mysql> select week(wanneer) as w, count(*) as total from chat group by w;
+------+-------+
| w    | total |
+------+-------+
|   11 |    12 |
|   13 |    32 |
|   14 |     6 |
|   15 |    24 |
|   16 |    32 |
|   18 |    58 |
|   19 |     4 |
|   20 |    28 |
|   21 |    52 |
|   22 |    12 |
|   23 |   182 |
|   24 |   236 |
|   25 |    16 |
|   26 |    76 |
|   27 |    52 |
|   28 |   776 |
|   29 |  1304 |
|   30 |   130 |
+------+-------+
18 rows in set (0,09 sec)

mysql>

Hier komt wel heel duidelijk naar voren dat week 29 de week is voorafgaand aan de strakke deadline van 22 juli: in deze week zijn verreweg de meeste berichten verstuurd, gevolgd door de week daarvoor. Het is nog interessant om te kijken wat er gebeurde in weken 23 en 24, want daar zijn ook veel meer berichten verstuurd dan gemiddeld (het gemiddelde is 3032/20 = 151 berichten per week, maar als we de uitbijters van week 28 en 29 negeren daalt dat naar zo’n vijftig berichten per week).

Minstens zo interessant is om te kijken naar de dagen waarop berichten worden verstuurd. Hier heb ik even een trucje moeten toepassen om de dagen op een normale (niet-lexicografische) manier gesorteerd te krijgen:

mysql> select dayofweek(wanneer) as d_nr, dayname(wanneer) as dag, count(*) as total from chat group by dag order by d_nr;

En de drukste uren:

mysql> select hour(wanneer) as h, count(*) from chat group by h;

Uit deze data bleek dat er geen enkel uur in het etmaal is waarin er geen bericht is verstuurd: de studenten werkten blijkbaar altijd door.

Maar interessanter is natuurlijk om te zien wie wanneer een berichtje stuurt; daarvoor maakte ik gebruik van de optie om een if-statement in een sum-clause te stoppen, zoals hier beschreven wordt:

mysql>
select hour(wanneer) as h
, sum(if(wie like '%Henk%', 1, 0)) as Henk
, sum(if(wie like '%Karel%', 1, 0)) as Karel
, sum(if(wie like '%Sjaak%', 1, 0)) as Sjaak
, sum(if(wie like '%Margriet%', 1, 0)) as Margriet
from chat
group by h;

Via deze query (althans, de echte versie ervan natuurlijk) konden we mooi achterhalen wie van de groep de nachtbrakers waren en wie de vroege vogels.

Conclusie
Het gebruiken van whatsapp voor onderlinge communicatie werkt heel goed. Het is snel, vertrouwd en makkelijk te gebruiken. Statistieken zoals deze zijn natuurlijk in eerste instantie alleen bedoeld voor de grap, maar wie weet kunnen we het ook eens gaan gebruiken om de inzet van bepaalde studenten te monitoren.

Leave a comment

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