Типичные задачи Excel, продемонстрированные в pandas

Open in Colab


telegram

Введение

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

В качестве дополнительного бонуса я собираюсь выполнить нечеткое сопоставление строк (fuzzy string matching), чтобы продемонстрировать, как pandas могут использовать модули Python.

оригинал статьи Криса тут

Разберемся? Давайте начнем.

Добавление суммы в строку

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

Начнем с импорта данных из Excel в кадр данных pandas:

Мы хотим добавить столбец с итогами, чтобы показать общие продажи за январь, февраль и март. Это просто сделать в Excel и в pandas.

Для Excel я добавил формулу SUM(G2:I2) в столбец J.

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

Далее, вот как это делается в pandas:

Затем получим итоговые и некоторые другие значения за каждый месяц.

Пытаемся сделать в Excel:

Как видите, мы добавили SUM(G2:G16) в строку 17 в каждом столбце, чтобы получить итоги по месяцам.

В pandas легко выполнять анализ на уровне столбцов. Вот пара примеров:

Теперь хотим в pandas сложить сумму по месяцам с итогом (total).

Здесь pandas и Excel немного расходятся. В Excel очень просто складывать итоги в ячейках за каждый месяц.

Поскольку pandas необходимо поддерживать целостность всего DataFrame, то придется добавить еще пару шагов.

Сначала создайте сумму для столбцов по месяцам и итога (total).

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

Для начала - транспонировать данные и преобразовать Series в DataFrame, чтобы было проще объединить существующие данные.

Атрибут T позволяет преобразовать данные из строк в столбцы.

Последнее, что нужно сделать перед суммированием итогов, - это добавить недостающие столбцы.

Для этого используем функцию reindex.

Хитрость заключается в том, чтобы добавить все наши столбцы, а затем разрешить pandas заполнить отсутствующие значения.

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

Дополнительные преобразования данных

В качестве примера попробуем добавить к набору данных аббревиатуру штата.

С точки зрения Excel, самый простой способ - это добавить новый столбец, выполнить vlookup (ВПР) по имени штата и заполнить аббревиатуру.

Вот снимок того, как выглядят результаты:

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

В pandas у нас есть вся мощь экосистемы Python. Размышляя о том, как решить эту проблему с грязными данными, я подумал о попытке сопоставления нечеткого текста (fuzzy text matching), чтобы определить правильное значение.

К счастью, кто-то проделал большую работу в этом направлении.

В библиотеке fuzzy wuzzy есть несколько довольно полезных функций для таких ситуаций.

fuzzywuzzy использует расстояние Левенштейна для вычисления различий между последовательностями.

см. Применение библиотеки FuzzyWuzzy для нечёткого сравнения в Python на Хабре

Начнем с импорта соответствующих нечетких функций:

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

Вот несколько примеров того, как работает функция сопоставления нечеткого текста:

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

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

В функции мы либо возвращаем допустимое сокращение, либо np.nan, чтобы у нас были допустимые значения в поле.

Добавьте столбец в нужном месте и заполните его значениями NaN:

Теперь используем apply для добавления сокращений в столбец abbrev:

Думаю, это круто!

Мы разработали очень простой процесс для очистки данных. Очевидно, когда у вас 15 строк, это не имеет большого значения. Однако что, если бы у вас было 15 000?

Промежуточные итоги

В последнем разделе этой статьи давайте рассмотрим промежуточные итоги (subtotal) по штатам.

В Excel мы бы использовали инструмент subtotal:

Результат будет выглядеть так:

Создание промежуточного итога в pandas выполняется с помощью метода groupby:

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

Форматирование выглядит неплохо, теперь можем получить итоговые значения, как раньше:

Преобразуйте значения в столбцы и отформатируйте их:

Наконец, добавьте итоговое значение в DataFrame:

Вы заметите, что для итоговой строки индекс равен 0.

Можем изменить это с помощью метода rename:

Модуль sidetable значительно упрощает этот процесс и делает его более надежным.

Заключение

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

В качестве бонуса рекомендую видео Excel is Evil - Why it has no place in research