CODEWITHSUNDEEP
rgroup-bytidyversedplyr
Simon
Simon

Reputation: 325

Calculate difference between values using different column and with gaps using R

Can anyone help me figure out how to calculate the difference in values based on my monthly data? For example I would like to calculate the difference in groundwater values between Jan-Jul, Feb-Aug, Mar-Sept etc, for each well by year. Note in some years there will be some months missing. Any tidyverse solutions would be appreciated.

    Well  year month     value
   <dbl> <dbl> <fct>     <dbl>
 1   222  1995 February   8.53
 2   222  1995 March      8.69
 3   222  1995 April      8.92
 4   222  1995 May        9.59
 5   222  1995 June       9.59
 6   222  1995 July       9.70
 7   222  1995 August     9.66
 8   222  1995 September  9.46
 9   222  1995 October    9.49
10   222  1995 November   9.31
# ... with 18,400 more rows

df1 <- subset(df, month %in% c("February", "August"))
test <- df1 %>% 
  dcast(site + year + Well ~ month, value.var = "value") %>%
  mutate(Diff = February - August)

Thanks,

Simon

Upvotes: 0

Views: 125

Answers (1)

jdata
jdata

Reputation: 56

So I attempted to manufacture a data set and use dplyr to create a solution. It is best practice to include a method of generating a sample data set, so please do so in future questions.

# load required library
library(dplyr)

# generate data set of all site, well, and month combinations
## define valid values
sites = letters[1:3]
wells = 1:5
months = month.name

## perform a series of merges 
full_sites_wells_months_set <- 
    merge(sites, wells) %>%
    dplyr::rename(sites = x, wells = y) %>% # this line and the prior could be replaced on your system with initial_tibble %>% dplyr::select(sites, wells) %>% unique() 
    merge(months) %>% 
    dplyr::rename(months = y) %>% 
    dplyr::arrange(sites, wells)

# create sample initial_tibble
## define fraction of records to simulate missing months
data_availability <- 0.8

initial_tibble <- 
    full_sites_wells_months_set %>% 
    dplyr::sample_frac(data_availability) %>% 
    dplyr::mutate(values = runif(nrow(full_sites_wells_months_set)*data_availability)) # generate random groundwater values

# generate final result by joining full expected set of sites, wells, and months to actual data, then group by sites and wells and perform lag subtraction
final_tibble <- 
    full_sites_wells_months_set %>% 
    dplyr::left_join(initial_tibble) %>% 
    dplyr::group_by(sites, wells) %>% 
    dplyr::mutate(trailing_difference_6_months = values - dplyr::lag(values, 6L))

Upvotes: 1

Related Questions