Чтение и запись данных (Excel, Json, SQL, MongoDB) / pd 8

Чтение и запись в файлы Microsoft Excel

Данные очень легко читать из файлов CSV, но они часто хранятся в табличной форме в формате Excel.

pandas предоставляет специальные функции для работы с ним:

• to_excel()
• read_excel()

Функция read_excel() может читать из файлов Excel 2003 (.xls) и Excel 2007 (.xlsx). Это возможно благодаря модулю xlrd.

Для начала откроем файл Excel и введем данные со следующий таблиц. Разместим их в листах sheet1 и sheet2. Сохраним файл как ch05_data.xlsx.

Для чтения данных из файла XLS нужно всего лишь конвертировать его в Dataframe, используя для этого функцию read_excel().

>>> pd.read_excel('ch05_data.xlsx')

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

>>> pd.read_excel('ch05_data.xlsx','Sheet2')

>>> pd.read_excel('ch05_data.xlsx',1)

Запись работает по тому же принципу. Для конвертации объекта Dataframe в Excel нужно написать следующее.

>>> frame = pd.DataFrame(np.random.random((4,4)),
... 			 index = ['exp1','exp2','exp3','exp4'],
... 			 columns = ['Jan2015','Fab2015','Mar2015','Apr2005'])
>>> frame.to_excel('data2.xlsx')
>>> frame

В рабочей директории будет создан файл с соответствующими данными.

Данные JSON

JSON (JavaScript Object Notation) стал одним из самых распространенных стандартных форматов для передачи данных в сети.

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

В этом разделе вы узнаете, как использовать функции read_json() и to_json() для использования API. А в следующем — познакомитесь с другим примером взаимодействия со структурированными данными формата, который чаще встречается в реальной жизни.

http://jsonviewer.stack.hu/ — полезный онлайн-инструмент для проверки формата JSON. Нужно вставить данные в этом формате, и сайт покажет, представлены ли они в корректной форме, а также покажет дерево структуры.

{
  "up": {
    "white": 0,
    "black": 4,
    "red": 8,
    "blue": 12
  },
  "down": {
    "white": 1,
    "black": 5,
    "red": 9,
    "blue": 13
  },
  "right": {
    "white": 2,
    "black": 6,
    "red": 10,
    "blue": 14
  },
  "left": {
    "white": 3,
    "black": 7,
    "red": 11,
    "blue": 15
  }
}

Начнем с самого полезного примера, когда есть объект Dataframe и его нужно конвертировать в файл JSON. Определим такой объект и используем его для вызова функции to_json(), указав название для итогового файла.

>>> frame = pd.DataFrame(np.arange(16).reshape(4,4),
... 			 index=['white','black','red','blue'],
... 			 columns=['up','down','right','left'])
>>> frame.to_json('frame.json')

Он будет находится в рабочей папке и включать все данные в формате JSON.

Обратную операцию можно выполнить с помощью функции read_json(). Параметром здесь должен выступать файл с данными.

>>> pd.read_json('frame.json')

Это был простейший пример, где данные JSON представлены в табличной форме (поскольку источником файла frame.json служил именно такой объект — Dataframe). Но в большинстве случаев у JSON-файлов нет такой четкой структуры. Поэтому нужно конвертировать файл в табличную форму. Этот процесс называется нормализацией.

Библиотека pandas предоставляет функцию json_normalize(), которая умеет конвертировать объект dict или список в таблицу. Для начала ее нужно импортировать:

>>> from pandas.io.json import json_normalize

Создадим JSON-файл как в следующем примере с помощью любого текстового редактора и сохраним его в рабочей директории как books.json.

[{"writer": "Mark Ross",
 "nationality": "USA",
 "books": [
 {"title": "XML Cookbook", "price": 23.56},
 {"title": "Python Fundamentals", "price": 50.70},
 {"title": "The NumPy library", "price": 12.30}
 ]
},
{"writer": "Barbara Bracket",
 "nationality": "UK",
 "books": [
 {"title": "Java Enterprise", "price": 28.60},
 {"title": "HTML5", "price": 31.35},
 {"title": "Python for Dummies", "price": 28.00}
 ]
}]

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

>>> import json
>>> file = open('books.json','r')
>>> text = file.read()
>>> text = json.loads(text)

После этого можно использовать функцию json_normalize(). Например, можно получить список книг. Для этого необходимо указать ключ books в качестве второго параметра.

>>> json_normalize(text,'books')

Функция считает содержимое всех элементов, у которых ключом является books. Все свойства будут конвертированы в имена вложенных колонок, а соответствующие значения заполнят объект Dataframe. В качестве индексов будет использоваться возрастающая последовательность чисел.

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

>>> json_normalize(text,'books',['nationality','writer'])

Результатом будет Dataframe с готовой структурой.

Формат HDF5

До сих пор в примерах использовалась запись данных лишь в текстовом формате. Но когда речь заходит о больших объемах, то предпочтительнее использовать бинарный. Для этого в Python есть несколько инструментов. Один из них — библиотека HDF5.

HDF расшифровывается как hierarchical data format (иерархический формат данных), а сама библиотека используется для чтения и записи файлов HDF5, содержащих структуру с узлами и возможностью хранить несколько наборов данных.

Библиотека разработана на C, но предусматривает интерфейсы для других языков: Python, MATLAB и Java. Она особенно эффективна при сохранении больших объемов данных. В сравнении с остальными форматами, работающими в бинарном виде, HDF5 поддерживает сжатие в реальном времени, используя преимущества повторяющихся паттернов в структуре для уменьшения размера файла.

Возможные варианты в Python — это PyTables и h5py. Они отличаются по нескольким аспектам, а выбирать их стоит, основываясь на том, что нужно программисту.

h5py предоставляет прямой интерфейс с высокоуровневыми API HDF5, а PyTables скрывает за абстракциями многие детали HDF5 с более гибкими контейнерами данных, индексированные таблицы, запросы и другие способы вычислений.

В pandas есть классовый dict под названием HDFStore, который использует PyTables для хранения объектов pandas. Поэтому перед началом работы с форматом необходимо импортировать класс HDFStore:

>>> from pandas.io.pytables import HDFStore

Теперь данные объекта Dataframe можно хранить в файле с расширением .h5. Для начала создадим Dataframe.

>>> frame = pd.DataFrame(np.arange(16).reshape(4,4),
... 			 index=['white','black','red','blue'],
... 			 columns=['up','down','right','left'])

Дальше нужен файл HDF5 под названием mydata.h5. Добавим в него содержимое объекта Dataframe.

>>> store = HDFStore('mydata.h5')
>>> store['obj1'] = frame

Можете догадаться, как хранить несколько структур данных в одном файле HDF5, указав для каждой из них метку. С помощью этого формата можно хранить несколько структур данных в одном файле, при том что он будет представлен переменной store.

>>> store
<class 'pandas.io.pytables.HDFStore'>
File path: ch05_data.h5

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

Взаимодействие с базами данных

В большинстве приложений текстовые файлы редко выступают источниками данных, просто потому что это не эффективно. Они хранятся в реляционных базах данных (SQL) или альтернативных (NoSQL), которые стали особо популярными в последнее время.

Загрузка из SQL в Dataframe — это простой процесс, а pandas предлагает дополнительные функции для еще большего упрощения.

Модуль pandas.io.sql предоставляет объединенный интерфейс, независимый от базы данных, под названием sqlalchemy. Он упрощает режим соединения, поскольку команды неизменны вне зависимости от типа базы. Для создания соединения используется функция create_engine(). Это же позволяет настроить все необходимые свойства: ввести имя пользователя, пароль и порт, а также создать экземпляр базы данных.

Вот список разных типов баз данных:

>>> from sqlalchemy import create_engine
# For PostgreSQL:
>>> engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')
# For MySQL
>>> engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# For Oracle
>>> engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
# For MSSQL
>>> engine = create_engine('mssql+pyodbc://mydsn')
# For SQLite
>>> engine = create_engine('sqlite:///foo.db')

Загрузка и запись данных с SQLite3

Для первого примера используем базу данных SQLite, применив встроенный Python sqlite3. SQLite3 — это инструмент, реализующий реляционную базу данных очень простым путем. Это самый легкий способ добавить ее в любое приложение на Python. С помощью SQLite фактически можно создать встроенную базу данных в одном файле.

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

Создадим объект Dataframe, который будет использоваться для создания новой таблицы в базе данных SQLite3.

>>> frame = pd.DataFrame(np.arange(20).reshape(4,5),
... 			 columns=['white','red','blue','black','green'])
>>> frame

Теперь нужно реализовать соединение с базой.

>>> engine = create_engine('sqlite:///foo.db')

Конвертируем объект в таблицу внутри базы данных.

>>> frame.to_sql('colors',engine)

А вот для чтения базы нужно использовать функцию read_sql(), указав название таблицы и движок.

>>> pd.read_sql('colors',engine)

На примере видно, что даже в этом случае процесс записи очень прост благодаря API библиотеки pandas.

Однако того же можно добиться и без них. Это покажет, почему pandas считается эффективным инструментом для работы с базой данных.

Во-первых, нужно установить соединение и создать таблицу, определив правильные типы данных, которые впоследствии будут загружаться.

>>> import sqlite3
>>> query = """
... CREATE TABLE test
... (a VARCHAR(20), b VARCHAR(20),
... c REAL, d INTEGER
... );"""
>>> con = sqlite3.connect(':memory:')
>>> con.execute(query)
<sqlite3.Cursor object at 0x0000000009E7D730>
>>> con.commit()

Теперь можно добавлять сами данные с помощью SQL INSERT.

>>> data = [('white','up',1,3),
... ('black','down',2,8),
... ('green','up',4,4),
... ('red','down',5,5)]
>>> stmt = "INSERT INTO test VALUES(?,?,?,?)"
>>> con.executemany(stmt, data)
<sqlite3.Cursor object at 0x0000000009E7D8F0>
>>> con.commit()

Наконец, можно перейти к запросам из базы данных. Это делается с помощью SQL SELECT.

>>> cursor = con.execute('select * from test')
>>> cursor
<sqlite3.Cursor object at 0x0000000009E7D730>
>>> rows = cursor.fetchall()
>>> rows
[('white', 'up', 1.0, 3),
 ('black', 'down', 2.0, 8),
 ('green', 'up', 4.0, 4),
 ('red', 'down', 5.0, 5)]

Конструктору Dataframe можно передать список кортежей, а если нужны названия колонок, то их можно найти в атрибуте description своего cursor.

>>> cursor.description
(('a', None, None, None, None, None, None),
 ('b', None, None, None, None, None, None),
 ('c', None, None, None, None, None, None),
 ('d', None, None, None, None, None, None))
>>> pd.DataFrame(rows, columns=zip(*cursor.description)[0])

Этот подход куда сложнее.

Загрузка и запись с помощью PostgreSQL

Начиная с pandas 0.14, PostgreSQL также поддерживается. Для начала нужно проверить версию библиотеки.

>>> pd.__version__
>>> '0.22.0'

Для запуска примера база PostgreSQL должна быть установлена в системе. В этом примере была создана база postgres, где пользователя зовут postgres, а пароль — password. Замените значения на соответствующие в вашей системе.

Сначала нужно установить библиотеку psycopg2, которая предназначена для управления соединениями с базой данных.

В Anaconda:

conda install psycopg2

Или с помощью PyPl:

pip install psycopg2

Теперь можно установить соединение:

>>> import psycopg2
>>> engine = create_engine('postgresql://postgres:password@localhost:5432/
postgres')

Примечание. В этом примере вне зависимости от установленной версии в Windows может возникать ошибка:

from psycopg2._psycopg import BINARY, NUMBER, STRING,
DATETIME, ROWID
ImportError: DLL load failed: The specified module could not
be found.

Это почти наверняка значит, что DLL для PostgreSQL (в частности, libpq.dll) не установлены в PATH. Добавьте одну из папок postgres\x.x\bin в PATH и теперь соединение Python с базой данных PostgreSQL должно работать без проблем.

Создайте объект Dataframe:

>>> frame = pd.DataFrame(np.random.random((4,4)),
...		 index=['exp1','exp2','exp3','exp4'],
...		 columns=['feb','mar','apr','may']);

Вот как просто переносить данные в таблицу. С помощью to_sql() вы без проблем запишите их в таблицу dataframe.

>>> frame.to_sql('dataframe',engine)

pgAdmin III — это графическое приложение для управления базами данных PostgreSQL. Крайне удобный инструмент для Windows и Linux. С его помощью можно легко изучить созданную базу данных.

Если вы хорошо знаете язык SQL, то есть и классический способ рассмотреть созданную таблицу с помощью сессии psql.

>>> psql -U postgres

В этом случае соединение произошло от имени пользователя postgres. Оно может отличаться. После соединения просто осуществите SQL-запрос к таблице.

postgres=# SELECT * FROM DATAFRAME;
index| 		   feb | 	     mar | 	       apr |		 may
-----+-----------------+-----------------+-----------------+-----------------
exp1 |0.757871296789076|0.422582915331819|0.979085739226726|0.332288515791064
exp2 |0.124353978978927|0.273461421503087|0.049433776453223|0.0271413946693556
exp3 |0.538089036334938|0.097041417119426|0.905979807772598|0.123448718583967
exp4 |0.736585422687497|0.982331931474687|0.958014824504186|0.448063967996436
(4 righe)

Даже конвертация таблицы в объект Dataframe — тривиальная задача. Для этого есть функция read_sql_table(), которая считывает данные из таблицы и записывает их в новый объект.

>>> pd.read_sql_table('dataframe',engine)

Но когда нужно считать данные из базы, конвертация целой таблицы в Dataframe — не самая полезная операция. Те, кто работают с реляционными базами данных, предпочитают использовать для этих целей SQL. Он подходит для выбора того. какие данные и в каком виде требуется получить с помощью SQL-запроса.

Текст запроса может быть использован в функции read_sql_query().

>>> pd.read_sql_query('SELECT index,apr,may FROM DATAFRAME WHERE apr >
0.5',engine)

Чтение и запись данных в базу данных NoSQL: MongoDB

Среди всех баз данных NoSQL (BerkeleyDB, Tokyo Cabinet и MongoDB) MongoDB — одна из самых распространенных. Она доступна в разных системах и подходит для чтения и записи данных при анализе данных.

Работу нужно начать с того, что указать на конкретную директорию.

mongod --dbpath C:\MongoDB_data

Теперь, когда сервис случает порт 27017, к базе можно подключиться, используя официальный драйвер для MongoDB, pymongo.

>>> import pymongo
>>> client = MongoClient('localhost',27017)

Один экземпляр MongoDB способен поддерживать несколько баз данных одновременно. Поэтому нужно указать на конкретную.

>>> db = client.mydatabase
>>> db
Database(MongoClient('localhost', 27017), 'mycollection')
>>> # Чтобы ссылаться на этот объект, используйте
>>> client['mydatabase']
Database(MongoClient('localhost', 27017), 'mydatabase')

Когда база данных определена, нужно определить коллекцию. Она представляет собой группу документов, сохраненных в MongoDB. Ее можно воспринимать как эквивалент таблиц из SQL.

>>> collection = db.mycollection
>>> db['mycollection']
Collection(Database(MongoClient('localhost', 27017), 'mydatabase'),
'mycollection')
>>> collection
Collection(Database(MongoClient('localhost', 27017), 'mydatabase'),
'mycollection')

Теперь нужно добавить данные в коллекцию. Создайте Dataframe.

>>> frame = pd.DataFrame(np.arange(20).reshape(4,5),
... 			 columns=['white','red','blue','black','green'])
>>> frame

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

>>> import json
>>> record = json.loads(frame.T.to_json()).values()
>>> record
[{'blue': 7, 'green': 9, 'white': 5, 'black': 8, 'red': 6},
 {'blue': 2, 'green': 4, 'white': 0, 'black': 3, 'red': 1},    
 {'blue': 17, 'green': 19, 'white': 15, 'black': 18, 'red': 16}, 
 {'blue': 12, 'green': 14, 'white': 10, 'black': 13, 'red': 11}]

Теперь все готово для добавления документа в коллекцию. Для этого используется функция insert().

>>> collection.mydocument.insert(record)
[ObjectId('54fc3afb9bfbee47f4260357'), ObjectId('54fc3afb9bfbee47f4260358'),
 ObjectId('54fc3afb9bfbee47f4260359'), ObjectId('54fc3afb9bfbee47f426035a')]

В этом случае каждый объект представлен на отдельной строке. Когда данные загружены в документ базы данных, можно выполнить и обратный процесс, то есть, прочитать данные и конвертировать их в Dataframe.

>>> cursor = collection['mydocument'].find()
>>> dataframe = (list(cursor))
>>> del dataframe['_id']
>>> dataframe

Была удалена колонка с ID для внутренней навигации по MongoDB.