********************************************

Author: DataAnayltic

Description Creating Excel style pivotables

********************************************

#Watch the video on YouTube to see the explanation

YouTube Video link

Create excel style pivot tables in R. There is lots of functionality in tidyquant package. Countifs is one of such feature which is similar to Excel.

library(tidyquant)   # for creating pivots
library(flextable)   # for data display
library(dplyr)       # for data manipulation
library(lubridate)   # for date handling
library(scales)      # for handing the number formats

Prepare your sample data

YouTube Video link https://youtu.be/5tvGGeJWIPM

# Get data
df <- tidyquant::tq_get(x = "IBM")
flextable(head(df))

symbol	date	open	high	low	close	volume	adjusted
IBM	2013-01-02	185.5545	187.7151	185.2773	187.7151	4,428,869	122.3938
IBM	2013-01-03	187.0650	187.6577	185.8891	186.6826	3,812,356	121.7206
IBM	2013-01-04	185.6501	185.9082	184.3021	185.4589	3,535,689	120.9227
IBM	2013-01-07	184.8948	185.2581	183.8815	184.6463	2,993,966	120.3929
IBM	2013-01-08	184.4359	184.7992	183.1740	184.3881	3,166,137	120.2246
IBM	2013-01-09	184.9713	184.9809	183.2218	183.8623	3,359,752	119.8817

Modify data to suit our needs

YouTube Video link https://youtu.be/5tvGGeJWIPM

df <- df%>%
  dplyr::mutate(year =  lubridate::year(date))%>% 
  dplyr::mutate(month = lubridate::month(date
                                         , label= TRUE
                                         ,abbr = FALSE))%>%
  dplyr::select(year,month,adjusted)

flextable(head(df))

year	month	adjusted
2,013	January	122.3938
2,013	January	121.7206
2,013	January	120.9227
2,013	January	120.3929
2,013	January	120.2246
2,013	January	119.8817

Create your pivot table

YouTube Video link https://youtu.be/5tvGGeJWIPM

# Create the pivot table
tbl1 <- df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                       , .columns  =  ~ year
                       , .values   =  ~ scales::number(mean(adjusted)
                                                       ,accuracy = 0.01))  
flextable(tbl1)

month	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
January	122.97	116.96	101.59	86.30	119.12	118.76	94.39	109.45	105.15	122.89	136.06
February	125.57	115.21	104.73	86.35	125.51	112.89	104.64	118.98	101.96	120.66	130.36
March	132.07	119.85	104.74	97.55	124.20	114.14	106.44	90.50	108.52	118.31	123.85
April	128.16	123.82	108.29	101.43	117.55	110.58	108.68	95.52	116.32	122.17	125.09
May	128.96	120.66	113.63	101.44	108.67	105.98	104.20	98.25	123.46	125.73	123.23
June	126.32	117.61	110.38	103.85	109.26	105.86	105.22	100.86	125.93	130.83	132.40
July	122.23	122.76	108.80	108.52	106.65	107.12	112.71	99.62	120.66	126.65	135.12
August	118.16	122.19	101.60	110.96	102.09	108.56	106.39	102.68	121.93	126.41	143.30
September	119.31	123.78	96.80	108.51	103.96	111.09	111.47	100.45	118.93	119.78	146.22
October	114.22	113.72	97.29	106.31	108.66	102.41	108.43	98.75	118.89	119.97	140.60
November	115.14	105.57	92.04	110.54	109.19	91.14	107.27	97.98	108.93	137.56
December	113.82	104.12	92.73	115.46	111.57	88.78	106.38	104.29	115.55	139.30

Bit of theming in flextable

YouTube Video link https://youtu.be/5tvGGeJWIPM

flextable(tbl1)%>%
  theme_zebra(odd_header = "gold"
              ,odd_body = "lightblue")%>%
  theme_box()

month	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
January	122.97	116.96	101.59	86.30	119.12	118.76	94.39	109.45	105.15	122.89	136.06
February	125.57	115.21	104.73	86.35	125.51	112.89	104.64	118.98	101.96	120.66	130.36
March	132.07	119.85	104.74	97.55	124.20	114.14	106.44	90.50	108.52	118.31	123.85
April	128.16	123.82	108.29	101.43	117.55	110.58	108.68	95.52	116.32	122.17	125.09
May	128.96	120.66	113.63	101.44	108.67	105.98	104.20	98.25	123.46	125.73	123.23
June	126.32	117.61	110.38	103.85	109.26	105.86	105.22	100.86	125.93	130.83	132.40
July	122.23	122.76	108.80	108.52	106.65	107.12	112.71	99.62	120.66	126.65	135.12
August	118.16	122.19	101.60	110.96	102.09	108.56	106.39	102.68	121.93	126.41	143.30
September	119.31	123.78	96.80	108.51	103.96	111.09	111.47	100.45	118.93	119.78	146.22
October	114.22	113.72	97.29	106.31	108.66	102.41	108.43	98.75	118.89	119.97	140.60
November	115.14	105.57	92.04	110.54	109.19	91.14	107.27	97.98	108.93	137.56
December	113.82	104.12	92.73	115.46	111.57	88.78	106.38	104.29	115.55	139.30

Median

YouTube Video link https://youtu.be/5tvGGeJWIPM

# Median price
df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ median(adjusted) )

Median

You can also use capital letters for median (MEDIAN) YouTube Video link https://youtu.be/5tvGGeJWIPM

# In this example I used capital letters in MEDIAN 
#and it worked
df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ MEDIAN(adjusted) )

Count

YouTube Video link https://youtu.be/5tvGGeJWIPM

# # lower case count does not work
# df%>%
#   tidyquant::pivot_table(.rows     =  ~ month
#                          , .columns  =  ~ year

# It is better to use capital letters in COUNT

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ COUNT(adjusted) )

Show when and how many times the price hit the maximum

YouTube Video link https://youtu.be/5tvGGeJWIPM

# When was the price highest and how many times
max(df$adjusted)

FALSE [1] 149.83

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ COUNT_IFS(adjusted
                                                     , x == max(df$adjusted)) )

Using COUNT_IFS functionality

YouTube Video link https://youtu.be/5tvGGeJWIPM

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ COUNT_IFS(adjusted
                                                     , adjusted > quantile(adjusted, 0.75)))

Get the price on the first of the month

YouTube Video link https://youtu.be/5tvGGeJWIPM

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ first(adjusted) )

Get the price on the last of the month

YouTube Video link https://youtu.be/5tvGGeJWIPM

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~ last(adjusted) )

Get standard deviation of the price for each month

YouTube Video link https://youtu.be/5tvGGeJWIPM

df%>%
  tidyquant::pivot_table(.rows        =  ~ month
                         , .columns   =  ~ year
                         , .values    = ~ sd(adjusted))

Get the monthly rate of returns

By comparing the difference between the price on the first and the last day of each month. You can see if the percentage return went up or down each month. YouTube Video link https://youtu.be/5tvGGeJWIPM

df%>%
  tidyquant::pivot_table(.rows     =  ~ month
                         , .columns  =  ~ year
                         , .values     = ~scales::percent(PCT_CHANGE_FIRSTLAST(adjusted))  )

Watch this script in action on YouTube YouTube Video link https://youtu.be/5tvGGeJWIPM

Creating Excel style pivotables in R

********************************************

Author: DataAnayltic

Description Creating Excel style pivotables

********************************************

YouTube Video link

Prepare your sample data

Modify data to suit our needs

Create your pivot table

Bit of theming in flextable

Median

Median

Count

Show when and how many times the price hit the maximum

Using COUNT_IFS functionality

Get the price on the first of the month

Get the price on the last of the month

Get standard deviation of the price for each month

Get the monthly rate of returns