sqldf, csv и поля, содержащие запятые

Мне потребовалось время, чтобы понять это. Итак, я отвечаю на свой вопрос.

У вас есть файл .csv, вы хотите его загрузить >быстро, вы хотите использовать пакет sqldf. Ваш обычный код раздражают несколько надоедливых полей. Пример:

1001,     Amy,9:43:00, 99.2
1002,"Ben,Jr",9:43:00, 99.2
1003,"Ben,Sr",9:44:00, 99.3

Этот код работает только на системах *nix.

library(sqldf)
system("touch temp.csv")
system("echo '1001, Amy,9:43:00, 99.2\n1002,\"Ben,Jr\",9:43:00, 99.2\n1003,\"Ben,Sr\",9:44:00, 99.3' > temp.csv")

Если попытаться прочитать с

x <- read.csv.sql("temp.csv", header=FALSE)

R жалуется

Error in try({ : 
  RS-DBI driver: (RS_sqlite_import: ./temp.csv line 2 expected 4 columns of data but found 5)

sqldf-FAQ.13 также не работает:

x <- read.csv.sql("temp.csv", filter = "tr -d '\"' ", header=FALSE)

Опять же, R жалуется

Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings,  : 
  line 1 did not have 5 elements

Фактически фильтр удаляет только двойные кавычки.

Итак, как действовать?


r sqldf
person Ryogi    schedule 10.11.2011    source источник
comment
Я разъяснил и улучшил FAQ и включил сюда gawk-версию вашего кода Perl с банком ссылок.   -  person G. Grothendieck    schedule 11.11.2011
comment
Сначала я спутал вас с кем-то другим, и вы, должно быть, видели это как раз перед тем, как я исправил имя.   -  person G. Grothendieck    schedule 11.11.2011

Ответы (2)


arrow_upward
3
arrow_downward

Perl и регулярные выражения спешат на помощь. Копаясь в SO и играя с регулярными выражениями здесь, не так уж сложно найти правильное:

 s/(\"[^\",]+),([^\"]+\")/$1_$2/g

что соответствует "...,...", здесь точки — это что угодно, но только не двойные кавычки и запятые, а запятая заменяется символом подчеркивания. Однострочник Perl — правильный фильтр для передачи в sqldf:

x <- read.csv.sql("temp.csv", 
        filter = "perl -e 's/(\"[^\",]+)_([^\"]+\")/$1_$2/g'", 
        header=FALSE)

Вот кадр данных x

> x
    V1       V2      V3   V4
1 1001      Amy 9:43:00 99.2
2 1002 "Ben_Jr" 9:43:00 99.2
3 1003 "Ben_Sr" 9:44:00 99.3

Теперь косметика DYO на струнах...

EDIT: приведенное выше регулярное выражение заменяет только первое вхождение запятой в поле. Чтобы заменить все вхождения, используйте это

s{(\"[^\",]+),([^\"]+\")}{$_= $&, s/,/_/g, $_}eg

Что изменилось?

  1. Я заменил разделители / на {};
  2. Опция e в самом конце указывает синтаксическому анализатору интерпретировать поле замены как код perl;
  3. Замена представляет собой простую замену регулярного выражения, которая заменяет все "," на "_" в совпадающей подстроке $&.

Пример:

system("touch temp.csv")
system("echo '1001, Amy,9:43:00, 99.2\n1002,\"Ben,Jr,More,Commas\",9:43:00, 99.2\n1003,\"Ben,Sr\",9:44:00, 99.3' > temp.csv")

Файл temp.csv выглядит так:

1001,                 Amy,9:43:00, 99.2
1002,"Ben,Jr,More,Commas",9:43:00, 99.2
1003,            "Ben,Sr",9:44:00, 99.3

И можно читать с

x <- read.csv.sql("temp.csv", 
       filter = "perl -p -e 's{(\"[^\",]+),([^\"]+\")}{$_= $&, s/,/_/g, $_}eg'", 
       header=FALSE)
> x
    V1                   V2      V3   V4
1 1001                  Amy 9:43:00 99.2
2 1002 "Ben_Jr_More_Commas" 9:43:00 99.2
3 1003             "Ben_Sr" 9:44:00 99.3
person Ryogi    schedule 10.11.2011
comment
В аргументе adding filter в функции read.csv.sql выдается ошибка Error in file(file, "rt") : cannot open the connection In addition: Warning message:In file(file, "rt") : cannot open file 'C:\Users\user\AppData\Local\Temp\Rtmpcbqrz6\filee4819344504': No such file or directory. Я пишу cmd в RStudio x <- read.csv.sql("D:/d/myfile.csv", sql="Select * from file where agent='agnt1455' ",filter = "perl -e 's/(\"[^\",]+)_([^\"]+\")/$1_$2/g'") - person Akki; 22.10.2017

arrow_upward
0
arrow_downward

Для Windows sqldf теперь поставляется с trcomma2dot.vbs, который делает это по умолчанию с read.csv2.sql . Хотя оказалось, что это медленно для очень больших данных (> 1 миллион строк).

В нем упоминается «tr» для системы, отличной от Windows, но я не смог попробовать.

person shetaa    schedule 19.06.2015
comment
Обратите внимание, что trcomma2dot переводит все запятые в точки, а не только те, что в кавычках. - person G. Grothendieck; 20.06.2015