Sneeze Tracking: Year Two

[ adhoc , sneezes ]

Reports through my first two years of sneeze tracking.

Reports


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

date sneezes
2017-06-24 6
Several Tied 4

back to top

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

input prep

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):

# 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

Install Tools

python3 setup

brew update
brew install q
brew install jq
brew install tphummel/util/date-range
brew install tphummel/util/streak

brew install [email protected]
# 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

add header row, enrich date format, filtered time window, filtered event type. save to intermediate file.

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

sneezes by day. save to intermediate file

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/'