Крупные организации и предприятия часто хранят данные в электронных таблицах и нуждаются в интерфейсе для ввода этих данных в свои веб-приложения. Общая идея состоит в том, чтобы загрузить файл, прочитать его содержимое и сохранить его либо в файлах, либо в базах данных, которые использует веб-приложение. Организациям также может потребоваться экспортировать данные из веб-приложения. Например, им может потребоваться экспортировать оценки всех учащихся в классе. Опять же, электронные таблицы являются предпочтительным средством.
В этом посте мы обсудим различные способы обработки этих файлов и их анализа для получения необходимой информации с помощью Python.
- Краткое руководство по работе с электронными таблицами
- Настройка среды Python
- Чтение электронных таблиц
- Откройте файл электронной таблицы
- Открытие определенного листа
- Получение данных из ячеек
- Создание электронных таблиц
- Создать новый файл
- Добавление листов
- Дополнительные возможности при сохранении таблицы
- Чтение старых таблиц (.xls)
- Краткое описание файлов CSV
- Вывод
Краткое руководство по работе с электронными таблицами
Прежде чем анализировать электронные таблицы, вы должны понять, как они структурированы. Файл электронной таблицы — это набор листов, а каждый лист — это набор ячеек данных, помещенных в сетку, похожую на таблицу. На листе ячейка данных идентифицируется двумя значениями: номерами строки и столбца.
Например, на скриншоте выше электронная таблица содержит только один лист «Sheet1». Ячейка «2A» соответствует второй строке и первому столбцу. Значение ячейки 2А равно 1.
Хотя программы с графическим интерфейсом назначают буквы именам столбцов, когда мы анализируем данные, мы начинаем номера строк и столбцов с 0. Это означает, что ячейка 2A будет соответствовать (1, 0), 4B — (1,3), От 3C до (2, 2) и так далее.
Настройка среды Python
Мы будем использовать Python 3 для чтения и записи электронных таблиц. Для чтения и записи файлов XLSX необходимо установить модуль Pandas. Вы можете сделать это через один из установщиков Python: pipили easy_install. Pandas использует openpyxlмодуль для чтения новых файлов электронных таблиц (.xlsx) и xlrdмодули для чтения устаревших электронных таблиц (файлы.xls). Оба они openpyxlи xlrdустанавливаются как зависимости при установке Pandas:
pip3 install pandas
Для чтения и записи файлов CSV вам понадобится csvмодуль, который предустановлен вместе с Python. Вы также можете читать файлы CSV через Pandas.
Чтение электронных таблиц
Если у вас есть файл и вы хотите проанализировать содержащиеся в нем данные, вам необходимо выполнить следующее в таком порядке:
- импортировать pandas модуль
- откройте файл электронной таблицы (или книгу)
- выберите лист
- извлекать значения определенных ячеек данных
Откройте файл электронной таблицы
Давайте сначала откроем файл на Python. Чтобы продолжить, вы можете использовать следующий образец электронной таблицы, любезно предоставленный Learning Container :
import pandas as pd workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx') workbook.head()
Segment | Country | Product | Discount Band | Units Sold | Manufacturing Price | Sale Price | Gross Sales | Discounts | Sales | COGS | Profit | Date | Month Number | Month Name | Year | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | Government | Canada | Carretera | None | 1618.5 | 3 | 20 | 32370.0 | 0.0 | 32370.0 | 16185.0 | 16185.0 | 2014-01-01 | 1 | January | 2014 |
1 | Government | Germany | Carretera | None | 1321.0 | 3 | 20 | 26420.0 | 0.0 | 26420.0 | 13210.0 | 13210.0 | 2014-01-01 | 1 | January | 2014 |
2 | Midmarket | France | Carretera | None | 2178.0 | 3 | 15 | 32670.0 | 0.0 | 32670.0 | 21780.0 | 10890.0 | 2014-06-01 | 6 | June | 2014 |
3 | Midmarket | Germany | Carretera | None | 888.0 | 3 | 15 | 13320.0 | 0.0 | 13320.0 | 8880.0 | 4440.0 | 2014-06-01 | 6 | June | 2014 |
4 | Midmarket | Mexico | Carretera | None | 2470.0 | 3 | 15 | 37050.0 | 0.0 | 37050.0 | 24700.0 | 12350.0 | 2014-06-01 | 6 | June | 2014 |
Pandas читает электронную таблицу как таблицу и сохраняет ее как фрейм данных Pandas.
Если ваш файл содержит символы, отличные от ASCII, вы должны открыть его в формате Unicode следующим образом:
import sys workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx', encoding=sys.getfilesystemencoding())
Если ваша электронная таблица очень большая, вы можете добавить аргумент use_cols, который загружает только определенные столбцы в фрейм данных. Например, следующий аргумент будет читать только первые пять столбцов:
workbook = pd.read_excel('~/Desktop/import-export-data.xlsx', usecols = 'A:E') workbook.head()
Segment | Country | Product | Discount Band | Units Sold | |
---|---|---|---|---|---|
0 | Government | Canada | Carretera | None | 1618.5 |
1 | Government | Germany | Carretera | None | 1321.0 |
2 | Midmarket | France | Carretera | None | 2178.0 |
3 | Midmarket | Germany | Carretera | None | 888.0 |
4 | Midmarket | Mexico | Carretera | None | 2470.0 |
Кроме того, вы можете использовать nrowsи skiprowsаргументы, чтобы читать только определенное количество строк, или игнорировать определенное количество строк в начале, соответственно.
Открытие определенного листа
Вы можете выбрать определенный лист из своей электронной таблицы с помощью sheet_nameаргумента. По умолчанию функция read_excel () анализирует первый лист в файле. Вы можете указать имя листа в виде строки или индекс листа (начиная с 0):
# Read the sheet with the name 'Sheet1' worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 'Sheet1')
# Read the 1st sheet in the file worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 0)
Вы также можете выбрать количество листов для хранения в виде фреймов данных Pandas, передав список в качестве sheet_nameаргумента:
# Read the first two sheets and a sheet with the name 'Sheet 3' worksheets = pd.read_excel('~/Desktop/import-export-data.xlsx', sheet_name = [0, 1, 'Sheet 3'])
Получение данных из ячеек
После того, как вы выбрали рабочий лист во фрейм данных, вы можете извлечь значение определенной ячейки данных, запросив в фреймворке Pandas:
import pandas as pd workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx') # Print the 1st value of the Product column print(workbook['Product'].iloc[0]) => Carretera
Метод .iloc () помогает искать значение на основе местоположения индекса. В приведенном выше коде.iloc()выполняется поиск значения в нулевой позиции индекса. Точно так же вы можете искать значение, используя метку с помощью метода.loc (). Например, если вы передаете аргумент 0к.loc()методу, он будет искать для метки 0в индексе:
print(workbook['Product'].loc[0]) => Carretera
Вы можете запросить свой набор данных после его загрузки в фрейм данных с помощью встроенных функций в Pandas.
Создание электронных таблиц
Рабочий процесс для создания рабочих листов аналогичен описанному в предыдущем разделе.
- импортировать pandasмодуль
- сохранять данные в книгу
- создать лист в книге
- добавить стиль к ячейкам в книге
Создать новый файл
Чтобы создать новый файл, нам сначала понадобится фрейм данных. Давайте воссоздадим демонстрационный лист из верхней части статьи:
import pandas as pd name = ['John', 'Mary', 'Sherlock'] age = [11, 12, 13] df = pd.DataFrame({ 'Name': name, 'Age': age }) df.index.name = 'ID'
Затем вы можете создать новый файл электронной таблицы, вызвав функцию to_excel () в фрейме данных, указав имя файла, который он должен сохранить как:
df.to_excel('my_file.xlsx')
Вы также можете открыть тот же файл с помощью функции read_excel().
Добавление листов
Вы можете сохранить фрейм данных как определенный лист в книге, используя sheet_nameаргумент. Значение этого аргумента по умолчанию Sheet1:
df.to_excel('my_file.xlsx', sheet_name = 'My Sheet')
Дополнительные возможности при сохранении таблицы
Вы можете использовать класс ExcelWriter, чтобы получить больше возможностей при сохранении в электронную таблицу. Если вы хотите сохранить несколько фреймов данных в одном файле, вы можете использовать следующий синтаксис:
import pandas as pd workbook = pd.read_excel('my_file.xlsx') # Creating a copy of workbook workbook_2 = workbook.copy() with pd.ExcelWriter('my_file_1.xlsx') as writer: workbook.to_excel(writer, sheet_name='Sheet1') workbook_2.to_excel(writer, sheet_name='Sheet2')
Чтобы добавить фрейм данных в существующую электронную таблицу, используйте modeаргумент. Обратите внимание, что режим добавления поддерживается только в том случае, если вы указываете движок как openpyxl:
with pd.ExcelWriter('my_file_1.xlsx', engine="openpyxl", mode='a') as writer: workbook_2.to_excel(writer, sheet_name='Sheet3'
Кроме того, используйте date_formatи datetime_formatдля установки значений даты и времени:
with pd.ExcelWriter('my_file.xlsx', date_format='YYYY-MM-DD', datetime_format='YYYY-MM-DD HH:MM:SS') as writer: workbook.to_excel(writer)
Чтение старых таблиц (.xls)
Вы можете читать устаревшие электронные таблицы с.xls расширением, используя тот же синтаксис в Pandas:
workbook = pd.read_excel('my_file_name.xls')
Пока вы использовали ту же read_excel()функцию, Pandas использует xlrdдвижок для ее чтения. Вы можете читать и писать устаревшие электронные таблицы, используя тот же синтаксис, который мы обсуждали ранее в этом руководстве.
Краткое описание файлов CSV
CSV означает «значения, разделенные запятыми» (или иногда разделенные символами, если в качестве разделителя используется какой-либо символ, отличный от запятой), а имя довольно понятно. Типичный CSV-файл выглядит следующим образом:
'ID', 'Name', 'Age' '1', 'John', '11' '2', 'Mary', '12' '3', 'Sherlock', '13'
Вы можете конвертировать электронные таблицы в файлы CSV, чтобы упростить анализ. Файлы CSV можно легко проанализировать с помощью csvмодуля в Python в дополнение к Pandas:
workbook = pd.read_csv('my_file_name.csv')
Вывод
Как я упоминал ранее, создание и анализ электронных таблиц неизбежно, когда вы работаете с огромными веб-приложениями. Таким образом, знакомство с библиотеками синтаксического анализа может помочь вам только тогда, когда возникнет необходимость.