Previous Entry Share Next Entry
Перенос табличных данных в SQLite с дальнейшим использованием в GNU R
alexandre_putt
В этой заметке я делюсь своим опытом переноса данных скрупулёзно собранной российской экономической региональной статистики из формата электронной таблицы OpenOffice/LibreOffice Calc (ods) в базу данных SQL на основе SQLite для дальнейшего использования в R. Думаю, это может быть полезно тем, кто занимается обработкой и анализом объёмных экономических или других статистических данных сколь-либо серьёзным, хотя и не индустриальным, образом. Всем остальным эта заметка будет неинтересна, так как является обыкновенным how-to на специфичную тему. Честно говоря, я не обнаружил, предоставляет ли прямой доступ Госкомстат к своей базе данных (не на основе убогого web-интерфейса). По всей видимости нет, что я считаю весьма неприятным недоразумением с их стороны.

Почему SQL?


Лично я большой приверженец использования электронных таблиц, однако возник такой момент, когда данных стало слишком много, и работа с многомегабайтными файлами стала трудоёмкой. Много времени уходит на копирование и шаблонную модификацию рядов цифр.

Типичный "алгоритм" работы со статистикой может выглядеть так: открытие OpenOffice, пролистывание листов, поиск нужной колонки, копирование, вставка, сохранение в csv, импорт в R. Когда приходится делать это десятки раз, начинаешь задумываться об автоматизации обращения к данным.

Пожалуй, современные электронные таблицы оптимальны для передачи данных (хотя рабоче-крестьянский формат csv предпочтительнее), но не для хранения и сложной обработки.

Причём наиболее серьёзная проблема - это необходимость ручного отслеживания обновления данных. В ходе эконометрического анализа могут создаваться десятки небольших частных исследований, для каждого из которых требуется репликация аналогичного набора данных. После (или вследствие) проведения таких исследований данные могут быть обновлены или скорректированы. Могут появиться свежие данные за самый последний отчётный период. Естественно, что возникнет желание пересчитать предыдущие результаты. В случае работы с csv это потребует обновления каждого файла в каждом мини-проекте, что может быстро стать кошмаром. Гораздо разумнее хранить один экземпляр данных в одном централизованном месте, откуда они могут быть запрошены разными инструментами и задачами.

Другие форматы?

Данные можно хранить в Rdata (внутренний формат R), однако это не так удобно, если требуется использование в других средствах или редактирование.

Несколько плюсов и минусов от перехода на SQL


  • По сравнению с OpenOffice мнгновенное получение данных

  • Устранение хаоса с множественными копиями данных, часть из которых возможно устарела

  • Стандартный формат. Изначально я данные хранил в Lotus 1-2-3, конверсия в Calc доставила немало проблем и нет гарантии, что не придётся переходить на другую платформу. С SQL таких проблем, хочется надеяться, нет и не будет

  • Возможность нормальной организации дополнительной информации (примечаний, ссылок и т.п.) к данным в строго определённом месте

  • Управляемый экспорт по сложным признакам. Ранее для работы с таблицами мне приходилось писать множество одноразовых скриптов на Rexx, чтобы к примеру извлечь каждую пятую строку. Такие запросы лучше выполнять стандартно с помощью обращений SQL

  • Сравнительная лёгкость добавления данных и изменения их структуры. Например, в Calc для табличной организации я выбрал отправным годом 1995 для всех таблиц. Однако позднее мне понадобилось добавить несколько серий с 1990 г. Это было весьма неудобно.

Есть и свои недостатки:
  • При работе с таблицами данные лучше представляются визуально. Например, можно использовать цветовую раскраску колонок и строк, пусть и примитивную как у меня. Можно непосредственно ``видеть'' цифры

  • Требуется изучение SQL и других инструментов. Однако у меня это заняло не так уж много времени (с SQL я фактически сталкиваюсь впервые)

  • Невозможность автоматического пересчёта части данных (пожалуй, самая мощная функциональность электронных таблиц). Например, ряд статистики у меня является производным - вычисленным на основе других доступных данных. При обновлении последних автоматически обновлялись первые. Теперь это придётся делать вручную.

Итак, считая плюсы существенными, приступим!

Создание базы данных


Этап 0. Перевод данных в csv.

Для начала следует сохранить все данные в csv для дальнейшего импорта. Следует удалить неинформативные (пустые) колонки и незначащие строки. К сожалению, импорт csv в SQLite сделан примитивно, поэтому придётся совершить ряд промежуточных манипуляций.

Для удобства я использовал две верхние строки для указания краткого обозначения каждой переменной, которое будет использоваться в R, и для более подробного необязательного примечания, которое можно будет получить отдельной таблицей. Остальные строки - данные.

Не рекомендуется использовать зарезервированные слова для названий колонок. Например, таким словом у меня было index. Также возникли проблемы из-за названий, содержащих точку ("."). Проще всего их переименовать, но в крайнем случае можно использовать обрамление имени прямоугольными скобками, например [index] или двойными кавычками. Ещё нежелательно допускать кавычки и запятые где-либо в значениях (в тексте примечаний, например), так как это осложняет последующий импорт.

Исходные данные после копирования из основной таблицы выглядят примерно вот так:



Этап 1. Создание базы данных

Изначально данные сгруппированы по страницам, поэтому вполне естественно вставить их в виде таблиц. Для создания таблиц нам потребуется заполучить имена колонок из второй строки импортируемого csv файла. Делается это просто

awk 'NR==2' имя_csv_файла


Получив список переменных, создаём таблицу

sqlite3 russia.db create table имя_таблицы ( переменные, ... ) ; 


Вместо переменных нужно вставить полученный ранее список.

Однако создать таблицу мало - нужно ещё загрузить в неё данные.

Этап 2. Импорт данных

Осуществляется стандартными средствами SQLite, нужно только указать разделитель, использованный в файле csv:

sqlite3 -separator , .import file.csv tablename


Если в полях данных встречается запятая, то скорее всего этот способ не пройдёт. Тогда следует все записи в csv файле обрамить кавычками, а в качестве разделителя использовать "," .

Вот кусок небольшого bash скрипта, который осуществляет импорт одного файла:

#	csv import into sqlite3 database

# input: 
# 	filename	name of a file with data in csv format
# 			the first row contains variables' description
# 			the second row contains variables' names
# 	database	SQL database file name
#

# requirements:
#	read the second line of a data file for column names
#  	create an SQL table with name = name of file (stripped)
#  	import the rest of the file into the sql table
#


	# temporary files for data exchange
	tempfile='.sqlcmd'
	datafile='.tempdata.csv'

	filename=$1
	database=$2

	# SQL table name is constructed from the file name
	# by omitting the csv extension
	tablename=${filename%.csv}

	# retrieve names of the variables in the dataset
	echo "create table $tablename (" > $tempfile
	awk 'NR==2' $filename >> $tempfile 
	echo ') ;' >> $tempfile

	# retrieve the data starting from the 3rd row
	awk 'NR > 2' $filename > $datafile

	# actual import into sql takes place here
	sqlite3 $database < $tempfile
	# remember to indicate the separator
	sqlite3 -separator , $database ".import $datafile $tablename"

	# clean up
	if [ -a $tempfile ]; then
		rm $tempfile
	fi
	if [ -a $datafile ]; then
		rm $datafile
	fi


Посмотреть на результат можно с помощью SQLite Browser:



Совсем как обычная электронная таблица. К ней также можно настроить доступ через ODBC с помощью ODBCConfig (и подключить к OpenOffice) - см. ниже.

Работа с SQL запросами

На данном этапе полезно данные "почистить". Делается это всё обычными SQL запросами. Это касается в первую очередь отсутствующих наблюдений, корректная работа с которыми крайне важна. Разные программы используют разные способы обозначения отсутствующих наблюдений (например, Госкомстат проставляет точки в зависимости от настроения), что затрудняет обработку файлов. Лучший вариант в нашем случае - использование пустых полей. При необходимости как-то специально обозначить отсутствие наблюдений (если получатель данных не обрабатывает пустые значения корректно) эта функция целиком ложится на экспорт и может быть решена другими подручными средствами.

В результате всех этих разнообразных конвенций безобразий у меня в файле накопились значения вроде "#NA", "NA", ".", ".NaN" и наверное другие, о которых я даже не догадываюсь. Самое время их удалить. Для этого используются запросы вроде

UPDATE table
	SET column=""
	WHERE column = '"#NA"';


Для автоматизации этой задачи я попотел и выдал скрипт, автоматически генерирующий подобные запросы для всех таблиц и всех колонок. Вообще основная проблема при работе с SQL - это необходимость автоматической генерации запросов, которая решается с использованием других средств (хотя бы bash и coreutils).

Для региональных данных (как в нашем случае) индексация данных осуществляется по номеру региона. Каждая страница электронной таблицы ранее содержала пару колонок с полным названием региона (только для одного года, чтобы избежать clutter). Такое дублирование на самом деле излишне, поэтому лучше вынести название регионов, соответствующие номерам, в отдельную информационную таблицу, удалив прежнюю информацию.

Создаём таблицу, например

 CREATE TABLE regions (region_id, name, fedregion_id)


Используем уже имеющиеся данные из любой основной таблицы. Так как эта информация везде повторяется, достаточно запросить её из любой таблицы. Например, следующим запросом осуществляется вставка нужной информации

INSERT INTO regions
	(region_id, name)
	SELECT [index], [region.name] FROM product
	WHERE [year] = '1995' ;


К сожалению, SQLite не позволяет удалить переменные из таблицы. Для этого приходится их пересоздавать, опуская ненужные колонки. Эту задачу я тоже предпочёл автоматизировать, хотя и потратил какое-то время.

Теперь создадим таблицу с описанием всех переменных и примечаний. Такую таблицу я тоже сгенерировал автоматически на основе информации, которую заранее разместил в csv (в первой строке, если помните). После того, как таблица готова, информацию можно запросить по

 SELECT * FROM variables




В общей сложности 225 переменных, которые охватывают период с 1995 по 2006 гг (примерно, на деле намного меньше).

Следующий запрос к sqlite3 получает список всех таблиц

SELECT name FROM sqlite_master
	WHERE type='table'
	ORDER BY name ;




Интересно, что занимаемое данными место на диске заметно уменьшилось (1 Мб против 2.6 Мб в ods и 15 Мб в xls).

Обращение к данным из R


Для чего всё это затевалось? А собственно для возможности загружать данные прямо в R!

Для доступа к данным используется пакет RSQLite для бэкенда SQLite и DBI для интерфейса, т.е. непосредственного обращения к данным.

Для начала необходимо создать драйвер

 require(SQLite)
 drv <- dbDriver("SQLite") 


Затем подключиться к базе данных, идентифицируемой по имени файла

 con <- dbConnect(drv, dbname="russia.db")


Всё! Так просто! Можно работать с данными, отправляя SQL запросы. Список таблиц получается вызовом

 dbListTables(con)


Список переменных в таблице запрашивается по

 dbListFields(con, "product3")


Загрузить таблицу целиком (если нужно):

 dbReadTable(con, "product3")


Запросы SQL отправляются с помощью

 dbGetQuery(con, )


Результатом запроса является data.frame. Собственно ради этого всё и затевалось.

Для получения переменной достаточно отправить SELECT var FROM table или SELECT * FROM table для загрузки таблицы целиком.

Завершение работы (важно):

 dbDisconnect(con)
 dbUnloadDriver(drv)


Пример использования данных в R


Теперь посмотрим, как это всё работает. Учитывая повторяющиеся инструкции для загрузки данных, есть смысл разместить их в отдельном файле, который можно разместить в корневой директории рабочих проектов.

Вот возможное содержание такого файла, в целом повторяющее инструкции выше
# getdata.R
# connect to a database

require(RSQLite)

.path.to.data <- "~/Data/sql-data/russia.db"

.drv <- dbDriver("SQLite")

db.connection <- dbConnect(.drv, dbname=.path.to.data) 


и файла отключения от базы данных

# releasedata.R
# close database connections

dbDisconnect(db.connection)
dbUnloadDriver(.drv) 


Вот пример построения графика отношения капитала к выпуску в российских регионах (официальные данные)

# plotcapout.R

source("../getdata.R")

product.data <- dbGetQuery(db.connection, "SELECT * FROM product")
capital.data <- dbGetQuery(db.connection, "SELECT * FROM capital")

boxplot(as.numeric(capital.data$nomcap)[1:1232] / as.numeric(product.data$grossprod) ~ product.data$year, ylab="cap:out", xlab="year")

source("../releasedata.R")




Как видим, работа с SQL элементарна и требует лишь незначительных усилий, в частности добавления лишь двух строк в каждый файл, работающий с данными, в остальном мало отличаясь от стандартных функций загрузки данных.

В завершение хочу заметить, что ничто не мешает подключить эту базу к OpenOffice. Для того, как это сделать, см. ссылку в конце сообщения. Выглядит всё это вот так:



Литература

R-Databases Special Interest Group. 2002. A Common Database Interface (DBI). (Документация пакета DBI.)
Using_SQLite_With_OpenOffice.org

  • 1
Выглядит добротно. Ничего не мешает использовать этот метод и с MS Office, и вообще с любым, кто может выдавать csv-формат.
У меня нет задач, где требуется R. Поэтому возник лишь один вопрос - почему SQLite? Есть какие-то условия?

Да, конечно, OpenOffice тут непринципиален.

"почему SQLite? Есть какие-то условия?"

Непринципиально, можно использовать любую БД, так как интерфейс в R универсальный и поддерживается практически всё. SQLite мне показался максимально простым и полностью удовлетворяющим моим скромным запросам.

  • 1
?

Log in

No account? Create an account