Hi, I am a beginner at using r and r studio, and i have bumped into an issue regarding the dplyr package. I have been presented with a task of converting daily stock data into monthly stock data with its average.
The problem is that rather than giving me a monthly average, my codes are generating the entire data average and displaying it on every month.
these are my codes:
library(dplyr)
X10_Stocks_ToF <- X10_Stocks_ToF %>% # this is my daily stock data
mutate(MonthYear = format(Date, "%Y-%m"))
M_Stocks <- X10_Stocks_ToF %>% # converting it to monthly
group_by(MonthYear) %>%
summarise(EAB_RTNs = mean(R_EAB)) %>% # R_EAB are the daily returns of a stock
arrange(MonthYear)
the image above shows the problem I stated above
I'm not sure if I'm missing a certain line of code or that I haven't written them properly, but I need help... I would highly appreciate assistance on this
these are some samples from my dataset
> SampleSet <- data.frame(sample_set$Date,sample_set$`EAST AFRICAN BREWERIES`)
> dput(SampleSet)
structure(list(sample_set.Date = structure(c(1141948800, 1142208000,
1142294400, 1142380800, 1142467200, 1142553600, 1142812800, 1142899200,
1142985600, 1143072000, 1143158400, 1143417600, 1143504000, 1143590400,
1143676800, 1143763200, 1144022400, 1144108800, 1144195200, 1144281600,
1144368000, 1144627200, 1144713600, 1144800000, 1144886400, 1144972800,
1145232000, 1145318400, 1145404800, 1145491200, 1145577600, 1145836800,
1145923200, 1146009600, 1146096000, 1146182400, 1146441600, 1146528000,
1146614400, 1146700800, 1146787200, 1147046400, 1147132800, 1147219200,
1147305600, 1147392000, 1147651200, 1147737600, 1147824000, 1147910400,
1147996800, 1148256000, 1148342400, 1148428800, 1148515200, 1148601600,
1148860800, 1148947200, 1149033600, 1149120000, 1149206400, 1149465600,
1149552000, 1149638400, 1149724800, 1149811200, 1150070400, 1150156800,
1150243200, 1150329600, 1150416000, 1150675200, 1150761600, 1150848000,
1150934400, 1151020800, 1151280000, 1151366400, 1151452800, 1151539200,
1151625600, 1151884800, 1151971200, 1152057600, 1152144000, 1152230400,
1152489600, 1152576000, 1152662400, 1152748800, 1152835200, 1153094400,
1153180800, 1153267200, 1153353600, 1153440000, 1153699200, 1153785600,
1153872000, 1153958400, 1154044800, 1154304000, 1154390400, 1154476800,
1154563200, 1154649600, 1154908800, 1154995200, 1155081600, 1155168000,
1155254400, 1155513600, 1155600000, 1155686400, 1155772800, 1155859200,
1156118400, 1156204800, 1156291200, 1156377600, 1156464000, 1156723200,
1156809600, 1156896000, 1156982400, 1157068800, 1157328000, 1157414400,
1157500800, 1157587200, 1157673600, 1157932800, 1158019200, 1158105600,
1158192000, 1158278400, 1158537600, 1158624000, 1158710400, 1158796800,
1158883200, 1159142400, 1159228800, 1159315200, 1159401600, 1159488000,
1159747200, 1159833600, 1159920000, 1160006400, 1160092800, 1160352000,
1160438400, 1160524800, 1160611200, 1160697600, 1160956800, 1161043200,
1161129600, 1161216000, 1161302400, 1161561600, 1161648000, 1161734400,
1161820800, 1161907200, 1162166400, 1162252800, 1162339200, 1162425600,
1162512000, 1162771200, 1162857600, 1162944000, 1163030400, 1163116800,
1163376000, 1163462400, 1163548800, 1163635200, 1163721600, 1163980800,
1164067200, 1164153600, 1164240000, 1164326400, 1164585600, 1164672000,
1164758400, 1164844800, 1164931200, 1165190400, 1165276800, 1165363200,
1165449600, 1165536000, 1165795200, 1165881600, 1165968000, 1166054400,
1166140800, 1166400000, 1166486400, 1166572800, 1166659200, 1166745600,
1167004800, 1167091200, 1167177600, 1167264000, 1167350400, 1167609600,
1167696000, 1167782400, 1167868800, 1167955200, 1168214400, 1168300800,
1168387200, 1168473600, 1168560000, 1168819200, 1168905600, 1168992000,
1169078400, 1169164800, 1169424000, 1169510400, 1169596800, 1169683200,
1169769600, 1170028800, 1170115200, 1170201600), class = c("POSIXct",
"POSIXt"), tzone = "UTC"), sample_set..EAST.AFRICAN.BREWERIES. = c(107.5,
107.5, 106.67, 106.67, 106.67, 106.67, 106.67, 107.5, 108.33,
110.83, 110.83, 111.67, 110.83, 110.83, 110.83, 110.83, 110.83,
110.83, 110, 110, 110, 109.17, 109.17, 108.33, 107.5, 107.5,
107.5, 108.33, 108.33, 110, 110, 109.17, 109.17, 109.17, 110,
109.17, 109.17, 108.33, 109.17, 110, 111.67, 112.5, 112.5, 112.5,
113.33, 119.17, 123.33, 120.83, 120.83, 119.17, 119.17, 119.17,
118.33, 118.33, 117.5, 119.17, 116.67, 115.83, 116.67, 116.67,
115, 115.83, 115.83, 115, 115.83, 115, 115.83, 115.83, 115.83,
115.83, 115.83, 115.83, 115.83, 115.83, 115.83, 115.83, 115.83,
115, 114.17, 115, 115.83, 115, 115, 115, 115, 115, 114.17, 114.17,
115, 115, 115, 114.17, 114.17, 114.17, 114.17, 111.67, 112.5,
112.5, 112.5, 111.67, 111.67, 111.67, 111.67, 121.67, 110.83,
111.67, 111.67, 111.67, 111.67, 111.67, 112.5, 113.33, 115, 114.17,
115, 115, 114.17, 115, 115, 118.33, 119.17, 121.67, 121.67, 120.83,
120, 117.5, 119.17, 116.67, 117.5, 117.5, 117.5, 116.67, 117.5,
117.5, 117.5, 117.5, 117.5, 117.5, 117.5, 119.17, 120.83, 118.33,
120, 120.83, 121.67, 122.5, 125, 124.17, 125, 126.67, 127.5,
130.83, 130.83, 130, 131.67, 131.67, 127.5, 125, 120.83, 120,
120, 118.33, 118.33, 119.17, 123.33, 123.33, 120.83, 116.67,
119.17, 119.17, 119.17, 119.17, 119.17, 120.83, 120, 122.5, 120.83,
120.83, 120, 120.83, 120.83, 120, 120, 120.83, 120, 117.5, 119.17,
120, 115.83, 116.67, 115.83, 113.33, 111.67, 113.33, 115.83,
115, 116.67, 116.67, 116.67, 116.67, 117.5, 115.83, 117.5, 115.83,
115, 115, 115, 115, 111.67, 114.17, 115.83, 115.83, 125, 116.67,
116.67, 117.5, 118.33, 120, 124.17, 124.17, 123.33, 122.5, 121.67,
122.5, 120, 123.33, 122.5, 121.67, 123.33, 123.33, 121.67, 125,
121.67, 123.33)), class = "data.frame", row.names = c(NA, -234L
))
> SampleSet
sample_set.Date sample_set..EAST.AFRICAN.BREWERIES.
1 2006-03-10 107.50
2 2006-03-13 107.50
3 2006-03-14 106.67
4 2006-03-15 106.67
5 2006-03-16 106.67
6 2006-03-17 106.67
7 2006-03-20 106.67
8 2006-03-21 107.50
9 2006-03-22 108.33
10 2006-03-23 110.83
11 2006-03-24 110.83
12 2006-03-27 111.67
13 2006-03-28 110.83
14 2006-03-29 110.83
15 2006-03-30 110.83
16 2006-03-31 110.83
17 2006-04-03 110.83
18 2006-04-04 110.83
19 2006-04-05 110.00
20 2006-04-06 110.00
21 2006-04-07 110.00
22 2006-04-10 109.17
23 2006-04-11 109.17
24 2006-04-12 108.33
25 2006-04-13 107.50
26 2006-04-14 107.50
27 2006-04-17 107.50
28 2006-04-18 108.33
29 2006-04-19 108.33
30 2006-04-20 110.00
31 2006-04-21 110.00
32 2006-04-24 109.17
33 2006-04-25 109.17
34 2006-04-26 109.17
35 2006-04-27 110.00
36 2006-04-28 109.17
37 2006-05-01 109.17
38 2006-05-02 108.33
39 2006-05-03 109.17
40 2006-05-04 110.00
41 2006-05-05 111.67
42 2006-05-08 112.50
43 2006-05-09 112.50
44 2006-05-10 112.50
45 2006-05-11 113.33
46 2006-05-12 119.17
47 2006-05-15 123.33
48 2006-05-16 120.83
49 2006-05-17 120.83
50 2006-05-18 119.17
51 2006-05-19 119.17
52 2006-05-22 119.17
53 2006-05-23 118.33
54 2006-05-24 118.33
55 2006-05-25 117.50
56 2006-05-26 119.17
57 2006-05-29 116.67
58 2006-05-30 115.83
59 2006-05-31 116.67
60 2006-06-01 116.67
61 2006-06-02 115.00
62 2006-06-05 115.83
63 2006-06-06 115.83
64 2006-06-07 115.00
65 2006-06-08 115.83
66 2006-06-09 115.00
67 2006-06-12 115.83
68 2006-06-13 115.83
69 2006-06-14 115.83
70 2006-06-15 115.83
71 2006-06-16 115.83
72 2006-06-19 115.83
73 2006-06-20 115.83
74 2006-06-21 115.83
75 2006-06-22 115.83
76 2006-06-23 115.83
77 2006-06-26 115.83
78 2006-06-27 115.00
79 2006-06-28 114.17
80 2006-06-29 115.00
81 2006-06-30 115.83
82 2006-07-03 115.00
83 2006-07-04 115.00
84 2006-07-05 115.00
85 2006-07-06 115.00
86 2006-07-07 115.00
87 2006-07-10 114.17
88 2006-07-11 114.17
89 2006-07-12 115.00
90 2006-07-13 115.00
91 2006-07-14 115.00
92 2006-07-17 114.17
93 2006-07-18 114.17
94 2006-07-19 114.17
95 2006-07-20 114.17
96 2006-07-21 111.67
97 2006-07-24 112.50
98 2006-07-25 112.50
99 2006-07-26 112.50
100 2006-07-27 111.67
101 2006-07-28 111.67
102 2006-07-31 111.67
103 2006-08-01 111.67
104 2006-08-02 121.67
105 2006-08-03 110.83
106 2006-08-04 111.67
107 2006-08-07 111.67
108 2006-08-08 111.67
109 2006-08-09 111.67
110 2006-08-10 111.67
111 2006-08-11 112.50
112 2006-08-14 113.33
113 2006-08-15 115.00
114 2006-08-16 114.17
115 2006-08-17 115.00
116 2006-08-18 115.00
117 2006-08-21 114.17
118 2006-08-22 115.00
119 2006-08-23 115.00
120 2006-08-24 118.33
121 2006-08-25 119.17
122 2006-08-28 121.67
123 2006-08-29 121.67
124 2006-08-30 120.83
125 2006-08-31 120.00
126 2006-09-01 117.50
127 2006-09-04 119.17
128 2006-09-05 116.67
129 2006-09-06 117.50
130 2006-09-07 117.50
131 2006-09-08 117.50
132 2006-09-11 116.67
133 2006-09-12 117.50
134 2006-09-13 117.50
135 2006-09-14 117.50
136 2006-09-15 117.50
137 2006-09-18 117.50
138 2006-09-19 117.50
139 2006-09-20 117.50
140 2006-09-21 119.17
141 2006-09-22 120.83
142 2006-09-25 118.33
143 2006-09-26 120.00
144 2006-09-27 120.83
145 2006-09-28 121.67
146 2006-09-29 122.50
147 2006-10-02 125.00
148 2006-10-03 124.17
149 2006-10-04 125.00
150 2006-10-05 126.67
151 2006-10-06 127.50
152 2006-10-09 130.83
153 2006-10-10 130.83
154 2006-10-11 130.00
155 2006-10-12 131.67
156 2006-10-13 131.67
157 2006-10-16 127.50
158 2006-10-17 125.00
159 2006-10-18 120.83
160 2006-10-19 120.00
161 2006-10-20 120.00
162 2006-10-23 118.33
163 2006-10-24 118.33
164 2006-10-25 119.17
165 2006-10-26 123.33
166 2006-10-27 123.33
167 2006-10-30 120.83
168 2006-10-31 116.67
169 2006-11-01 119.17
170 2006-11-02 119.17
171 2006-11-03 119.17
172 2006-11-06 119.17
173 2006-11-07 119.17
174 2006-11-08 120.83
175 2006-11-09 120.00
176 2006-11-10 122.50
177 2006-11-13 120.83
178 2006-11-14 120.83
179 2006-11-15 120.00
180 2006-11-16 120.83
181 2006-11-17 120.83
182 2006-11-20 120.00
183 2006-11-21 120.00
184 2006-11-22 120.83
185 2006-11-23 120.00
186 2006-11-24 117.50
187 2006-11-27 119.17
188 2006-11-28 120.00
189 2006-11-29 115.83
190 2006-11-30 116.67
191 2006-12-01 115.83
192 2006-12-04 113.33
193 2006-12-05 111.67
194 2006-12-06 113.33
195 2006-12-07 115.83
196 2006-12-08 115.00
197 2006-12-11 116.67
198 2006-12-12 116.67
199 2006-12-13 116.67
200 2006-12-14 116.67
201 2006-12-15 117.50
202 2006-12-18 115.83
203 2006-12-19 117.50
204 2006-12-20 115.83
205 2006-12-21 115.00
206 2006-12-22 115.00
207 2006-12-25 115.00
208 2006-12-26 115.00
209 2006-12-27 111.67
210 2006-12-28 114.17
211 2006-12-29 115.83
212 2007-01-01 115.83
213 2007-01-02 125.00
214 2007-01-03 116.67
215 2007-01-04 116.67
216 2007-01-05 117.50
217 2007-01-08 118.33
218 2007-01-09 120.00
219 2007-01-10 124.17
220 2007-01-11 124.17
221 2007-01-12 123.33
222 2007-01-15 122.50
223 2007-01-16 121.67
224 2007-01-17 122.50
225 2007-01-18 120.00
226 2007-01-19 123.33
227 2007-01-22 122.50
228 2007-01-23 121.67
229 2007-01-24 123.33
230 2007-01-25 123.33
231 2007-01-26 121.67
232 2007-01-29 125.00
233 2007-01-30 121.67
234 2007-01-31 123.33