neil.mckillop.org

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Project Wifi Scanner: Data Scan 3a ======
Full scan of Feb 1st to April 10th 2020 - are people self isolating for COVID-19?  Seems so.

{{:projects:wifi:feb-apr.png?600|}}

I loaded data in as below.  Then exported the basics to a CSV in MySQL:
<code sql>
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'
</code>

Then use [[gnuplot]] to generate a table of this data:
<code>
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'
</code>

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




==== Original Version (Scan 3) ====

{{:projects:wifi:covid-19-wifi-weekend.png?600|}}

Extract just March's data, then import it to the database:
<code bash>
grep -E "^2020-03" tcpdump.log > /tmp/march.wifi.log
php trim.php /tmp/march.wifi.log > march-trimmed.csv
</code>

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

Then import to the mySQL database:
<code sql>
LOAD DATA INFILE 'march-trimmed.csv' INTO TABLE simple_data;
</code>

==== Basic Stats ====
<code sql>
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';
</code>
|Date|Hits|
|2020-03-01|119994|
|2020-03-02|122174|
|2020-03-03|127180|
|2020-03-04|138274|
|2020-03-05|142102|
|2020-03-06|150793|
|2020-03-07|142417|
|2020-03-08|135554|
|2020-03-09|137481|
|2020-03-10|125954|
|2020-03-11|131901|
|2020-03-12|135391|
|2020-03-13|127303|
|2020-03-14|118234|
|2020-03-15|113126|
|2020-03-16|106237|
|2020-03-17|110425|
|2020-03-18|106013|
|2020-03-19|108127|
|2020-03-20|115171|
|2020-03-21|113031|
|2020-03-22|108512|
|2020-03-23|118570|
|2020-03-24|110394|
|2020-03-25|109092|
|2020-03-26|113263|
|2020-03-27|117891|
|2020-03-28|114374|
|2020-03-29|111808|
|2020-03-30|116348|
|2020-03-31|119926|

<code sql>
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';
</code>
|Date|Unique MACs|
|2020-03-01|9372|
|2020-03-02|12085|
|2020-03-03|14876|
|2020-03-04|17284|
|2020-03-05|19644|
|2020-03-06|21099|
|2020-03-07|15523|
|2020-03-08|12208|
|2020-03-09|15426|
|2020-03-10|13678|
|2020-03-11|13240|
|2020-03-12|13734|
|2020-03-13|13742|
|2020-03-14|8752|
|2020-03-15|7683|
|2020-03-16|9916|
|2020-03-17|8676|
|2020-03-18|7949|
|2020-03-19|8166|
|2020-03-20|8624|
|2020-03-21|5615|
|2020-03-22|4818|
|2020-03-23|7242|
|2020-03-24|4621|
|2020-03-25|4568|
|2020-03-26|4451|
|2020-03-27|4819|
|2020-03-28|3527|
|2020-03-29|3645|
|2020-03-30|4900|
|2020-03-31|4283|
neil.mckillop.org

User Tools

Site Tools

Page Tools
