Consistency is the hobgoblin of better researchers than me

March 23, 2020 7:36 PM

Ten days ago, I started taking my temperature a few times a day, entirely at a whim, on the basis that a temperature rise might be my first notice of a COVID infection. I get about 3 readings a day, but the times are random. Just now I entered the data and tried to chart it and encountered an old foe: the basic charting functions of Google Sheets assume a consistent X-axis interval between datapoints.

But they aren't, in reality. The times at which I recorded my temp are all over the place, and the length of interval varies a lot, from 6 to 14 hours. Sheets is just treating the time/date as a label for the next data point, rather than as a point in time. As a result, the nice trendline feature is effing worthless.

I've encountered this before in work-related stuff, but never solved it. How do you chart data recorded over time with inconsistent intervals, if it can be done using Google Sheets or Excel 365? If not them, then some free product? AdTHANXvance!

posted by Sunburnt to Computers & Internet (11 answers total)

Best answer: You want a scatter plot where the x axis is the date/time for each measurement.
posted by janell at 7:38 PM on March 23, 2020 [11 favorites]

In Excel I've managed to force this by creating blank spots for the hours when you didn't take your temp and then leaving those blank. Your labels at the bottom end up pretty messed up, and you want to just do a bar or point graph, no trendline. (Following for a better solution.)
posted by slidell at 9:01 PM on March 23, 2020 [1 favorite]

This may help: explicitly telling Google Sheets to interpret your dates and times as special date/time formatted values. You may need to enter them in a particular way, following one of the examples on that page, so that Google Sheets understands that they are date/times and not just arbitrary strings of characters.
posted by snowmentality at 9:05 PM on March 23, 2020 [2 favorites]

Maybe averaging the measurements for each day would be better, as it would also reduce error from your temperature fluctuations throughout the day. Then each averaged data point would be 1 day apart.
posted by permiechickie at 5:55 AM on March 24, 2020

You want it explicitly as a time-series chart. Snowmentality's link should get you there -- basically, there's a very specific format that will be recognized as a calendar datetime (or which you can force using a bit more complex programming stuff). Get it into that format and your software should graph it so that the x-axis is basically a timeline. I like points connected by lines to tell an overall 'story', but often get more information from points alone.

(I do this for a living, basically. Make time-series plots, I mean, not creep on your daily temperature ;) Let me know if I can help with formatting or setting datatypes, or for that matter I can whip you up a graph in R in like < 5 minutes.)
posted by kalimac at 8:01 AM on March 24, 2020 [1 favorite]

Response by poster: Trying these things today, thanks.
posted by Sunburnt at 9:27 AM on March 24, 2020

Not what you are asking, but...
When i charted my temperature for fertility, one of the important data points was regularity of time in taking measurements in order to be able to compare them. Not sure but it seems to me that if you take the reading at ramdom times it will be difficult to arrive at a conclusion?
posted by 15L06 at 10:45 AM on March 24, 2020

Your body temperature varies by several degrees over the course of the day; this is entirely normal and it's due to our circadian rhythms. It's just like your energy levels and alertness that way.
What you're doing is going to produce meaningless results UNLESS you include time-of-day for that "consistency."
posted by dustpuppy at 12:46 PM on March 24, 2020

Response by poster: I'm only taking my temp as a potential early-warning system; the non-warning data don't really matter to me, nor the trends; just having fun.

I posted this because it's a flippant example of data that has the same problem of something I keep trying to solve at work-- a group of servers that stop synchronizing for about 40 minutes at random times and frequencies. I want to chart the occurrence visually to see if it tells me anything, but never figured out how because of the random interval.
posted by Sunburnt at 1:10 PM on March 24, 2020

Best answer: Yes, this is what the scatterplot function in Sheets (or Excel) is for--you want quantitative variables on both the x and y axis, while the default graph it's making is treating your x-axis as a categorical variable. The defaults in this case are dumb.
posted by hydropsyche at 4:53 PM on March 24, 2020 [1 favorite]

Response by poster: Sheets continued to fail when in Scatterplot mode, and Excel worked perfectly on the first attempt, except, as usual, for the axis labels being a dog's breakfast.

Thanks all.
posted by Sunburnt at 7:51 PM on March 25, 2020 [2 favorites]