Subtracting one value from another based on date


#1

Hello,
This may be basic, but…I have data on one column and I want to subtract some of those data from data in the same column, based on site and date (so I want to subtract today’s number from site A from today’s data from site B). I am flummoxed and would greatly appreciate any help. Thanks!
Very simplified version of the really long dataframe:
date site number
2/28/18 A 16
2/28/18 B 22


#2

You can do it with tidyr by spreading your site to columns and then getting what you need:

library(tidyverse)

df <- tibble::tribble(
  ~date, ~site, ~number,
  "2018-02-28", "A", 16,
  "2018-02-28", "B", 22
)

df %>%
  tidyr::spread(key = site, value = number) %>%
  dplyr::mutate(diff = B - A)

# A tibble: 1 x 4
  date           A     B  diff
  <chr>      <dbl> <dbl> <dbl>
1 2018-02-28  16.0  22.0  6.00


#3

Thanks, I will try that!


#4
site<-c("A", "B")
value<-c(16, 22)
date <- as.Date(c('2018-3-7','2018-3-7'))
data<- data.frame(date, site, value)
date_q<-Sys.Date()
data$value_opps[data$date == date_q & data$site == 'A']<- data$value[data$date == date_q  & data$site == 'A' ] - data$value[data$date == date_q & data$site == 'B' ]

#replace NA with 0
data$value_opps[is.na(data$value_opps)]<- 0

hope this answers your question


#5

Thanks, let me try this!