Sneeze Tracking: Year Two
Reports through my first two years of sneeze tracking.
- Start Date: April 23, 2017
- End Date: April 22, 2019
- Total Sneezes: 1936
Reports
- Totals
- Sneezes in a Single Day
- Sun, Mon, Tue, Wed, Thu, Fri, Sat
- Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
- Sneezes in a Single Hour
- Consecutive Day Streaks:
- Spans:
- Appendix
Sneezes By Month
| year |
month |
sneezes |
| 2017 |
04 |
29 |
| 2017 |
05 |
76 |
| 2017 |
06 |
61 |
| 2017 |
07 |
69 |
| 2017 |
08 |
73 |
| 2017 |
09 |
65 |
| 2017 |
10 |
82 |
| 2017 |
11 |
103 |
| 2017 |
12 |
74 |
| 2018 |
01 |
114 |
| 2018 |
02 |
57 |
| 2018 |
03 |
76 |
| 2018 |
04 |
70 |
| 2018 |
05 |
65 |
| 2018 |
06 |
56 |
| 2018 |
07 |
186 |
| 2018 |
08 |
76 |
| 2018 |
09 |
47 |
| 2018 |
10 |
116 |
| 2018 |
11 |
92 |
| 2018 |
12 |
53 |
| 2019 |
01 |
97 |
| 2019 |
02 |
50 |
| 2019 |
03 |
52 |
| 2019 |
04 |
97 |
| back to top |
|
|
Sneezes By Week
| year |
week |
count |
| 2017 |
16 |
6 |
| 2017 |
17 |
23 |
| 2017 |
18 |
17 |
| 2017 |
19 |
14 |
| 2017 |
20 |
27 |
| 2017 |
21 |
12 |
| 2017 |
22 |
13 |
| 2017 |
23 |
10 |
| 2017 |
24 |
13 |
| 2017 |
25 |
22 |
| 2017 |
26 |
13 |
| 2017 |
27 |
16 |
| 2017 |
28 |
13 |
| 2017 |
29 |
17 |
| 2017 |
30 |
19 |
| 2017 |
31 |
11 |
| 2017 |
32 |
10 |
| 2017 |
33 |
26 |
| 2017 |
34 |
16 |
| 2017 |
35 |
17 |
| 2017 |
36 |
22 |
| 2017 |
37 |
21 |
| 2017 |
38 |
12 |
| 2017 |
39 |
3 |
| 2017 |
40 |
13 |
| 2017 |
41 |
22 |
| 2017 |
42 |
17 |
| 2017 |
43 |
26 |
| 2017 |
44 |
24 |
| 2017 |
45 |
17 |
| 2017 |
46 |
33 |
| 2017 |
47 |
24 |
| 2017 |
48 |
14 |
| 2017 |
49 |
26 |
| 2017 |
50 |
9 |
| 2017 |
51 |
13 |
| 2017 |
52 |
21 |
| 2018 |
01 |
69 |
| 2018 |
02 |
11 |
| 2018 |
03 |
15 |
| 2018 |
04 |
16 |
| 2018 |
05 |
16 |
| 2018 |
06 |
22 |
| 2018 |
07 |
7 |
| 2018 |
08 |
9 |
| 2018 |
09 |
10 |
| 2018 |
10 |
12 |
| 2018 |
11 |
36 |
| 2018 |
12 |
12 |
| 2018 |
13 |
13 |
| 2018 |
14 |
9 |
| 2018 |
15 |
20 |
| 2018 |
16 |
23 |
| 2018 |
17 |
17 |
| 2018 |
18 |
8 |
| 2018 |
19 |
30 |
| 2018 |
20 |
17 |
| 2018 |
21 |
9 |
| 2018 |
22 |
9 |
| 2018 |
23 |
12 |
| 2018 |
24 |
9 |
| 2018 |
25 |
15 |
| 2018 |
26 |
16 |
| 2018 |
27 |
63 |
| 2018 |
28 |
75 |
| 2018 |
29 |
23 |
| 2018 |
30 |
18 |
| 2018 |
31 |
15 |
| 2018 |
32 |
20 |
| 2018 |
33 |
22 |
| 2018 |
34 |
15 |
| 2018 |
35 |
10 |
| 2018 |
36 |
9 |
| 2018 |
37 |
13 |
| 2018 |
38 |
7 |
| 2018 |
39 |
15 |
| 2018 |
40 |
57 |
| 2018 |
41 |
18 |
| 2018 |
42 |
15 |
| 2018 |
43 |
17 |
| 2018 |
44 |
17 |
| 2018 |
45 |
13 |
| 2018 |
46 |
20 |
| 2018 |
47 |
40 |
| 2018 |
48 |
13 |
| 2018 |
49 |
11 |
| 2018 |
50 |
14 |
| 2018 |
51 |
12 |
| 2018 |
52 |
13 |
| 2018 |
53 |
1 |
| 2019 |
00 |
15 |
| 2019 |
01 |
14 |
| 2019 |
02 |
11 |
| 2019 |
03 |
47 |
| 2019 |
04 |
15 |
| 2019 |
05 |
15 |
| 2019 |
06 |
15 |
| 2019 |
07 |
7 |
| 2019 |
08 |
10 |
| 2019 |
09 |
15 |
| 2019 |
10 |
13 |
| 2019 |
11 |
13 |
| 2019 |
12 |
9 |
| 2019 |
13 |
22 |
| 2019 |
14 |
38 |
| 2019 |
15 |
37 |
| back to top |
|
|
Sneezes by Day of Week
| day |
sneezes per day |
sneezes |
days |
| Sunday |
3.07 |
289 |
94 |
| Monday |
3.08 |
268 |
87 |
| Tuesday |
2.87 |
264 |
92 |
| Wednesday |
2.6 |
229 |
88 |
| Thursday |
3.36 |
286 |
85 |
| Friday |
2.89 |
254 |
88 |
| Saturday |
3.53 |
346 |
98 |
back to top
Most Sneezes in a Single Day
| date |
sneezes |
| 2018-07-09 |
43 |
| 2018-01-04 |
42 |
| 2018-10-06 |
19 |
| 2018-07-08 |
18 |
| 2019-01-25 |
18 |
| 2018-07-07 |
17 |
| 2018-07-10 |
17 |
| back to top |
|
Most Sneezes in a Single Sunday
| date |
sneezes |
| 2018-07-08 |
18 |
| 2018-10-07 |
13 |
| 2019-01-27 |
13 |
| 2019-04-14 |
9 |
| 2017-09-17 |
7 |
| 2017-11-05 |
7 |
| 2017-12-10 |
7 |
| back to top |
|
Most Sneezes in a Single Monday
| date |
sneezes |
| 2018-07-09 |
43 |
| 2017-04-24 |
12 |
| 2017-08-21 |
6 |
| 2017-11-27 |
6 |
| back to top |
|
Most Sneezes in a Single Tuesday
| date |
sneezes |
| 2018-07-10 |
17 |
| 2017-09-12 |
8 |
| 2018-04-24 |
7 |
| 2018-08-14 |
7 |
| 2019-04-16 |
7 |
| back to top |
|
Most Sneezes in a Single Wednesday
| date |
sneezes |
| 2018-05-09 |
14 |
| 2018-01-03 |
9 |
| 2018-03-14 |
6 |
| 2018-07-18 |
6 |
| Several Tied |
5 |
| back to top |
|
Most Sneezes in a Single Thursday
| date |
sneezes |
| 2018-01-04 |
42 |
| 2018-10-04 |
12 |
| 2018-03-15 |
7 |
| Several Tied |
6 |
| back to top |
|
Most Sneezes in a Single Friday
| date |
sneezes |
| 2019-01-25 |
18 |
| 2018-07-06 |
13 |
| 2018-03-16 |
9 |
| 2017-05-19 |
8 |
| 2018-10-05 |
8 |
| back to top |
|
Most Sneezes in a Single Saturday
| date |
sneezes |
| 2018-10-06 |
19 |
| 2018-07-07 |
17 |
| 2018-11-24 |
15 |
| 2019-04-13 |
13 |
| back to top |
|
Most Sneezes in a Single Day, January
| date |
sneezes |
| 2018-01-04 |
42 |
| 2019-01-25 |
18 |
| 2019-01-27 |
13 |
| 2018-01-03 |
9 |
| 2019-01-24 |
6 |
| back to top |
|
Most Sneezes in a Single Day, February
| date |
sneezes |
| 2019-02-09 |
7 |
| 2018-02-10 |
6 |
| 2018-02-03 |
5 |
| 2018-02-06 |
5 |
| 2019-02-13 |
5 |
| back to top |
|
Most Sneezes in a Single Day, March
| date |
sneezes |
| 2018-03-16 |
9 |
| 2018-03-15 |
7 |
| 2018-03-14 |
6 |
| 2018-03-17 |
6 |
| 2019-03-09 |
5 |
| 2019-03-13 |
5 |
| back to top |
|
Most Sneezes in a Single Day, April
| date |
sneezes |
| 2019-04-13 |
13 |
| 2017-04-24 |
12 |
| 2019-04-14 |
9 |
| 2018-04-24 |
7 |
| 2019-04-16 |
7 |
| 2019-04-19 |
7 |
| back to top |
|
Most Sneezes in a Single Day, May
| date |
sneezes |
| 2018-05-09 |
14 |
| 2017-05-19 |
8 |
| 2017-05-20 |
7 |
| Several Tied |
5 |
| back to top |
|
Most Sneezes in a Single Day, June
Most Sneezes in a Single Day, July
| date |
sneezes |
| 2018-07-09 |
43 |
| 2018-07-08 |
18 |
| 2018-07-07 |
17 |
| 2018-07-10 |
17 |
| 2018-07-06 |
13 |
| back to top |
|
Most Sneezes in a Single Day, August
| date |
sneezes |
| 2017-08-19 |
7 |
| 2018-08-14 |
7 |
| 2017-08-21 |
6 |
| 2017-08-17 |
5 |
| 2018-08-02 |
5 |
| 2018-08-04 |
5 |
| back to top |
|
Most Sneezes in a Single Day, September
| date |
sneezes |
| 2017-09-12 |
8 |
| 2017-09-17 |
7 |
| 2017-09-07 |
6 |
| 2017-09-06 |
5 |
| back to top |
|
Most Sneezes in a Single Day, October
| date |
sneezes |
| 2018-10-06 |
19 |
| 2018-10-07 |
13 |
| 2018-10-04 |
12 |
| 2018-10-05 |
8 |
| 2017-10-27 |
6 |
| back to top |
|
Most Sneezes in a Single Day, November
| date |
sneezes |
| 2018-11-24 |
15 |
| 2017-11-05 |
7 |
| 2017-11-04 |
6 |
| 2017-11-16 |
6 |
| 2017-11-27 |
6 |
| back to top |
|
Most Sneezes in a Single Day, December
| date |
sneezes |
| 2017-12-10 |
7 |
| 2017-12-08 |
6 |
| 2017-12-09 |
5 |
| 2017-12-27 |
5 |
| Several Tied |
4 |
| back to top |
|
Most Sneezes in a Single Hour (pacific time)
| date |
hour |
sneezes |
| 2018-01-04 |
07 |
7 |
| 2018-07-12 |
06 |
6 |
| 2018-10-04 |
01 |
6 |
| 2018-01-04 |
08 |
5 |
| 2018-01-04 |
14 |
5 |
| 2018-07-09 |
13 |
5 |
| 2018-07-10 |
10 |
5 |
| 2018-10-07 |
08 |
5 |
| back to top |
|
|
Most Consecutive Days with 1+ Sneeze
| start |
end |
streak |
| 2018-06-15 |
2018-07-29 |
45 |
| 2017-06-08 |
2017-07-04 |
27 |
| 2017-10-06 |
2017-10-31 |
26 |
| 2018-08-06 |
2018-08-29 |
24 |
| 2018-11-10 |
2018-12-03 |
24 |
| 2019-03-29 |
2019-04-21 |
24 |
| back to top |
|
|
Most Consecutive Days with 0 Sneezes
| start |
end |
streak |
| 2017-09-27 |
2017-09-29 |
3 |
| 2018-04-08 |
2018-04-10 |
3 |
| 2017-05-27 |
2017-05-28 |
2 |
| 2017-07-31 |
2017-08-01 |
2 |
| 2017-10-04 |
2017-10-05 |
2 |
| 2017-12-17 |
2017-12-18 |
2 |
| 2018-01-24 |
2018-01-25 |
2 |
| 2018-03-22 |
2018-03-23 |
2 |
| back to top |
|
|
Most Sneezes in a Five-Day Span
| start |
end |
sneezes |
| 2018-07-06 |
2018-07-10 |
108 |
| 2018-07-07 |
2018-07-11 |
96 |
| 2018-07-05 |
2018-07-09 |
95 |
| 2018-07-08 |
2018-07-12 |
85 |
| 2018-07-09 |
2018-07-13 |
70 |
| back to top |
|
|
Appendix
the 2019 file was in progress, I copied the current year file from: My Drive/IFTTT/body-functions/body-via-do
downloads from google drive as csv (sheet 1):
My Drive/Annual Data Files/2017/2017-body-via-doMy Drive/Annual Data Files/2018/2018-body-via-do
# pwd my clone of data.tomhummel.com repo
git checkout sneezes-year-two
mkdir -p workspace/sneezes-year-two
cd !$
mv ~/Downloads/2018-body-via-do\ -\ Sheet1.csv ./2018-body-via-do.csv
mv ~/Downloads/2017-body-via-do\ -\ Sheet1.csv ./2017-body-via-do.csv
mv ~/Downloads/body-via-do\ -\ Sheet1.csv ./2019-body-via-do.csv
python3 setup
brew update
brew install q
brew install jq
brew install tphummel/util/date-range
brew install tphummel/util/streak
brew install python@2
# update shebang atop /usr/local/bin/q to use `python2`
mkvirtualenv data
workon data
pip3 install csvkit
(data) ➜ q --version
q version 1.7.4
(data) ➜ jq --version
jq-1.6
(data) ➜ date-range --version
2.1.0
(data) ➜ streak --version
1.0.1
(data) ➜ python --version
Python 3.7.3
(data) ➜ python2 --version
Python 2.7.16
(data) ➜ virtualenv --version
16.4.3
(data) ➜ csvlook --version
csvlook 1.0.4
echo "timestamp,type,lat,lon" > year-two-sneezes.csv && q -d "," "select substr(ltrim(substr(c1, instr(c1, ' '))),5,4)||'-'||substr('00' || CASE substr(c1, 0, instr(c1, ' ')) WHEN 'January' THEN 01 WHEN 'February' THEN 02 WHEN 'March' THEN 03 WHEN 'April' THEN 04 WHEN 'May' THEN 05 WHEN 'June' THEN 06 WHEN 'July' THEN 07 WHEN 'August' THEN 08 WHEN 'September' THEN 09 WHEN 'October' THEN 10 WHEN 'November' THEN 11 WHEN 'December' THEN 12 END, -2)||'-'||substr('00'||substr(ltrim(substr(c1, instr(c1, ' '))),1,2),-2)||'T'||substr('00' || CASE WHEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) = 12 and substr(substr(ltrim(substr(c1, instr(c1, ' '))),13),-2) = 'AM' THEN 0 WHEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) = 12 and substr(substr(ltrim(substr(c1, instr(c1, ' '))),13),-2) = 'PM' THEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) WHEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) >= 1 and cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) < 12 and substr(substr(ltrim(substr(c1, instr(c1, ' '))),13),-2) = 'AM' THEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) WHEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) >= 1 and cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER) < 12 and substr(substr(ltrim(substr(c1, instr(c1, ' '))),13),-2) = 'PM' THEN cast(substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':'), -2) as INTEGER)+12 END, -2)||':'||substr('00'||substr(substr(ltrim(substr(c1, instr(c1, ' '))),13), instr(substr(ltrim(substr(c1, instr(c1, ' '))),13), ':')+1, 2),-2) as timestamp, c2, c3, c4 from "<(q -d "," "select c1,c2,c3,c4 from ./2017-body-via-do.csv UNION select c1,c2,c3,c4 from ./2018-body-via-do.csv UNION select c1,c2,c3,c4 from ./2019-body-via-do.csv")" where c2 = 'sneeze' and timestamp >= '2017-04-23' and timestamp <= '2019-04-22' order by timestamp" >> year-two-sneezes.csv
query all rows via column names
q -H -d "," "select timestamp,type,lat,lon from ./year-two-sneezes.csv"
total sneeze count
q -H -d "," "select count(*) from ./year-two-sneezes.csv"
sneezes by year, month
q -H -d "," "select strftime('%Y', timestamp) as year, strftime('%m', timestamp) as month, count(*) from ./year-two-sneezes.csv group by year, month order by year, month" | (echo 'year,month,sneezes' && cat) | csvlook
sneezes by year, week
q -H -d "," "select strftime('%Y', timestamp) as year, strftime('%W', timestamp) as week, count(*) from ./year-two-sneezes.csv group by year, week" | (echo 'year,week,count' && cat) | csvlook
echo "date,count" > year-two-sneezes-daily.csv && q -H -d "," "select date(timestamp) as day, count(*) from ./year-two-sneezes.csv group by day" >> year-two-sneezes-daily.csv
sneezes summed by day of week (with day count for averaging)
q -H -d "," "select strftime('%w', date) as dow, round(avg(count),2) as sneezes_per_day, sum(count) as sneezes, count(date) as days from ./year-two-sneezes-daily.csv group by dow" | (echo 'day,sneezes per day,sneezes,days' && cat) | csvlook
most sneezes in one day
q -H -d "," "select date, count from ./year-two-sneezes-daily.csv order by count desc limit 10" | (echo 'date,sneezes' && cat) | csvlook
most sneezes in one day (by day of week, 0-6: sun-sat)
for i in $(seq 0 6); do echo "--$i--" && q -H -d "," "select date, count from ./year-two-sneezes-daily.csv where strftime('%w', date) = '$i' order by count desc" | (echo 'date,sneezes' && cat) | head -n 10; done
most sneezes in one day (by month of year, 01-12: jan-dec)
for i in $(seq -f "%02g" 1 12); do echo "--$i--" && q -H -d "," "select date, count from ./year-two-sneezes-daily.csv where strftime('%m', date) = '$i' order by count desc" | (echo 'date,sneezes' && cat) | head -n 10; done
most sneezes in a single hour
q -H -d "," "select date(timestamp) as date, strftime('%H', timestamp) as hour, count(*) as count from ./year-two-sneezes.csv group by date, hour order by count desc" | (echo 'date,hour,sneezes' && cat) | csvlook | head -n 15
most consecutive days with 1+ sneeze
date-range 2017-04-23 2019-04-22 | jq -r '.[]' | (echo "date" && cat) | csvjoin -c "1" --outer ./year-two-sneezes-daily.csv - 2>/dev/null | csvcut -c 3,2 | csvsort -c 1 | q -H -d "," "select date2, ifnull(count, 0) from -" | csvjson -H 2>/dev/null | jq ".[] | [.a, .b]" | jq --slurp . | streak --label 0 --column 1 --min 1 | jq -r ".[] | [.start, .end, .value] | @csv" | csvsort -H -c 3 -r 2>/dev/null | sed '1 s/.*/start,end,streak/' | csvlook | head -n 10
most consecutive days with 0 sneezes
date-range 2017-04-23 2018-04-22 | jq -r '.[]' | (echo "date" && cat) | csvjoin -c "1" --outer ./year-two-sneezes-daily.csv - 2>/dev/null | csvcut -c 3,2 | csvsort -c 1 | q -H -d "," "select date2, ifnull(count, 0) from -"| csvjson -H 2>/dev/null | jq ".[] | [.a, .b]" | jq --slurp . | streak --label 0 --column 1 --max 0 | jq -r ".[] | [.start, .end, .value] | @csv" | csvsort -H -c 3 -r 2>/dev/null | sed '1 s/.*/start,end,streak/' | csvlook | head -n 10
most sneezes in a 5-day span
q -H -d "," "select min(b.date), max(b.date), sum(b.count) as sneezes from ./year-two-sneezes-daily.csv a JOIN ./year-two-sneezes-daily.csv b ON (b.date < date(a.date, '+5 day') and b.date >= a.date) group by a.date order by sneezes desc" | (echo 'start,end,sneezes' && cat) | csvlook | head -n 10
tips
prepend a header row: | (echo "year,month,count" && cat)
overwrite the header row: | sed '1 s/.*/start,end,streak/'