ВПР является одной из самых используемых формул в Excel, так как позволяет объединять данные из двух таблиц по условию. В Python, а именно в пакете Pandas, мы можем делать тоже самое, но только быстрее и с большим вариантом применения.
Итак, для начала создадим Excel файл с примером, который будем использовать в этом уроке. В нем будет два листа. Первый лист назовем "Данные" и его содержание будет следующим:
Второй лист называем "Источник" и он будет содержать такую простую таблицу:
Сохраняем файл с названием "Excel_Python-2.xlsx". Теперь наша задача к первой таблице добавить доход наших сотрудников, основываясь на их фамилиях.
Запускаем Spyder, вставляем в новый скрипт код, представленный ниже, сохраняем в туже папку, что и Excel файл и запускаем. Если сделали все правильно, то у вас в листе "Данные" Excel файла должен появиться столбец "Доход" с соответствующими данными:
data_pd = pd.merge(data_pd,source_pd,on=['Фамилия'], how='left')
Итак, мы говорим что наш DataFrame data_pd равен результату работы встроенной в Pandas функции Merge. В качестве параметров указываем на первом месте DataFrame, куда должен попасть результат выполнения операции, вторым параметром - откуда брать данные, третий параметр - на основании какого столбца выполнять поиск и четвертый параметр - в какую таблицу вставлять данные.
Если вы не понимаете, одну из других строк кода, прочитайте мой первый урок про Excel и Python. Там я все подробно объяснил. Вот ссылочка.
Итак, мы получили желаемый результат. Однако это самый простой вариант применения ВПР. Давайте усложним наш пример и посмотрим на то, как еще можно использовать функцию Merge. В примере выше мы предполагали что в обеих таблицах есть столбец с названием "Фамилия", по которому мы производили поиск. А что делать если к примеру в одной таблице он называется к примеру "Фамилия сотрудника"? Давайте рассмотрим такую ситуацию:
Сначала измените в Excel файле в листе "Источник" названия столбца "Фамилия" на "Фамилия сотрудника", а на листе "Данные" удалите столбец "Доход", который мы создали при помощи скрипта ранее.
В коде замените строку с командой Merge на следующее:
Появились два новых параметра: left_on=['Фамилия'] - названия столбца, по которому ведется поиск в Dataframe data_pd; right_on=['Фамилия сотрудника'] - названия столбца, по которому ведется поиск в Dataframe source_pd.
Также появилась новая функция drop - она удаляет ненужный нам столбец "Фамилия сотрудника", который был создан в таблице data_pd, как результат работы функции merge.
Запускаем код, проверяем Excel файл, если все сделали верно - то столбец Доход должен снова появиться в листе "Данные".
Продолжаем усложнять условие. Предположим, что нам надо сделать ВПР не по одному, а по нескольким столбцам одновременно. В Excel для этого в формулу ВПР надо было бы добавлять СЦЕПИТЬ, а в просматриваемой таблице пришлось бы добавить столбец с ключом, который бы также формировался при помощи формулы СЦЕПИТЬ. В Pandas же можно воспользоваться всё той же функцией Merge. Давайте рассмотрим на примере:
Для начала усложним наши Excel таблицы. Лист "Данные" должен содержать следующую информацию:
А лист "Источник" соответственно:
Теперь обновим наш код. Будет он выглядеть следующим образом:
Итак, какие изменения у нас произошли:
1. Указаны новые размеры таблиц ('A1:C7') при создании DataFrame.
2. Обновлена функция Merge. Теперь мы указываем в параметрах left_on и right_on те столбцы, по которым ведется поиск соответствий.
3. Обновлена функция Drop. Так как Merge добавит лишние два столбца, то этой функцией мы их удаляем.
На этом урок закончен. Есть вопросы - задавайте в комментариях.
Итак, для начала создадим Excel файл с примером, который будем использовать в этом уроке. В нем будет два листа. Первый лист назовем "Данные" и его содержание будет следующим:
Второй лист называем "Источник" и он будет содержать такую простую таблицу:
Запускаем Spyder, вставляем в новый скрипт код, представленный ниже, сохраняем в туже папку, что и Excel файл и запускаем. Если сделали все правильно, то у вас в листе "Данные" Excel файла должен появиться столбец "Доход" с соответствующими данными:
import xlwings as xwРассмотрим новую строку кода, которой не было раньше в примере, а именно:
import pandas as pd
wb=xw.Book('Excel_Python-2.xlsx') # Открываем книгу
data_excel = wb.sheets['Данные'] # Читаем лист Данные
source_excel = wb.sheets['Источник'] # Читаем лист Источник
data_pd = data_excel.range('A1:C4').options(pd.DataFrame, header = 1, index = False).value # Создаем DataFrame
source_pd = source_excel.range('A1:B4').options(pd.DataFrame, header = 1, index = False).value # Создаем DataFrame
data_pd = pd.merge(data_pd,source_pd,on=['Фамилия'], how='left') # Аналог ВПР data_excel.range('A1').options(index = False).value = data_pd
data_pd = pd.merge(data_pd,source_pd,on=['Фамилия'], how='left')
Итак, мы говорим что наш DataFrame data_pd равен результату работы встроенной в Pandas функции Merge. В качестве параметров указываем на первом месте DataFrame, куда должен попасть результат выполнения операции, вторым параметром - откуда брать данные, третий параметр - на основании какого столбца выполнять поиск и четвертый параметр - в какую таблицу вставлять данные.
Если вы не понимаете, одну из других строк кода, прочитайте мой первый урок про Excel и Python. Там я все подробно объяснил. Вот ссылочка.
Итак, мы получили желаемый результат. Однако это самый простой вариант применения ВПР. Давайте усложним наш пример и посмотрим на то, как еще можно использовать функцию Merge. В примере выше мы предполагали что в обеих таблицах есть столбец с названием "Фамилия", по которому мы производили поиск. А что делать если к примеру в одной таблице он называется к примеру "Фамилия сотрудника"? Давайте рассмотрим такую ситуацию:
Сначала измените в Excel файле в листе "Источник" названия столбца "Фамилия" на "Фамилия сотрудника", а на листе "Данные" удалите столбец "Доход", который мы создали при помощи скрипта ранее.
В коде замените строку с командой Merge на следующее:
data_pd = pd.merge(data_pd,source_pd,left_on=['Фамилия'], right_on=['Фамилия сотрудника'], how='left') # ВПР
data_pd.drop(['Фамилия сотрудника'], axis='columns', inplace=True) #Удаляем лишний столбец
Появились два новых параметра: left_on=['Фамилия'] - названия столбца, по которому ведется поиск в Dataframe data_pd; right_on=['Фамилия сотрудника'] - названия столбца, по которому ведется поиск в Dataframe source_pd.
Также появилась новая функция drop - она удаляет ненужный нам столбец "Фамилия сотрудника", который был создан в таблице data_pd, как результат работы функции merge.
Запускаем код, проверяем Excel файл, если все сделали верно - то столбец Доход должен снова появиться в листе "Данные".
Продолжаем усложнять условие. Предположим, что нам надо сделать ВПР не по одному, а по нескольким столбцам одновременно. В Excel для этого в формулу ВПР надо было бы добавлять СЦЕПИТЬ, а в просматриваемой таблице пришлось бы добавить столбец с ключом, который бы также формировался при помощи формулы СЦЕПИТЬ. В Pandas же можно воспользоваться всё той же функцией Merge. Давайте рассмотрим на примере:
Для начала усложним наши Excel таблицы. Лист "Данные" должен содержать следующую информацию:
А лист "Источник" соответственно:
Теперь обновим наш код. Будет он выглядеть следующим образом:
import xlwings as xw
import pandas as pd
wb=xw.Book('Excel_Python-2.xlsx') # Открываем книгу
data_excel = wb.sheets['Данные'] # Читаем лист Данные
source_excel = wb.sheets['Источник'] # Читаем лист Источник
data_pd = data_excel.range('A1:C7').options(pd.DataFrame, header = 1, index = False).value # Создаем DataFrame
source_pd = source_excel.range('A1:C7').options(pd.DataFrame, header = 1, index = False).value # Создаем DataFrame
data_pd = pd.merge(data_pd, source_pd, left_on=['Фамилия','Имя'], right_on=['Фамилия сотрудника','Имя сотрудника'], how='left') # ВПР по нескольким столбцам
data_pd.drop(['Фамилия сотрудника', 'Имя сотрудника'], axis='columns', inplace=True) #Удаляем лишние столбцы
data_excel.range('A1').options(index = False).value = data_pd
Итак, какие изменения у нас произошли:
1. Указаны новые размеры таблиц ('A1:C7') при создании DataFrame.
2. Обновлена функция Merge. Теперь мы указываем в параметрах left_on и right_on те столбцы, по которым ведется поиск соответствий.
3. Обновлена функция Drop. Так как Merge добавит лишние два столбца, то этой функцией мы их удаляем.
На этом урок закончен. Есть вопросы - задавайте в комментариях.
0 comments:
Отправить комментарий
Спасибо за комментарий.