Pandas создание таблицы excel
Если Вы только начинаете свой путь знакомства с возможностями Python, ваши познания еще имеют начальный уровень — этот материал для Вас. В статье мы опишем, как можно извлекать информацию из данных, представленных в Excel файлах, работать с ними используя базовый функционал библиотек. В первой части статьи мы расскажем про установку необходимых библиотек и настройку среды. Во второй части — предоставим обзор библиотек, которые могут быть использованы для загрузки и записи таблиц в файлы с помощью Python и расскажем как работать с такими библиотеками как pandas, openpyxl, xlrd, xlutils, pyexcel.
В какой-то момент вы неизбежно столкнетесь с необходимостью работы с данными Excel, и нет гарантии, что работа с таким форматами хранения данных доставит вам удовольствие. Поэтому разработчики Python реализовали удобный способ читать, редактировать и производить иные манипуляции не только с файлами Excel, но и с файлами других типов.
Отправная точка — наличие данных
Когда вы начинаете проект по анализу данных, вы часто сталкиваетесь со статистикой собранной, возможно, при помощи счетчиков, возможно, при помощи выгрузок данных из систем типа Kaggle, Quandl и т. д. Но большая часть данных все-таки находится в Google или репозиториях, которыми поделились другие пользователи. Эти данные могут быть в формате Excel или в файле с .csv расширением.
Данные есть, данных много. Анализируй — не хочу. С чего начать? Первый шаг в анализе данных — их верификация. Иными словами — необходимо убедиться в качестве входящих данных.
В случае, если данные хранятся в таблице, необходимо не только подтвердить качество данных (нужно быть уверенным, что данные таблицы ответят на поставленный для исследования вопрос), но и оценить, можно ли доверять этим данным.
Проверка качества таблицы
Чтобы проверить качество таблицы, обычно используют простой чек-лист. Отвечают ли данные в таблице следующим условиям:
- данные являются статистикой;
- различные типы данных: время, вычисления, результат;
- данные полные и консистентные: структура данных в таблице — систематическая, а присутствующие формулы — работающие.
Бест-практикс табличных данных
Читать данные таблицы при помощи Python — это хорошо. Но данные хочется еще и редактировать. Причем редактирование данных в таблице, должно соответствовать следующим условиям:
Если вы работаете с Microsoft Excel, вы наверняка знаете, что есть большое количество вариантов сохранения файла помимо используемых по умолчанию расширения: .xls или .xlsx (переходим на вкладку “файл”, “сохранить как” и выбираем другое расширение (наиболее часто используемые расширения для сохранения данных с целью анализа — .CSV и.ТХТ)). В зависимости от варианта сохранения поля данных будут разделены знаками табуляции или запятыми, которые составляют поле “разделитель”. Итак, данные проверены и сохранены. Начинаем готовить рабочее пространство.
Подготовка рабочего пространства
Подготовка рабочего пространства — одна из первых вещей, которую надо сделать, чтобы быть уверенным в качественном результате анализа.
Первый шаг — проверка рабочей директории.
Когда вы работаете в терминале, вы можете сначала перейти к директории, в которой находится ваш файл, а затем запустить Python. В таком случае необходимо убедиться, что файл находится в директории, из которой вы хотите работать.
Для проверки дайте следующие команды:
Эти команды важны не только для загрузки данных, но и для дальнейшего анализа. Итак, вы прошли все проверки, вы сохранили данные и подготовили рабочее пространство. Уже можно начать чтение данных в Python? :) К сожалению пока нет. Нужно сделать еще одну последнюю вещь.
Установка пакетов для чтения и записи Excel файлов
Несмотря на то, что вы еще не знаете, какие библиотеки будут нужны для импорта данных, нужно убедиться, что у все готово для установки этих библиотек. Если у вас установлен Python 2> = 2.7.9 или Python 3> = 3.4, нет повода для беспокойства — обычно, в этих версиях уже все подготовлено. Поэтому просто убедитесь, что вы обновились до последней версии :)
Для этого запустите в своем компьютере следующую команду:
В случае, если вы еще не установили pip, запустите скрипт python get-pip.py, который вы можете найти здесь (там же есть инструкции по установке и help).
Установка Anaconda
Установка дистрибутива Anaconda Python — альтернативный вариант, если вы используете Python для анализа данных. Это простой и быстрый способ начать работу с анализом данных — ведь отдельно устанавливать пакеты, необходимые для data science не придется.
Это особенно удобно для новичков, однако даже опытные разработчики часто идут этим путем, ведь Anakonda — удобный способ быстро протестировать некоторые вещи без необходимости устанавливать каждый пакет отдельно.
Anaconda включает в себя 100 наиболее популярных библиотек Python, R и Scala для анализа данных в нескольких средах разработки с открытым исходным кодом, таких как Jupyter и Spyder. Если вы хотите начать работу с Jupyter Notebook, то вам сюда.
Чтобы установить Anaconda — вам сюда.
Загрузка файлов Excel как Pandas DataFrame
Ну что ж, мы сделали все, чтобы настроить среду! Теперь самое время начать импорт файлов.
Один из способов, которым вы будете часто пользоваться для импорта файлов с целью анализа данных — импорт с помощью библиотеки Pandas (Pandas — программная библиотека на языке Python для обработки и анализа данных). Работа Pandas с данными происходит поверх библиотеки NumPy, являющейся инструментом более низкого уровня. Pandas — мощная и гибкая библиотека и она очень часто используется для структуризации данных в целях облегчения анализа.
Если у вас уже есть Pandas в Anaconda, вы можете просто загрузить файлы в Pandas DataFrames с помощью pd.Excelfile ():
Если вы не установили Anaconda, просто запустите pip install pandas, чтобы установить пакет Pandas в вашей среде, а затем выполните команды, приведенные выше.
Для чтения .csv-файлов есть аналогичная функция загрузки данных в DataFrame: read_csv (). Вот пример того, как вы можете использовать эту функцию:
Разделителем, который эта функция будет учитывать, является по умолчанию запятая, но вы можете, если хотите, указать альтернативный разделитель. Перейдите к документации, если хотите узнать, какие другие аргументы можно указать, чтобы произвести импорт.
Как записывать Pandas DataFrame в Excel файл
Предположим, после анализа данных вы хотите записать данные в новый файл. Существует способ записать данные Pandas DataFrames (с помощью функции to_excel ). Но, прежде чем использовать эту функцию, убедитесь, что у вас установлен XlsxWriter, если вы хотите записать свои данные на несколько листов в файле .xlsx:
Обратите внимание, что в фрагменте кода используется объект ExcelWriter для вывода DataFrame. Иными словами, вы передаете переменную writer в функцию to_excel (), и указываете имя листа. Таким образом, вы добавляете лист с данными в существующую книгу. Также можно использовать ExcelWriter для сохранения нескольких разных DataFrames в одной книге.
То есть если вы просто хотите сохранить один файл DataFrame в файл, вы можете обойтись без установки библиотеки XlsxWriter. Просто не указываете аргумент, который передается функции pd.ExcelWriter (), остальные шаги остаются неизменными.
Подобно функциям, которые используются для чтения в .csv-файлах, есть также функция to_csv () для записи результатов обратно в файл с разделителями-запятыми. Он работает так же, как когда мы использовали ее для чтения в файле:
Если вы хотите иметь отдельный файл с вкладкой, вы можете передать a \ t аргументу sep. Обратите внимание, что существуют различные другие функции, которые можно использовать для вывода файлов. Их можно найти здесь.
Использование виртуальной среды
Общий совет по установке библиотек — делать установку в виртуальной среде Python без системных библиотек. Вы можете использовать virtualenv для создания изолированных сред Python: он создает папку, содержащую все необходимое для использования библиотек, которые потребуются для Python.
Чтобы начать работу с virtualenv, сначала нужно его установить. Потом перейти в директорию, где будет находится проект. Создать virtualenv в этой папке и загрузить, если нужно, в определенную версию Python. После этого активируете виртуальную среду. Теперь можно начинать загрузку других библиотек и начинать работать с ними.
Не забудьте отключить среду, когда вы закончите!
Обратите внимание, что виртуальная среда может показаться сначала проблематичной, если вы делаете первые шаги в области анализа данных с помощью Python. И особенно, если у вас только один проект, вы можете не понимать, зачем вообще нужна виртуальная среда.
Но что делать, если у вас несколько проектов, работающих одновременно, и вы не хотите, чтобы они использовали одну и ту же установку Python? Или если у ваших проектов есть противоречивые требования. В таких случаях виртуальная среда — идеальное решение.
Во второй части статьи мы расскажем об основных библиотеках для анализа данных.
Продолжение следует…
Pandas — одна из самых популярных библиотек Python для аналитики и работы с Data Science. Это как SQL для Python. Все потому, что pandas позволяет работать с двухмерными таблицами данных в Python. У нее есть и масса других особенностей. В этой серии руководств по pandas вы узнаете самое важное (и часто используемое), что необходимо знать аналитику или специалисту по Data Science. Это первая часть, в которой речь пойдет об основах.
Примечание: это практическое руководство, поэтому рекомендуется самостоятельно писать код, повторяя инструкции!
Чтобы разобраться со всем, необходимо…
- Установить Python3.7+, numpy и Pandas.
- Следующий шаг: подключиться к серверу (или локально) и запустить Jupyter. Затем открыть Jupyter Notebook в любимом браузере. Создайте новый ноутбук с именем «pandas_tutorial_1».
- Импортировать numpy и pandas в Jupyter Notebook с помощью двух строк кода:
Теперь все настроено! Переходим к руководству по pandas! Первый вопрос:
Как открывать файлы с данными в pandas
Информация может храниться в файлах .csv или таблицах SQL. Возможно, в файлах Excel. Или даже файлах .tsv. Или еще в каком-то другом формате. Но цель всегда одна и та же. Если необходимо анализировать данные с помощью pandas, нужна структура данных, совместимая с pandas.
Структуры данных Python
В pandas есть два вида структур данных: Series и DataFrame.
Series в pandas — это одномерная структура данных («одномерная ndarray»), которая хранит данные. Для каждого значения в ней есть уникальный индекс.
DataFrame — двухмерная структура, состоящая из колонок и строк. У колонок есть имена, а у строк — индексы.
В руководстве по pandas основной акцент будет сделан на DataFrames. Причина проста: с большей частью аналитических методов логичнее работать в двухмерной структуре.
Загрузка файла .csv в pandas DataFrame
Для загрузки .csv файла с данными в pandas используется функция read_csv() .
Начнем с простого образца под названием zoo. В этот раз для практики вам предстоит создать файл .csv самостоятельно. Вот сырые данные:
затем скопируем данные выше, чтобы вставить информацию в этот текстовый файл…
…и назовем его zoo.csv!
Это ваш первый .csv файл.
Вернемся в Jupyter Notebook (который называется «pandas_tutorial_1») и откроем в нем этот .csv файл!
Для этого нужна функция read_csv()
Введем следующее в новую строку:
Готово! Это файл zoo.csv , перенесенный в pandas. Это двухмерная таблица — DataFrame. Числа слева — это индексы. А названия колонок вверху взяты из первой строки файла zoo.csv.
На самом деле, вам вряд ли придется когда-нибудь создавать .csv файл для себя, как это было сделано в примере. Вы будете использовать готовые файлы с данными. Поэтому нужно знать, как загружать их на сервер!
Если кликнуть на ссылку, файл с данными загрузится на компьютер. Но он ведь не нужен вам на ПК. Его нужно загрузить на сервер и потом в Jupyter Notebook. Для этого нужно всего два шага.
Шаг 1) Вернуться в Jupyter Notebook и ввести эту команду:
Это загрузит файл pandas_tutorial_read.csv на сервер. Проверьте:
Если кликнуть на него…
…можно получить всю информацию из файла.
Шаг 2) Вернуться в Jupyter Notebook и использовать ту же функцию read_csv (не забыв поменять имя файла и значение разделителя):
Данные загружены в pandas!
Что-то не так? В этот раз не было заголовка, поэтому его нужно настроить самостоятельно. Для этого необходимо добавить параметры имен в функцию!
Так лучше!
Теперь файл .csv окончательно загружен в pandas DataFrame .
Примечание: есть альтернативный способ. Вы можете загрузить файл .csv через URL напрямую. В этом случае данные не загрузятся на сервер данных.
Примечание: если вам интересно, что в этом наборе, то это лог данных из блога о путешествиях. Ну а названия колонок говорят сами за себя.
Отбор данных из dataframe в pandas
Это первая часть руководства, поэтому начнем с самых простых методов отбора данных, а уже в следующих углубимся и разберем более сложные.
Вывод всего dataframe
Базовый метод — вывести все данные из dataframe на экран. Для этого не придется запускать функцию pd.read_csv() снова и снова. Просто сохраните денные в переменную при чтении!
После этого можно будет вызывать значение article_read каждый раз для вывода DataFrame!
Вывод части dataframe
Иногда удобно вывести не целый dataframe, заполнив экран данными, а выбрать несколько строк. Например, первые 5 строк можно вывести, набрав:
Или последние 5 строк:
Или 5 случайных строк:
Вывод определенных колонок из dataframe
А это уже посложнее! Предположим, что вы хотите вывести только колонки «country» и «user_id».
Для этого нужно использовать команду в следующем формате:
Есть предположения, почему здесь понадобились двойные квадратные скобки? Это может показаться сложным, но, возможно, так удастся запомнить: внешние скобки сообщают pandas, что вы хотите выбрать колонки, а внутренние — список (помните? Списки в Python указываются в квадратных скобках) имен колонок.
Поменяв порядок имен колонов, изменится и результат вывода.
Это DataFrame выбранных колонок.
Примечание: иногда (особенно в проектах аналитического прогнозирования) нужно получить объекты Series вместе DataFrames. Это можно сделать с помощью одного из способов:
Фильтрация определенных значений в dataframe
Если прошлый шаг показался сложным, то этот будет еще сложнее!
Предположим, что вы хотите сохранить только тех пользователей, которые представлены в источнике «SEO». Для этого нужно отфильтровать по значению «SEO» в колонке «source»:
Важно понимать, как pandas работает с фильтрацией данных:
Шаг 1) В первую очередь он оценивает каждую строчку в квадратных скобках: является ли 'SEO' значением колонки article_read.source ? Результат всегда будет булевым значением ( True или False ).
Шаг 2) Затем он выводит каждую строку со значением True из таблицы article_read .
Выглядит сложно? Возможно. Но именно так это и работает, поэтому просто выучите, потому что пользоваться этим придется часто!
Функции могут использоваться одна за другой
Важно понимать, что логика pandas очень линейна (как в SQL, например). Поэтому если вы применяете функцию, то можете применить другую к ней же. В таком случае входящие данные последней функции будут выводом предыдущей.
Например, объединим эти два метода перебора:
Первая строчка выбирает первые 5 строк из набора данных. Потом она выбирает колонки «country» и «user_id».
Можно ли получить тот же результат с иной цепочкой функций? Конечно:
В этом случае сначала выбираются колонки, а потом берутся первые 5 строк. Результат такой же — порядок функций (и их исполнение) отличается.
А что будет, если заменить значение «article_read» на оригинальную функцию read_csv():
Так тоже можно, но это некрасиво и неэффективно. Важно понять, что работа с pandas — это применение функций и методов один за одним, и ничего больше.
Проверьте себя!
Как обычно, небольшой тест для проверки! Выполните его, чтобы лучше запомнить материал!
Выберите used_id , country и topic для пользователей из country_2 . Выведите первые 5 строк!
А вот и решение!
Его можно преподнести одной строкой:
Или, чтобы было понятнее, можно разбить на несколько строк:
В любом случае, логика не отличается. Сначала берется оригинальный dataframe ( article_read ), затем отфильтровываются строки со значением для колонки country — country_2 ( [article_read.country == 'country_2'] ). Потому берутся три нужные колонки ( [['user_id', 'topic', 'country']] ) и в конечном итоге выбираются только первые пять строк ( .head() ).
Итого
Вот и все. В следующей статье вы узнаете больше о разных методах агрегации (например, sum, mean, max, min) и группировки.
Данные очень легко читать из файлов CSV, но они часто хранятся в табличной форме в формате Excel.
pandas предоставляет специальные функции для работы с ним:
Функция read_excel() может читать из файлов Excel 2003 (.xls) и Excel 2007 (.xlsx). Это возможно благодаря модулю xlrd .
Для начала откроем файл Excel и введем данные со следующий таблиц. Разместим их в листах sheet1 и sheet2 . Сохраним файл как ch05_data.xlsx .
white | red | green | black | |
---|---|---|---|---|
a | 12 | 23 | 17 | 18 |
b | 22 | 16 | 19 | 18 |
c | 14 | 23 | 22 | 21 |
yellow | purple | blue | orange | |
---|---|---|---|---|
A | 11 | 16 | 44 | 22 |
B | 20 | 22 | 23 | 44 |
C | 30 | 31 | 37 | 32 |
Для чтения данных из файла XLS нужно всего лишь конвертировать его в Dataframe , используя для этого функцию read_excel() .
По умолчанию готовый объект pandas Dataframe будет состоять из данных первого листа файла. Но если нужно загрузить и второй, то достаточно просто указать его номер (индекс) или название в качестве второго аргумента.
yellow | purple | blue | orange | |
---|---|---|---|---|
A | 11 | 16 | 44 | 22 |
B | 20 | 22 | 23 | 44 |
C | 30 | 31 | 37 | 32 |
yellow | purple | blue | orange | |
---|---|---|---|---|
A | 11 | 16 | 44 | 22 |
B | 20 | 22 | 23 | 44 |
C | 30 | 31 | 37 | 32 |
Запись работает по тому же принципу. Для конвертации объекта Dataframe в Excel нужно написать следующее.
Jan2015 | Feb2015 | Mar2015 | Apr2015 | |
---|---|---|---|---|
exp1 | 0.671044 | 0.437715 | 0.497103 | 0.070595 |
exp2 | 0.864018 | 0.575196 | 0.240343 | 0.471081 |
exp3 | 0.957986 | 0.311648 | 0.381975 | 0.622556 |
exp4 | 0.407909 | 0.015926 | 0.180611 | 0.579783 |
В рабочей директории будет создан файл с соответствующими данными.
Данные JSON
JSON (JavaScript Object Notation) стал одним из самых распространенных стандартных форматов для передачи данных в сети.
Одна из главных его особенностей — гибкость, хотя структура и не похожа на привычные таблицы.
В этом разделе вы узнаете, как использовать функции read_json() и to_json() для использования API. А в следующем — познакомитесь с другим примером взаимодействия со структурированными данными формата, который чаще встречается в реальной жизни.
Начнем с самого полезного примера, когда есть объект Dataframe и его нужно конвертировать в файл JSON. Определим такой объект и используем его для вызова функции to_json() , указав название для итогового файла.
Он будет находится в рабочей папке и включать все данные в формате JSON.
Обратную операцию можно выполнить с помощью функции read_json() . Параметром здесь должен выступать файл с данными.
down | left | right | up | |
---|---|---|---|---|
black | 5 | 7 | 6 | 4 |
blue | 13 | 15 | 14 | 12 |
red | 9 | 11 | 10 | 8 |
white | 1 | 3 | 2 | 0 |
Это был простейший пример, где данные JSON представлены в табличной форме (поскольку источником файла frame.json служил именно такой объект — Dataframe ). Но в большинстве случаев у JSON-файлов нет такой четкой структуры. Поэтому нужно конвертировать файл в табличную форму. Этот процесс называется нормализацией.
Библиотека pandas предоставляет функцию json_normalize() , которая умеет конвертировать объект dict или список в таблицу. Для начала ее нужно импортировать:
Создадим JSON-файл как в следующем примере с помощью любого текстового редактора и сохраним его в рабочей директории как books.json .
Как видите, структура файла более сложная и не похожа на таблицу. В таком случае функция read_json() уже не сработает. Однако данные в нужной форме все еще можно получить. Во-первых, нужно загрузить содержимое файла и конвертировать его в строку.
После этого можно использовать функцию json_normalize() . Например, можно получить список книг. Для этого необходимо указать ключ books в качестве второго параметра.
price | title | |
---|---|---|
0 | 23.56 | XML Cookbook |
1 | 50.70 | Python Fundamentals |
2 | 12.30 | The NumPy library |
3 | 28.60 | Java Enterprise |
4 | 31.35 | HTML5 |
5 | 28.30 | Python for Dummies |
Функция считает содержимое всех элементов, у которых ключом является books . Все свойства будут конвертированы в имена вложенных колонок, а соответствующие значения заполнят объект Dataframe . В качестве индексов будет использоваться возрастающая последовательность чисел.
Однако в этом случае Dataframe включает только внутреннюю информацию. Не лишним было бы добавить и значения остальных ключей на том же уровне. Для этого необходимо добавить другие колонки, вставив список ключей в качестве третьего элемента функции.
price | title | writer | nationality | |
---|---|---|---|---|
0 | 23.56 | XML Cookbook | Mark Ross | USA |
1 | 50.70 | Python Fundamentals | Mark Ross | USA |
2 | 12.30 | The NumPy library | Mark Ross | USA |
3 | 28.60 | Java Enterprise | Barbara Bracket | UK |
4 | 31.35 | HTML5 | Barbara Bracket | UK |
5 | 28.30 | Python for Dummies | Barbara Bracket | UK |
Результатом будет 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 :
Теперь данные объекта Dataframe можно хранить в файле с расширением .h5 . Для начала создадим Dataframe .
Дальше нужен файл HDF5 под названием mydata.h5 . Добавим в него содержимое объекта Dataframe .
Можете догадаться, как хранить несколько структур данных в одном файле HDF5, указав для каждой из них метку. С помощью этого формата можно хранить несколько структур данных в одном файле, при том что он будет представлен переменной store .
Обратный процесс также прост. Учитывая наличие файла HDF5 с разными структурами данных вызвать их можно следующим путем.
Взаимодействие с базами данных
В большинстве приложений текстовые файлы редко выступают источниками данных, просто потому что это не эффективно. Они хранятся в реляционных базах данных (SQL) или альтернативных (NoSQL), которые стали особо популярными в последнее время.
Загрузка из SQL в Dataframe — это простой процесс, а pandas предлагает дополнительные функции для еще большего упрощения.
Модуль pandas.io.sql предоставляет объединенный интерфейс, независимый от базы данных, под названием sqlalchemy . Он упрощает режим соединения, поскольку команды неизменны вне зависимости от типа базы. Для создания соединения используется функция create_engine() . Это же позволяет настроить все необходимые свойства: ввести имя пользователя, пароль и порт, а также создать экземпляр базы данных.
Вот список разных типов баз данных:
Загрузка и запись данных с SQLite3
Для первого примера используем базу данных SQLite, применив встроенный Python sqlite3 . SQLite3 — это инструмент, реализующий реляционную базу данных очень простым путем. Это самый легкий способ добавить ее в любое приложение на Python. С помощью SQLite фактически можно создать встроенную базу данных в одном файле.
Идеальный вариант для тех, кому нужна база, но нет желания устанавливать реальную. SQLite3 же можно использовать для тренировки или для использования функций базы при сборе данных, не выходя за рамки программы.
Создадим объект Dataframe , который будет использоваться для создания новой таблицы в базе данных SQLite3.
white | red | blue | black | green | |
---|---|---|---|---|---|
0 | 0 | 1 | 2 | 3 | 4 |
1 | 5 | 6 | 7 | 8 | 9 |
2 | 10 | 11 | 12 | 13 | 14 |
3 | 15 | 16 | 17 | 18 | 19 |
Теперь нужно реализовать соединение с базой.
Конвертируем объект в таблицу внутри базы данных.
А вот для чтения базы нужно использовать функцию read_sql() , указав название таблицы и движок.
index | white | red | blue | black | green | |
---|---|---|---|---|---|---|
0 | 0 | 0 | 1 | 2 | 3 | 4 |
1 | 1 | 5 | 6 | 7 | 8 | 9 |
2 | 2 | 10 | 11 | 12 | 13 | 14 |
3 | 3 | 15 | 16 | 17 | 18 | 19 |
На примере видно, что даже в этом случае процесс записи очень прост благодаря API библиотеки pandas.
Однако того же можно добиться и без них. Это покажет, почему pandas считается эффективным инструментом для работы с базой данных.
Во-первых, нужно установить соединение и создать таблицу, определив правильные типы данных, которые впоследствии будут загружаться.
Теперь можно добавлять сами данные с помощью SQL INSERT .
Наконец, можно перейти к запросам из базы данных. Это делается с помощью SQL SELECT .
Конструктору Dataframe можно передать список кортежей, а если нужны названия колонок, то их можно найти в атрибуте description своего cursor .
Этот подход куда сложнее.
Загрузка и запись с помощью PostgreSQL
Начиная с pandas 0.14, PostgreSQL также поддерживается. Для начала нужно проверить версию библиотеки.
Для запуска примера база PostgreSQL должна быть установлена в системе. В этом примере была создана база postgres , где пользователя зовут postgres , а пароль — password . Замените значения на соответствующие в вашей системе.
Сначала нужно установить библиотеку psycopg2 , которая предназначена для управления соединениями с базой данных.
Или с помощью PyPl:
Теперь можно установить соединение:
Примечание. В этом примере вне зависимости от установленной версии в Windows может возникать ошибка:
Это почти наверняка значит, что DLL для PostgreSQL (в частности, libpq.dll) не установлены в PATH. Добавьте одну из папок postgres\x.x\bin в PATH и теперь соединение Python с базой данных PostgreSQL должно работать без проблем.
Создайте объект Dataframe:
Вот как просто переносить данные в таблицу. С помощью to_sql() вы без проблем запишите их в таблицу dataframe .
pgAdmin III — это графическое приложение для управления базами данных PostgreSQL. Крайне удобный инструмент для Windows и Linux. С его помощью можно легко изучить созданную базу данных.
Если вы хорошо знаете язык SQL, то есть и классический способ рассмотреть созданную таблицу с помощью сессии psql .
В этом случае соединение произошло от имени пользователя postgres . Оно может отличаться. После соединения просто осуществите SQL-запрос к таблице.
Даже конвертация таблицы в объект Dataframe — тривиальная задача. Для этого есть функция read_sql_table() , которая считывает данные из таблицы и записывает их в новый объект.
Но когда нужно считать данные из базы, конвертация целой таблицы в Dataframe — не самая полезная операция. Те, кто работают с реляционными базами данных, предпочитают использовать для этих целей SQL. Он подходит для выбора того. какие данные и в каком виде требуется получить с помощью SQL-запроса.
Текст запроса может быть использован в функции read_sql_query() .
Чтение и запись данных в базу данных NoSQL: MongoDB
Среди всех баз данных NoSQL (BerkeleyDB, Tokyo Cabinet и MongoDB) MongoDB — одна из самых распространенных. Она доступна в разных системах и подходит для чтения и записи данных при анализе данных.
Работу нужно начать с того, что указать на конкретную директорию.
Теперь, когда сервис случает порт 27017, к базе можно подключиться, используя официальный драйвер для MongoDB, pymongo .
Один экземпляр MongoDB способен поддерживать несколько баз данных одновременно. Поэтому нужно указать на конкретную.
Когда база данных определена, нужно определить коллекцию. Она представляет собой группу документов, сохраненных в MongoDB. Ее можно воспринимать как эквивалент таблиц из SQL.
Теперь нужно добавить данные в коллекцию. Создайте Dataframe .
white | red | blue | black | green | |
---|---|---|---|---|---|
0 | 0 | 1 | 2 | 3 | 4 |
1 | 5 | 6 | 7 | 8 | 9 |
2 | 10 | 11 | 12 | 13 | 14 |
3 | 15 | 16 | 17 | 18 | 19 |
Перед добавлением его нужно конвертировать в формат JSON. Процесс конвертации не такой простой, потому что нужно задать данные, которые будут записаны в базу, чтобы потом с легкостью извлекать их снова в объекте.
Теперь все готово для добавления документа в коллекцию. Для этого используется функция insert() .
В этом случае каждый объект представлен на отдельной строке. Когда данные загружены в документ базы данных, можно выполнить и обратный процесс, то есть, прочитать данные и конвертировать их в Dataframe .
Pandas можно использовать для чтения и записи файлов Excel с помощью Python. Это работает по аналогии с другими форматами. В этом материале рассмотрим, как это делается с помощью DataFrame.
Помимо чтения и записи рассмотрим, как записывать несколько DataFrame в Excel-файл, как считывать определенные строки и колонки из таблицы и как задавать имена для одной или нескольких таблиц в файле.
Установка Pandas
Для начала Pandas нужно установить. Проще всего это сделать с помощью pip .
Если у вас Windows, Linux или macOS:
В процессе можно столкнуться с ошибками ModuleNotFoundError или ImportError при попытке запустить этот код. Например:
В таком случае нужно установить недостающие модули:
Запись в файл Excel с python
Будем хранить информацию, которую нужно записать в файл Excel, в DataFrame . А с помощью встроенной функции to_excel() ее можно будет записать в Excel.
Сначала импортируем модуль pandas . Потом используем словарь для заполнения DataFrame :
Ключи в словаре — это названия колонок. А значения станут строками с информацией.
Теперь можно использовать функцию to_excel() для записи содержимого в файл. Единственный аргумент — это путь к файлу:
Стоит обратить внимание на то, что в этом примере не использовались параметры. Таким образом название листа в файле останется по умолчанию — «Sheet1». В файле может быть и дополнительная колонка с числами. Эти числа представляют собой индексы, которые взяты напрямую из DataFrame.
Поменять название листа можно, добавив параметр sheet_name в вызов to_excel() :
Также можно добавили параметр index со значением False , чтобы избавиться от колонки с индексами. Теперь файл Excel будет выглядеть следующим образом:
Запись нескольких DataFrame в файл Excel
Также есть возможность записать несколько DataFrame в файл Excel. Для этого можно указать отдельный лист для каждого объекта:
Здесь создаются 3 разных DataFrame с разными названиями, которые включают имена сотрудников, а также размер их зарплаты. Каждый объект заполняется соответствующим словарем.
Объединим все три в переменной salary_sheets , где каждый ключ будет названием листа, а значение — объектом DataFrame .
Дальше используем движок xlsxwriter для создания объекта writer . Он и передается функции to_excel() .
Перед записью пройдемся по ключам salary_sheets и для каждого ключа запишем содержимое в лист с соответствующим именем. Вот сгенерированный файл:
Можно увидеть, что в этом файле Excel есть три листа: Group1, Group2 и Group3. Каждый из этих листов содержит имена сотрудников и их зарплаты в соответствии с данными в трех DataFrame из кода.
Параметр движка в функции to_excel() используется для определения модуля, который задействуется библиотекой Pandas для создания файла Excel. В этом случае использовался xslswriter , который нужен для работы с классом ExcelWriter . Разные движка можно определять в соответствии с их функциями.
В зависимости от установленных в системе модулей Python другими параметрами для движка могут быть openpyxl (для xlsx или xlsm) и xlwt (для xls). Подробности о модуле xlswriter можно найти в официальной документации.
Наконец, в коде была строка writer.save() , которая нужна для сохранения файла на диске.
Чтение файлов Excel с python
По аналогии с записью объектов DataFrame в файл Excel, эти файлы можно и читать, сохраняя данные в объект DataFrame . Для этого достаточно воспользоваться функцией read_excel() :
Excel — это чрезвычайно распространённый инструмент для анализа данных. С ним легко научиться работать, есть он практически на каждом компьютере, а тот, кто его освоил, может с его помощью решать довольно сложные задачи. Python часто считают инструментом, возможности которого практически безграничны, но который освоить сложнее, чем Excel. Автор материала, перевод которого мы сегодня публикуем, хочет рассказать о решении с помощью Python трёх задач, которые обычно решают в Excel. Эта статья представляет собой нечто вроде введения в Python для тех, кто хорошо знает Excel.
Загрузка данных
Начнём с импорта Python-библиотеки pandas и с загрузки в датафреймы данных, которые хранятся на листах sales и states книги Excel. Такие же имена мы дадим и соответствующим датафреймам.
Теперь воспользуемся методом .head() датафрейма sales для того чтобы вывести элементы, находящиеся в начале датафрейма:
Сравним то, что будет выведено, с тем, что можно видеть в Excel.
Сравнение внешнего вида данных, выводимых в Excel, с внешним видом данных, выводимых из датафрейма pandas
Тут можно видеть, что результаты визуализации данных из датафрейма очень похожи на то, что можно видеть в Excel. Но тут имеются и некоторые очень важные различия:
- Нумерация строк в Excel начинается с 1, а в pandas номер (индекс) первой строки равняется 0.
- В Excel столбцы имеют буквенные обозначения, начинающиеся с буквы A , а в pandas названия столбцов соответствуют именам соответствующих переменных.
Реализация возможностей Excel-функции IF в Python
В Excel существует очень удобная функция IF , которая позволяет, например, записать что-либо в ячейку, основываясь на проверке того, что находится в другой ячейке. Предположим, нужно создать в Excel новый столбец, ячейки которого будут сообщать нам о том, превышают ли 500 значения, записанные в соответствующие ячейки столбца B . В Excel такому столбцу (в нашем случае это столбец E ) можно назначить заголовок MoreThan500 , записав соответствующий текст в ячейку E1 . После этого, в ячейке E2 , можно ввести следующее:
Использование функции IF в Excel
Для того чтобы сделать то же самое с использованием pandas, можно воспользоваться списковым включением (list comprehension):
Списковые включения в Python: если текущее значение больше 500 — в список попадает Yes, в противном случае — No
Списковые включения — это отличное средство для решения подобных задач, позволяющее упростить код за счёт уменьшения потребности в сложных конструкциях вида if/else. Ту же задачу можно решить и с помощью if/else, но предложенный подход экономит время и делает код немного чище. Подробности о списковых включениях можно найти здесь.
Реализация возможностей Excel-функции VLOOKUP в Python
В нашем наборе данных, на одном из листов Excel, есть названия городов, а на другом — названия штатов и провинций. Как узнать о том, где именно находится каждый город? Для этого подходит Excel-функция VLOOKUP , с помощью которой можно связать данные двух таблиц. Эта функция работает по принципу левого соединения, когда сохраняется каждая запись из набора данных, находящегося в левой части выражения. Применяя функцию VLOOKUP , мы предлагаем системе выполнить поиск определённого значения в заданном столбце указанного листа, а затем — вернуть значение, которое находится на заданное число столбцов правее найденного значения. Вот как это выглядит:
Зададим на листе sales заголовок столбца F как State и воспользуемся функцией VLOOKUP для того чтобы заполнить ячейки этого столбца названиями штатов и провинций, в которых расположены города.
Использование функции VLOOKUP в Excel
В Python сделать то же самое можно, воспользовавшись методом merge из pandas. Он принимает два датафрейма и объединяет их. Для решения этой задачи нам понадобится следующий код:
- Первый аргумент метода merge — это исходный датафрейм.
- Второй аргумент — это датафрейм, в котором мы ищем значения.
- Аргумент how указывает на то, как именно мы хотим соединить данные.
- Аргумент on указывает на переменную, по которой нужно выполнить соединение (тут ещё можно использовать аргументы left_on и right_on , нужные в том случае, если интересующие нас данные в разных датафреймах названы по-разному).
Сводные таблицы
Сводные таблицы (Pivot Tables) — это одна из самых мощных возможностей Excel. Такие таблицы позволяют очень быстро извлекать ценные сведения из больших наборов данных. Создадим в Excel сводную таблицу, выводящую сведения о суммарных продажах по каждому городу.
Создание сводной таблицы в Excel
Как видите, для создания подобной таблицы достаточно перетащить поле City в раздел Rows , а поле Sales — в раздел Values . После этого Excel автоматически выведет суммарные продажи для каждого города.
Для того чтобы создать такую же сводную таблицу в pandas, нужно будет написать следующий код:
- Здесь мы используем метод sales.pivot_table , сообщая pandas о том, что мы хотим создать сводную таблицу, основанную на датафрейме sales .
- Аргумент index указывает на столбец, по которому мы хотим агрегировать данные.
- Аргумент values указывает на то, какие значения мы собираемся агрегировать.
- Аргумент aggfunc задаёт функцию, которую мы хотим использовать при обработке значений (тут ещё можно воспользоваться функциями mean , max , min и так далее).
Итоги
Из этого материала вы узнали о том, как импортировать Excel-данные в pandas, о том, как реализовать средствами Python и pandas возможности Excel-функций IF и VLOOKUP , а также о том, как воспроизвести средствами pandas функционал сводных таблиц Excel. Возможно, сейчас вы задаётесь вопросом о том, зачем вам пользоваться pandas, если то же самое можно сделать и в Excel. На этот вопрос нет однозначного ответа. Python позволяет создавать код, который поддаётся тонкой настройке и глубокому исследованию. Такой код можно использовать многократно. Средствами Python можно описывать очень сложные схемы анализа данных. А возможностей Excel, вероятно, достаточно лишь для менее масштабных исследований данных. Если вы до этого момента пользовались только Excel — рекомендую испытать Python и pandas, и узнать о том, что у вас из этого получится.
А какие инструменты вы используете для анализа данных?
Напоминаем, что у нас продолжается конкурс прогнозов, в котором можно выиграть новенький iPhone. Еще есть время ворваться в него, и сделать максимально точный прогноз по злободневным величинам.
Читайте также: