1. Introduction

1.1. About this practical

This  practical  will  introduce  the  manipulation  and exploration of  data  using  Microsoft  Excel  and  the  R  statistical environment. The result of your work in Excel can contribute to the formative coursework assignment.

We will investigate changes in water height (‘water level’) in the River Frome in Dorset, linked to rainfall in the upstream catchment. Field data are from a pressure transducer placed on the riverbed at a site near Frampton, which recorded water pressure from May 2003 to April 2005 (data used in the practical are from Moggridge and Goodson, 2005). Pressure Transducers (PTs) measure the pressure produced by a column of water above a sensing element and output a voltage. The higher the pressure recorded by the PT, the higher the water level sitting above it. The voltage output readings were taken every 15 minutes for the duration of the study and recorded in a data logger.

This practical will introduce (or maybe refresh) you to the following Excel functions: simple formulae, scatter plots, simple linear regression, time and date formats and conversions, text to columns, pivot tables, time-series graphs and graph formatting. Some knowledge of Excel is assumed. This practical will also introduce the R statistical environment and show how it can be used to perform the same functions.

For the formative coursework you should present plots and graphs created in Excel only. The instructions for R are to get you started learning how to use that software environment – we will focus more on R in future weeks.

1.2.        Download the data

Data from (Moggridge and Goodson, 2005) are provided in an Excel spreadsheet “Frome_Data.xlsx” on KEATS. Download this file to your computer and open it in the Desktop App (do not use the online web-version because this lacks some of the functionality we’ll be using).

1.3. Where to save the data?

At King’s, your personal computing storage is in the ‘cloud’, referred to as (Microsoft) OneDrive. It is accessible through the same suite of services as your Outlook email, Calendar, and the online versions of MS Office Apps (Word, Excel, PowerPoint), from any laptop or desktop computer, at home or at King’s.

Open your OneDrive to see what kind of directory structure it has and to create a new Folder for your EDA practical exercises. It is also good practice to always have a USB memory stick with you for saving your work, so that you can access your files when offline. Furthermore, when doing GIS and Remote Sensing (other modules), many files are extremely large (many GB) and a USB memory stick can be more efficient that uploading and downloading from the ‘cloud’ .

In whichever file managing method you use, you will need to be able to identify full paths for files and folders when you start working in R. Paths may look like: ‘E:/My Documents/EDA/etc’ (PC) or like ‘~/users/yourusername/desktop/etc’ (Mac).

• Q1: After doing some research about your machine, what is the path (address) you would use to access the folder you have just created? Type the path here:

Make sure to start saving your work in this folder.

2. Data familiarization

Open the Frome data in the Excel spreadsheet and familiarise yourself with the data. This workbook contains three worksheets:

1. Metadata: information on the data in the other worksheets

2. Logger: the data from the logger

3. Calibration Data: a small set of data points where the relative water height (also called water level) in the river was manually measured and matched against the recorded PT voltage output

Go to the worksheet Logger. This is typical of what a data logger output may look like. These data show the PT voltages over the duration of the study. However, for purpose of this study, we need water level information, not PT voltage.

In order to decipher water level from a water pressure measurement, at certain points in the study the water level was recorded manually and the corresponding PT voltage was noted. This is recorded in the Calibration Data worksheet. By doing this we can convert the voltages to relative water heights.

Check you understand what the values in each column of each worksheet refer to by reviewing the Metadata worksheet. You should always go through this process of checking what the fields and values refer to when encountering a new dataset.

3.   Converting PT Output to Water Levels

Using the Calibration Data, we can establish the relationship between voltage and water height and apply this relationship to all the Logger data, to obtain a full time-series of water  heights. We will do this  using  a technique  called  linear regression, which we will cover in more detail later on in the module. One way to do a linear regression in Excel is to plot data as a scatterplot and then add a simple linear regression line. To do this:

a)    Go to the Calibration worksheet.

b)   Select the PT and WaterLevel data. Create a scatterplot of these data. If you do not know how to create the plot: with data selected goto Insert  Charts X Y (Scatter)

You plot should appear as a window within the worksheet. It is often convenient to move plots (known as charts in Excel) to their own worksheet. To do this:

c)    Right-click on an empty area near the top of the plot  Move chart Select new sheet

d)    Rename the new sheet created (from ‘Chart1’ to something sensible) by right-clicking on the tab at bottom of the window, clicking Rename, and then typing the new name.

Putting your chart in a new sheet will make it easier to edit the chart.

You should see a clear pattern in the data. You can now perform a simple linear regression on these data, which will create a line of best fit. We can then use the equation of this line to convert the PT voltages to water levels. To perform a simple linear regression on data in a chart in Excel:

e)    Right-click on the data series and select Add Trendline. A menu should appear.

o In Trendline options, make sure Linear is selected

o Check Display equation on chart and Display R-squared value on chart

o Close the menu by clicking the cross in the top right

To convert the dates and times from the data logger to Excel format you will first need to find out what the date was on the 126th day of  the  year in 2003. This  can easily be done  with a quick  internet  search,  for  example  try  this  site

https://www.epochconverter.com/days/2003

Establishing the date and time of the first data point

a)    Create a new column and call it “DateTime”

b)   Type in the correct date and time for the first data row using this format dd/mm/yyyy hh:mm

c)    Correctly format the column in Excel as follows: a.    Select the column

b.    Right-click on the selected cells and click Format Cells

c.    A new window should appear

i.    In the list to the left, select Custom

ii.   Select dd/mm/yyyy hh:mm (if this is not in the list you can type it in yourself). iii.   Click OK

Next you will need to add 15-minute intervals to the first cell in order to fill the entire column accurately

1.    Select the cell below the one you just filled in

2.   Type in a formula that adds 15 minutes to the cell above it, e.g.: “=E2+TIME(0,15,0)” . Note,if you are not working in column E you will need to adjust the formula accordingly.

3.    Copy this formula to the rest of the column

Check Point, is this correct? Confirm with the GTA available that you have formatted the dates correctly.

You should now have a column which contains data in the following format 01/01/2004 00:00 (for example).

Compare the values in your new DateTime column with those in the respective Year, Day and Time columns to check the conversion makes sense, especially at the end of the dataset(!). This is a habit you should get into, to always verify that your data processing and manipulations are working correctly throughout the whole dataset.

• Q2: What do you notice when you check whether the conversion has gone correctly all the way to the end of the dataset? How are you going to rectify this?

Do not proceed until you have fixed the problem, because otherwise your time-series will be incorrect.

Save your Excel workbook!

a)    Highlight all the columns of data on your sheet

b)   Click on Insert → Pivot Table → Pivot Table. All data should be automatically selected and a new window should appear.

c)    Make sure that the destination of the Pivot Table is a New Worksheet and click “OK”

d)   A new worksheet will appear, with a blank table

e)   On the right, drag the “Date” field into the section labelled “ Rows” at the bottom of the window

f)     Drag the “WaterLevel” field into the section labelled Values at the bottom of the window

g)    In the Values section, click on “Sum of WaterLevel”, click Value Field Settings, and change the function from “Sum” to “Average”, the click OK

a.    Right click in your Pivot Table on one of the years in the Row Labels column and click on Ungroup

h)    Right click in your Pivot Table (i.e. on one of the values that has been calculated) and select Pivot Table Options

 Totals & Filters

o Uncheck the “Show grand totals for columns” and “Show grand totals for rows”

Think about what has happened here. We have gone from data measured every 15 to a summary of those data for each day in the dataset. What has this done to the amount of data in the new summary data?

Pivot tables are interactive, so it is best to copy and paste the data from the pivot tables into a new worksheet:

i)     Create a new worksheet and give it an appropriate label

j)     Copy the data in the Pivot Table

k)    In your new worksheet Paste Special with Values and Number Formatting

Through this section you have created several new variables (columns of data) and worksheets. You should consider addingto the metadata worksheet to ensure these are described properly. This will be very helpful when you come back to your data in future.

Save your Excel workbook!

6. Plotting a Time-Series

Using your new complete data set of predicted average daily water level, create a line plot (time series) of the data (e.g., select your data thengo to Insert → Line →2D Line → Line).

Format your chart in a manner which you think is suitable, so that it is clear and easy to interpret. Most of this can be done through the Design menu, which appears on the top of the screen when you select a chart. Remember, you may want to move the chart to a new window before you start editing.

Consider the following:

 Titles and labels of axes

 Suitable formats of the axis labels (right click on each axis to change this or use Chart Tools). For example, the date should be clear

 Are the axes a suitable length? Do they allow easy interpretation of patterns? (this can also be changed by right- clicking on the axis or through Chart Tools)

 Suitable format of data series: is it clear? (Right click on the data series to change this)

 The background of the chart (can be changed by right-clicking in the chart and selecting Format PlotArea)

 Gridlines (which can be changed via the Add Chart Elements button):

o Do you need gridlines on the x andy axis?

o The format of gridlines – they should not dominate the graph! Save your Excel workbook!

7. Download NRFA Data

As rainfall is the key driver behind the river flow we will download daily rainfall amounts for the catchment area upstream of Frampton from the UK National River Flow Archive (NRFA). Moggridge and Goodson (2005) collected their data at Frampton, a little distance upstream of gauging station 44004 ‘Fromeat Dorchester’ . Information about stations and the data collected at them are available online from the  NRFA website which is managed by the Centre for  Ecology and

Hydrology (CEH). The NRFA website URL is:http://nrfa.ceh.ac.uk To download daily rainfall data:

a)    Go to the link above and click on Data → Search for Data

b)    In the Search Table box, enter the station ID number

c)    Click on the station ID number in the filtered table

d)   Click on the Daily Flow Data tab

e)   Click Download catchment daily rainfall data

f)    Select appropriate responses, agree to the terms and conditions and click Download

Depending on what browser you are using the data will either be automatically downloaded or you will received a dialogue box asking if you want open or save the data. When downloading data from the Internet you almost always want to save the file to disc. If you click open you are at high risk of losing the data later. It is much easier to save the data to disk, then open the data in your desired software.

The data you have downloaded are in comma separated values format. You can tell this because the file suffix is ‘ .csv’ . A succinct description of csv files from file.org is as follows:

“Files that contain the .csv file extension are comma delimited files that contain separated database fields. These database fields have been exported into a format that contains a single line for each database record. The record is then divided and each field of the record that has been exported into a single line is separated by a comma.” [http://file.org/extension/csv]

Many environmental datasets you will download and use are in csv format. Excel can open csv files for viewing, and save to this format. However, remember that csv format is a basic text file which will not contain, for example, Excel formulas and other formatting that you might later add to it in Excel. If you plan to work with data in Excel in the future you should save the data in .xlsx format. Let’s do that for the file you just downloaded:

a)    Open the .csv file in Excel

b)   ‘save as’ an .xlsx file