Import Spreadsheet

Data are usually collected in a spreadsheet program such as Microsoft Excel or Open Office. Once data collection is complete it is preferable to import the data directly from the spreadsheet into JGR, rather than entering it manually or using the ‘copy and paste’ method.

The same data as used in the ‘manual‘ and ‘copy and paste‘ methods is provided in two formats. The comma separated values is the most versatile file format, but the Excel format is often preferred.

Comma Separated Values (CSV)

Heights of 10 people (csv file)

Microsoft Excel (XLS)

Heights of 10 people (xls file)

There should be no spaces in variable names. In a spreadsheet, the variable names are stored in the first row. If the variable name contains a space, this space will be converted to a full stop “.”.  However, it is generally preferable not to use spaces in variable names.

To import the spreadsheet, make sure the Data Viewer in JGR is open. If the Data Viewer is not visible, make the Console the active window and select from the menu bar: Packages & Data and then Data Viewer.

If no data are loaded, the Data Viewer looks like this:

(If a data frame is open, the data viewer will show that data frame. To create a new data frame, make sure the Data Viewer window is active and select File and then New Data from the menu bar).

Select the Open Data Frame icon, manoeuvre to the saved file (height10.xls or height10.csv) and open it.

Excel: Select which worksheet should be loaded: 1 (JGR can only load one worksheet from a workbook) and click OK. This should load the data in the Data Viewer ready for analysis with JGR / R.

CSV: Make sure the Comma ‘,’ is selected as record separator, the Double Quote ” as quote and the header has a tick mark (The header is the first row in the file. If the header contains the variable name (as it does in this example) make sure it is ticked; otherwise un-tick it as you will lose the first row of your data! You can see what happens in the viewer if you tick and un-tick the header tick-box.).

Before proceeding with statistical analysis, make sure the variables are set of the appropriate type. The available data types are:

1. Character (text)
2. Factor (different groups, categorical variable)
3. Double (double precision floating point, for decimal numbers)
4. Integer (for integer numbers, discrete numeral data)
5. Logical (a binary variable, true / false, yes / no etc)
6. Date
7. Time
8. Other

Select the Variable View tab and check the variable name (should be height) and the variable type (should be double). Once set according to the variable properties, select the Data View tab and the data are available for analysis. Making the Console the active window, will change the menu bar to perform statistical analysis, create plots etc.

To save the data frame, select the save data frame icon:and save the file with an appropriate name in an appropriate location. Select *.RDA (R DAta file) as the file format.

The data frame can also be saved by selecting File and then Save Data from the menu bar when the Data Viewer is the active window.

Advanced Users

To import a csv file:

height10 <- read.table(“/PATH/TO/FILE/height10.csv”,header=T,sep=”,”,quote=”\””)

This will create a data frame ‘height10′ that is available in the Data Viewer (please edit the /PATH/TO/FILE/ to the appropriate location of the file).

To import a xls file:

library(foreign)
library(XLConnect)
height10 <- readWorksheet(loadWorkbook(‘/PATH/TO/FILE/height10.xls’),sheet=1)

The packages foreign 1 and XLConnect 2 should be installed as described. (please edit the /PATH/TO/FILE/ to the appropriate location of the file)

To import a xlsx file:

For example the SpecGroup.xlsx file:

library(foreign)
Warning message:
package ‘survival’ was built under R version 3.2.5
library(XLConnect)
Loading required package: XLConnectJars
XLConnect 0.2-11 by Mirai Solutions GmbH [aut],
  Martin Studer [cre],
  The Apache Software Foundation [ctb, cph] (Apache POI, Apache Commons
    Codec),
  Stephen Colebourne [ctb, cph] (Joda-Time Java library)
http://www.mirai-solutions.com ,
http://miraisolutions.wordpress.com
SpecGroup <- readWorksheet(loadWorkbook(‘/PATH/TO/FILE/SpecGroup.xlsx’),sheet=1)

The packages foreign 1 and XLConnect 2 should be installed as described. (please edit the /PATH/TO/FILE/ to the appropriate location of the file)