Data Wrangling 3: Time Series Data and Missing Values

Load Packages

library(dplyr)
library(tidyr)

# Specify package preferences for filter, count, select, and arrange
library(conflicted)
conflict_prefer("filter", "dplyr", quiet = TRUE)
conflict_prefer("count", "dplyr", quiet = TRUE)
conflict_prefer("select", "dplyr", quiet = TRUE)
conflict_prefer("arrange", "dplyr", quiet = TRUE)

Import Weather Data

First, we import some weather data from the CIMIS network that has been saved to a Google Sheet that can be viewed by anyone with the link.

library(googlesheets4)
arvinedison_url <- "https://docs.google.com/spreadsheets/d/13FR4Mji24KqZmLqK_BiLKew8nN4MbCdh1KpzT54hH4M/"

## Tell R not to authorize with Google (not needed for this public Google Sheet)
gs4_deauth()

arvinedison_tbl <- read_sheet(arvinedison_url, sheet = "Sheet1")

v Reading from Arvin-Edison CIMIS Data Sep-2023.
v Range ''Sheet1''.

## View(arvinedison_tbl)
head(arvinedison_tbl)

Pro Tip:

You can download data from the CIMIS network directly into R using the cimir package (Michael Koohafkan).

library(cimir)
set_key("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
arvinedison_tbl <- cimis_data(targets = 125, start.date = "2023-09-16", end.date = "2023-10-15",
                               items = "day-air-tmp-max,day-air-tmp-min,day-eto,day-precip")

CHALLENGE #1

Would you consider the CIMIS data to be long or wide?

It is tidy?

Create a date column

library(lubridate)
arvedsn_dt_tbl <- arvinedison_tbl |> 
  mutate(Dt = make_date(year = Year, month = Month, day = Day)) |> 
  relocate(Dt, .after = Day)

## View(arvedsn_dt_tbl)
head(arvedsn_dt_tbl)

Create a wide version of the table

Guide to pivoting: https://tidyr.tidyverse.org/articles/pivot.html

library(tidyr)
arvedsn_wide_tbl <- arvedsn_dt_tbl |> 
  pivot_wider(names_from = Item, values_from = Value, id_cols = c(Station, Dt))

## View(arvedsn_wide_tbl)
head(arvedsn_wide_tbl)

Deal with Missing Values

The evapotranspiration column has some missing values. In this section, we’ll look at a couple of different methods of filling them in. (For a real project, we’d want to consult someone who knows about evapotranspiration to decide which method is most appropriate for these data!)

First, we create a data frame with just Eto:

arvedsn_eto_tbl <- arvedsn_dt_tbl |> 
  filter(Item == "DayEto") |> 
  select(Station, Dt, Item, Value, Unit)

head(arvedsn_eto_tbl)

Are there missing values?

## View(arvedsn_eto_tbl)

summary(arvedsn_eto_tbl)

    Station          Dt                 Item               Value            Unit          
 Min.   :125   Min.   :2023-09-16   Length:30          Min.   :0.1000   Length:30         
 1st Qu.:125   1st Qu.:2023-09-23   Class :character   1st Qu.:0.1500   Class :character  
 Median :125   Median :2023-09-30   Mode  :character   Median :0.1800   Mode  :character  
 Mean   :125   Mean   :2023-09-30                      Mean   :0.1708                     
 3rd Qu.:125   3rd Qu.:2023-10-07                      3rd Qu.:0.1900                     
 Max.   :125   Max.   :2023-10-15                      Max.   :0.2100                     
                                                       NA's   :5                          

Fix #1. Throw away incomplete rows

tidyr::drop_na() will throw away rows that have NA values in any of the specified columns (default is check all columns):

arvedsn_eto_fix1_tbl <- arvedsn_eto_tbl |> 
  tidyr::drop_na()

# View(arvedsn_eto_fix1_tbl)
nrow(arvedsn_eto_tbl); nrow(arvedsn_eto_fix1_tbl)

[1] 30
[1] 25

Fix #2. Replace with the mean or median

Step 1. Decide: mean or median?

arvedsn_eto_tbl |> pull(Value) |> hist(breaks = 20)

Step 2. Compute the median

value_median <- arvedsn_eto_tbl |> pull(Value) |> median(na.rm = TRUE)
value_median

[1] 0.18

Step 3. Substitute the median whenever Value is NA.

arvedsn_eto_fix2_tbl <- arvedsn_eto_tbl |> 
  mutate(Value_Fix1 = if_else(is.na(Value), value_median, Value))

## You can also use tidyr::replace_na()
## arvedsn_eto_fix2_tbl <- arvedsn_eto_tbl |> 
##   replace_na(list(Value = value_median))

# View(arvedsn_eto_fix2_tbl) 

Fix #3. Interpolate missing values

To interpolate missing values, we’ll create a vector with the replacement values (using the zoo package), and then tack it on to the data frame.

First, create the replacement values:

library(zoo)
vals_linear_interpolation <- na.approx(arvedsn_eto_tbl$Value)
vals_linear_interpolation

 [1] 0.2100 0.1900 0.2000 0.1800 0.1900 0.1800 0.1900 0.1900 0.1900 0.1800 0.1825 0.1850 0.1875 0.1900 0.1300 0.1200 0.1600 0.1700
[19] 0.1800 0.1900 0.1800 0.1900 0.1800 0.1700 0.1600 0.1000 0.1500 0.1500 0.1500 0.1500

# You can also do spline interpolation
# vals_spline_interpolation <- na.spline(arvedsn_eto_tbl$Value)
# vals_spline_interpolation

Next, add the vector with imputed values as a new column with bind_cols:

arvedsn_eto_fix3_tbl <- arvedsn_eto_tbl |> 
  bind_cols(Value_Fix2 = vals_linear_interpolation)

# View(arvedsn_eto_fix3_tbl)
head(arvedsn_eto_fix3_tbl)

This is just scratching the surface of dealing with missing values!

See also: imputeTS

CHALLENGE #2

There are also some missing values in the Maximum Daily Air Temperature (DayAirTmpMax).

Decide what would be a reasonable method to deal with these, and implement it.

Resampling data

Bin the Eto values into low, medium, and high:

arvedsn_eto_fix3_tbl <- arvedsn_eto_fix2_tbl |> 
  mutate(eto_level = case_when(Value_Fix2 <= 0.14 ~ "low",
                               Value_Fix2 <= 0.19 ~ "medium",
                               TRUE ~ "high"))

Error in `mutate()`:
i In argument: `eto_level = case_when(...)`.
Caused by error in `case_when()`:
! Failed to evaluate the left-hand side of formula 1.
Caused by error:
! object 'Value_Fix2' not found
Backtrace:
  1. dplyr::mutate(...)
  8. dplyr::case_when(...)
  9. dplyr:::case_formula_evaluate(...)
 11. rlang::eval_tidy(pair$lhs, env = default_env)

End

Remember to save your work to render a HTML file.