Project Wifi Scanner: Data Scan 3a

Full scan of Feb 1st to April 10th 2020 - are people self isolating for COVID-19? Seems so.

I loaded data in as below. Then exported the basics to a CSV in MySQL:

SELECT DATE(seen_time),COUNT(DISTINCT mac) FROM simple_data WHERE DATE(seen_time) BETWEEN '2020-02-01' AND '2020-04-31' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/feb-apr-plot.csv'

Then use gnuplot to generate a table of this data:

set xdata time
set key off
set timefmt "%Y-%m-%d"
set boxwidth 0.9 relative
set format x "%Y-%m-%d"
set title "Distinct MACs seen"
set style fill solid 1
set label "Pubs are closed" at "2020-03-20", 10000 rotate by 90
plot "/tmp/feb-apr-plot.csv" using 1:2 with boxes lt rgb 'orange'

I had to manually colour the weekends (until I can figure out how to get gnuplot to do that for me.

Extract just March's data, then import it to the database:

grep -E "^2020-03" tcpdump.log > /tmp/march.wifi.log
php trim.php /tmp/march.wifi.log > march-trimmed.csv

Raw data for March was 21Gb, approx 168 million lines.

Then import to the mySQL database:

LOAD DATA INFILE 'march-trimmed.csv' INTO TABLE simple_data;

SELECT DATE(seen_time), COUNT(mac) FROM simple_data WHERE DATE(seen_time) >= '2020-03-01' AND seen_time <= '2020-04-01' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/march-rawdata.csv';

SELECT DATE(seen_time),COUNT(DISTINCT mac) FROM simple_data WHERE DATE(seen_time) BETWEEN '2020-03-01' AND '2020-03-31' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/distinct-march.csv';