Одна из интересных вещей в программировании заключается в том, что с помощью нескольких строк кода вы можете заставить свой компьютер выполнять задачу, которая в противном случае была бы рутинной и раздражающей для вас, выполняя ее самостоятельно. Одна из таких рутинных задач - извлечение информации из большого листа Excel. Язык программирования Python очень надежен, и одна из областей, в которых он проявляется, помогает нам автоматизировать утомительные и повторяющиеся задачи Excel.
В этом сообщении блога мы приступим к пошаговому процессу извлечения некоторой ценной информации из таблицы Excel. Лист Excel, который мы будем использовать, - это лист, содержащий все продажи фруктов в супермаркете за месяц. Каждая строка содержит отдельные записи фруктов, приобретенных покупателями. На листе Excel есть три столбца. В столбце A указана стоимость фунта купленных фруктов, в столбце B указано количество проданных фунтов, а в столбце C указана общая стоимость покупки. На листе Excel 23758 строк и четыре столбца. Вы можете скачать таблицу Excel здесь.
Наша цель - выяснить и задокументировать общую сумму проданных фунтов стерлингов, а также все экземпляры покупок для каждого фрукта в этом месяце. Вы можете представить себе разочарование от необходимости просматривать 23758 строк для извлечения информации о каждом фрукте, что ж, нам повезло, поскольку Python поможет нам выполнить эту задачу в кратчайшие сроки. Приведенные ниже шаги дадут подробное и практическое объяснение того, как вы можете использовать Python для выполнения этой задачи.
Прежде чем мы перейдем к этой задаче, я хочу предположить, что у вас есть базовые знания о написании кода на Python и что Python установлен на вашем компьютере.
Установите модуль openpyxl
Модуль python, с которым мы будем работать, - это модуль OpenPyXL. Модуль OpenPyXL - это библиотека, которая позволяет использовать Python для чтения и записи файлов Excel или файлов с расширением .xlsx / xlsm / xltx / xltm. Если он не установлен в вашей среде IDE, вы можете установить его с помощью
pip install openpyxl
Чтобы проверить, успешно ли вы его установили, импортируйте его, используя
Import openpyxl
Пока ошибка не возвращается, вы установили модуль OpenPyXL и готовы работать с некоторыми документами Excel.
Прочтите и откройте документ Excel с помощью OpenPyXL
Следующим портом вызова является чтение таблицы Excel в нашу среду Python. Убедитесь, что Excel, с которым вы будете работать, находится в вашем текущем рабочем каталоге (CWD). Вы можете получить доступ к вашему CWD, используя:
import os os.getcwd() os.chdir() #this changes our CWD, if the excel sheet is not in CWD
Что делать, если лист Excel отсутствует в вашем CWD? Вы можете скопировать файл и вставить его в свой CWD, чтобы получить к нему доступ оттуда. Убедившись, что у нас есть документ Excel в нашем CWD, мы можем его прочитать.
После прочтения документа Excel мы можем получить к нему доступ для получения различной информации о листе Excel.
import pandas as pd file = ‘produceSales.xlsx’ data = pd.ExcelFile(file) print(data.sheet_names) #this returns the all the sheets in the excel file [‘Sheet1’]
Затем мы анализируем лист, с которым будем работать, во фрейм данных, это позволит нам узнать, правильно ли был прочитан наш лист Excel.
df = data.parse(‘Sheet1’) df.info df.head(10)
Считывание данных из электронной таблицы
Следующим шагом является считывание данных из электронной таблицы [Sheet1].
ps = openpyxl.load_workbook(‘produceSales.xlsx’) sheet = ps[‘Sheet1’] sheet.max_row #returns the total number of rows in the sheet 23758
Затем мы используем цикл For для перебора всех строк на листе.
for row in range(2, sheet.max_row + 1): # each row in the spreadsheet represents information for a particular purchase. produce = sheet[‘B’ + str(row)].value cost_per_pound = sheet[‘C’ + str(row)].value pounds_sold = sheet[‘D’ + str(row)].value total_sales = sheet[‘E’ + str(row)].value # the first column is B followed by C and so on. # Each value in a cell is represented by a column letter and a row number. So #the first element in the sheet is B1, next column C1 and so on. This enables #to iterate over the entire cells.
Создайте пустой словарь, содержащий всю информацию о каждом фрукте. Затем мы используем метод set.default () для заполнения первого набора элементов в словаре. Первый аргумент set.default () проверяет, существует ли ключ в словаре, если нет, он заменяет его вторым аргументом. Таким образом, мы можем начать заполнение нашего словаря вторым аргументом функции set.default.
TotalInfo.setdefault(produce,{‘Total_cost_per_pound’: 0,
‘Total_pounds_sold’: 0,
‘Total_sales’: 0,
‘Total_Purchase_Instances’: 0})
# so with this set default method, we have set all metrics we want to collect to zero. When we are iterating, we start from Zero and add new iterated to the dictionary. The key of the dictionary is the fruit which is mapped to their various metrics.
Наконец, мы заполняем словарь. Для каждого нового продукта, отображаемого в новой строке, мы увеличиваем показатель на соответствующее значение в новой строке.
# Each row represents a fruit, so increment by the new corresponding values. TotalInfo[produce][‘Total_cost_per_pound’] += float(cost_per_pound) TotalInfo[produce][‘Total_pounds_sold’] += int(pounds_sold) TotalInfo[produce][‘Total_sales’] += int(total_sales) # Each row represents a fruit, so increment by one. TotalInfo[produce][‘Total_Purchase_Instances’] += 1
После запуска этого блока кода мы бы заполнили словарь TotalInfo всеми различными метриками для каждого фрукта за месяц. Заполненный словарь выглядит так:
‘Apples’: {‘Total_Purchase_Instances’: 627,
‘Total_cost_per_pound’: 1178.7600000000068,
‘Total_pounds_sold’: 12119,
‘Total_sales’: 22999},
Запишите результаты в файл
После заполнения словаря TotalInfo. Мы можем записать этот заполненный словарь в любой файл по нашему выбору, будь то .csv, .txt, .py и др. Мы будем использовать модуль pprint.pformat для красивой печати значений нашего словаря, и мы будем использовать режим записи python для записи значений словаря в файл. Приведенный ниже фрагмент кода дает иллюстрацию:
resultFile = open(‘Total_info.txt’, ‘w’) resultFile.write(pprint.pformat(TotalInfo)) resultFile.close() print(‘Done.’)
Файл Total_info.txt будет найден в вашем CWD.
Вы всегда можете изменить формат файла, изменив расширение .txt на любой формат файла, который вы хотите.
В приведенном ниже фрагменте кода показано, как перейти в формат файла .csv.
Open (‘Total_info.csv’, ‘w’)
Заключение
В этом сообщении блога мы продемонстрировали, как можно использовать Python для извлечения информации из таблицы Excel. Знание того, как получать информацию из таблицы Excel, всегда является желанным дополнением к вашему набору инструментов, поскольку это экономит вам много времени от выполнения повторяющихся задач. Не стесняйтесь изменять код в статье в соответствии с вашими потребностями; Вы можете получить доступ к записной книжке, содержащей сквозной код, использованный в этом сообщении блога, здесь.
Удачного питонинга.
