Машинное обучение с Pytnon : чтение и запись данных в формате Excel

Почти год назад прошел все шесть курсов "Профессиональная сертификация 'IBM Machine Learning', пора "освежить" в памяти полученные знания и для этого решил написать серию статей по материалам этого курса. Подход при этом будет такой : за основу последовательно беру пройденные темы и прорабатываю их на своих данных с некоторым расширением, которое мне кажется полезным. Начну, как и полагается, с общего похода работы с данными.

Работа с данными начинается с их извлечения. В материалах курса рассматривается загрузка данных разных форматов и источников, но так как я в работе практически всегда использую файлы Excel, то буду разбирать, как это можно сделать используя библиотеку Pandas при работе с файлами Excel. 

Библиотека pandas поддерживает чтение данных в формате Excel 2003 и более поздних форматах с помощью функции pd.read_excel() или класса ExcelFile. Оба способа используют либо пакет XLRD, либо пакет OpenPyXL, поэтому необходимо убедиться в том, что один из них установлен в нашей среде Python.

Работать будем с файлом, состоящим из пяти листов, на каждом из которых находятся месячные продажи одного магазина с января 2015 по декабрь 2019.

Загружаем необходимую библиотеку с настройками

import pandas as pd

pd.set_option("display.max_rows", 5)
pd.set_option("display.precision", 3)

В самом простом случае read_excel принимает путь к файлу Excel, все остальные параметры по умолчанию. В результате мы получаем DataFrame с первого листа файла.

#Использование всех значений по умолчанию
# Возвращает DataFrame
df=pd.read_excel("five_shops_base.xlsx")
print(type(df))
df

<class 'pandas.core.frame.DataFrame'>

month
shop
sex
clothing_cat
season
bal_unit
bal_val
sale
rev_wd
rev
0
2015-01-01
Sh1
female
pants
winter
6
374
5
291
247
1
2015-01-01
Sh1
female
pants
universal
442
43871
176
15749
12516
...
...
...
...
...
...
...
...
...
...
...
1450
2019-12-01
Sh1
male
t-shirt
summer
39
1118
47
1498
1106
1451
2019-12-01
Sh1
male
t-shirt
universal
141
6752
42
2050
1520
1452 rows × 10 columns

Можно использовать второй аргумент для указания загружаемого листа.
Указание листов
Атрибут sheet_names в файле Excel предоставляет доступ к списку листов.
    • Аргументы имя листа позволяют указать лист или листы для чтения.
    • Значение по умолчанию для sheet_name равно 0, что означает чтение 
первого листа.
    • Можно передать строку для ссылки на имя определенного листа в книге.
    • Можно передать целое число для ссылки на индекс листа. 
        Индексы следуют соглашению Python, начиная с 0.
    • Можно передать список либо строк, либо целых чисел, чтобы вернуть словарь указанных листов.
    • Можно передайть None, чтобы вернуть словарь всех доступных листов.

Загружаем данные по имени листа
df=pd.read_excel("five_shops_base.xlsx", sheet_name="Sh1")
print(type(df))
df

<class 'pandas.core.frame.DataFrame'>

month
shop
sex
clothing_cat
season
bal_unit
bal_val
sale
rev_wd
rev
0
2015-01-01
Sh1
female
pants
winter
6
374
5
291
247
1
2015-01-01
Sh1
female
pants
universal
442
43871
176
15749
12516
...
...
...
...
...
...
...
...
...
...
...
1450
2019-12-01
Sh1
male
t-shirt
summer
39
1118
47
1498
1106
1451
2019-12-01
Sh1
male
t-shirt
universal
141
6752
42
2050
1520
1452 rows × 10 columns

Загружаем данные по номеру листа
df=pd.read_excel("five_shops_base.xlsx", 0)
print(type(df))
df

<class 'pandas.core.frame.DataFrame'>

month
shop
sex
clothing_cat
season
bal_unit
bal_val
sale
rev_wd
rev
0
2015-01-01
Sh1
female
pants
winter
6
374
5
291
247
1
2015-01-01
Sh1
female
pants
universal
442
43871
176
15749
12516
...
...
...
...
...
...
...
...
...
...
...
1450
2019-12-01
Sh1
male
t-shirt
summer
39
1118
47
1498
1106
1451
2019-12-01
Sh1
male
t-shirt
universal
141
6752
42
2050
1520
1452 rows × 10 columns

Указывая вместо имени параметр None загружаем все листы и получаем словарь
 датафреймов
#Использование None для получения всех листов:
# Возвращает словарь DataFrames
df=pd.read_excel("five_shops_base.xlsx", sheet_name=None)
print(type(df),'\n',df.keys())

<class 'dict'> 
 dict_keys(['Sh1', 'Sh2', 'Sh3', 'Sh4', 'Sh5'])

Для того, чтобы загрузить несколько листов, используем список

#Использование списка для получения нескольких листов:
# Возвращает листы из списка в виде словаря DataFrames.
df=pd.read_excel("five_shops_base.xlsx", sheet_name=["Sh1", "Sh5"])
print(type(df),'\n',df.keys())

<class 'dict'> 
 dict_keys(['Sh1', 'Sh5'])

Третий параметр header касается заголовка. По умолчанию он равен нулю - 
это означает, что заголовки столбцов лежат в строке с номером 0, если заголовка нет, то нужно присвоить этому параметру значение None.


Класс ExcelFile 
Чтобы упростить работу с несколькими листами из одного и того же файла, класс
 ExcelFile можно использовать для переноса файла и
может быть передано в read_excel Будет повышена производительность при чтении нескольких листов, поскольку файл
 считывается в
память только один раз

Для работы с Excel-файлом можно использовать класс ExcelFile,
объекты которого связываются с определенным файлом на диске.
Объекты этого класса являются контекстными менеджерами, что делает
возможным работу с конструкцией with и упрощает работу с несколькими листами

with pd.ExcelFile("five_shops_base.xlsx") as xls:
    df1 = pd.read_excel(xls, "Sh1")
    df2 = pd.read_excel(xls, "Sh2")
print(df1[['month','shop']])
print(df2[['month','shop']])

month shop
0    2015-01-01  Sh1
1    2015-01-01  Sh1
...         ...  ...
1450 2019-12-01  Sh1
1451 2019-12-01  Sh1

[1452 rows x 2 columns]
          month shop
0    2015-01-01  Sh2
1    2015-01-01  Sh2
...         ...  ...
1350 2019-10-01  Sh2
1351 2019-10-01  Sh2

[1352 rows x 2 columns]



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

df=pd.read_excel("five_shops_base.xlsx", "Sh1", usecols=["month","season","rev"])
df


month
season
rev
0
2015-01-01
winter
247
1
2015-01-01
universal
12516
...
...
...
...
1450
2019-12-01
summer
1106
1451
2019-12-01
universal
1520
1452 rows × 3 columns
В качестве параметра в usecols можно передать лямбда-функцию, что позволит 
отобрать столбцы по определенному правилу, например колонки с названием меньше
 6 символов
df=pd.read_excel("five_shops_base.xlsx", "Sh1", usecols=lambda x: len(x)<6)
df

month
shop
sex
sale
rev
0
2015-01-01
Sh1
female
5
247
1
2015-01-01
Sh1
female
176
12516
...
...
...
...
...
...
1450
2019-12-01
Sh1
male
47
1106
1451
2019-12-01
Sh1
male
42
1520
1452 rows × 5 columns

Значения, подобные дате и времени, обычно автоматически преобразуются в
соответствующий тип dtype при чтении файла Excel. Но если у вас есть столбец строк,
 которые выглядят как даты (но на самом деле не отформатированы как даты в Excel),
 можно использовать ключевое слово parse_dates для разбора этих строк до даты и 
времени:
df=pd.read_excel("five_shops_base.xlsx", "Sh1", parse_dates=["month"])
df
month
shop
sex
clothing_cat
season
bal_unit
bal_val
sale
rev_wd
rev
0
2015-01-01
Sh1
female
pants
winter
6
374
5
291
247
1
2015-01-01
Sh1
female
pants
universal
442
43871
176
15749
12516
...
...
...
...
...
...
...
...
...
...
...
1450
2019-12-01
Sh1
male
t-shirt
summer
39
1118
47
1498
1106
1451
2019-12-01
Sh1
male
t-shirt
universal
141
6752
42
2050
1520
1452 rows × 10 columns

Преобразование типов
Содержимое ячеек Excel можно преобразовать с помощью опции преобразователей. Например, чтобы преобразовать столбец в
значение типа float :
df=pd.read_excel("five_shops_base.xlsx", "Sh1", parse_dates=["month"],
converters={"rev": float})
df.dtypes

month     datetime64[ns]
shop              object
               ...      
rev_wd             int64
rev              float64
Length: 10, dtype: object

converters - это словарь, содержащий отображение номеров или имен столбцов на
функции. Например, {'rev': f} означает, что нужно применить функцию f ко всем
значением в столбце rev, в данном случае мы использовали преобразование типов

В качестве альтернативы преобразователям тип всего столбца можно указать
 с помощью ключевого слова dtype, которое принимает словарь, отображающий имена 
столбцов в типы, чтобы интерпретировать данные без вывода типа.

df=pd.read_excel("five_shops_base.xlsx", dtype={"rev_wd": "float", "rev": "float"})
df.dtypes

month     datetime64[ns]
shop              object
               ...      
rev_wd           float64
rev              float64
Length: 10, dtype: object

Использование для загрузки буфер обмена

Очень удобный способ получить данные — использовать метод read_clipboard(), 
который берет содержимое буфера буфера обмена.
Например, можно скопировать таблицу из файла Excel в буфер обмена, а затем
 импортировать данные непосредственно в DataFrame, вызвав:

df = pd.read_clipboard()

Я часто пользуюсь этим методом, когда необходимо быстро загрузить и использовать
 данные из какого-нибудь файла.

Чтобы записать объект DataFrame на лист файла Excel используем метод 
to_excel, первый аргумент - имя файла, а необязательный второй аргумент имя листа,
на который должен быть записан DataFrame, дополнительно полезно указать 
index=False, чтобы не переносить из DataFrame индекс.
df.to_excel("Sh1.xlsx", sheet_name="Sh1",index=False)
Чтобы записать отдельные DataFrame данных на отдельные листы в одном
файле Excel, можно использовать ExcelWriter.

df=pd.read_excel("five_shops_base.xlsx", sheet_name=None)
with pd.ExcelWriter("df_new.xlsx") as writer:
    df.get('Sh1').to_excel(writer, sheet_name="Sh1")
    df.get('Sh2').to_excel(writer, sheet_name="Sh2")

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