CODEWITHSUNDEEP
rdataframe
Hubert Rzeminski
Hubert Rzeminski

Reputation: 469

Counting number of values based on conditions in R

I'm new to R and have a dataset of historical economic calendar events like this:

Date, Currency
01/03/2017 15:00:00,USD
01/03/2017 15:00:00,USD
01/04/2017 10:00:00,EUR
01/04/2017 10:00:00,EUR
01/04/2017 19:00:00,USD
01/06/2017 13:30:00,USD
01/06/2017 13:30:00,USD
01/06/2017 13:30:00,USD

And from this, I want to count how many EUR events there are per date and then also count the number of USD events per date (Ignoring time).

The final data frame should look like this:

Date, EUR, USD
01/03/2017, 0, 2
01/04/2017, 2, 1
01/06/2017, 0, 3

How can I go about doing this?

Upvotes: 0

Views: 75

Answers (3)

Uwe
Uwe

Reputation: 42582

For the sake of completeness, the dcast() function can be used to reshape and count in one step:

library(data.table)
dcast(events[, Date := anytime::anydate(Date)], 
      Date ~ paste0(Currency, ".count"), length)

         Date EUR.count USD.count
1: 2017-01-03         0         2
2: 2017-01-04         2         1
3: 2017-01-06         0         3

Here, anydate() is used to coerce the character time stamps to class Date.

Also note that the currency columns have been renamed as requested by the OP.

Data

library(data.table)
events <- fread("Date, Currency
01/03/2017 15:00:00,USD
01/03/2017 15:00:00,USD
01/04/2017 10:00:00,EUR
01/04/2017 10:00:00,EUR
01/04/2017 19:00:00,USD
01/06/2017 13:30:00,USD
01/06/2017 13:30:00,USD
01/06/2017 13:30:00,USD")

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389235

After extracting the date you can do this with pivot_wider.

library(dplyr)
library(tidyr)

df %>%
  mutate(Date = as.Date(lubridate::mdy_hms(Date))) %>%
  pivot_wider(names_from = Currency, values_from = Currency, 
              values_fn = length, values_fill = 0)

#  Date         USD   EUR
#  <date>     <int> <int>
#1 2017-01-03     2     0
#2 2017-01-04     1     2
#3 2017-01-06     3     0

Upvotes: 0

Baraliuh
Baraliuh

Reputation: 2141

library(dplyr)
library(tidyr)
your_data %>%
    group_by(Date) %>%
    count(Currency) %>%
    pivot_wider(
        names_from = Currency, 
        names_glue = '{.value.count}',
        values_from = n
        )

Upvotes: 2

Related Questions