Displaying three pieces of information on a graph

Question

Note: 50 points of raw data are attached now.

I want to display how much study I have done, and how many pages I have completed throughout the week, broken up by day, and I have done so as shown below:

I have had people tell me that they can't understand the graphs, but I have no idea how else I can display them. Since I would essentially need three dimensions without make a cumulative depiction. I want to avoid using numerous line graphs, since after a few weeks the graphs will become illegible. Is there nothing I can do?

How can I display these more clearly?

Date        Total   Total pages
21/11/2014  2.4166   0
22/11/2014  0        0
23/11/2014  1.5833   4
24/11/2014  3.0166  13
25/11/2014  2.4999   6
26/11/2014  1.4833   3
27/11/2014  3.0499   6
28/11/2014  0        0
29/11/2014  2.4499   5
30/11/2014  2.8833   2
 1/12/2014  0        0
 2/12/2014  4.1166   8
 3/12/2014  1.3333   5
 4/12/2014  1.2499   3
 5/12/2014  1.6666   8
 6/12/2014  0        0
 7/12/2014  2.4833   9
29/12/2014  0        0
30/12/2014  1.2332   1
31/12/2014  0.3333   0
 1/01/2015  3.5666   2
 2/01/2015  0.8166   0
 3/01/2015  2.75    28
 4/01/2015  0.4166   0
 5/01/2015  1.2833   0
 6/01/2015  0.3333   3
 7/01/2015  0        0
 8/01/2015  0        0
 9/01/2015  2.35     2
10/01/2015  0.5666   0
11/01/2015  0        0
12/01/2015  1.6666   0
13/01/2015  2.2666   5
14/01/2015  2.5165   6
15/01/2015  2.0166   0
16/01/2015  2.9666   1
17/01/2015  0.8333   0
18/01/2015  0.6666   1
19/01/2015  1.45     0
20/01/2015  0.3166   0
21/01/2015  0        0
22/01/2015  0.2333   0
23/01/2015  0.85     2
24/01/2015  0        0
25/01/2015  0        0
26/01/2015  0.6666   4
27/01/2015  0.8333   1
28/01/2015  1.5498   5
29/01/2015  6.4159   9
30/01/2015  2.9166   0

Answer 1

One way of visualizing data that is date/calendar based is via a matrix display that encodes the data with color. The matrix (or table) is arrange so that rows represent weeks and column represent days. You can add a final column for the weekly total if that is desirable.

This can be implemented somewhat simply in Excel with conditional formatting if the data is arranged correctly. In particular, you can build a "grid" of values with formulas that lookup into your original data. From there, you can use conditional formatting to display the result.

Here is what the result could look like. Sorry I changed the date format. The formula in cell H1 is: "=IFERROR(VLOOKUP($G$1+$G6*7+H$5, $B$5:$C$16,2,FALSE), 0)". It is doing some math to get the days in the right order. Hopefully it's straightforward.

If you are really looking to push the envelope, you can use a framework like d3 and its calendar plugin to display this data. That might be more of an undertaking than it's worth though.

This format is very similar to how GitHub displays user activity/contributions over time. Here is one user's (not me!).

Answer 2

The prominent feature of the original is the weekly sums. The individual values are meaningful only after you've learned the colors, and I imagine that's a big reason the plot doesn't work for new viewers. Related to that, the time aspect of the days is lost. A sequential set of colors may help (e.g., 7 shades of blue).

I normally don't care to label every item -- are the exact values that important? The graph isn't doing its job if you can't interpret it without every value labeled.

On to my try. Given the apparent importance of the weekly sums, I've plotted the weekly cumulative sums. It shows the weekly sums and the days in time order. Exact day values are less clear, but outlier values will still stand out.

For these kinds of small line plots (which could be reduced to sparkline size) it's helpful to have a reference line or area. For illustration, I've added a target range. If a target is not appropriate, then the reference could be something like the range over the last three weeks or some fixed reference value.

I've used red to indicate which weeks were below target for quick scanning.

With a lot more weeks, you might organize them into a grid rather than a vertical list.

Answer 3

As I understand your question, it would be feasible to display hours and pages separately. I'll do that first. Afterwards, I'll display Total and Pages in one plot. I'm guessing that the actual numbers are not the most important thing - it's more important to get an overview of the weeks and weekdays, which were productive and which weren't. In that case, I suggest that you keep the natural temporal structure as there is actually only one temporal dimension in your data. We can still find a way to delimit the weeks.

I used the following R-code and the ggplot2-package to produce this first plot. Your data has been loaded into the object data in the below code. The plot is a grouped bar plot, with the grey bars indicating weekly sums of pages.

data <- rbind(data.frame(Date = c("17/11/2014", "18/11/2014", "19/11/2014", "20/11/2014"),
                         Total = rep(0, 4),
                         Pages = rep(0, 4)), 
              data,
              data.frame(Date = c("31/01/2015", "01/02/2015"),
                         Total = c(0, 0),
                         Pages = c(0, 0)))

n <- dim(data)[1]

data$Date <- as.Date(data$Date, format = "%d/%m/%Y")
data$weekday <- factor(rep(c("Monday", "Tuesday", "Wednesday", "Thursday",
                             "Friday", "Saturday", "Sunday"), length.out = n))
data$weekday <- factor(data$weekday, levels(data$weekday)[c(2,6,7,5,1,3,4)])
data$week <- factor(rep(seq(from = 0, to = ceiling(((n - 3)/7))), 
                        each = 7, length.out = n))

ggplot(data = data, aes(x = week, y = Pages)) + 
  geom_bar(aes(fill = weekday), stat = "identity", position = "dodge") + 
  labs(fill = NULL) + xlab(NULL) + ylab("Number of pages") + 
  geom_bar(stat = "identity", alpha = 0.2) + theme(panel.background = element_blank()) +
  scale_x_discrete(labels = paste("Week", seq(from = 0, to = 7)))

This is clearly not perfect. The grey bars dominate to much as they compared to a day bar have a larger area for the same amount of reading. We could make them thinner, but I like the way they delimit the weeks. They indicate quite nicely which days are in the same week - something that wouldn't necessarily be intelligible otherwise. Especially because we have zero counts.

In the next plot, I've used the mean number of pages (within week) as the height of the grey bar.

This probably represents data better. However, notice that week 0 and 7 are misleading because they didn't include 7 days. You could easily work around this.

If you insist on displaying pages and time simultaneously, you could do a back-to-back bar plot. It might be a little confusing as the two vertical scales are not the same. On the other hand, it might be nice to compare time spent and work done directly like this.

EDIT: Realizing that the colors are really not needed that much and inspired by xan (see below comments) you could simplify the plot to something like this. I've marked Thurdays to give an additional visual guide. You could also argue in favor of using the same color for all bars to not overemphasize some (arbitrary) days.

On a final note, you could also try scaling the axes differently by dividing your values by the mean value. This would make 1 a "normal" value. We could include a line at 1 to emphasize this point - now done on the back-to-back plot. This separates "good" from "bad" days in terms of mean work load.

On this plot we might also make sure that one unit corresponds to the same distance on both axes as they are comparable now.

Also note that I messed up the days in the first version. I've corrected the code and plots and I'll go practice the seven days of the week now.

The code that produced the last plot:

data$normPages <- data$Pages/mean(data$Pages)
data$normTotal <- data$Total/mean(data$Total)

data$weekNormPages <- data$Pages/(7*mean(data$Pages))
data$weekNormTotal <- data$Total/(7*mean(data$Total))

pTop <- ggplot(data = data, aes(x = week)) + geom_bar(aes(linetype = weekday, y = normPages), 
                                                      stat = "identity", position = "dodge", 
                                                      fill = "dodgerblue") + labs(fill = NULL) +
  xlab(NULL) + ylab("Number of pages") + geom_bar(aes(y = weekNormPages), stat = "identity", alpha = 0.3) + 
  theme(panel.background = element_blank(), axis.ticks.length=unit(0,"cm")) + guides(linetype = FALSE) +
  scale_x_discrete(labels = paste("Week", seq(from = 0, to = 7))) + ylab(NULL) + 
  annotate("text", label = "Pages read", x = "1", y = 10) +
  theme(plot.margin = unit(c(1,.5,.1,.8), "cm")) + geom_hline(yintercept = 1)
pTop

pBot <- ggplot(data = data, aes(x = week)) + geom_bar(aes(linetype = weekday, y = normTotal), 
                                                      stat = "identity", position = "dodge", fill = "dodgerblue") + 
  labs(fill = NULL) +
  xlab(NULL) + ylab("Number of hours") + geom_bar(aes(y = weekNormPages), stat = "identity", alpha = 0.3) + 
  theme(panel.background = element_blank(), axis.ticks.length=unit(0,"cm")) + guides(linetype = FALSE) + 
  scale_x_discrete(labels = NULL) + guides(fill = FALSE) + ylab(NULL) + scale_y_reverse() + 
  theme(plot.margin = unit(c(.1,.5,1,.8), "cm")) + 
  annotate("text", label = "Time spent", x = "1", y = 4) + geom_hline(yintercept = 1)
pBot

grid.arrange(pTop, pBot, heights = c(.5, .5), widths = c(0.5, 0.1))

Answer 4

If I understand you correctly, the reason you don't want to use the line graphs is that you have too many weeks and the graphs would get messy.

If this is the problem then you can divide the time series into components:

Daily variation

Weekly variation

Long term trend

Anything else.

William S. Cleveland shows a nice example of this in one of his books (I am not at my office and can't remember which of his books has the example but it is either Visualizing data or The elements of graphing data).

Both R and SAS have tools for doing this. Do you have access to either of them?

Answer 5

I will first spell out some objections to your original stacked or divided bar graphs.

a. The colour coding appears completely arbitrary. Hence the graph cannot be studied without repeatedly going back and forth between legend and graph.

b. Zeros are implicit, as invisible bar segments. Zeros are part of the variation.

For those and other reasons, the graphs are difficult to decode.

That said, the graph has merit if the interest is mostly in studying variation in totals from week to week. Many weeks could be plotted as many bars. The corresponding downside is that it would get harder and harder to study variations within weeks.

Backing up: There are three variables here in each problem.

1. Time studied or pages complete.

2. Day of week.

3. Week number.

As the number of weeks increases, any graph will get more detailed. The challenge is to keep that detail under control.

I would consider a cycle plot (other names have been used in the literature, but most refer to its use for looking at seasonal variation). There is a lucid introduction here by Naomi Robbins Her examples include those like yours where the interest is in variations within and between weeks.

Answer 6

The line graphs would probably be easier to interpret if you took a rolling seven-day, fourteen-day or maybe 28-day moving average. That would smooth them out and still allow you to spot trends.

This has some similarities with Peter Flom's solution, though is rather simpler and hence doesn't tell quite as full a picture - but it may well suffice for your needs. If you are recording your data in a spreadsheet, it has the advantage that such averaging can easily be performed within the spreadsheet itself by setting up some formulas, and the graph will automatically update as you fill in new data.

Update to include graphs

The spreadsheet graph for the seven-day rolling averages is unspectacular but seems to do its job well - daily variation is smoothed out so trends are easier to detect (compared to the equivalent daily chart which is so noisy as to be incomprehensible). Some key features are picked out well by this plot: for instance, a large quantity of work was done in mid-January, in hourly terms, but this was not accompanied by a proportionate rise in the average pages completed per day. The Christmas break is very visible and so long as individual data points are clearly plotted then it's not too misleading (if just the line was visible, it would be impossible to determine that the flat period was due to lack of data!). Nevertheless, I'd strongly recommend including rest days in the table, albeit with zero hours and zero pages. The graph could then respond to this, rather than hover $\approx 1.5$ hours per week over the break.

With just fifty items of data it did not seem worth trying averaging over a longer period of time to detect longer run trends. Similarly I suspect that Peter Flom's excellent idea of seasonal decomposition would struggle with such limited data. If you were to perform the decomposition in your spreadsheet, it would be even more important to include the break as zero data.

To reproduce my formulas, paste this so that 'Date' is in cell A1:

Date    Hours   Pages   7-day rolling hours 7-day rolling pages
25/11/14    2.4999  6       
26/11/14    1.4833  3       
27/11/14    3.0499  6       
28/11/14    0   0       
29/11/14    2.4499  5       
30/11/14    2.8833  2       
01/12/14    0   0   =AVERAGE(B2:B8) =AVERAGE(C2:C8)
02/12/14    4.1166  8   =AVERAGE(B3:B9) =AVERAGE(C3:C9)
03/12/14    1.3333  5   =AVERAGE(B4:B10)    =AVERAGE(C4:C10)
04/12/14    1.2499  3   =AVERAGE(B5:B11)    =AVERAGE(C5:C11)
05/12/14    1.6666  8   =AVERAGE(B6:B12)    =AVERAGE(C6:C12)
06/12/14    0   0   =AVERAGE(B7:B13)    =AVERAGE(C7:C13)
07/12/14    2.4833  9   =AVERAGE(B8:B14)    =AVERAGE(C8:C14)
29/12/14    0   0   =AVERAGE(B9:B15)    =AVERAGE(C9:C15)
30/12/14    1.2332  1   =AVERAGE(B10:B16)   =AVERAGE(C10:C16)
31/12/14    0.3333  0   =AVERAGE(B11:B17)   =AVERAGE(C11:C17)
01/01/15    3.5666  2   =AVERAGE(B12:B18)   =AVERAGE(C12:C18)
02/01/15    0.8166  0   =AVERAGE(B13:B19)   =AVERAGE(C13:C19)
03/01/15    2.75    28  =AVERAGE(B14:B20)   =AVERAGE(C14:C20)
04/01/15    0.4166  0   =AVERAGE(B15:B21)   =AVERAGE(C15:C21)
05/01/15    1.2833  0   =AVERAGE(B16:B22)   =AVERAGE(C16:C22)
06/01/15    0.3333  3   =AVERAGE(B17:B23)   =AVERAGE(C17:C23)
07/01/15    0   0   =AVERAGE(B18:B24)   =AVERAGE(C18:C24)
08/01/15    0   0   =AVERAGE(B19:B25)   =AVERAGE(C19:C25)
09/01/15    2.35    2   =AVERAGE(B20:B26)   =AVERAGE(C20:C26)
10/01/15    0.5666  0   =AVERAGE(B21:B27)   =AVERAGE(C21:C27)
11/01/15    0   0   =AVERAGE(B22:B28)   =AVERAGE(C22:C28)
12/01/15    1.6666  0   =AVERAGE(B23:B29)   =AVERAGE(C23:C29)
13/01/15    2.2666  5   =AVERAGE(B24:B30)   =AVERAGE(C24:C30)
14/01/15    2.5165  6   =AVERAGE(B25:B31)   =AVERAGE(C25:C31)
15/01/15    2.0166  0   =AVERAGE(B26:B32)   =AVERAGE(C26:C32)
16/01/15    2.9666  1   =AVERAGE(B27:B33)   =AVERAGE(C27:C33)
17/01/15    0.8333  0   =AVERAGE(B28:B34)   =AVERAGE(C28:C34)
18/01/15    0.6666  1   =AVERAGE(B29:B35)   =AVERAGE(C29:C35)
19/01/15    1.45    0   =AVERAGE(B30:B36)   =AVERAGE(C30:C36)
20/01/15    0.3166  0   =AVERAGE(B31:B37)   =AVERAGE(C31:C37)
21/01/15    0   0   =AVERAGE(B32:B38)   =AVERAGE(C32:C38)
22/01/15    0.2333  0   =AVERAGE(B33:B39)   =AVERAGE(C33:C39)
23/01/15    0.85    2   =AVERAGE(B34:B40)   =AVERAGE(C34:C40)
24/01/15    0   0   =AVERAGE(B35:B41)   =AVERAGE(C35:C41)
25/01/15    0   0   =AVERAGE(B36:B42)   =AVERAGE(C36:C42)
26/01/15    0.6666  4   =AVERAGE(B37:B43)   =AVERAGE(C37:C43)
27/01/15    0.8333  1   =AVERAGE(B38:B44)   =AVERAGE(C38:C44)
28/01/15    1.5498  5   =AVERAGE(B39:B45)   =AVERAGE(C39:C45)
29/01/15    6.4159  9   =AVERAGE(B40:B46)   =AVERAGE(C40:C46)
30/01/15    2.9166  0   =AVERAGE(B41:B47)   =AVERAGE(C41:C47)

Answer 7

Change $x$ axis to weekdays, let $y$ the same and:

1. plot the data as lines with two weeks as grouping variables - so to get two separate lines for each week,
2. or use grouped bar plots where for each weekday you have two bars for week 1 and week 2, each with count of pages/hours per day.

Answer 8

The plot below shows cumulative Hours of Study and Total Pages within each week using lines instead of stacked bars, which hopefully will make it easier to see the trend within each week and compare between weeks. I've filled in the missing weeks with zeros, but you can exclude those if you wish. The R code for the data processing and plot generation is posted below the graph.

In carrying out the steps below, I first loaded the data posted in the question into a data frame called dat.

library(lubridate)
library(dplyr)
library(reshape2)
library(ggplot2)
library(scales)

# Ordered vector of weekdays
weekdayVec = c("Sunday","Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

# Change column name
names(dat)[2] = "Hours of Study"

# Convert Date to date format
dat$Date = as.Date(dmy(dat$Date))

# Add a weekday variable and order from Sunday to Saturday
dat$Day = weekdays(dat$Date)
dat$Day = factor(dat$Day, levels=weekdayVec)

# Number the weeks from 1 to 11 and convert to a factor
dat$Week = paste("Week", (as.numeric(dat$Date) - as.numeric(dat$Date[3])) %/% 7 + 2)
dat$Week = factor(dat$Week, levels=paste("Week", c(1:11))) 

## Fill in empty dates (so we can show zero pages/hours during weeks 5 and 6 if we want)
dataFill = expand.grid(Week = paste("Week",1:11), Day=weekdayVec)
dat = merge(dataFill, dat, by=c("Week","Day"), all=TRUE)

# Fill in missing dates
dat$Date = as.Date(c(rep(NA,5), seq(as.Date("2014-11-21"),as.Date("2015-01-30"),1), NA))

# Convert missing data to zeros for Hours of Study and Total Pages
dat = dat %>% mutate(`Hours of Study` = ifelse(is.na(`Hours of Study`), 0, `Hours of Study`),
               `Total Pages` = ifelse(is.na(`Total Pages`), 0, `Total Pages`)) 

# Melt data into long format (for facetting in ggplot2)
dat.m = dat %>% melt(id.var=1:3) %>%
  group_by(Week, variable) %>%
  mutate(cumValue = cumsum(value))

# Plot Hours and Pages by date, with separate cumulative 
# curves for each week
ggplot(dat.m %>% group_by(Week, variable) %>% arrange(Week, Day), 
       aes(Date, cumValue, colour=Week, group=Week)) +
  geom_vline(xintercept=as.numeric(seq(as.Date("2014-11-16"), as.Date("2015-02-06"), 7)-0.5), colour="grey70") +
  geom_line(position=position_dodge(width=0.5)) +
  geom_point(size=2.5, position=position_dodge(width=0.5)) +
  facet_grid(variable ~ ., scales="free_y") +
  guides(colour=guide_legend(reverse=TRUE)) + labs(y="",x="") +
  guides(colour=FALSE) +
  scale_x_date(limits=c(as.Date("2014-11-16"),as.Date("2015-01-31")),
               breaks=seq(as.Date("2014-11-16"),as.Date("2015-01-31"), 7)-0.5,
               labels=paste("                  Week",1:11)) +
  theme_grey(base_size=15)

Answer 9

Another option is the bubble chart, where you can have vertical height for one variable and dot size for the other. Below, date (day) is horizontal, Hours studied is vertical, Pages covered per day is bubble size, and week is colored.

Answer 10

You could plot in 3d. I didn't verify that the day of week was calculated correctly, find the best viewing angle, etc, but this should give you the idea. Further embellishments are also possible. For example, it might be better to connect the points with a line and move the gridlines to correspond to each Monday.

Actually what would be very interesting to try is having each left-right and up-down gridline (as shown in this angle) correspond to the same day of the week (e.g. monday), then putting boxplots on the bottom and back right walls within the gridlines. The boxplots would correspond to the total hours and total pages for each week, respectively. I am near certain that would be possible to do with rgl, but would require some tinkering. It may be worth it. Violin plots or beanplots may be even better.

The data (for inputting to R):

dat<-structure(list(Date = structure(c(17L, 19L, 21L, 23L, 25L, 27L, 
29L, 31L, 33L, 38L, 2L, 14L, 36L, 42L, 44L, 46L, 48L, 34L, 39L, 
40L, 1L, 13L, 35L, 41L, 43L, 45L, 47L, 49L, 50L, 3L, 4L, 5L, 
6L, 7L, 8L, 9L, 10L, 11L, 12L, 15L, 16L, 18L, 20L, 22L, 24L, 
26L, 28L, 30L, 32L, 37L), .Label = c("1/1/2015", "1/12/2014", 
"10/1/2015", "11/1/2015", "12/1/2015", "13/01/2015", "14/01/2015", 
"15/01/2015", "16/01/2015", "17/01/2015", "18/01/2015", "19/01/2015", 
"2/1/2015", "2/12/2014", "20/01/2015", "21/01/2015", "21/11/2014", 
"22/01/2015", "22/11/2014", "23/01/2015", "23/11/2014", "24/01/2015", 
"24/11/2014", "25/01/2015", "25/11/2014", "26/01/2015", "26/11/2014", 
"27/01/2015", "27/11/2014", "28/01/2015", "28/11/2014", "29/01/2015", 
"29/11/2014", "29/12/2014", "3/1/2015", "3/12/2014", "30/01/2015", 
"30/11/2014", "30/12/2014", "31/12/2014", "4/1/2015", "4/12/2014", 
"5/1/2015", "5/12/2014", "6/1/2015", "6/12/2014", "7/1/2015", 
"7/12/2014", "8/1/2015", "9/1/2015"), class = "factor"), TotalHours = c(2.4166, 
0, 1.5833, 3.0166, 2.4999, 1.4833, 3.0499, 0, 2.4499, 2.8833, 
0, 4.1166, 1.3333, 1.2499, 1.6666, 0, 2.4833, 0, 1.2332, 0.3333, 
3.5666, 0.8166, 2.75, 0.4166, 1.2833, 0.3333, 0, 0, 2.35, 0.5666, 
0, 1.6666, 2.2666, 2.5165, 2.0166, 2.9666, 0.8333, 0.6666, 1.45, 
0.3166, 0, 0.2333, 0.85, 0, 0, 0.6666, 0.8333, 1.5498, 6.4159, 
2.9166), TotalPages = c(0L, 0L, 4L, 13L, 6L, 3L, 6L, 0L, 5L, 
2L, 0L, 8L, 5L, 3L, 8L, 0L, 9L, 0L, 1L, 0L, 2L, 0L, 28L, 0L, 
0L, 3L, 0L, 0L, 2L, 0L, 0L, 0L, 5L, 6L, 0L, 1L, 0L, 1L, 0L, 0L, 
0L, 0L, 2L, 0L, 0L, 4L, 1L, 5L, 9L, 0L)), .Names = c("Date", 
"TotalHours", "TotalPages"), class = "data.frame", row.names = c(NA, 
-50L))

Make the plot:

#Get Day of Week
dat<-cbind(weekdays(as.Date(dat[,1], format="%d/%m/%Y")),dat)
colnames(dat)[1]<-"DoW"

#3D Plot
require(rgl)
plot3d(dat[,2],dat[,3],dat[,4],size=15, 
xlab=colnames(dat)[2], ylab=colnames(dat)[3],
zlab=colnames(dat)[4],col=rainbow(7)[as.numeric(dat[,1])])
text3d(x=10, y=6, z=seq(25,15,length=7),levels(dat[,1]),
col=rainbow(7), font=2)
grid3d(side=c("x", "y+", "z"), lwd=1)

Answer 11

Following heatmap with week number (of year), day of week and facets for hours and pages may be helpful:

Removing 2 high values give better color gradients on plot:

Following barchart may also be helpful.

It clearly shows a 2 week period when no work was done.

Plot with lines may also be useful (lines are not cluttered; the points can also be removed, keeping only two lines)

They clearly convey the information while simplifying the plot for easy understanding.