creating grouped box plot in Excel (using RExcel)

See the related posts on RExcel (for basic, Excel 2003 and Excel 2007) for basic information.

In previous posts we discussed about RExcel getting started, here is one example of implementation - on creating grouped bar plots.

Just for example, we will use already available data in lattice package. This excercise will also demostrate how you can load data from R library and visualized in excel.

Let's use the data barley from R library (lattice package). I put data in excel sheet using Data ->  read data data set in packages, package datasets and clicking barley dataset .

In excel sheet click get active dataframe. The you can see the data embedded in the excel sheet.

In this dataset we different varities grown in different sites in different years. The measured variable is yield. Let's produced plotts in different ways. Let's summarize the data in different way one way is to create boxplot and compare over sites or years etc.

From graphs menu click boxplot and then select variable (yield) and a grouping variable (site in this example).

Here are other grouping outputs (by year, by variety) you want to plot.

Happy plotting !!

 

Some tricks in Barplot

Barplot (column or row bar plots)

How to change negative number to different color or back to black number in axis:

Similarly we can create horizontal and vertical bars:

Adding lines to barplots:

If we like a to add new series and add series lines to all barplots,  the following is way to go:

However in many time we just want only or others represented as lines other as bars. Then let's do overlayed bar, select the series only and then change the plot type.

Adding error bars:
Click Format the data series where we want to add the error bar to.

With 2003 it is not possible to add separate labels such as mean comparisons  but is possible in excel 2013 it is possible to do so.

Grouped bars:

Over stacked bars, grouped bars provide side by side comparisions of the series.

We can add a number of series and then convert the series to different chart type (for example line type).

Sometime we might want to plot a constant line (such as representing a threshold), we can do so by just specifying a series with just a single value and plot this as line type. Note that the place of series affects the start and end point of the line.


 We can add the error bars to the data series we want to add to. We can specify error amount as fixed value or from a column.

A busy plot will look like the following:

Perhaps overlayed plot will be more suitable for this kind data where sum of the series is 1 (100%) and each component is composition of the whole: