dplyr - grouping and summarizing issues

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

Hi @JVR, welcome to RStudio Community.

Would it be possible for you to share a small sample of your data set? Maybe pick a single stock and values for a few days from multiple months. Once you have created this subset, you can share it with us in an easy-to-reproduce format by using the dput() function.

For example, if your data frame is named sample_df, run dput(sample_df) and paste the output in your post.

1 Like

Hi @siddharthprabhu , I have put up some samples for some few days of some few months, I hope these helps.

Thanks. The same code you've written works with the sample data set. However, the numbers are quite different from what you've shown in the screenshot. Are you performing any other transformation on the data?

SampleSet %>% 
  mutate(MonthYear = format(sample_set.Date, "%Y-%m")) %>% 
  group_by(MonthYear) %>%
  summarise(EAB_RTNs = mean(sample_set..EAST.AFRICAN.BREWERIES.)) %>%  # R_EAB are the daily returns of a stock 
  arrange(MonthYear)
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 11 x 2
   MonthYear EAB_RTNs
   <chr>        <dbl>
 1 2006-03       109.
 2 2006-04       109.
 3 2006-05       116.
 4 2006-06       116.
 5 2006-07       114.
 6 2006-08       115.
 7 2006-09       118.
 8 2006-10       124.
 9 2006-11       120.
10 2006-12       115.
11 2007-01       121.

the data i sent are some of the prices of that particular stock, and the screenshot was for their returns, however i faced the same issue while doing both.
I have done some basic statistics on these; generating the mean, log RTNs, co-variances and tests such as skewness, kurtosis and the Jaque-Bera, and a few plots, is it possible that these might have caused some interference?

Hard to say. Maybe try again in a fresh R session and see if you encounter the problem?

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.