Как в питоне выгрузить двухъярусную таблицу
Перейти к содержимому

Как в питоне выгрузить двухъярусную таблицу

  • автор:

Чтение и запись файлов Excel (XLSX) в Python

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

Помимо чтения и записи рассмотрим, как записывать несколько DataFrame в Excel-файл, как считывать определенные строки и колонки из таблицы и как задавать имена для одной или нескольких таблиц в файле.

Установка Pandas

Для начала Pandas нужно установить. Проще всего это сделать с помощью pip .

Если у вас Windows, Linux или macOS:

pip install pandas # или pip3

В процессе можно столкнуться с ошибками ModuleNotFoundError или ImportError при попытке запустить этот код. Например:

ModuleNotFoundError: No module named 'openpyxl'

В таком случае нужно установить недостающие модули:

pip install openpyxl xlsxwriter xlrd # или pip3

Запись в файл Excel с python

Будем хранить информацию, которую нужно записать в файл Excel, в DataFrame . А с помощью встроенной функции to_excel() ее можно будет записать в Excel.

Сначала импортируем модуль pandas . Потом используем словарь для заполнения DataFrame :

 
import pandas as pd

df = pd.DataFrame( 'FC Bayern München', 'FC Barcelona', 'Juventus'],
'League': ['English Premier League (1)', 'Spain Primera Division (1)',
'English Premier League (1)', 'German 1. Bundesliga (1)',
'Spain Primera Division (1)', 'Italian Serie A (1)'],
'TransferBudget': [176000000, 188500000, 90000000,
100000000, 180500000, 105000000]>)

Ключи в словаре — это названия колонок. А значения станут строками с информацией.

Теперь можно использовать функцию to_excel() для записи содержимого в файл. Единственный аргумент — это путь к файлу:

 
df.to_excel('./teams.xlsx')

А вот и созданный файл Excel:

файл Excel в python

Стоит обратить внимание на то, что в этом примере не использовались параметры. Таким образом название листа в файле останется по умолчанию — «Sheet1». В файле может быть и дополнительная колонка с числами. Эти числа представляют собой индексы, которые взяты напрямую из DataFrame.

Поменять название листа можно, добавив параметр sheet_name в вызов to_excel() :

 
df.to_excel('./teams.xlsx', sheet_name='Budgets', index=False)

Также можно добавили параметр index со значением False , чтобы избавиться от колонки с индексами. Теперь файл Excel будет выглядеть следующим образом:

Чтение и запись файлов Excel (XLSX) в Python

Запись нескольких DataFrame в файл Excel

Также есть возможность записать несколько DataFrame в файл Excel. Для этого можно указать отдельный лист для каждого объекта:

 
salaries1 = pd.DataFrame( 'Salary': [560000, 220000, 125000]>)

salaries2 = pd.DataFrame( 'Salary': [370000, 270000, 240000]>)

salaries3 = pd.DataFrame( 'Salary': [160000, 260000, 250000]>)

salary_sheets =
writer = pd.ExcelWriter('./salaries.xlsx', engine='xlsxwriter')

for sheet_name in salary_sheets.keys():
salary_sheets[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)

writer.save()

Здесь создаются 3 разных DataFrame с разными названиями, которые включают имена сотрудников, а также размер их зарплаты. Каждый объект заполняется соответствующим словарем.

Объединим все три в переменной salary_sheets , где каждый ключ будет названием листа, а значение — объектом DataFrame .

Дальше используем движок xlsxwriter для создания объекта writer . Он и передается функции to_excel() .

Перед записью пройдемся по ключам salary_sheets и для каждого ключа запишем содержимое в лист с соответствующим именем. Вот сгенерированный файл:

Чтение и запись файлов Excel (XLSX) в Python

Можно увидеть, что в этом файле 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-файлами в Pandas — Python: Pandas

Для работы с табличными данными часто используют продукт Microsoft Excel. В таблицы Excel помещают как списки покупок, так и отчетности компаний. Благодаря распространенности данного формата разработчики создали инструменты для атоматизации обработки данных.

Pandas является средством работы с табличными данными и умеет работать с файлами формата Excel-таблиц: .xls и .xlsx. И каждый разработчик должен уметь работать с такими форматами наравне с текстовыми файлами и файлами формата json и html.

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

Обработка Excel файлов в Python

Среди форматов файлов Excel наиболее популярными являются:

  • .xls — использовался в версиях Microsoft Excel до 2007
  • .xlsx — используется во всех версиях после 2007

Для работы с обоими типами в Python есть ряд открытых библиотек:

В библиотеке Pandas не реализован свой функционал работы с Excel-файлами, но есть единый интерфейс для работы с каждой из указанных выше библиотек.

Чтобы использовать этот функционал, нужно установить указанные библиотеки в окружение, в котором установлена библиотека Pandas. Библиотеки не являются взаимозаменяемыми и дополняют друг друга — лучше установить их все.

Чтение таблиц из Excel файлов

Чтобы читать файлы в Pandas, используется метод read_excel() . Ему на вход подается путь к читаемому файлу:

import pandas as pd df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx') print(df_orders.head()) # => weekday shop_1 shop_2 shop_3 shop_4 # 0 mon 7 1 7 8 # 1 tue 4 2 4 5 # 2 wed 3 5 2 3 # 3 thu 8 12 8 7 # 4 fri 15 11 13 9 # 5 sat 21 18 17 21 # 6 sun 25 16 25 17 

В примере выше прочитан файл продаж четырех магазинов за неделю и размещен в объекте DataFrame . Pandas по умолчанию добавил столбец индексов — последовательность целых чисел от 0 до 6.

Чтобы указать, какой из столбцов является столбцом индексов, необходимо указать его номер в параметре index_col . В нашем случае это первый столбец, в котором указаны дни недели:

df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx', index_col=0) print(df_orders.head()) # => shop_1 shop_2 shop_3 shop_4 # weekday # mon 7 1 7 8 # tue 4 2 4 5 # wed 3 5 2 3 # thu 8 12 8 7 # fri 15 11 13 9 # sat 21 18 17 21 # sun 25 16 25 17 

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

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx') print(df_orders.head()) # => Orders by shop Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 # 0 NaN NaN NaN NaN NaN # 1 weekday shop_1 shop_2 shop_3 shop_4 # 2 mon 7 1 7 8 # 3 tue 4 2 4 5 # 4 wed 3 5 2 3 

Для корректного прочтения необходимо пропустить некоторое количество строк при прочтении. Для этого нужно использовать параметр skiprows и указать количество пропускаемых строк:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx', skiprows=2) print(df_orders.head()) # => weekday shop_1 shop_2 shop_3 shop_4 # 0 mon 7 1 7 8 # 1 tue 4 2 4 5 # 2 wed 3 5 2 3 # 3 thu 8 12 8 7 # 4 fri 15 11 13 9 

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

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xls', skiprows=2, index_col=0) print(df_orders.head()) # => shop_1 shop_2 shop_3 shop_4 # weekday # mon 7 1 7 8 # tue 4 2 4 5 # wed 3 5 2 3 # thu 8 12 8 7 # fri 15 11 13 9 

Запись таблиц в Excel файл

Также в Excel-файл можно записывать результаты работы программы. Эту задачу можно разделить на два типа по сложности используемого синтаксиса:

  • Быстрая запись на один лист — записывается одна таблица, которая будет размещена на одном листе файла Excel
  • Создание файла с несколькими листами — если результаты работы программы располагаются в нескольких итоговых таблицах, то для формирования единого файла Excel с несколькими листами потребуется применить определенные правила создания

Быстрая запись на один лист

В качестве результатов работы программы используем среднее по магазинам за неделю:

df_orders_mean = pd.DataFrame(df_orders.mean()).T.round(1) df_orders_mean.index = ['mean'] print(df_orders_mean) # => shop_1 shop_2 shop_3 shop_4 # mean 11.9 9.3 10.9 10.0 

Сформируем итоговую таблицу на основе исходной и добавим аналитические результаты:

df_analitic_results = pd.concat([ df_orders, df_orders_mean ]) print(df_analitic_results) # => shop_1 shop_2 shop_3 shop_4 # mon 7.0 1.0 7.0 8.0 # tue 4.0 2.0 4.0 5.0 # wed 3.0 5.0 2.0 3.0 # thu 8.0 12.0 8.0 7.0 # fri 15.0 11.0 13.0 9.0 # sat 21.0 18.0 17.0 21.0 # sun 25.0 16.0 25.0 17.0 # mean 11.9 9.3 10.9 10.0 

Чтобы быстро записать данную таблицу, достаточно воспользоваться методом to_excel() . Формат файла .xls или .xlsx необходимо указать в расширении файла. Pandas автоматически определит, какой библиотекой воспользоваться для конкретного формата:

df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xlsx') df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xls') 

Создание файла с несколькими листами

Чтобы задать имя листа, на котором располагается таблица, необходимо указать его в параметре sheet_name . В данном примере получится лист Total:

path_for_analitic_results = 'data_read/Shop_orders_one_week_analitics.xlsx' df_analitic_results.to_excel( path_for_analitic_results, sheet_name='Total' ) 

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

df_analitic_results[['shop_1']].to_excel( path_for_analitic_results, sheet_name='shop_1', ) 

Все выполнено без ошибок, но в итоговом файле листа Total нет. Чтобы перезаписать файл и удалить предыдущий, вызовем функцию to_excel() .

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

with pd.ExcelWriter( path_for_analitic_results, engine="xlsxwriter", mode='w') as excel_writer: # Add total df df_analitic_results.to_excel(excel_writer, sheet_name='Total') # Add all shop df results for shop_name in df_analitic_results.columns.to_list(): df_analitic_results[[shop_name]].to_excel(excel_writer, sheet_name=shop_name) 

В коде выше создается экземпляр класса ExcelWriter на "движке" библиотеки xlsxwriter . Далее мы используем инициализированный экземпляр excel_writer в качестве первого параметра метода to_excel() . Конструкция with. as. позволяет безопасно работать с потоком данных и закрыть файл, даже когда возникают ошибки записи.

Чтение таблиц из Excel файлов с несколькими листами

Чтобы прочитать файл с несколькими листами, не хватит метода read_excel() , поскольку будет прочитан только первый лист из файла:

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0) print(df_analitic_results_from_file) # => shop_1 shop_2 shop_3 shop_4 # mon 7.0 1.0 7.0 8 # tue 4.0 2.0 4.0 5 # wed 3.0 5.0 2.0 3 # thu 8.0 12.0 8.0 7 # fri 15.0 11.0 13.0 9 # sat 21.0 18.0 17.0 21 # sun 25.0 16.0 25.0 17 # mean 11.9 9.3 10.9 10 

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

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0, sheet_name='shop_1') print(df_analitic_results_from_file) # => shop_1 # mon 7.0 # tue 4.0 # wed 3.0 # thu 8.0 # fri 15.0 # sat 21.0 # sun 25.0 # mean 11.9 

Чтобы прочитать несколько листов и не переоткрывать файл, достаточно использовать экземпляр класса ExcelFile и его метод parse() . В последнем указывается имя нужного листа и дополнительные параметры чтения, аналогичные методу read_excel() .

excel_reader = pd.ExcelFile(path_for_analitic_results) df_shop_1 = excel_reader.parse('shop_1', index_col=0) df_shop_2 = excel_reader.parse('shop_2', index_col=0) print(df_shop_1) print(df_shop_2) # => shop_1 # mon 7.0 # tue 4.0 # wed 3.0 # thu 8.0 # fri 15.0 # sat 21.0 # sun 25.0 # mean 11.9 # shop_2 # mon 1.0 # tue 2.0 # wed 5.0 # thu 12.0 # fri 11.0 # sat 18.0 # sun 16.0 # mean 9.3 

Данный подход для чтения файла Excel удобен, чтобы получить список всех листов. Для этого нужно посмотреть на атрибут sheet_names :

print(excel_reader.sheet_names) # => ['Total', 'shop_1', 'shop_2', 'shop_3', 'shop_4'] 

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

sheet_to_df_map = <> for sheet_name in excel_reader.sheet_names: sheet_to_df_map[sheet_name] = excel_reader.parse(sheet_name, index_col=0) print(sheet_to_df_map['shop_1']) print(sheet_to_df_map['Total']) # => shop_1 # mon 7.0 # tue 4.0 # wed 3.0 # thu 8.0 # fri 15.0 # sat 21.0 # sun 25.0 # mean 11.9 # shop_1 shop_2 shop_3 shop_4 # mon 7.0 1.0 7.0 8 # tue 4.0 2.0 4.0 5 # wed 3.0 5.0 2.0 3 # thu 8.0 12.0 8.0 7 # fri 15.0 11.0 13.0 9 # sat 21.0 18.0 17.0 21 # sun 25.0 16.0 25.0 17 # mean 11.9 9.3 10.9 10 

Форматирование таблиц

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

В примере ниже мы форматируем итоговые аналитические данные: если значения в таблице превышают порог в одиннадцать заказов, то они раскрашиваются в один цвет, иначе — в другой. Цветовая дифференциация данных удобна, чтобы быстро оценивать результаты и искать закономерности в данных:

with pd.ExcelWriter( path_for_analitic_results, engine="xlsxwriter", mode='w') as excel_writer: # Add total df df_analitic_results.to_excel(excel_writer, sheet_name='Total') # Formatting total df threshold = 11 workbook = excel_writer.book worksheet = excel_writer.sheets['Total'] format1 = workbook.add_format('bg_color': '#FFC7CD', 'font_color': '#9C0006'>) format2 = workbook.add_format('bg_color': '#C6EFCD', 'font_color': '#006100'>) worksheet.conditional_format('B2:E9',  'type' : 'cell', 'criteria' : '>=', 'value' : threshold, 'format' : format1> ) worksheet.conditional_format('B2:E9',  'type' : 'cell', 'criteria' : ', 'value' : threshold, 'format' : format2> ) 

В примере выше используются методы движка xlsxwriter . Разбор всех возможностей форматирования данных при записи выходит за рамки данного урока. Можно глубже погрузиться в данную тему через документацию с примерами по следующей ссылке .

Выводы

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

Как при помощи Pyhton выгрузить все таблицы БД в один лист таблицы excel?

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

import pandas as pd from openpyxl import Workbook import sqlite3 from ini import ini_stat from open_file import read_list from workdb import add_db for i in read_list('k13.txt'): add_db(ini_stat(i)) connect = sqlite3.connect('kvm.db') curs = connect.cursor() curs.execute(f"SELECT name FROM sqlite_master WHERE type='table';") mass_name_table = curs.fetchall() #двумерный массив с именами таблиц my_path="/STORAGE/kvm/table.xlsx" r,c = 1,0 with pd.ExcelWriter(my_path, engine='xlsxwriter') as my_obj: for i in mass_name_table: df = pd.read_sql(f"SELECT * FROM ", connect) df.to_excel(my_obj, sheet_name='page1', startrow=r, startcol=c) r += 35 connect.close() 

Помогите пжлст ).
Отслеживать
задан 10 окт 2023 в 19:06
11 3 3 бронзовых знака
Пробовали считать базы, объединить в один фрейм и один раз записать?
10 окт 2023 в 19:09

df.to_excel(my_obj, sheet_name='page1'. - может, это потому, что вы говорите коду все писать на один лист? меняйте номер page при каждой итерации.

10 окт 2023 в 19:21

Записываю все в один page1, записывая в разные он запишет каждую таблицу базы в свой лист excel мне надо что бы все было на 1

Объединение таблиц и выборка данных в результирующую таблицу Python

Требуется построить сводную xlsx таблицу по всем договорам с условиями: исключить клиентов из файла таблица исключений и договоры не имеют одновременно атрибута 1 и 2. Вывести данные столбцов (из 4 таблиц) в сводную, чтобы столбцы не дублировались.

Я загрузил 4 таблицы через pandas, во всех исходных таблицах добавил единый столбец (номер договора), чтобы он был ключом, по которому через merge связать их. Решение вижу такое - склеить 4 таблицы в единый датафрейм, задать фильтрующее условие, выгрузить датафрейм в excel. Не понимаю как правильно склеить таблицы и как задать правильное условие для выбора требуемых атрибутов в финальной таблице. На экран команда почему-то выводит больше строчек результирующей таблицы, чем требуется, в частности есть строки с клиентами-исключениями, которых быть не должно

 import pandas as pd import openpyxl da = pd.read_excel('Договоры.xlsx') db = pd.read_excel('Исключения.xlsx') dc = pd.read_excel('Клиенты.xlsx') de = pd.read_excel('ОС и ЗС.xlsx') new_df = da.merge(db, how = 'outer', on='№ договора').merge(dc, how = 'outer', on='№ договора').merge(de, how = 'outer', on='№ договора') Client=new_df[(new_df['Атрибут B'] =='нет')|(new_df['Атрибут A'] =='нет')|(new_df['ИНН_исключения'] =='NaN')] Client.to_excel('Задание2.xlsx') 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *