Как считать excel в pandas

Хотя многие Data Scientist’ы больше привыкли работать с CSV-файлами, на практике очень часто приходится сталкиваться с обычными Excel-таблицами. Поэтому сегодня мы расскажем, как читать Excel-файлы в Pandas, а также рассмотрим основные возможности Python-библиотеки OpenPyXL для чтения метаданных ячеек.

Дополнительные зависимости для возможности чтения Excel таблиц

Для чтения таблиц Excel в Pandas требуются дополнительные зависимости:

• xlrd поддерживает старые и новые форматы MS Excel [1];
• OpenPyXL поддерживает новые форматы MS Excel (.xlsx) [2];
• ODFpy поддерживает свободные форматы OpenDocument (.odf, .ods и .odt) [3];
• pyxlsb поддерживает бинарные MS Excel файлы (формат .xlsb) [4].

Мы рекомендуем установить только OpenPyXL, поскольку он нам пригодится в дальнейшем. Для этого в командной строке прописывается следующая операция:

pip install openpyxl

Затем в Pandas нужно указать путь к Excel-файлу и одну из установленных зависимостей. Python-код выглядит следующим образом:

import pandas as pd
pd.read_excel(io='temp1.xlsx', engine='openpyxl')
#
     Name  Age  Weight
0    Alex   35      87
1   Lesha   57      72
2  Nastya   21      64

Читаем несколько листов

Excel-файл может содержать несколько листов. В Pandas, чтобы прочитать конкретный лист, в аргументе нужно указать sheet_name. Можно указать список названий листов, тогда Pandas вернет словарь (dict) с объектами DataFrame:

dfs = pd.read_excel(io='temp1.xlsx',
                    engine='openpyxl',
                    sheet_name=['Sheet1', 'Sheet2'])
dfs
#
{'Sheet1':      Name  Age  Weight
 0    Alex   35      87
 1   Lesha   57      72
 2  Nastya   21      64,
 'Sheet2':     Name  Age  Weight
 0  Gosha   43      95
 1   Anna   24      65
 2   Lena   22      78}

Если таблицы в словаре имеют одинаковые атрибуты, то их можно объединить в один DataFrame. В Python это выглядит так:

pd.concat(dfs).reset_index(drop=True)
     Name  Age  Weight
0    Alex   35      87
1   Lesha   57      72
2  Nastya   21      64
3   Gosha   43      95
4    Anna   24      65
5    Lena   22      78

Указание диапазонов

Таблицы могут размещаться не в самом начале, а как, например, на рисунке ниже. Как видим, таблица располагается в диапазоне A:F.

Чтобы прочитать такую таблицу, нужно указать диапазон в аргументе usecols. Также дополнительно можно добавить header — номер заголовка таблицы, а также nrows — количество строк, которые нужно прочитать. В аргументе header всегда передается номер строки на единицу меньше, чем в Excel-файле, поскольку в Python индексация начинается с 0 (на рисунке это номер 5, тогда указываем 4):

pd.read_excel(io='temp1.xlsx',
              engine='openpyxl',
              usecols='D:F',
              header=4, # в excel это №5
              nrows=3)
#
    Name  Age  Weight
0  Gosha   43      95
1   Anna   24      65
2   Lena   22      78

Читаем таблицы в OpenPyXL

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

from openpyxl import load_workbook
wb = load_workbook('temp2.xlsx')
ws = wb['Лист1']
type(ws)
# openpyxl.worksheet.worksheet.Worksheet

Допустим, имеется Excel-файл с несколькими таблицами на листе (см. рисунок выше). Если бы мы использовали Pandas, то он бы выдал следующий результат:

pd.read_excel(io='temp2.xlsx',
              engine='openpyxl')
#
     Name  Age  Weight  Unnamed: 3 Name.1  Age.1  Weight.1
0    Alex   35      87         NaN  Tanya     25        66
1   Lesha   57      72         NaN  Gosha     43        77
2  Nastya   21      64         NaN  Tolya     32        54

Можно, конечно, заняться обработкой и привести таблицы в нормальный вид, а можно воспользоваться OpenPyXL, который хранит таблицу и его диапазон в словаре. Чтобы посмотреть этот словарь, нужно вызвать ws.tables.items. Вот так выглядит Python-код:

ws.tables.items()
wb = load_workbook('temp2.xlsx')
ws = wb['Лист1']
ws.tables.items()
#
[('Таблица1', 'A1:C4'), ('Таблица13', 'E1:G4')]

Обращаясь к каждому диапазону, можно проходить по каждой строке или столбцу, а внутри них – по каждой ячейке. Например, следующий код на Python таблицы объединяет строки в список, где первая строка уходит на заголовок, а затем преобразует их в DataFrame:

dfs = []
for table_name, value in ws.tables.items():
    table = ws[value]
    header, *body = [[cell.value for cell in row]
                      for row in table]
    df = pd.DataFrame(body, columns=header)
    dfs.append(df)

Если таблицы имеют одинаковые атрибуты, то их можно соединить в одну:

pd.concat(dfs)
#
     Name  Age  Weight
0    Alex   35      87
1   Lesha   57      72
2  Nastya   21      64
0   Tanya   25      66
1   Gosha   43      77
2   Tolya   32      54

Сохраняем метаданные таблицы

Как указано в коде выше, у ячейки OpenPyXL есть атрибут value, который хранит ее значение. Помимо value, можно получить тип ячейки (data_type), цвет заливки (fill), примечание (comment) и др.

Например, требуется сохранить данные о цвете ячеек. Для этого мы каждую ячейку с числами перезапишем в виде <значение,RGB>, где RGB — значение цвета в формате RGB (red, green, blue). Python-код выглядит следующим образом:

# _TYPES = {int:'n', float:'n', str:'s', bool:'b'}
data = []
for row in ws.rows:
    row_cells = []
    for cell in row:
        cell_value = cell.value
        if cell.data_type == 'n':
            cell_value = f"{cell_value},{cell.fill.fgColor.rgb}"
        row_cells.append(cell_value)
    data.append(row_cells)

Первым элементом списка является строка-заголовок, а все остальное уже значения таблицы:

pd.DataFrame(data[1:], columns=data[0])
#
     Name          Age       Weight
0    Alex  35,00000000  87,00000000
1   Lesha  57,00000000  72,FFFF0000
2  Nastya  21,FF00A933  64,00000000

Теперь представим атрибуты в виде индексов с помощью метода stack, а после разобьём все записи на значение и цвет методом str.split:

(pd.DataFrame(data[1:], columns=data[0])
 .set_index('Name')
 .stack()
 .str.split(',', expand=True)
)
#
                0         1
Name                       
Alex   Age     35  00000000
       Weight  87  00000000
Lesha  Age     57  00000000
       Weight  72  FFFF0000
Nastya Age     21  FF00A933
       Weight  64  0000000

Осталось только переименовать 0 и 1 на Value и Color, а также добавить атрибут Variable, который обозначит Вес и Возраст. Полный код на Python выглядит следующим образом:

(pd.DataFrame(data[1:], columns=data[0])
 .set_index('Name')
 .stack()
 .str.split(',', expand=True)
 .set_axis(['Value', 'Color'], axis=1)
 .rename_axis(index=['Name', 'Variable'])
 .reset_index()
)
#
     Name Variable Value     Color
0    Alex      Age    35  00000000
1    Alex   Weight    87  00000000
2   Lesha      Age    57  00000000
3   Lesha   Weight    72  FFFF0000
4  Nastya      Age    21  FF00A933
5  Nastya   Weight    64  00000000

Ещё больше подробностей о работе с таблицами в Pandas, а также их обработке на реальных примерах Data Science задач, вы узнаете на наших курсах по Python в лицензированном учебном центре обучения и повышения квалификации IT-специалистов в Москве.

Источники

1. https://xlrd.readthedocs.io/en/latest/
2. https://openpyxl.readthedocs.io/en/latest/
3. https://github.com/eea/odfpy
4. https://github.com/willtrnr/pyxlsb

Excel sheets are very instinctive and user-friendly, which makes them ideal for manipulating large datasets even for less technical folks. If you are looking for places to learn to manipulate and automate stuff in excel files using Python, look no more. You are at the right place.

Python Pandas With Excel Sheet

In this article, you will learn how to use Pandas to work with Excel spreadsheets. At the end of the article, you will have the knowledge of:

• Necessary modules are needed for this and how to set them up in your system.
• Reading data from excel files into pandas using Python.
• Exploring the data from excel files in Pandas.
• Using functions to manipulate and reshape the data in Pandas.

Installation

To install Pandas in Anaconda, we can use the following command in Anaconda Terminal:

conda install pandas

To install Pandas in regular Python (Non-Anaconda), we can use the following command in the command prompt:

pip install pandas

Getting Started

First of all, we need to import the Pandas module which can be done by running the command: Pandas

Input File: Let’s suppose the excel file looks like this 

Sheet 1: 

Sheet 2: 

Now we can import the excel file using the read_excel function in Pandas. The second statement reads the data from excel and stores it into a pandas Data Frame which is represented by the variable newData. If there are multiple sheets in the excel workbook, the command will import data of the first sheet. To make a data frame with all the sheets in the workbook, the easiest method is to create different data frames separately and then concatenate them. The read_excel method takes argument sheet_name and index_col where we can specify the sheet of which the data frame should be made of and index_col specifies the title column, as is shown below: 

Output: 

Example: 

The third statement concatenates both sheets. Now to check the whole data frame, we can simply run the following command: 

Output: 

To view 5 columns from the top and from the bottom of the data frame, we can run the command. This head() and tail() method also take arguments as numbers for the number of columns to show. 

Output: 

The shape() method can be used to view the number of rows and columns in the data frame as follows: 

Output: 

If any column contains numerical data, we can sort that column using the sort_values() method in pandas as follows: 

Now, let’s suppose we want the top 5 values of the sorted column, we can use the head() method here: 

Output: 

 We can do that with any numerical column of the data frame as shown below: 

Output: 

Now, suppose our data is mostly numerical. We can get the statistical information like mean, max, min, etc. about the data frame using the describe() method as shown below: 

Output: 

This can also be done separately for all the numerical columns using the following command: 

Output: 

Other statistical information can also be calculated using the respective methods. Like in excel, formulas can also be applied and calculated columns can be created as follows: 

Output: 

After operating on the data in the data frame, we can export the data back to an excel file using the method to_excel. For this we need to specify an output excel file where the transformed data is to be written, as shown below: 

Output: 

Microsoft Excel is one of the most powerful spreadsheet software applications in the world, and it has become critical in all business processes. Companies across the world, both big and small, are using Microsoft Excel to store, organize, analyze, and visualize data.

As a data professional, when you combine Python with Excel, you create a unique data analysis bundle that unlocks the value of the enterprise data.

In this tutorial, we’re going to learn how to read and work with Excel files in Python.

After you finish this tutorial, you’ll understand the following:

• Loading Excel spreadsheets into pandas DataFrames
• Working with an Excel workbook with multiple spreadsheets
• Combining multiple spreadsheets
• Reading Excel files using the xlrd package

In this tutorial, we assume you know the fundamentals of pandas DataFrames. If you aren’t familiar with the pandas library, you might like to try our Pandas and NumPy Fundamentals – Dataquest.

Let’s dive in.

Reading Spreadsheets with Pandas

Technically, multiple packages allow us to work with Excel files in Python. However, in this tutorial, we’ll use pandas and xlrd libraries to interact with Excel workbooks. Essentially, you can think of a pandas DataFrame as a spreadsheet with rows and columns stored in Series objects. Traversability of Series as iterable objects allows us to grab specific data easily. Once we load an Excel workbook into a pandas DataFrame, we can perform any kind of data analysis on the data.

Before we proceed to the next step, let’s first download the following spreadsheet:

Sales Data Excel Workbook — xlsx ver.

The Excel workbook consists of two sheets that contain stationery sales data for 2020 and 2021.

NOTE

Although Excel spreadsheets can contain formula and also support formatting, pandas only imports Excel spreadsheets as flat files, and it doesn’t support spreadsheet formatting.

To import the Excel spreadsheet into a pandas DataFrame, first, we need to import the pandas package and then use the read_excel() method:

import pandas as pd
df = pd.read_excel('sales_data.xlsx')

display(df)

If you want to load only a limited number of rows into the DataFrame, you can specify the number of rows using the nrows argument:

df = pd.read_excel('sales_data.xlsx', nrows=5)
display(df)

Skipping a specific number of rows from the begining of a spreadsheet or skipping over a list of particular rows is available through the skiprows argument, as follows:

df = pd.read_excel('sales_data.xlsx', skiprows=range(5))
display(df)

The code above skips the first five rows and returns the rest of the data. Instead, the following code returns all the rows except for those with the mentioned indices:

df = pd.read_excel('sales_data.xlsx', skiprows=[1, 4,7,10])
display(df)

Another useful argument is usecols, which allows us to select spreadsheet columns with their letters, names, or positional numbers. Let’s see how it works:

df = pd.read_excel('sales_data.xlsx', usecols='A:C,G')
display(df)

In the code above, the string assigned to the usecols argument contains a range of columns with : plus column G separated by a comma. Also, we’re able to provide a list of column names and assign it to the usecols argument, as follows:

df = pd.read_excel('sales_data.xlsx', usecols=['OrderDate', 'Region', 'Rep', 'Total'])
display(df)

The usecols argument accepts a list of column numbers, too. The following code shows how we can pick up specific columns using their indices:

df = pd.read_excel('sales_data.xlsx', usecols=[0, 1, 2, 6])
display(df)

Working with Multiple Spreadsheets

Excel files or workbooks usually contain more than one spreadsheet. The pandas library allows us to load data from a specific sheet or combine multiple spreadsheets into a single DataFrame. In this section, we’ll explore how to use these valuable capabilities.

By default, the read_excel() method reads the first Excel sheet with the index 0. However, we can choose the other sheets by assigning a particular sheet name, sheet index, or even a list of sheet names or indices to the sheet_name argument. Let’s try it:

df = pd.read_excel('sales_data.xlsx', sheet_name='2021')
display(df)

The code above reads the second spreadsheet in the workbook, whose name is 2021. As mentioned before, we also can assign a sheet position number (zero-indexed) to the sheet_name argument. Let’s see how it works:

df = pd.read_excel('sales_data.xlsx', sheet_name=1)
display(df)

As you can see, both statements take in either the actual sheet name or sheet index to return the same result.

Sometimes, we want to import all the spreadsheets stored in an Excel file into pandas DataFrames simultaneously. The good news is that the read_excel() method provides this feature for us. In order to do this, we can assign a list of sheet names or their indices to the sheet_name argument. But there is a much easier way to do the same: to assign None to the sheet_name argument. Let’s try it:

all_sheets = pd.read_excel('sales_data.xlsx', sheet_name=None)

Before exploring the data stored in the all_sheets variable, let’s check its data type:

type(all_sheets)

dict

As you can see, the variable is a dictionary. Now, let’s reveal what is stored in this dictionary:

for key, value in all_sheets.items():
    print(key, type(value))

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

The code above shows that the dictionary’s keys are the Excel workbook sheet names, and its values are pandas DataFrames for each spreadsheet. To print out the content of the dictionary, we can use the following code:

for key, value in all_sheets.items():
    print(key)
    display(value)

2020

2021

Combining Multiple Excel Spreadsheets into a Single Pandas DataFrame

Having one DataFrame per sheet allows us to have different columns or content in different sheets.

But what if we prefer to store all the spreadsheets’ data in a single DataFrame? In this tutorial, the workbook spreadsheets have the same columns, so we can combine them with the concat() method of pandas.

If you run the code below, you’ll see that the two DataFrames stored in the dictionary are concatenated:

combined_df = pd.concat(all_sheets.values(), ignore_index=True)
display(combined_df)

Now the data stored in the combined_df DataFrame is ready for further processing or visualization. In the following piece of code, we’re going to create a simple bar chart that shows the total sales amount made by each representative. Let’s run it and see the output plot:

total_sales_amount = combined_df.groupby('Rep').Total.sum()
total_sales_amount.plot.bar(figsize=(10, 6))

Reading Excel Files Using xlrd

Although importing data into a pandas DataFrame is much more common, another helpful package for reading Excel files in Python is xlrd. In this section, we’re going to scratch the surface of how to read Excel spreadsheets using this package.

NOTE

The xlrd package doesn’t support xlsx files due to a potential security vulnerability. So, we use the xls version of the sales data. You can download the xls version from the link below:
Sales Data Excel Workbook — xls ver.

Let’s see how it works:

import xlrd
excel_workbook = xlrd.open_workbook('sales_data.xls')

Above, the first line imports the xlrd package, then the open_workbook method reads the sales_data.xls file.

We can also open an individual sheet containing the actual data. There are two ways to do so: opening a sheet by index or by name. Let’s open the first sheet by index and the second one by name:

excel_worksheet_2020 = excel_workbook.sheet_by_index(0)
excel_worksheet_2021 = excel_workbook.sheet_by_name('2021')

Now, let’s see how we can print a cell value. The xlrd package provides a method called cell_value() that takes in two arguments: the cell’s row index and column index. Let’s explore it:

print(excel_worksheet_2020.cell_value(1, 3))

Pencil

We can see that the cell_value function returned the value of the cell at row index 1 (the 2nd row) and column index 3 (the 4th column).

The xlrd package provides two helpful properties: nrows and ncols, returning the number of nonempty spreadsheet’s rows and columns respectively:

print('Columns#:', excel_worksheet_2020.ncols)
print('Rows#:', excel_worksheet_2020.nrows)

Columns#: 8
Rows#: 13

Knowing the number of nonempty rows and columns in a spreadsheet helps us with iterating over the data using nested for loops. This makes all the Excel sheet data accessible via the cell_value() method.

Conclusion

This tutorial discussed how to load Excel spreadsheets into pandas DataFrames, work with multiple Excel sheets, and combine them into a single pandas DataFrame. We also explored the main aspects of the xlrd package as one of the simplest tools for accessing the Excel spreadsheets data.