Sqldf в R возвращает NA для всех строк в операторе SQL CASE

У меня есть фрейм данных в R под названием «data». Столбец EventDate - это date format, а остальные поля - это двойные или символьные поля.

Я пытаюсь вытащить большинство столбцов из "data", но aggregate значения EventDate в обозначениях год-месяц вместо обозначения текущего года-месяца-дня (поэтому я хочу закончить с 01-2018, 02-2018 , 03-2018 и т. д. В качестве значений строк).

У меня было много проблем с тем, чтобы сделать это в базе R или найти какие-либо пакеты, чтобы сделать это легко. У меня гораздо больше опыта в SQL, чем в R, поэтому я решил использовать пакет sqldf.

Вот код, который я написал:

newdata <- sqldf("SELECT ID1, ID2, ID3, Staff, Type, POC, TOC1, TOC2, TOC3, CASE
  WHEN EventDate >= '2018-01-01' AND EventDate < '2018-02-01' THEN 'Jan 2018'
  WHEN EventDate >= '2018-02-01' AND EventDate < '2018-03-01' THEN 'Feb 2018'
  WHEN EventDate >= '2018-03-01' AND EventDate < '2018-04-01' THEN 'Mar 2018'
  WHEN EventDate >= '2018-04-01' AND EventDate < '2018-05-01' THEN 'Apr 2018'
  WHEN EventDate >= '2018-05-01' AND EventDate < '2018-06-01' THEN 'May 2018'
  WHEN EventDate >= '2018-06-01' AND EventDate < '2018-07-01' THEN 'Jun 2018'
  WHEN EventDate >= '2018-07-01' AND EventDate < '2018-08-01' THEN 'Jul 2018'
  WHEN EventDate >= '2018-08-01' AND EventDate < '2018-09-01' THEN 'Aug 2018'
  WHEN EventDate >= '2018-09-01' AND EventDate < '2018-10-01' THEN 'Sep 2018'
  WHEN EventDate >= '2018-10-01' AND EventDate < '2018-11-01' THEN 'Oct 2018'
  WHEN EventDate >= '2018-11-01' AND EventDate < '2018-12-01' THEN 'Nov 2018'
  WHEN EventDate >= '2018-12-01' AND EventDate < '2019-01-01' THEN 'Dec 2018'
  END AS EventMonth
  FROM data
  ORDER BY EventMonth, Staff, ID1")

Код выполняется без каких-либо проблем и дает в основном правильные результаты, за исключением того, что он возвращает NA для всех строк в столбце EventMonth «newdata» вместо "Jan 2018", "Feb 2018" и т. д.

Это может быть проблема форматирования столбца EventDate в «data», но data$EventDate указан как тип даты в RStudio и в yyyy-mm-dd format.

Я также пробовал запустить следующее (что не вызывает ошибок и предупреждений) перед запуском предыдущего кода в качестве проверки работоспособности, но он все равно дал мне столбец, полный NA для EventMonth:

data$EventDate <- as.Date(data$EventDate)

SQL дает ожидаемый результат, когда я запускаю его в SQL Server (в столбце EventMonth нет NA), но, к сожалению, у меня не всегда будет доступ к SQL Server в будущем, поэтому я вообще беспокоюсь о R .

Мне нужен способ сделать это без реального движка базы данных SQL, чтобы выполнять аналогичные агрегаты в будущем. Я недавно установил R, RStudio и пакет sqldf в течение последнего месяца, так что мне не следует использовать что-то ужасно устаревшее.

Есть ли способ получить желаемый результат с помощью sqldf, base R или какой-либо другой пакет R?

Если у вас есть дата, вы можете просто format, используя необходимые компоненты - format(data$EventDate, "%m-%Y")

— 04.01.2019 00:44

Есть некоторые сложности с использованием объектов даты в sqldf с серверной частью sqlite. Вам нужно сравнить текст с текстом, чтобы понять логику, с которой вы собираетесь работать - см. stackoverflow.com/questions/39241030/… и ladvien.com/sqldf-dates

— 04.01.2019 01:05

Задавая вопросы по SO, пожалуйста, покажите некоторые комментарии в воспроизводимой форме. В этом случае, показывая вывод dput(head(data)) в вопросе.

— 04.01.2019 14:40

Если ваш входной столбец EventDate относится к классу Date, тогда, если вы используете бэкэнд h2 sqldf, сначала выполнив library(RH2), вы можете работать с датами обычным способом в SQL.

— 04.01.2019 21:23

Спасибо! Использование format () вместо sqldf (), как указано в @thelatemail, похоже, работает отлично. Я также ценю опыт использования объектов даты с sqldf.

— 04.01.2019 23:34

@ G.Grothendieck благодарит за совет за публикацию на SO. Я не включил какие-либо данные, с которыми работаю, чтобы защитить конфиденциальность, но в следующий раз обязательно создам фиктивные данные, если потребуется. Кроме того, я все еще получаю столбец, заполненный НА, когда я пытаюсь выполнить свой запрос с помощью sqldf после загрузки библиотеки RH2 (т. Е. Выполнения библиотеки (RH2) после установки пакета). Не уверены, что мне нужно сделать что-то еще?

— 04.01.2019 23:48

sql r sql-server sqldf

04.01.2019 00:38

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

658

Ответы 1

можно ли использовать SQLquery вместо sqldf. Если да, ваш запрос может работать таким образом.

У меня есть такая таблица на SQL-сервере.

Вы можете установить соединение с вашим SQL-сервером с помощью R.

 library(odbc)
 library(RODBC)
 library(sqldf)

 conn <- odbcDriverConnect('driver = {SQL Server};server= 
 YOURserver;database=Yourdatabase;trusted_connection=true')

  Datanew <- sqlQuery(conn,"SELECT ID,Dates,Amount  ,
  case when Dates >= '2018-01-01' and Dates < '2018-02-01' then 'Jan 2018'
                when Dates >= '2018-02-01' and Dates < '2018-03-01' then 'Feb 2018' 
                when Dates >= '2018-03-01' and Dates < '2018-04-01' then 'Mar 2018' 
  end as Months, 
                 format(dates,'MMM yyyy') NewapproachDates
                FROM dbo.practicR;"); 

View(Datanew)

Оператор case для преобразования дат в месяц и год верен, но эта функция форматирования будет намного проще, поскольку вам не нужно каждый раз указывать диапазон дат, как вы это делаете в заявлении case. Есть два столбца для дат (один - тот, который вы указали, а другой - новый).

Выход такой: