CODEWITHSUNDEEP
rdplyr
Tahum
Tahum

Reputation: 37

Perform division operation on grouped data using tidyverse

Security_id Ticker Comp_Ticker Exch_Ticker YEAR MONTH DATE Security_Name                ADJ_CLOSE 
1           A      A:US        A:UN        2010  01   04    Agilent Technologies Inc.   2.093230e+01
1           A      A:US        A:UN        2020  01   10    Agilent Technologies Inc.   8.759000e+01
8           AAL    AAL:US      AAL:UW      2010  01   04    American Airlines Group Inc 4.511977e+00
8           AAL    AAL:US      AAL:UW      2020  01   10    American Airlines Group Inc 2.732000e+01

I have a DataFrame that has the above format I want to divide the 2020 years' ADJ_CLOSE by the 2010 years' ADJ_CLOSE by TICKER Groupings

How would I do/is it possible to do this with the d <- d %>% group_by (TICKER) %>% summarize (Fn here) format?

Upvotes: 0

Views: 91

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 389335

If you have data only for the year 2010 and 2020, we can arrange the data by Ticker and YEAR, group_by Ticker and divide last value of ADJ_CLOSE by first. 

library(dplyr)

df %>%
  arrange(Ticker, YEAR) %>%
  group_by(Ticker) %>%
  summarise(ratio = last(ADJ_CLOSE)/first(ADJ_CLOSE))

# A tibble: 2 x 2
#  Ticker ratio
#  <fct>  <dbl>
#1 A       4.18
#2 AAL     6.05

If you have data for other years as well, you can first filter the data and then use the same code. 

df %>%
  filter(YEAR %in% c(2020, 2010)) %>%
  arrange(Ticker, YEAR) %>%
  group_by(Ticker) %>%
  summarise(diff = last(ADJ_CLOSE)/first(ADJ_CLOSE))

data

df <- structure(list(Security_id = c(1L, 1L, 8L, 8L), Ticker = structure(c(1L, 
1L, 2L, 2L), .Label = c("A", "AAL"), class = "factor"), Comp_Ticker = structure(c(1L, 
1L, 2L, 2L), .Label = c("A:US", "AAL:US"), class = "factor"), 
Exch_Ticker = structure(c(1L, 1L, 2L, 2L), .Label = c("A:UN", 
"AAL:UW"), class = "factor"), YEAR = c(2010L, 2020L, 2010L, 
2020L), MONTH = c(1L, 1L, 1L, 1L), DATE = c(4L, 10L, 4L, 
10L), Security_Name = structure(c(1L, 1L, 2L, 2L), .Label = c("AgilentTechnologiesInc.", 
"AmericanAirlinesGroupInc"), class = "factor"), ADJ_CLOSE = c(20.9323, 
87.59, 4.511977, 27.32)), class = "data.frame", row.names = c(NA, -4L))

Upvotes: 1

akrun
akrun

Reputation: 887981

Assuming no duplicates for 'Year' within each 'TICKER', use either == or match to return logical or numeric index to filter the 'ADJ_CLOSE' for each 'Year' and then divide

library(dplyr)
d %>% 
  group_by(TICKER) %>% 
  summarize(out = ADJ_CLOSE[Year == 2020]/ADJ_CLOSE[Year == 2010])

Upvotes: 2

Related Questions