CODEWITHSUNDEEP
r
Helena
Helena

Reputation: 217

How to calculate the ratio of a condition based on another condition

I have a simplified data frame like this

date        state   hour  
2020-01-01  A       6     
2020-01-01  B       3
2020-01-02  A       4
2020-01-02  B       3.5
2020-01-03  A       5
2020-01-03  B       2.5

For each date, there are two states. I want to calculate the ratio of state A/B in hour each day

For example,

date        ratio
2020-01-01  2 
2020-01-02  1.143
2020-01-03  2

How do I get this result? Thank you!

Upvotes: 2

Views: 157

Answers (4)

ThomasIsCoding
ThomasIsCoding

Reputation: 102379

A data.table option

> setDT(df)[, .(ratio = Reduce(`/`, hour[order(state)])), date]
         date    ratio
1: 2020-01-01 2.000000
2: 2020-01-02 1.142857
3: 2020-01-03 2.000000

Upvotes: 1

Anoushiravan R
Anoushiravan R

Reputation: 21938

You can also use the following solution, albeit it is to some extent similar to the one posted by dear @Ronak Shah .

library(dplyr)
library(tidyr)

df %>%
  pivot_wider(names_from = state, values_from = hour) %>%
  group_by(date) %>%
  summarise(ratio = A/B)

# A tibble: 3 x 2
  date       ratio
  <chr>      <dbl>
1 2020-01-01  2   
2 2020-01-02  1.14
3 2020-01-03  2

Upvotes: 0

GKi
GKi

Reputation: 39717

You can use xtabs:

tt <- xtabs(hour ~ date + state, x)
data.frame(dimnames(tt)[1], ratio = tt[,1] / tt[,2])
#                 date    ratio
#2020-01-01 2020-01-01 2.000000
#2020-01-02 2020-01-02 1.142857
#2020-01-03 2020-01-03 2.000000

Data:

x <- data.frame(date = c("2020-01-01", "2020-01-01", "2020-01-02", 
"2020-01-02", "2020-01-03", "2020-01-03"), state = c("A", "B", 
"A", "B", "A", "B"), hour = c(6, 3, 4, 3.5, 5, 2.5))

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 389175

With the help of match you can do :

library(dplyr)

df %>%
  group_by(date) %>%
  summarise(ratio = hour[match('A', state)]/hour[match('B', state)])

#  date       ratio
#  <chr>      <dbl>
#1 2020-01-01  2   
#2 2020-01-02  1.14
#3 2020-01-03  2

Upvotes: 2

Related Questions