Sneeze Tracking: Year One

home / report / adhoc / sneezes-year-one
[ adhoc , sneezes ]

Reports from my first year of sneeze tracking.

Reports

Sneezes By Month

year month sneezes
2017 4 29
2017 5 76
2017 6 61
2017 7 69
2017 8 73
2017 9 65
2017 10 82
2017 11 103
2017 12 74
2018 1 114
2018 2 57
2018 3 76
2018 4 48
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 1 69
2018 2 11
2018 3 15
2018 4 16
2018 5 16
2018 6 22
2018 7 7
2018 8 9
2018 9 10
2018 10 12
2018 11 36
2018 12 12
2018 13 13
2018 14 9
2018 15 20
2018 16 18
back to top

Sneezes by Day of Week

day sneezes per day sneezes days
Sunday 2.80 126 45
Monday 2.71 122 45
Tuesday 2.60 122 47
Wednesday 2.48 99 40
Thursday 3.93 165 42
Friday 2.83 130 46
Saturday 3.33 163 49
back to top

Most Sneezes in a Single Day

date sneezes
2018-01-04 42
2017-04-24 12
2018-01-03 9
2018-03-16 9
2017-05-19 8
2017-09-12 8
back to top

Most Sneezes in a Single Sunday

date sneezes
2017-09-17 7
2017-11-05 7
2017-12-10 7
2017-04-23 6
2018-04-15 6
back to top

Most Sneezes in a Single Monday

date sneezes
2017-04-24 12
2017-08-21 6
2017-11-27 6
Several Tied 5
back to top

Most Sneezes in a Single Tuesday

date sneezes
2017-09-12 8
2017-10-10 5
2017-11-14 5
2018-01-02 5
2018-02-06 5
back to top

Most Sneezes in a Single Wednesday

date sneezes
2018-01-03 9
2018-03-14 6
Several Tied 5
back to top

Most Sneezes in a Single Thursday

date sneezes
2018-01-04 42
2018-03-15 7
2017-09-07 6
2017-11-16 6
Several Tied 5
back to top

Most Sneezes in a Single Friday

date sneezes
2018-03-16 9
2017-05-19 8
2017-10-27 6
2017-12-08 6
Three Tied 5
back to top

Most Sneezes in a Single Saturday

date sneezes
2017-05-20 7
2017-08-19 7
Four Tied 6
back to top

Most Sneezes in a Single Day, January

date sneezes
2018-01-04 42
2018-01-03 9
2018-01-02 5
2018-01-05 5
2018-01-10 5
2018-01-27 5
2018-01-01 4
2018-01-18 4
back to top

Most Sneezes in a Single Day, February

date sneezes
2018-02-10 6
2018-02-03 5
2018-02-06 5
2018-02-04 4
2018-02-08 4
2018-02-11 3
2018-02-27 3
2018-02-01 2
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
2018-03-18 4
2018-03-19 4
2018-03-04 3
2018-03-05 3
back to top

Most Sneezes in a Single Day, April

date sneezes
2017-04-24 12
2017-04-23 6
2018-04-15 6
2018-04-19 5
2018-04-12 4
2018-04-13 4
2018-04-14 4
2018-04-21 4
back to top

Most Sneezes in a Single Day, May

date sneezes
2017-05-19 8
2017-05-20 7
2017-05-06 5
2017-05-12 5
2017-05-22 5
2017-05-02 4
2017-05-21 4
2017-05-09 3
back to top

Most Sneezes in a Single Day, June

date sneezes
2017-06-24 6
2017-06-03 4
2017-06-08 4
2017-06-17 4
2017-06-19 4
2017-06-21 3
2017-06-23 3
2017-06-25 3
back to top

Most Sneezes in a Single Day, July

date sneezes
2017-07-03 5
2017-07-27 5
2017-07-06 4
2017-07-15 4
2017-07-18 4
2017-07-19 4
2017-07-25 4
2017-07-01 3
back to top

Most Sneezes in a Single Day, August

date sneezes
2017-08-19 7
2017-08-21 6
2017-08-17 5
2017-08-05 4
2017-08-14 4
2017-08-26 4
2017-08-04 3
2017-08-11 3
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
2017-09-08 4
2017-09-24 4
2017-09-01 3
2017-09-05 3
back to top

Most Sneezes in a Single Day, October

date sneezes
2017-10-27 6
2017-10-10 5
2017-10-23 5
2017-10-03 4
2017-10-13 4
2017-10-14 4
2017-10-18 4
2017-10-22 4
back to top

Most Sneezes in a Single Day, November

date sneezes
2017-11-05 7
2017-11-04 6
2017-11-16 6
2017-11-27 6
2017-11-06 5
2017-11-13 5
2017-11-14 5
2017-11-15 5
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
2017-12-07 4
2017-12-16 3
2017-12-21 3
2017-12-22 3
back to top

Most Sneezes in a Single Hour (pacific time)

date hour sneezes
2018-01-04 7 7
2018-01-04 8 5
2018-01-04 14 5
2018-01-03 20 4
2018-01-04 12 4
2018-01-04 17 4
back to top

Most Consecutive Days with 1+ Sneeze

start end streak
2017-06-08 2017-07-04 27
2017-10-06 2017-10-31 26
2017-12-21 2018-01-08 19
2017-11-02 2017-11-19 18
2018-01-26 2018-02-12 18
2018-03-04 2018-03-21 18
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
back to top

Most Sneezes in a Five-Day Span

start end sneezes
2018-01-01 2018-01-05 65
2018-01-02 2018-01-06 64
2017-12-31 2018-01-04 63
2018-01-03 2018-01-07 60
2018-01-04 2018-01-08 52
back to top

Appendix

input prep

the 2018 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 first-year-of-sneezes
mkdir -p workspace/sneezes-year-one
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

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

echo "timestamp,type,lat,lon" > year-one-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")" where c2 = 'sneeze' and timestamp >= '2017-04-23' and timestamp <= '2018-04-22' order by timestamp" >> year-one-sneezes.csv

query all rows via column names

q -H -d "," "select timestamp,type,lat,lon from ./year-one-sneezes.csv"

total sneeze count

q -H -d "," "select count(*) from ./year-one-sneezes.csv"

sneezes by year, month

q -H -d "," "select strftime('%Y', timestamp) as year, strftime('%m', timestamp) as month, count(*) from ./year-one-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-one-sneezes.csv group by year, week" | (echo 'year,week,count' && cat) | csvlook

sneezes by day. save to intermediate file

echo "date,count" > year-one-sneezes-daily.csv && q -H -d "," "select date(timestamp) as day, count(*) from ./year-one-sneezes.csv group by day" >> year-one-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-one-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-one-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-one-sneezes-daily.csv where strftime('%w', date) = '$i' order by count desc" | (echo 'date,sneezes' && cat) | csvlook | 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-one-sneezes-daily.csv where strftime('%m', date) = '$i' order by count desc" | (echo 'date,sneezes' && cat) | csvlook | 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-one-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 2018-04-22 | jq -r '.[]' | (echo "date" && cat) | csvjoin -c "1" --outer ./year-one-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-one-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-one-sneezes-daily.csv a JOIN ./year-one-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/'

Updated At: 2024-04-13 15:23 +0000 (b59f114)

🛠 by Tom Hummel