Differences

This shows you the differences between two versions of the page.

--- projects:wifi:scan_3 [2020/04/01 04:54]
admin created
+++ projects:wifi:scan_3 [2020/04/16 00:09] (current)
neil
@@ Line 1: / Line 1: @@
-====== Project Wifi Scanner: Data Scan 3 ======
+====== Project Wifi Scanner: Data Scan 3a ======
-Full scan of March 2020 - are people self isolating for COVID-19?
+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:
@@ Line 12: / Line 41: @@
 Then import to the mySQL database:
 <code sql>
-LOAD DATA INFILE 'march-trimmed.csv' INTO TABLE wifi_data;
+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

Differences

Page Tools