комбайн Excel и Пайтън стана един от най-мощните начини за работа с данни Когато трябва да обработваме повтарящи се отчети, да консолидираме файлове от различни източници или да автоматизираме задачи, които могат да отнемат часове ръчно в Excel, научаването да комбинираме тези два свята отваря вратата към много по-бързи и най-вече повтаряеми работни процеси. Ако работите ежедневно с електронни таблици, CSV файлове, бизнес отчети или списъци за анализ, този подход е безценен.
Ключовата идея е проста: Excel остава вашият удобен интерфейс за преглед и подготовка на данни, а Python е двигателят, който автоматизира и комбинира всичко зад кулисите.Благодарение на използването на новия Python, интегриран в Excel, вече можете да покриете практически всяка нужда.
Опции за комбиниране на данни от Excel с Python с помощта на pandas
Най-често срещаният подход за обединяване на данни от Excel с Python е използвайте библиотеката на pandasкойто предлага много мощни функции за четене, сливане и трансформиране на електронни таблици. Ядрото на тези операции е pandas.merge(), което работи подобно на комбинациите от таблици в бази данни или много подобрения VLOOKUP/XLOOKUP.
El основен поток Обикновено следва четири стъпки:
- Внос на панди.
- Прочетете файловете.
- Обединете ги.
- Изчистване на липсващите стойности.
С този процес можете да извършвате пълни съединения между два файла, като гарантирате, че няма да загубите информация и контролирате по всяко време кои редове са включени и кои са изключени.
Импортиране и четене
Да започнем с това, pandas се импортира във вашия Python скрипт с инструкция, толкова проста, колкото import pandas as pdОттам можете да четете вашите работни книги на Excel с pd.read_excel() Или, ако работите с CSV файлове, използвайте pd.read_csv()променяйки само функцията за четене, но запазвайки останалата част от потока абсолютно същата.
Четенето на Excel и CSV файлове с pandas ви позволява да конвертирате всеки лист или файл в DataFrame.Това е таблица в паметта, върху която можете да извършвате филтри, групирания и, разбира се, обединения. Например, можете да прочетете два файла по следния начин: df_left = pd.read_excel("ventas_enero.xlsx") y df_right = pd.read_excel("ventas_febrero.xlsx")или вариантът с read_csv ако източникът е CSV.
сливане
Ключовият момент идва, когато използвате pd.merge() да обедините два DataFramesТази функция получава, като минимум, следните аргументи left y rightкоито показват кои таблици искате да комбинирате, и параметър how което определя вида на обединението. Поведението на how Важно е да се контролира дали се запазват всички редове, само съвпадащите редове или само тези от едната страна.
Сред най-често използваните стойности за how están inner y outer, с inner Ще бъдат запазени само редовете, които съществуват и в двата DataFrames според посочения от вас ключ за сливане; тоест, запазвате само пресечната точка на данните. outer Всички редове от двата файла се обединяват. И когато липсва стойност за определена колона в някоя от таблиците, тя ще бъде запълнена със стойност NaN (несъществуваща).
В допълнение към колоните, pandas позволява сливане по индекси с помощта на left_index y right_indexАко активирате тези аргументи (например, left_index=True, right_index=True), съединението се извършва с помощта на индекси на редове, вместо на конкретни колони. Тази опция е много полезна, когато вече имате настроен индекс като уникален идентификатор или когато колоните не са правилно подравнени, но индексът е.
Празни стойности
След присъединяване към DataFrames, обикновено е необходимо да се опита празни стойностиЧесто е необходимо да конвертирате числови NaN стойности в нули и да оставите някакъв чист текст за низови полета. Често срещан модел е нещо подобно: използвайте select_dtypes('string') за да намерите колони с текст и да ги попълните с дума като „празен“ и използвайте select_dtypes('number') за числови колони и заменете тяхното NaN с 0Това предотвратява проблеми по-късно, когато експортирате в Excel или извършвате изчисления.
Имайте предвид, че попълването на липсващи стойности изисква спазвайте типа данни на всяка колонаАко се опитате да приложите fillna(0) Ако се опитате да обработите текстови колони директно, процесът ще се провали, така че е най-добре първо да разделите числовите и низовите колони. Това разделяне предотвратява грешки и ви позволява да контролирате кой заместител да използвате за всеки тип данни.
Обединяване на Excel и CSV файлове с еднакъв брой редове
В много практически сценарии, с които се сблъсквате Два файла, които споделят абсолютно еднакъв брой редове и една обща колона който действа като централна ос: например списък с URL адреси за даден домейн, заедно с SEO показатели в един файл и данни за посещения или реализации в друг. В тези ситуации свързването е много лесно и ви позволява да разширите всеки ред с повече информация, без да губите подравняване на данните.
Когато броят на редовете съвпада и в двата файла и ключовата колона не се променя, комбинирането на данните е сравнително лесно, но все пак е препоръчително да се поддържа организирана структура. В идеалния случай трябва да прочетете и двата файла с pandas, да се уверите, че колоната за свързване (като „URL“) е написана идентично и има същия тип данни, след което да изпълните merge което запазва всички редове.
Този тип съюз е от особен интерес проверете дали и двете таблици имат еднаква дължинаАко някой от файловете съдържа допълнителни редове, може да се стигне до загуба на информация или генериране на редове, които не би трябвало да са там. Следователно, преди обединяването, е разумно да проверите нещо подобно... len(df1) y len(df2) за да се потвърди, че обемът на записите наистина е един и същ.
След като Excel и/или CSV файлът бъдат обединени, следващата логична стъпка е експортирайте резултата в нова работна книгаPandas ви позволява да пишете директно с to_excel()Това ще генерира файл в папката на вашия Python проект, освен ако не посочите друг път. По този начин цялата работа по сливането се отразява в стандартен Excel файл, който можете да отваряте, филтрирате, форматирате и споделяте.
Използване на Python директно в Excel (Python в Excel)
В допълнение към работата с външни скриптове, използването на външни скриптове става все по-важно. Официална интеграция на Python в ExcelТази функционалност ви позволява да пишете Python код в клетки, подобно на начина, по който бихте написали формула, и да го комбинирате с диапазони и таблици, които вече са налични в работната книга.
За да започнете да използвате Python в Excel, можете да направите следното: от раздела с формуликато изберете опцията за вмъкване на Python в активната клетка или като директно използвате функцията =PY()След като направите това, Excel разбира, че съдържанието в тази клетка ще бъде интерпретирано като Python код, поддържайки малък визуален маркер с идентифицираща икона. Ако се нуждаете от повече практически подробности, вижте нашите Пълно ръководство за Python в Excel.
Един от ключовете за тази интеграция е спомагателна функция xl()който служи като мост между Excel и PythonБлагодарение на него можете да се обръщате към диапазони, таблици, заявки или дефинирани имена в Excel в Python код.
Електронната таблица все още спазва реда на изчисление, но клетките на Python се изпълняват ред по ред.Отляво надясно, а след това надолу по страницата.
Лентата с формули предлага удобен режим на редактиране на Python код.позволявайки прекъсвания на редовете, разширяване за едновременно показване на няколко реда и клавишни комбинации за разширяване или свиване на областта за писане.
Контрол на изхода, преизчисляване и обработка на грешки в Python за Excel
Когато използвате Python в Excel, можете изберете как искате да се върнат резултатитеИмате възможност да конвертирате резултата в класически стойности на Excel, които се записват директно в клетката, или да ги запазите като Python обекти. Това е особено полезно при работа със структури като DataFrames.
Ако върнете изчислението като обект на Python, Клетката се показва с икона на карта.Щракването върху обект отваря предварителен преглед, показващ неговите детайли. Това е много полезно при работа с големи набори от данни. Този подход ви позволява да обработвате богати резултати, без да претрупвате листа със стотици или хиляди видими редове.
Някои типове данни работят особено добре с тази интеграция, а DataFrames на Panda са едни от най-забележителните. Работата с DataFrames в Excel улеснява прехода от анализ в кода към представяне на резултатите в традиционни таблици или диаграми.
Преизчисляването се управлява по същия начин, както при други формули на Excel, но с някои особености.Всеки път, когато промените клетка, от която зависи формула на Python, последователността от участващи клетки на Python се преизчислява. За да подобрите производителността, особено при работа с големи модели, можете да превключите към режим на частично или ръчно изчисление, така че преизчисляването да се извършва само когато изрично го поискате.
В тези ръчни режими имате няколко начина за повторно изпълнение на изчислениетоМожете да актуализирате стойност, като натиснете F9, щракнете върху бутона „Изчисли сега“ в лентата с формули или в някои случаи, като използвате индикатора на клетката, който показва, че стойността е остаряла. Тази гъвкавост ви помага да балансирате точността и производителността, докато разработвате анализите си.
Автоматизирайте зареждането и обработката на Excel с Python скриптове
Отвъд интегрираната среда в Excel, тя остава много често срещана работа с външни Python скриптове за обработка на входни файловеТипичен модел включва наличието на конфигурационен файл (например, config.json), скрипт като data_processing.py и една или повече работни книги на Excel, които действат като източници на данни.
Работният процес е следният:
- Подгответе входния Excel файлкоето бихте могли да наречете нещо подобно
input.xlsxТози файл се поставя в същата папка като Python скрипта, за да се опростят пътищата, особено ако тепърва започвате и не искате да се затруднявате с по-сложни абсолютни или относителни пътища. - Създайте файла с кода. Например
data_processing.pyВ любимия си редактор или IDE копирайте съществуващата си кодова база (класове, функции и т.н.) и я адаптирайте към специфичните си нужди, като запазвате промените всеки път, когато добавяте нова логика, свързана с четене или трансформиране на данни. - Запазете скрипта и стартирайте програмата от терминала.Ако сте в същата директория, където се намира
data_processing.pyЩеше да хвърлиш нещо подобноpython data_processing.py config.json input.xlsxПромяна на името на конфигурационния файл, за да съответства на вашето местоположение. Идеята е скриптът да четеconfig.jsonда разберете кои операции да приложите и след това да работите върхуinput.xlsx.
IronXL: Комбиниране и манипулиране на Excel клетки с Python
В допълнение към pandas и вградената интеграция с Python в Excel, Има специфични библиотеки, като например IronXL проектиран да обработва Excel файлове по много детайлен начинС тях можете не само да четете и записвате данни, но и да стилизирате, обединявате и разделяте клетки или да работите с разширени формули от вашите Python приложения.
IronXL е проектиран да работите с различни формати на електронни таблициот класиката XLSX y XLS дори книги с макроси (XLSM), шаблони ( ), шаблони ( ),XLTX) или дори структурирани текстови файлове, като например CSV y TSVВсичко това работи на множество платформи: Windows, macOS, Linux, Docker контейнери и дори облачни среди като Azure или AWS.
IronXL API улеснява ежедневните задачи, когато е необходимо да манипулирате формата.Можете да избирате работни листове, да четете и записвате стойности в конкретни клетки, да контролирате шрифтове, цветове на фона, рамки, подравняване и формати за числа, дати, проценти, валута и други. Освен това, формулите на Excel се преизчисляват автоматично, когато промените съответните клетки, като винаги се поддържа очакваното поведение за потребители, запознати с Excel.
За да започнете да използвате IronXL в Python проект, първо ще трябва инсталирайте пакета с pip, използвайки команда като pip install ironxlСлед това ще импортирате модула с нещо подобно from ironxl import * И в среди, които го изискват, ще трябва да създадете лицензионен ключ, който в случай на пробни версии можете да получите безплатно от уебсайта на доставчика.
След като библиотеката е конфигурирана, първата стъпка е Заредете работната книга на Excel, която искате да обработите., Например, workbook = WorkBook.Load("test_excel.xlsx") ще отвори файл с име test_excel.xlsx да работите с него в паметта. От този момент нататък можете да навигирате през листовете му, да променяте данни, да обединявате региони и накрая да запазвате резултата с едно просто workbook.Save().
Обединяване и разединяване на специфични клетки с IronXL
Когато целта ви е не само да комбинирате данни от различни източници, но и да подобрите представянето в Excel, Програмното обединяване на клетки може да ви спести много времеПредставете си колона с държави, където няколко реда съдържат „Съединени щати“. Може да искате да обедините тези клетки, за да направите отчета по-ясен за човека, който ще го преглежда.
С IronXL можете Изберете листа, върху който искате да работите, като отворите неговия индекс или име., Например, worksheet = workbook.WorkSheets Това ще ви отведе директно до първия лист на работната книга. Оттам вашите операции се прилагат към този лист: четене на клетки, писане, стилове или сливане.
за обединяване на клетки в определен диапазон, IronXL има метода Merge() в обекта leaf. Това означава, че можете да изпълните нещо подобно worksheet.Merge("E5:E7") да се комбинират редове от 5 до 7 на колона E и worksheet.Merge("E9:E10") за друга група. Тогава просто ще трябва да призовете workbook.Save() за да запишете промените във файла на Excel.
Ако имате нужда да се знае кои слети региони съществуват в даден листIronXL позволява тяхното програмно възстановяване. С метод като GetMergedRegions() Можете да получите списък с всички обединени области на клетките и да ги прегледате циклично, като например отпечатате mergedRegion.RangeAddressAsString за да видите засегнатия диапазон в четлив формат (A1:B3, E5:E7 и др.).
В един момент може да поискате да отменят тези сливанияНапример, за да се манипулират данните ред по ред. В този случай, същият лист предоставя метод Unmerge() към който можете да предавате диапазони като "E5:E7" o "E9:E10"След изпълнение на тези повиквания и запазване на работната книга, клетките отново стават независими, готови да приемат различни стойности или да бъдат обработени по друг начин.
В крайна сметка, Комбинирането на Excel и Python ви позволява да преминете от ръчна и повтаряща се работа към много по-интелигентни работни процеси.Excel остава вашият прегледник и контролен панел, но Python върши тежката работа по четенето, съединяването, почистването, обединяването на клетки и преизчисляването. След като свикнете с този начин на работа, повтарянето на сложни процеси става въпрос само за няколко кликвания. Или за една команда в терминала.
