1

I have a dataset that has daily values. I want to find the monthly average of the values of columns. The following code used to work for me but I don't understand why, it doesn't work anymore. It gives me data1 as 1 obs of 1 variable which is NA.

data %>% group_by(month=floor_date(Timestamp, "month")) %>%
   summarize(USDTRY=mean(USDTRY)) -> data1

The following is how my data looks:

dput(head(data))
structure(list(Timestamp = structure(c(1629417600, 1629331200, 
1629244800, 1629158400, 1629072000, 1628812800), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), USDTRY = c(8.4852, 8.4939, 8.4485, 8.4284, 8.453, 
8.5171), EURTRY = c(9.9325, 9.9311, 9.8916, 9.8746, 9.9618, 10.0539
), EURUSD = c(1.1696, 1.1674, 1.171, 1.1708, 1.1777, 1.1791), 
    BIST100 = c(1444.63, 1439.86, 1449.59, 1461.69, 1455.25, 
    1447.64), TR2YT = c(18.01, 18.01, 18.01, 18.01, 18.01, 18.15
    ), TR10YT = c(16.88, 16.87, 16.79, 16.8, 16.69, 16.77), TR_EURBON_2 = c(3.648673, 
    3.63085, 3.611969, 3.572728, 3.567871, 3.559959), TR_EURBON_10 = c(6.302608, 
    6.307343, 6.276473, 6.240502, 6.255035, 6.301358), BRENT = c(65.18, 
    66.45, 68.23, 69.03, 69.51, 70.59), WTI = c(62.32, 63.69, 
    65.46, 66.59, 67.29, 68.44), Altın = c(1780.8668, 1780.179, 
    1787.59, 1785.9556, 1787.2383, 1779.1515), Gümüş = c(23.01, 
    23.23, 23.4805, 23.6351, 23.8235, 23.74)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

Any idea how can I solve it?

Thanks.

(Additionally note that my Timestamp variable has the column values as 2021-08-01, 2021-08-18... when I view(data) but it seems as 1629417600, 1629331200 in the dput output.)

SBA
  • 259
  • 1
  • 2
  • 10
  • Please include a reproducible example of your data otherwise it's impossible to anyone to tell you why it "doesn't work". – user438383 Aug 24 '21 at 15:53
  • It's a little difficult to answer since we don't have sample data and don't know how it is not working anymore. Please provide sample data (paste the output from `dput(head(data))`) as well as your expected output. Refs: https://stackoverflow.com/q/5963269, [mcve], and https://stackoverflow.com/tags/r/info. Thanks! – r2evans Aug 24 '21 at 15:54
  • (1) `dput` output will always show `POSIXct` as numeric, since that's how it is stored internally. That's normal. (2) *This* sample data only has `2021-08-**`, so it will aggregate to a single month. I suspect that what you are seeing is due to multiple months and/or other assumptions. Please update your sample data so that the data *you give us* reflects what is not working correctly. And then please *explicitly* show what the output should look like. Thank you. (3) For me on my R console, I had to add quotes in the `dput` output for `"Altın"` and `"Gümüş"`, it's my locale problem, not yours. – r2evans Aug 24 '21 at 16:13
  • I found the solution. First I created a new variable called "date" which includes only month and year. Then I run the group_by function as follows: ```data$date <- data$Timestamp data$date <- format(as.Date(data$date), "%Y/%m") ``` then the group_by is as follows: ```data1 <- data %>% group_by(date) %>% summarise_at(vars(USDTRY), list(USDTRYav = mean))``` – SBA Aug 25 '21 at 09:25

0 Answers0