Home > Back-end >  how to convert quarterly data to different quarterly format R
how to convert quarterly data to different quarterly format R

Time:12-23

I would like to change in a data frame the structure of one column from this "1996-10-01" to "1996 Q4".

Here data:

narepurchasement <-
structure(list(Date = structure(c(844128000, 852076800, 859852800, 
867715200, 875664000, 883612800, 891388800, 899251200, 907200000, 
915148800, 922924800, 930787200, 938736000, 946684800, 954547200, 
962409600, 970358400, 978307200, 986083200, 993945600, 1001894400, 
1009843200, 1017619200, 1025481600, 1033430400, 1041379200, 1049155200, 
1057017600, 1064966400, 1072915200, 1080777600, 1088640000, 1096588800, 
1104537600, 1112313600, 1120176000, 1128124800, 1136073600, 1143849600, 
1151712000, 1159660800, 1167609600, 1175385600, 1183248000, 1191196800, 
1199145600, 1207008000, 1214870400, 1222819200, 1230768000, 1238544000, 
1246406400, 1254355200, 1262304000, 1270080000, 1277942400, 1285891200, 
1293840000, 1301616000, 1309478400, 1317427200, 1325376000, 1333238400, 
1341100800, 1349049600, 1356998400, 1364774400, 1372636800, 1380585600, 
1388534400, 1396310400, 1404172800, 1412121600, 1420070400, 1427846400, 
1435708800, 1443657600, 1451606400, 1459468800, 1467331200, 1475280000, 
1483228800, 1491004800, 1498867200, 1506816000, 1514764800, 1522540800, 
1530403200, 1538352000, 1546300800, 1554076800, 1561939200, 1569888000, 
1577836800, 1585699200, 1593561600, 1601510400, 1609459200, 1617235200, 
1625097600, 1633046400, 1640995200, 1648771200), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), NetIssuance = c("-7450", "-13950", 
"-14675", "-22875", "-25875", "-21675", "-17808", "-64840", "-111214", 
"-6920", "-76700", "-26188", "-1", "27044", "-50630", "-10731", 
"-83887", "-4850", "-14775", "-27350", "-1150", "-2644", "6357", 
"-20316", "2098", "-10173", "-3438", "0", "-2055", "-0.802", 
"-16823", "-32200", "-70730", "-43031", "-58722", "-90630", "-83784", 
"-110795", "-116977", "-107859", "-137542", "-109583", "-149516", 
"-162019", "-226618", "-84099", "-38612", "-73533", "-93475", 
"-37950", "39311", "20920", "-62302", "-35987", "-35433", "-71238", 
"-58295", "-59766", "-101392", "-133088", "-88329", "-49568", 
"-99135", "-73428", "-77876", "-38256", "-73497", "-60269", "-105274", 
"-101911", "-48493", "-80452", "-71090", "-116963", "-102404", 
"-129399", "-104711", "-127487", "-136914", "-150658", "-80792", 
"-89438", "-55464", "-119607", "-61042", "-122438", "-225035", 
"-79778", "-190075", "-174006", "-46583", "-111504", "-124927", 
"-95947", "-14946", "7398", "-67450", "-30403", "-133211", "-218291", 
"-237670", "-227868", "-135084"), GrossIssuance = c(35393, 34426, 
39963, 36586, 40630, 36993, 57637, 31110, 52737, 52487, 78711, 
65846, 95574, 113349, 86067, 75480, 71906, 54552, 64094, 39824, 
55322, 43624, 50257, 29329, 35664, 32098, 36084, 42285, 48634, 
57955, 47497, 43892, 55599, 48385, 52197, 63692, 63159, 68401, 
69557, 63825, 94723, 88627, 97967, 102944, 108022, 86316, 96002, 
93730, 75885, 64674, 77307, 62616, 66705, 54873, 57173, 48392, 
68703, 64334, 69966, 43637, 55198, 66678, 70380, 68331, 72198, 
73702, 83784, 103945, 94138, 89471, 100239, 100418, 111302, 129933, 
124281, 116589, 97678, 106734, 118234, 106262, 107965, 122679, 
115625, 107485, 112226, 106358, 99560, 101952, 91526, 95447, 
118912, 108570, 100615, 107853, 154908, 134115, 154227, 163567, 
126579, 112180, 132474, 92327, 80342), GrossRetirement = c(42843, 
48376, 54638, 59461, 66505, 58668, 75445, 95950, 163951, 59407, 
155411, 92034, 96134, 86305, 136697, 86211, 155793, 59402, 78869, 
67174, 56472, 46268, 43900, 49645, 33566, 42271, 39522, 42226, 
50689, 58757, 64320, 76092, 126329, 91416, 110919, 154322, 146943, 
179196, 186534, 171684, 232265, 198210, 247483, 264963, 334640, 
170415, 134614, 167263, 169360, 102624, 37996, 41696, 129007, 
90860, 92606, 119630, 126998, 124100, 171358, 176725, 143527, 
116246, 169515, 141759, 150074, 111958, 157281, 164214, 199412, 
191382, 148732, 180870, 182392, 246896, 226685, 245988, 202389, 
234221, 255148, 256920, 188757, 212117, 171089, 227092, 173268, 
228796, 324595, 181730, 281601, 269453, 165495, 220074, 225542, 
203800, 169854, 126717, 221677, 193970, 259790, 330471, 370144, 
320195, 215426), Repurchases = c(22263, 22638, 23514, 25005, 
34369, 26643, 29082, 41095, 27253, 31805, 30779, 29350, 35972, 
38084, 22859, 24761, 30152, 25245, 26623, 27689, 24038, 20954, 
27243, 27314, 18885, 20208, 22000, 25993, 34329, 31567, 34011, 
42358, 46643, 52980, 63201, 66599, 90778, 76295, 97243, 91990, 
96248, 92541, 121025, 121251, 98213, 94359, 75799, 80943, 45745, 
26459, 17862, 24888, 33600, 40277, 59624, 57199, 62624, 66172, 
73022, 96186, 74495, 64511, 83483, 65770, 86040, 77135, 100169, 
97375, 105120, 124551, 99652, 108215, 106062, 113685, 100343, 
122057, 107005, 123418, 99546, 75010, 89025, 93073, 81638, 84879, 
87762, 143170, 138764, 134874, 148169, 137193, 107400, 108922, 
119371, 143785, 79929, 88312, 110984, 128796, 141252, 154680, 
195502, 220050, 120000), MA = c(20579, 25738, 31124, 34456, 32136, 
32025, 46364, 54855, 136698, 27602, 124632, 62684, 60162, 48221, 
113837, 61450, 125641, 34157, 52246, 39486, 32434, 25314, 16657, 
22331, 14681, 22063, 17522, 16233, 16360, 27191, 30309, 33735, 
79686, 38436, 47718, 87723, 56166, 102901, 89291, 79694, 136016, 
105669, 126458, 143711, 236427, 76055, 58816, 86320, 123615, 
76166, 20134, 16809, 95407, 50583, 32982, 62430, 64373, 57928, 
98336, 80539, 69032, 51735, 86032, 75988, 64033, 34823, 57112, 
66838, 94292, 66831, 49079, 72655, 76330, 133211, 126342, 123931, 
95384, 110803, 155602, 181911, 99732, 119044, 89451, 142213, 
85506, 85626, 185832, 46856, 133432, 132260, 58095, 111152, 106172, 
60015, 89925, 38404, 110693, 65174, 118539, 175791, 174642, 100146, 
95426), GDP = c(8259.771, 8362.655, 8518.825, 8662.823, 8765.907, 
8866.48, 8969.699, 9121.097, 9293.991, 9411.682, 9526.21, 9686.626, 
9900.169, 10002.179, 10247.72, 10318.165, 10435.744, 10470.231, 
10599, 10598.02, 10660.465, 10783.5, 10887.46, 10984.04, 11061.433, 
11174.129, 11312.766, 11566.669, 11772.234, 11923.447, 12112.815, 
12305.307, 12527.214, 12767.286, 12922.656, 13142.642, 13324.204, 
13599.16, 13753.424, 13870.188, 14039.56, 14215.651, 14402.082, 
14564.117, 14715.058, 14706.538, 14865.701, 14898.999, 14608.208, 
14430.901, 14381.236, 14448.882, 14651.248, 14764.611, 14980.193, 
15141.605, 15309.471, 15351.444, 15557.535, 15647.681, 15842.267, 
16068.824, 16207.13, 16319.54, 16420.386, 16629.05, 16699.551, 
16911.068, 17133.114, 17144.281, 17462.703, 17743.227, 17852.54, 
17991.348, 18193.707, 18306.96, 18332.079, 18425.306, 18611.617, 
18775.459, 18968.041, 19148.194, 19304.506, 19561.896, 19894.75, 
20155.486, 20470.197, 20687.278, 20819.269, 21013.085, 21272.448, 
21531.839, 21706.532, 21538.032, 19636.731, 21362.428, 21704.706, 
22313.85, 23046.934, 23550.42, 24349.121, 24740.48, 25248.476
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-103L))

I really didn't know how to start

CodePudding user response:

Here's an approach using lubridate. I had problems importing your structure so I extracted just the Date part.

library(lubridate)

paste0(format(Dates, "%Y"), " Q", quarter(Dates))
  [1] "1996 Q4" "1997 Q1" "1997 Q2" "1997 Q3" "1997 Q4" "1998 Q1" "1998 Q2"
  [8] "1998 Q3" "1998 Q4" "1999 Q1" "1999 Q2" "1999 Q3" "1999 Q4" "2000 Q1"
 [15] "2000 Q2" "2000 Q3" "2000 Q4" "2001 Q1" "2001 Q2" "2001 Q3" "2001 Q4"
 [22] "2002 Q1" "2002 Q2" "2002 Q3" "2002 Q4" "2003 Q1" "2003 Q2" "2003 Q3"
 [29] "2003 Q4" "2004 Q1" "2004 Q2" "2004 Q3" "2004 Q4" "2005 Q1" "2005 Q2"
 [36] "2005 Q3" "2005 Q4" "2006 Q1" "2006 Q2" "2006 Q3" "2006 Q4" "2007 Q1"
 [43] "2007 Q2" "2007 Q3" "2007 Q4" "2008 Q1" "2008 Q2" "2008 Q3" "2008 Q4"
 [50] "2009 Q1" "2009 Q2" "2009 Q3" "2009 Q4" "2010 Q1" "2010 Q2" "2010 Q3"
 [57] "2010 Q4" "2011 Q1" "2011 Q2" "2011 Q3" "2011 Q4" "2012 Q1" "2012 Q2"
 [64] "2012 Q3" "2012 Q4" "2013 Q1" "2013 Q2" "2013 Q3" "2013 Q4" "2014 Q1"
 [71] "2014 Q2" "2014 Q3" "2014 Q4" "2015 Q1" "2015 Q2" "2015 Q3" "2015 Q4"
 [78] "2016 Q1" "2016 Q2" "2016 Q3" "2016 Q4" "2017 Q1" "2017 Q2" "2017 Q3"
 [85] "2017 Q4" "2018 Q1" "2018 Q2" "2018 Q3" "2018 Q4" "2019 Q1" "2019 Q2"
 [92] "2019 Q3" "2019 Q4" "2020 Q1" "2020 Q2" "2020 Q3" "2020 Q4" "2021 Q1"
 [99] "2021 Q2" "2021 Q3" "2021 Q4" "2022 Q1" "2022 Q2"

Data

Dates <- structure(c(844128000, 852076800, 859852800, 867715200, 875664000, 
883612800, 891388800, 899251200, 907200000, 915148800, 922924800, 
930787200, 938736000, 946684800, 954547200, 962409600, 970358400, 
978307200, 986083200, 993945600, 1001894400, 1009843200, 1017619200, 
1025481600, 1033430400, 1041379200, 1049155200, 1057017600, 1064966400, 
1072915200, 1080777600, 1088640000, 1096588800, 1104537600, 1112313600, 
1120176000, 1128124800, 1136073600, 1143849600, 1151712000, 1159660800, 
1167609600, 1175385600, 1183248000, 1191196800, 1199145600, 1207008000, 
1214870400, 1222819200, 1230768000, 1238544000, 1246406400, 1254355200, 
1262304000, 1270080000, 1277942400, 1285891200, 1293840000, 1301616000, 
1309478400, 1317427200, 1325376000, 1333238400, 1341100800, 1349049600, 
1356998400, 1364774400, 1372636800, 1380585600, 1388534400, 1396310400, 
1404172800, 1412121600, 1420070400, 1427846400, 1435708800, 1443657600, 
1451606400, 1459468800, 1467331200, 1475280000, 1483228800, 1491004800, 
1498867200, 1506816000, 1514764800, 1522540800, 1530403200, 1538352000, 
1546300800, 1554076800, 1561939200, 1569888000, 1577836800, 1585699200, 
1593561600, 1601510400, 1609459200, 1617235200, 1625097600, 1633046400, 
1640995200, 1648771200), class = c("POSIXct", "POSIXt"), tzone = "UTC")

CodePudding user response:

Convert to yearqtr class which represents a year and quarter internally as year and fractions of a year (0, 0.25, 0.5, 0.75) so it is easily manipulated, e.g. add 1 to get next year or add 1/4 to get next quarter.

library(dplyr)
library(zoo)

narepurchasement %>%
  mutate(Date = as.yearqtr(Date))

giving

# A tibble: 103 × 7
   Date      NetIssuance GrossIssuance GrossRetirement Repurchases     MA   GDP
   <yearqtr> <chr>               <dbl>           <dbl>       <dbl>  <dbl> <dbl>
 1 1996 Q4   -7450               35393           42843       22263  20579 8260.
 2 1997 Q1   -13950              34426           48376       22638  25738 8363.
 3 1997 Q2   -14675              39963           54638       23514  31124 8519.
 4 1997 Q3   -22875              36586           59461       25005  34456 8663.
 5 1997 Q4   -25875              40630           66505       34369  32136 8766.
 6 1998 Q1   -21675              36993           58668       26643  32025 8866.
 7 1998 Q2   -17808              57637           75445       29082  46364 8970.
 8 1998 Q3   -64840              31110           95950       41095  54855 9121.
 9 1998 Q4   -111214             52737          163951       27253 136698 9294.
10 1999 Q1   -6920               52487           59407       31805  27602 9412.
# … with 93 more rows
# ℹ Use `print(n = ...)` to see more rows
  •  Tags:  
  • r
  • Related