2

I am trying to do calculations with rows in a column: I have the following data for one product:

Day    Price
1      3$
2      12$
3      4$
4      2$
5      4$

I want to divide the price change of a day by the day before so, for example for Day 2:

12$/3$ = 4

Results should be:

Day    Price    Calculation
1      3$       NA
2      12$      4
3      4$       0,33
4      2$       0,5
5      4$       2

I have a list of 5000 prices. I am also concerned how to get the NA in Day 1 were no calculation is possible.

Thank you!

upflow
  • 35
  • 4

3 Answers3

2

Here is dplyr only solution using gsub instead of parse_number:

library(dplyr)
df %>% 
  mutate(Calculation=as.numeric(gsub("\\$", "", Price)),
         Calculation=round(Calculation/lag(Calculation), 2))

Day Price Calculation
1   1    3$          NA
2   2   12$        4.00
3   3    4$        0.33
4   4    2$        0.50
5   5    4$        2.00
TarJae
  • 72,363
  • 6
  • 19
  • 66
1

We may divide the current value by the previous value (lag). The $ is not considered in the numeric class. We may need to extract the numeric value (parse_number) does that and do the calculation

library(dplyr)
df1 <- df1 %>%
    mutate(Calculation = readr::parse_number(as.character(Price)),
        Calculation = round(Calculation/lag(Calculation), 2))

-output

df1
 Day Price Calculation
1   1    3$          NA
2   2   12$        4.00
3   3    4$        0.33
4   4    2$        0.50
5   5    4$        2.00

data

df1 <- structure(list(Day = 1:5, Price = c("3$", "12$", "4$", "2$", 
"4$")), class = "data.frame", row.names = c(NA, -5L))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank for the quick response, trying your code I receive a error: `Error: Problem with mutate() column Calculation`. `i Calculation = readr::parse_number(Price)`. `x is.character(x) is not TRUE` – upflow Oct 09 '21 at 18:36
  • @upflow maybe you have`factor` class. Try with `as.character` as in my updated code – akrun Oct 09 '21 at 18:38
  • Thank you, A calculation is done now. But the new column "Calculation" is still missing somehow. – upflow Oct 09 '21 at 18:41
  • @upflow just do the assignment `<-` back to original data i.e. `df1 <- df1 %>% mutate(Calculation = readr::parse_number(as.character(Price)), Calculation = round(Calculation/lag(Calculation), 2))` – akrun Oct 09 '21 at 18:44
  • Thanks, now I have the needed results! A short question: Is there a possibility to duplicate the column `Price`, but to put the price data one row down? So for `Day 2`: the original `Price` will be 12 and for `Price_duplicate` it will be 3? – upflow Oct 09 '21 at 18:50
  • @upflow You can add `df1 <- df1 %>% mutate(Price_duplicate= lag(Price), Calculation = readr::parse_number(as.character(Price)), Calculation = round(Calculation/lag(Calculation), 2))` – akrun Oct 09 '21 at 18:51
1

Base R option -

Change Price column to numeric and subtract the current Price value with the previous one.

df$Price <- as.numeric(sub('$', '', df$Price, fixed = TRUE))
df$Calculation <-  c(NA, df$Price[-1]/df$Price[-nrow(df)])
df
#  Day Price Calculation
#1   1     3          NA
#2   2    12       4.000
#3   3     4       0.333
#4   4     2       0.500
#5   5     4       2.000
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213