# R Panel Chart Beats Excel Panel Chart

In this post, I show how to make a 3 variable time series panel chart in R. As an Excel panel chart pioneer, I can tell you that it is very difficult and messy to produce this type of panel chart in Excel.  The example R panel chart uses R’s a step chart format for one plot and R’s vertical line  format for the other 2 plots. Several of R’s dynamic capabilities, not inherently available in Excel, are also used. No helper series are needed and the chart can easily be regenerated each month as new data is available.

Here’s The R Panel Chart

First, let’s take a look at the chart to see what’s so good about R charting.

Panel Charts

I started making Excel panel charts in May, 2006. Since then, I worked to improve my techniques and began to automate panel charts. Finally, I was able to make automated lattice charts in Excel-VBA. When I compared the effort needed to make the Excel-VBA code for lattice plots versus using R’s readily available lattice plot capabilities, I realized how much more powerful R was for doing the types of charts I wanted to make. Why try to reinvent the wheel in Excel when R can do it better, faster, easier?

In this video I discuss trellis – lattice plots, my automated Excel – VBA trellis plot and my  R version of the same trellis plot and why it is so much easier to produce panel  charts with R.

Since my Excel – VBA versus R experiment, I have used R for all of my panel – lattice – trellis charts.

In this current panel chart example, I chart 3 time series. Since Excel doesn’t have a convenient panel chart capability, some users would be tempted to place the 3 series on the same plot, possibly using 2 Y axis scales or rescaling each series. Rather than compromise the data visualization quality of the chart, I recommend using R for multivariate time series.

Each series is plotted in its own panel with appropriate Y axis scale suited to the data range for the variable.   I have used R’s step chart format for the monthly RSS temperature data series and R’s thin vertical line format  for the monthly NINO34 and SATO Index data series. The NINO34 data is color coded so that values > 0 are red and value < 0 are blue.

The panels are annotated to assist the reader in interpreting the data. The RSS temperature and NINO34 series highlight the last data points and provide the actual values to give the reader the most up-to-date information. The SATO Index panel explains the volcano – Sato Index relationship and points out previous major eruptions.

The 2nd line of the title is dynamically generated to indicate the update month. Likewise, the lower right footer is dynamically generated to reflect print data for the chart.

R Script

The R script is shown below. I have extensively commented my code to make it easier for me to reuse it for other charts. The script file is here and the data file is here.

```# 3 Panel plot of RSS Temp Anomaly Trend, SATO Index, NINO3.4 Index
## Multiple trend chart version - Monthly anomaly data
## D Kelly O'Day; http://processtrends.com; https://chartsgraphs.wordpress.com
## Feb 6, 2009
#########################################################################
# Setup
#########################################################################
options(digits=6)
par(ps=10); par(cex.axis=c(1.1)); par(cex.lab=c(1));par(las="1")
#Set outer and figure margins to reduce gap between plots
par(mfrow=c(3,1)); par(oma=c(2.5,5,5,5));  par(mar=c(0,5,0,0))
## Start & end years for plot
yr_st <- 1980
yr_end<- 2010
x_grid_gap <-5
##################################################################
##################################################################
skip = 1, sep = ",", dec=".",
row.names = NULL, header = FALSE,
colClasses = rep("numeric",7),
comment.char = "#", na.strings = c("*", "-",-99.9, -999.9),
col.names = c("Yr", "Mo","Yr_frac", "GISS", "SATO", "NINO34", "RSS") )
attach(ts_data)
# Find last mo & year for title
c <- nrow(ts_data)
lst_mo <- ts_data\$Mo[c]
lst_yr <- ts_data\$Yr[c]
mo_names <- c("Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept",  "Oct", "Nov", "Dec")
lst_NINO34 <- NINO34[c]
#################################################################
# Create Plot 1: RSS Temperature Anomaly
#################################################################
plot(Yr_frac,RSS,  type = "S", col = "darkgrey",
xlim = c(yr_st, yr_end),  ylim=c(-0.4, 1),
xaxs="i", yaxs="i", axes= FALSE,
ylab = expression(paste("RSS Anomaly ",degree, "C")))
axis(1,  col = "lightgrey", at = NULL, labels = FALSE)
axis(2, col = "lightgrey", labels = TRUE )
abline(v=seq(yr_st,yr_end, x_grid_gap),col = "lightgrey")
axis(3, col = "lightgrey", at = NULL, labels = TRUE)
rect(1992,0.8, 2000, .95,col = "white", border = NA)
text(1990, 0.87, "RSS Land & Ocean Temp Anomaly V3.2",pos = 4)
rect(1982,0.55, 1986, .65,col = "white", border = NA)
text(1983, 0.6, "Area: 70S to 82.5N", col = "grey")
points( Yr_frac[c], lst_RSS, pch=19, col = "darkgrey")
points(2001.7, -0.2, pch=19, col = "darkgrey")
note <- paste(mo_names[lst_mo], ",", lst_yr, " @ ", lst_RSS,"C")
rect(2001.9, -0.3, 2007, -0.1, col = "white",border = NA)
text(2002, -0.2, note, pos = 4, col = "black", cex = 1)
########################################################################
# Create plot 2: Nino34 index
########################################################################
n_red =0, select=c(Yr_frac, NINO34))
n_blue <- subset(ts_data, NINO340) clr="red" else clr="blue"
points( Yr_frac[c], lst_NINO34, pch=19, col = clr)
points(2001.7, -2, pch=19,col=clr)
note_1 <- paste(mo_names[lst_mo], ",", lst_yr, " @ ", lst_NINO34,"C")
rect(2001.9, -1.8, 2007, -2.3, col = "white",border = NA)
text(2002, -2, note_1, pos = 4, col = "black", cex = 1)
#####################################################################
# Create plot 3: SATO index
#####################################################################
par(mar=c(2,5,0,0))
plot(Yr_frac, SATO, type = "h", col = "grey",
xlim = c(yr_st, yr_end), ylim=c(0, 0.15),
xaxs="i", yaxs = "i", axes = FALSE,  xlab ="",
ylab=expression(paste("SATO Index ")))
axis(1, col = "lightgrey", at = NULL, labels = TRUE )
axis(2, col = "lightgrey", at = NULL, labels = TRUE)
abline(v=seq(yr_st, yr_end, x_grid_gap),col = "lightgrey")
rect(1996,0.04, 2009, 0.08,col = "white", border = NA)
text(2002, 0.06,"Volcanoes increase SATO Index; \n Tend to decrease temperatures.\nNote Pinatobo effect in 1992")
axis(3, col = "lightgrey", at = NULL, labels = FALSE)
text(1988, 0.09,  "Pinatubo & \n Hudson \n Eruptions")
arrows(1988.3,0.073, 1992, 0.05,  col = "black", length = 0.0)
rect(1994.5,0.12, 2000, 0.145,col = "white", border = NA)
text(1995.5, 0.125,  "SATO Index", pos = 3)
text(1982, 0.12,  "El Chichon\n Eruption")
arrows(1982,0.1, 1982.5, 0.08,  col = "black", length = 0.0)
############################################################################
# Overall Chart Title   & Footer Notes
############################################################################
mtext("RSS and NINO3.4 Temperature Anomalies and SATO Index Trends Since 1980", 3,3, outer = TRUE)
mtext(paste("Data Updated Through ", mo_names[lst_mo], ",", lst_yr), 3,2, outer = TRUE, cex = 0.7)
# Add KOD & print date to chart
mtext("D Kelly O'Day - http://chartgraphs.wordpress.com", 1,1, adj = 0, cex = 0.7, outer=TRUE)
mtext(format(Sys.time(), "%m/%d/ %Y"), 1, 1, adj = 1, cex = 0.7, outer=TRUE)
detach(ts_data)```

### 10 responses to “R Panel Chart Beats Excel Panel Chart”

1. aswin

how can we create panes in excel using c# ??
because c# supports interoperability …
so i want use by writing code in c#

2. Kathryn

I also have spent considerable time in Excel to create readable graphics when using this Lattice package in R is clearly better and also easier. I also find it strange that Excel does not have these Trellis-chart type capabilities, since this is such a common and useful tool. Especially Office 2007 with the improved graphics in Excel. Anyway, THANK YOU for the R code here, amazing!

3. Dave

You could make things much easier on yourself if you used xyplot in lattice or if you used ggplot2. Unlike another commenter, I have used both ggplot2 and lattice and I much prefer lattice. (by the way, I’m doubtful this commenter really has used lattice if he didn’t notice that you weren’t actually using it above).

4. Mike Lawrence

Have you checked out ggplot2 yet? I used to use lattice but recently switched to ggplot2 and found my code has become much shorter and cleaner.

5. Good stuff as usual :-)…~

Don’t know hos usefull the videos are for instructional purposes, but as an R-evangilst the video is certainly a good starting point for converting excel users to the church of R.

Cheers