I'm trying to solve a medical mystery using data visualization. But I'm not very experienced in handling statistical data. My data does not have rows for every day of the year, only for dates which have a > 0 value. How can I avoid having to manually create the "empty" dates?

I have two years of data as a CSV file. The two columns are DATE and OCCURRENCES. Here are the first 10 rows:


Notice that there are no rows for dates which had 0 occurrences. Therefor, when I create a time-series chart in a spreadsheet program (Mac Numbers), the visualization does not show the empty days between occurrences.

time series chart with no zero-value dates

I'm trying to visualize the data to see how and when the occurrences cluster together, and if there is a recurring pattern to the clusters. Before I go writing some Python script to add in the missing dates and populate them with '0', can someone tell me a smarter, better way of doing this? Thank you.


1 Answer 1


I don't know if Mac Numbers supports this, but many data visualization programs let you specify whether a variable or an axis is categorical or continuous. In your case the date is being treated categorically: each date is an independent category with not relation to the other dates (that the order comes out right is probably a side effect of the YYYY-MM-DD format).

If you can indicate that the date column corresponds to continuous date values, you'll get a chart like this (thanks for including some data in your question):

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.