CODEWITHSUNDEEP
r
Juriv
Juriv

Reputation: 49

How to add row values based on specific column conditions?

I have a dataframe X with 20 different stocks, with observations of their respective daily returns for 10 years. The dataframe X looks as follows:

Stock Date Return
BKR 01.02.2002 2%
BKR 02.02.2002 -1%
BKR 03.02.2002 1%
BKR 04.02.2002 2.5%
BKR 05.02.2002 -3%
BKR 01.03.2002 2%
BKR 02.03.2002 1%
BKR 03.03.2002 -2%

And I wish to transform these daily returns into quarterly returns, thus adding daily returns for a period of 3 months (as these are continuous returns, they are additive). The condition should be that the stock is the same (the example only shows BKR and USP but I have 20 different ones). In other words, I wish to have a dataframe Y looking like this:

Stock Quarter Return
BKR 01.2002 4%
BKR 02.2002 -15%
BKR 03.2002 11%
BKR 04.2002 2%
BKR 01.2003 -31%
BKR 02.2003 21%
BKR 03.2003 12%
BKR 04.2003 -2%
USP 01.2002 1%
USP 02.2002 -13%
USP 03.2002 12%
USP 04.2002 8%
USP 01.2003 -13%
USP 02.2003 2%
USP 03.2003 14%
USP 04.2003 -3%

Upvotes: 0

Views: 72

Answers (3)

frustrated_bioinformatician
frustrated_bioinformatician

Reputation: 393

Consider the following solution using lubridate and dplyr:

#make a sample dataframe
x <- data.frame(Stock = c(rep('BKR',12), rep('TMP',12)),
                Date = c('01.01.2002','01.02.2002','01.03.2002','01.04.2002','01.05.2002','01.06.2002','01.07.2002','01.08.2002','01.09.2002','01.10.2002','01.11.2002','01.12.2002','01.01.2003','01.02.2003','01.03.2003','01.04.2003','01.05.2003','01.06.2003','01.07.2003','01.08.2003','01.09.2003','01.10.2003','01.11.2003','01.12.2003'),
                Return = c("2%","-4%","6%","1.2%","-1%","-1%","1%","2%","3%","8%","-3%","2%","12%","-8%","7%","13%","-24%","-1%","5%","6%","3%","2%","-3%","8%"))
x$Return <-  as.numeric(sub("%", "", x$Return, fixed=TRUE))/100
x$Date <- as.Date(x$Date, format='%d.%m.%Y')

#summarise the data
result <- x %>%
  group_by(quarter(Date, with_year = T), Stock) %>%
  summarize(year = year(Date), 
            quarter = quarter(Date),
            additive_return = sum(Return), 
            .groups = 'drop') %>% 
  unique() %>% 
  select(-`quarter(Date, with_year = T)`) %>% 
  mutate(additive_return = paste0(formatC(additive_return * 100, format = "f", digits = 2, ), "%"))

You will get the following output:

> result
# A tibble: 8 x 4
  Stock  year quarter additive_return
  <chr> <dbl>   <int> <chr>          
1 BKR    2002       1 4.00%          
2 BKR    2002       2 -0.80%         
3 BKR    2002       3 6.00%          
4 BKR    2002       4 7.00%          
5 TMP    2003       1 11.00%         
6 TMP    2003       2 -12.00%        
7 TMP    2003       3 14.00%         
8 TMP    2003       4 7.00%

Upvotes: 1

Chris Ruehlemann
Chris Ruehlemann

Reputation: 21400

Using dplyr, you can first mutate the dates so that they are distinguished only by year and month, then group_by Stock and Date, to finally calculate the summed Returns for the groups :

library(dplyr)
df %>%
  mutate(Date = sub("(\\d{4})-(\\d{2}).*", "\\2.\\1", Date)) %>%
  group_by(Stock, Date) %>%
  summarise(Return = sum(as.numeric(sub("%", "", Return))))
# A tibble: 2 x 3
# Groups:   Stock [2]
  Stock Date    Return
  <chr> <chr>    <dbl>
1 A     01.2000    6.5
2 B     02.2000    2.3

Data:

df <- data.frame(
  Stock = c(rep("A",3), rep("B",3)),
  Date = c(seq(as.Date("2000-1-1"), as.Date("2000-1-3"), "days"),
           seq(as.Date("2000-2-1"), as.Date("2000-2-3"), "days")),
  Return = c("4%", "-1%", "3.5", "2%", "4%", "-3.7%")
)

Upvotes: 1

Eyayaw
Eyayaw

Reputation: 1081

Convert the Return col to numeric first:

df$Date = as.Date(df$Date, "%d.%m.%y")
df$Q = quarters(df$Date)
aggregate(Return~Stock + Q, df, sum)

Stock  Q     Return
BKR    Q1    2.5

Upvotes: 0

Related Questions