Reputation: 119
I have this banking panel data which is in monthly frequency. I want to convert to a quarterly frequency using the mean of the three months in a given quarter. Below is the head() of my data frame:
db %>% select(Data,Bank,PC) %>% head()
# A tibble: 6 x 3
Data Bank PC
<date> <chr> <dbl>
1 2017-01-01 BANCO BM&FBOVESPA 502630099.
2 2017-01-01 BANCO BONSUCESSO S.A. 1716340938.
3 2017-01-01 BANCO BRADESCARD 5334785523.
4 2017-01-01 BANCO BTG PACTUAL S.A. 98935990703.
5 2017-01-01 BANCO CBSS 596039238.
6 2017-01-01 BANCO CIFRA 116806412.
This dataset contains monthly data for the year 2017 for more than 100 banks. I need to convert each bank series into a quarterly frequency. So in the example above, I need to compute the mean of PC for each quarter of the year for each bank in the sample. In other words, I need to change the frequency of my Panel data from monthly to quarterly. How can I accomplish data in R?
Below is a subset of my data if anyone wants to try it.
structure(list(Data = structure(c(17167, 17198, 17226, 17257,
17287, 17318, 17348, 17379, 17410, 17440, 17471, 17501), class = "Date"),
Bank = c("BANCO ORIGINAL", "BANCO ORIGINAL", "BANCO ORIGINAL",
"BANCO ORIGINAL", "BANCO ORIGINAL", "BANCO ORIGINAL", "BANCO ORIGINAL",
"BANCO ORIGINAL", "BANCO ORIGINAL", "BANCO ORIGINAL", "BANCO ORIGINAL",
"BANCO ORIGINAL"), Taxonomy = c("BANCOS MULTIPLOS", "BANCOS MULTIPLOS",
"BANCOS MULTIPLOS", "BANCOS MULTIPLOS", "BANCOS MULTIPLOS",
"BANCOS MULTIPLOS", "BANCOS MULTIPLOS", "BANCOS MULTIPLOS",
"BANCOS MULTIPLOS", "BANCOS MULTIPLOS", "BANCOS MULTIPLOS",
"BANCOS MULTIPLOS"), Liq = c(1.1997748808169, 1.19259719096416,
1.17984955128666, 1.18695199874656, 1.19834389025218, 1.22404552882798,
1.24812868632381, 1.25155515564228, 1.23908341171122, 1.22923764062342,
1.22573014109315, 1.19451856437255), Cap = c(0.0564130001266887,
0.0511332310836733, 0.055115912263737, 0.0610766393321285,
0.0516610760871821, 0.0519465686269887, 0.0582801135631064,
0.053508898053011, 0.0557890616137837, 0.0639122025397535,
0.0588865316259913, 0.0534625111456079), Size = c(24.3393816873135,
24.4387493714898, 24.3615598295339, 24.2589048360285, 24.4251409240454,
24.4154251928763, 24.3141208787945, 24.4120044047441, 24.3647597828241,
24.219599367394, 24.304650218349, 24.3922876799839), risk = c(0.220292714085706,
0.226351022665361, 0.225513481661864, 0.261226698162742,
0.268547230949181, 0.193220911345295, 0.189681335841555,
0.203642574652873, 0.17111480849187, 0.198715216005694, 0.189870257131344,
0.13419221010663), profitability = c(-0.01753800950183, -0.0175186818010891,
-0.017557013896287, -0.0175563818325023, -0.0175772644337278,
-0.0176513268874473, -0.0161626726677722, -0.015962366730429,
-0.0160506385110374, -0.0161994381957536, -0.0161484427069834,
-0.0162943410707466)), row.names = c(NA, -12L), class = c("tbl_df",
"tbl", "data.frame"))
Upvotes: 1
Views: 541
Reputation: 263362
You can divide the month value by three (and add one to shift qtr
to 1:4). You may want to paste on a year value if the data spans multiple years:
aggregate(dat[4:8], list( qtr=as.POSIXlt(dat$Data)$mon%/%3 +1, bank=dat$Bank), mean)
qtr bank Liq Cap Size risk profitability
1 1 BANCO ORIGINAL 1.190741 0.05422071 24.37990 0.2240524 -0.01753790
2 2 BANCO ORIGINAL 1.203114 0.05489476 24.36649 0.2409983 -0.01759499
3 3 BANCO ORIGINAL 1.246256 0.05585936 24.36363 0.1881462 -0.01605856
4 4 BANCO ORIGINAL 1.216495 0.05875375 24.30551 0.1742592 -0.01621407
Turns out there is a yearqtr class in zoo that would be even better:
library(zoo)
aggregate(dat[4:8], list( qtr=as.yearqtr(dat$Data), bank=dat$Bank), mean)
qtr bank Liq Cap Size risk profitability
1 2017 Q1 BANCO ORIGINAL 1.190741 0.05422071 24.37990 0.2240524 -0.01753790
2 2017 Q2 BANCO ORIGINAL 1.203114 0.05489476 24.36649 0.2409983 -0.01759499
3 2017 Q3 BANCO ORIGINAL 1.246256 0.05585936 24.36363 0.1881462 -0.01605856
4 2017 Q4 BANCO ORIGINAL 1.216495 0.05875375 24.30551 0.1742592 -0.01621407
Upvotes: 1
Reputation: 4480
If you use the library lubridate
you can generate the quarter:
library(lubridate)
db %>% mutate(qData = quarter(Data))
Best!
Upvotes: 0