Сравнение Excel и панд

Обновлено: 06.07.2024

Поскольку многие потенциальные пользователи pandas имеют некоторое представление о программах для работы с электронными таблицами, таких как Excel, эта страница предназначена для предоставления некоторых примеров того, как различные операции с электронными таблицами будут выполняться с помощью pandas. На этой странице будет использоваться терминология и ссылки на документацию для Excel, но многое будет таким же/похожим в Google Sheets, LibreOffice Calc, Apple Numbers и других программах для работы с электронными таблицами, совместимых с Excel.

Если вы новичок в пандах, вы можете сначала прочитать 10 Minutes to pandas, чтобы ознакомиться с библиотекой.

Как обычно, мы импортируем pandas и NumPy следующим образом:

Структуры данных¶

Перевод общей терминологии¶

Фрейм данных ¶

Кадр данных в pandas аналогичен рабочему листу Excel. В то время как книга Excel может содержать несколько рабочих листов, кадры данных pandas существуют независимо друг от друга.

Серия ¶

Серия — это структура данных, представляющая один столбец DataFrame . Работа с серией аналогична ссылке на столбец электронной таблицы.

Индекс ¶

Каждый кадр данных и серия имеют индекс , который представляет собой метки для строк данных. В пандах, если индекс не указан, по умолчанию используется RangeIndex (первая строка = 0, вторая строка = 1 и т. д.), аналогично заголовкам/номерам строк в электронных таблицах.

В пандах индексы могут быть установлены на одно (или несколько) уникальных значений, что похоже на наличие столбца, который используется в качестве идентификатора строки на листе. В отличие от большинства электронных таблиц, эти значения индекса могут фактически использоваться для ссылки на строки. (Обратите внимание, что это можно сделать в Excel со структурированными ссылками.) Например, в электронных таблицах вы должны ссылаться на первую строку как A1:Z1 , а в pandas вы можете использовать население.loc['Chicago'] .

Значения индекса также являются постоянными, поэтому при изменении порядка строк в DataFrame метка для конкретной строки не меняется.

Дополнительную информацию об эффективном использовании индекса см. в документации по индексированию.

Копии и операции на месте¶

Большинство операций pandas возвращают копии Series/DataFrame . Чтобы изменения «закрепились», вам нужно либо назначить новую переменную:

Несколько лет назад я написал статью об использовании pandas для создания различий между двумя файлами Excel. За прошедшие годы API панд изменился, и скрипт сравнения больше не работает с последними выпусками панд. Благодаря магии поисковых систем люди все еще находят эту статью и просят помощи, чтобы заставить ее работать с более поздними версиями pandas. Поскольку pandas близится к выпуску версии 1.0, я думаю, что сейчас самое время выпустить обновленную версию.

Начать

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

Я собираюсь пройти через это довольно быстро. Если есть интерес, я могу рассказать о паре этих концепций в следующем посте. Не стесняйтесь следовать в тетради. Файлы данных также находятся на github.

Первый шаг — импортировать pandas и читать файлы:

Вот как выглядит верхняя часть нового фрейма данных:

Наш процесс сравнения ищет три типа изменений:

Что такое новые аккаунты?
Что такое удаленные/удаленные учетные записи?
В каких аккаунтах изменилась информация?

Поскольку все ключи связаны с номером счета, мы можем использовать наборы Python, чтобы сделать процесс более простым для понимания. Это изменение по сравнению с исходным подходом, на мой взгляд, делает весь код более понятным. Я не проверял это на больших наборах данных, чтобы оценить производительность.

Затем мы объединяем все данные вместе и получаем чистый список уникальных данных и сохраняем все измененные строки с помощью drop_duplicates

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

Вот как сейчас выглядят дубликаты:

номер счета	имя	улица	город	штат	почтовый индекс	версия
0	935480	Bruen Group	5131 Nienow Viaduct Apt. 290	Порт-Арли	Алабама	14118	новый
1< /th>	371770	Cruickshank-Boyer	839 Lana Expressway Suite 234	Южная Вивиана	Алабама	57838	новый
2	548367	Спенсер, Грейди и Герман	65387 Lang Circle Apt. 516	Гринхолтбери	Аляска	58394	новый
3	132971	Уильямсон, Шумм и Хеттингер	89403 Casimer Spring	Джеремибург	Арканзас	62785	новый
4	985603	Bosco-Upton	03369 Moe Way	Порт Касандра	Арканзас	86014	новый

< td>Вайоминг

номер счета	имя	улица	город	штат	почтовый индекс	версия
24	595932	Кухик, Эйхман и Уэст	4059 Тобиас Инлет	Нью-Риланфурт	Иллинойс	89271	старый
30	558879	Watsica Group	95616 Enos Grove Suite 139	West Atlas	Айова	47419	старый
96	880043	Beatty Inc	3641 Schaefer Isle Suite 171	Северный Гарднертаун	Вайоминг	64318	старый
123	595932	Кухич, Эйхман и Вест< /td>	4059 Tobias St	Нью-Риланфурт	Иллинойс	89271	новый
129	558879	Watsica Group	Большая улица, 829	Смиттаун	Огайо	47919	новый
195	880043	Beatty Inc	3641 Schaefer Isle Suite 171	Северный Гарднертаун	64918	новый

Теперь мы разделяем старые и новые данные, удаляем ненужный столбец версии и устанавливаем номер счета в качестве индекса. Эти шаги настраивают данные для окончательного сравнения.

Теперь у нас есть кадр данных, который выглядит так:

td>

старый					новый
имя	улица	город	штат	почтовый индекс	имя< /th>	улица	город	штат	почтовый индекс
номер счета
595932	Кухик, Эйхман и Уэст	4059 Tobias Inlet	Новый Риланфурт	Иллинойс	89271	Кухик, Эйхманн и Вест	4059 Tobias St	Нью-Риланфурт	Иллинойс	89271
558879	Watsica Group	95616 Enos Grove Suite 139	West Atlas	Айова	47419	Watsica Group	Большая улица 829	Смиттаун	Огайо	47919
880043	Beatty Inc	3641 Schaefer Isle Suite 171	Северный Гарднертаун	Вайоминг	64318	Beatty Inc	3641 Schaefer Isle Suite 1 71	Северный Гарднертаун	Вайоминг	64918

Прежде чем мы выполним последнее большое объединение, нам нужно определить функцию, которая покажет нам, что изменилось от столбца к столбцу:

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

Что делает это изменение в кадре данных:

< th>новый < td>Beatty Inc td>

название		улица		город		штат		почтовый индекс
старый	новый	старый	новый	старый	старый	новый	старый	новый
аккаунт число
595932	Кухич, Эйхман и Уэст	Кухич, Эйхман и Уэст	4059 Tobias Inlet	4059 Tobias St	Нью-Риланфурт	Нью-Риланфурт	Иллинойс	Иллинойс	89271	89271
558879	Watsica Group	Watsica Group	95616 Enos Grove Suite 139	829 Big street	West Atlas	Смиттаун	Айова	Огайо	47419	47919
880043	Beatty Inc	3641 Schaefer Isle Su ite 171	3641 Schaefer Isle Suite 171	Северный Гарднертаун	Северный Гарднертаун	Вайоминг	Вайоминг	64318	64918

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

Что дает хорошую сводку измененных столбцов:

< td>47419 —-> 47919 td>

номер счета	город	имя	почтовый индекс код	штат	улица
0	595932	Нью-Риланфурт	Кухик, Эйхман и Вест	89271	Иллинойс	4059 Tobias Inlet —-> 4059 Tobias St
1	558879	West Atlas —-> Smithtown	Watsica Group	Айова —-> Огайо	95616 Enos Grove Suite 139 —-> 829 Большая улица
2	880043	Северный Гарднертаун	Beatty Inc	64318 —-> 64918	Вайоминг	3641 Schaefer Isle Suite 171

Заключительный этап анализа – выяснить, что было удалено и добавлено:

Мы можем вывести все в файл Excel с отдельной вкладкой для изменений, дополнений и удалений:

Вот как это выглядит:

Заключение

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

В этом руководстве мы сравним Excel и Python и посмотрим, как выполнять базовые задачи анализа на обеих платформах.

Excel – наиболее часто используемое программное обеспечение для анализа данных в мире. Почему? Его легко освоить, и он довольно мощный, как только вы его освоите. Напротив, репутация Python заключается в том, что его сложнее использовать, хотя то, что вы можете сделать с ним, это то, что после того, как вы его изучите, он практически неограничен.

Но как на самом деле сравнить эти два инструмента анализа данных? Их репутация не соответствует действительности. В этом руководстве мы рассмотрим некоторые распространенные задачи анализа данных, чтобы продемонстрировать, насколько доступным может быть анализ данных Python.

В этом руководстве предполагается, что вы владеете Excel на среднем уровне, в том числе умеете пользоваться формулами и сводными таблицами.

Мы будем использовать библиотеку pandas для Python, которая предназначена для облегчения анализа данных в Python, но для этого руководства вам не нужны знания Python или pandas.

Зачем использовать Python, а не Excel?

Прежде чем мы начнем, вам может быть интересно, почему Python вообще заслуживает внимания. Почему нельзя было продолжать использовать Excel?

Несмотря на то, что Excel великолепен, есть некоторые области, которые делают язык программирования, такой как Python, лучше для определенных типов анализа данных. Вот несколько причин из нашего поста «9 причин, по которым пользователям Excel следует изучить программирование»:

Вы можете читать и работать практически с любыми данными.
Автоматизированные и повторяющиеся задачи стали проще.
Работа с большими наборами данных намного быстрее и проще.
Другим будет проще воспроизвести и проверить вашу работу.
Легче находить и исправлять ошибки.
У Python открытый исходный код, поэтому вы можете видеть, что стоит за используемыми вами библиотеками.
Расширенная статистика и возможности машинного обучения.
Расширенные возможности визуализации данных.
Кроссплатформенная стабильность — ваш анализ можно запустить на любом компьютере.

Чтобы было ясно, мы не рекомендуем отказываться от Excel — это мощный инструмент, который можно использовать во многих случаях! Но как пользователь Excel, вы также можете использовать возможности Python, чтобы сэкономить часы времени и открыть возможности для карьерного роста.

Стоит помнить, что эти два инструмента могут хорошо работать в тандеме, и вы можете обнаружить, что некоторые задачи лучше оставить в Excel, в то время как другие выиграют от мощности, гибкости и прозрачности, предлагаемых Python.

Импорт наших данных

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

< бр />

Наши данные сохраняются в виде файла CSV с именем sales.csv . Чтобы импортировать наши данные в pandas, нам нужно начать с импорта самой библиотеки pandas.

Приведенный выше код импортирует pandas и использует псевдоним для синтаксиса pd . Это может показаться сложным, но на самом деле это просто своего рода псевдоним — это означает, что в будущем мы можем просто использовать pd для обозначения панд, чтобы нам не приходилось каждый раз вводить полное слово.

Чтобы прочитать наш файл, мы используем pd.read_csv() :

Мы присвоили результат pd.read_csv() переменной с именем sales , которую мы будем использовать для ссылки на наши данные.Мы также поместили имя переменной в последнюю строку нашего кода, что выводит данные в хорошо отформатированную таблицу.

Сразу мы можем заметить несколько различий между тем, как панды представляют данные, и тем, что мы видели в Excel:

В пандах номера строк начинаются с 0, а не с 1 в Excel.
Имена столбцов в pandas берутся из данных, а не в Excel, где столбцы обозначаются буквами.
Если в исходных данных отсутствует значение, в pandas используется заполнитель NaN, который указывает, что значение отсутствует или равно нулю.
В данных о продажах к каждому значению добавляется десятичная точка, потому что pandas хранит числовые значения, которые включают нулевые ( NaN ) значения, в виде числового типа, известного как float (на нас это ни на что не влияет, но мы просто хотели объяснить, почему это).

Прежде чем мы изучим нашу первую операцию pandas, мы быстро узнаем немного о том, как хранятся наши данные.

Давайте воспользуемся функцией type(), чтобы посмотреть тип нашей переменной продаж:

Эти выходные данные говорят нам, что наша переменная продаж является объектом DataFrame, который является объектом определенного типа в pandas. Большую часть времени в пандах, когда мы хотим изменить фрейм данных, мы будем использовать специальный синтаксис, называемый методом фрейма данных, который позволяет нам получить доступ к определенным функциям, связанным с объектами фрейма данных. Мы увидим пример этого, когда выполним нашу первую задачу в пандах!

Сортировка данных

Давайте узнаем, как сортировать данные в Excel и Python. В настоящее время наши данные не отсортированы. В Excel, если бы мы хотели отсортировать наши данные по столбцу «Дата начала», мы бы:

Выберите наши данные.
Нажмите кнопку "Сортировать" на панели инструментов.
Выберите «Дата начала» в открывшемся диалоговом окне.

В пандах мы используем метод DataFrame.sort_values(). Мы кратко упомянули методы минуту назад. Чтобы использовать их, мы должны заменить DataFrame именем фрейма данных, к которому мы хотим применить метод — в данном случае sales . Если вы работали со списками в Python, вы знакомы с этим шаблоном из метода list.append().

Мы предоставляем методу имя столбца, чтобы указать, по какому столбцу следует сортировать:

Значения в нашем фрейме данных были отсортированы с помощью одной простой строки кода pandas!

Суммирование стоимости продаж

Последние три столбца наших данных содержат данные о продажах за первые три месяца года, известные как первый квартал. Нашей следующей задачей будет суммирование этих значений как в Excel, так и в Python.

Начнем с того, как мы достигаем этого в Excel:

Введите название нового столбца "Продажи Q1" в ячейке H1.
В ячейке H2 используйте формулу СУММ() и укажите диапазон ячеек, используя их координаты.
Перетащите формулу вниз во все строки.

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

Полезно иметь возможность быстро находить различия между двумя файлами Excel. Ниже приведен краткий пример того, как это сделать с помощью Python и pandas.

Многие другие решили эту проблему. Вот хорошая статья pbpython (и обновленная версия) и версия yassineAlouini. Мне нужен был простой инструмент с определенным результатом, и я решил написать собственную версию.

1. Те же файлы форм

Для этой первой версии я предполагаю, что оба файла имеют одинаковую форму (одинаковое количество строк и столбцов) и могли измениться только данные в отдельных ячейках: цель состоит в том, чтобы пометить изменения между двумя версиями одного и того же файла. Это полезно для подтверждения того, что ничего непредвиденного не изменилось, особенно в случае с файлом, предоставленным вам третьей стороной.

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

Для сравнения мы будем использовать кадры данных pandas, поэтому сначала импортируем pandas и читаем файлы:

Затем мы создаем новый DataFrame для различий и просматриваем исходные файлы, чтобы определить изменения:

Проход по строкам и столбцам DataFrame и доступ к значениям с помощью df.iloc — не самый быстрый способ выполнения этой операции (см. это обсуждение stackoverflow для некоторых альтернатив, включая df.apply() и df.iterrows() ) . Однако мне это просто понять и легко реализовать.

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

Далее мы хотим отформатировать вывод, чтобы выделить измененные ячейки. Поскольку pandas использует XlsxWriter для сохранения файлов, мы можем получить доступ к параметрам форматирования XlsxWriter.

Чтобы начать экспорт, мы указываем, что используем XlsxWriter, и назначаем фреймы данных листам.

Затем мы выберем лист DIFF, к которому нужно применить форматирование, и определим grey_fmt для неизмененных значений и highlight_fmt для измененных значений.

Используя условное форматирование XlsxWriter, мы можем применить соответствующий формат к измененным и неизмененным ячейкам, используя стрелку ( → ), определенную ранее в diff.

Наш исходный файл table_OLD.xlsx содержит некоторые фиктивные данные.

В целях тестирования мы изменим несколько ячеек в table_NEW.xlsx . Возможно, вы их заметите:

Вы видите все 6 измененных ячеек? Если не беспокойтесь, мы запустим наш скрипт excel-diff.py, чтобы выделить изменения.

И вот! Наш отчет о различиях table_OLD и table_NEW.xlsx быстро показывает, что изменилось.

Обернутый некоторыми функциями, вот весь скрипт:

2. Обновленные строки и столбцы

Предположим, что строки были добавлены или удалены в дополнение к изменению отдельных значений. Этот обновленный файл excel-diff-2.py будет выделять изменения красным цветом, новые строки — зеленым, а удаленные строки — серым. Обратите внимание, что это выбирает первый столбец в ваших данных как index_col для целей определения изменений строки. Вот полный сценарий:

Заключительные мысли

Это простой способ увидеть разницу между двумя файлами Excel, но его легко модифицировать, чтобы он соответствовал определенному набору данных или варианту использования. Поскольку мы используем панды DataFrames, исходные данные могут быть файлами csv или таблицей базы данных или любым другим форматом, из которого панды могут читать. Мы также можем переформатировать выходные данные, чтобы отображать только изменения, или представить данные по-другому, или, например, указать типы данных по столбцам.

Читайте также: