-3

I need to write out a R querie that I already have in SQL. The task is to "transcribe" a querie from SQL to R. I have also imported the "Posts" library. I'm required to do the task in 3 ways: 1-Only base functions 2-Dplyr 3-Data.table

The SQL querie is the following: SELECT STRFTIME('%Y', CreationDate) AS Year, COUNT(*) AS TotalNumber FROM Posts GROUP BY Year

Help will be really appreciated. thanks ^^

I haven't written anything because I have no clue, but I have an example of some queries that are already done.

Henrik
  • 65,555
  • 14
  • 143
  • 159
pab10
  • 1
  • 1
  • I’m voting to close this question because it appears to be someone asking for help with a class assignment and is looking for answers rather than aiming to understand the topic. Very closely matches https://stackoverflow.com/questions/74518513/how-to-change-sql-code-into-r-base-funcions-dplyr-and-data-table-funcions – Andy Baxter Nov 21 '22 at 12:25

2 Answers2

1

Have you looked at the package sqldf?

install.packages("sqldf")
library("sqldf")

Posts <- data.frame(year = rep(c(2021, 2022), each = 2))
sqldf("select year,count(*) as TotalNumber from Posts group by Year")
DashdotdotDashdotdot
  • 449
  • 1
  • 9
  • 3
    Don't use `require` like that, use `library` or check its return value. https://stackoverflow.com/a/51263513/3358272, https://yihui.org/en/2014/07/library-vs-require/, https://r-pkgs.org/namespace.html#search-path – r2evans Nov 20 '22 at 20:28
  • 1
    Thank you, was not aware of the difference. – DashdotdotDashdotdot Nov 20 '22 at 22:22
0

base R

as.data.frame(
  table(Year = format(dat$CreationDate, format = "%Y")),
  responseName = "TotalNumber")

dplyr

library(dplyr)
dat %>%
  transmute(Year = format(CreationDate, format = "%Y")) %>%
  count(Year)

data.table

library(data.table)
as.data.table(dat)[, as.data.table(table(Year = format(CreationDate, format = "%Y")))]
# or
as.data.table(dat)[, Year := format(CreationDate, format = "%Y")][, .N, by = Year]
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • 1
    @pab10, my answer was downvoted and ***I agree***: I hastily answered the question without really looking at the question and seeing what was happening: I just did your homework for you with no effort on your part. That was hasty and arguably irresponsible of me to do that without opting for a more "teaching moment". Please don't fall into this trap: learn how to recreate what I wrote here and learn how to do it yourself. I'll be more careful next time, but please realize that many here want you to learn without us doing all of your work for you. Good luck. – r2evans Nov 21 '22 at 14:48