Excel Summarising "If" Functions
"IFS" functions are filtering versions of their summarization counterparts.
Simply add "cases" that filter if a condition is true.
Multiple cases are evaluated as "AND" filtering operations.
A single case with | ("OR") bars can be created to accomplish an "OR".
See details below.
These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:
Names are similar to Excel function names
By default, missing values are ignored (same as in Excel)
SUM_IFS(x, ...) COUNT_IFS(x, ...) AVERAGE_IFS(x, ...) MEDIAN_IFS(x, ...) MIN_IFS(x, ...) MAX_IFS(x, ...) CREATE_IFS(.f, ...)
x |
A vector. Most functions are designed for numeric data.
Some functions like |
... |
Add cases to evaluate. See Details. |
.f |
A function to convert to an "IFS" function.
Use |
"AND" Filtering: Multiple cases are evaluated as "AND" filtering operations.
"OR" Filtering:
Compound single cases with | ("OR") bars can be created to accomplish an "OR".
Simply use a statement like x > 10 | x < -10 to perform an "OR" if-statement.
Creating New "Summarizing IFS" Functions:
Users can create new "IFS" functions using the CREATE_IFS() function factory.
The only requirement is that the output of your function (.f) must be a single
value (scalar). See examples below.
Summary functions return a single value
Summary Functions - Return a single value from a vector
Sum: SUM_IFS()
Center: AVERAGE_IFS(), MEDIAN_IFS()
Count: COUNT_IFS()
_Create your own summary "IF" function This is a function factory that generates summary "_IFS" functions.
library(tidyverse)
library(tidyquant)
library(timetk)
library(stringr)
library(lubridate)
# --- Basic Usage ---
SUM_IFS(x = 1:10, x > 5)
COUNT_IFS(x = letters, str_detect(x, "a|b|c"))
SUM_IFS(-10:10, x > 8 | x < -5)
# Create your own IFS function (Mind blowingly simple)!
Q75_IFS <- CREATE_IFS(.f = quantile, probs = 0.75, na.rm = TRUE)
Q75_IFS(1:10, x > 5)
# --- Usage with tidyverse ---
# Using multiple cases IFS cases to count the frequency of days with
# high trade volume in a given year
FANG %>%
group_by(symbol) %>%
summarise(
high_volume_in_2015 = COUNT_IFS(volume,
year(date) == 2015,
volume > quantile(volume, 0.75))
)
# Count negative returns by month
FANG %>%
mutate(symbol = as_factor(symbol)) %>%
group_by(symbol) %>%
# Collapse from daily to FIRST value by month
summarise_by_time(
.date_var = date,
.by = "month",
adjusted = FIRST(adjusted)
) %>%
# Calculate monthly returns
group_by(symbol) %>%
mutate(
returns = PCT_CHANGE(adjusted, fill_na = 0)
) %>%
# Find returns less than zero and count the frequency
summarise(
negative_monthly_returns = COUNT_IFS(returns, returns < 0)
)Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.