I need help to solve a SQL task using R (base functions, dplyr and data.table functions)-CodePudding

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.

CodePudding user response：

Have you looked at the package sqldf?

install.packages("sqldf")
require("sqldf")

Posts <- data.frame(year = rep(c(2021, 2022), each = 2))
sqldf("select year,count(*) as TotalNumber from Posts group by Year")

CodePudding user response：

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]