Уже больше месяца экспериментирую, исследую, как разные схемы (паттерны) осознанного дыхания влияют на вариабельность сердечного ритма (Heart Rate Variability, HRV на чуждом языке). В скромной, но не совсем уж крошечной Excel-таблице со столбцами “Паттерн”, “HRV”, “Пульс” набралось 258 записей и мне понадобилось выбрать победителя -- дыхательный паттерн, дающий на выходе максимальное значение HRV. Не вручную же сортировать эти записи!
Да, я знаю: есть Pivot Table & Power Query. Но Pivot Table мне не по душе необходимостью после каждого изменения таблицы-источника делать REFRESH, во-первых, избыточной сложностью создания, во-вторых. И просто так не нравятся Pivot Table, что главное. Что же касается Power Query, то сочетание слов вызывает у меня трепет и учащенное сердцебиение: не понимаю, что это за зверь такой и насколько он страшный или полезный.
Поэтому для подсчета результатов -- и выбора победителя -- применил относительно недавно появившуюся в Excel функцию GROUPBY в связке с XLOOKUP. И, раз уж пришлось группировать данные, решил сравнить нативные функции Excel с GROUPBY от Pandas (мы ведь помним, что сейчас Python можно запустить внутри Excel).
Написал простой код:
df = xl("Table5[#All]", headers=True)
df = df.dropna(subset=['HRV'])
#select patterns which are recorded >= 6 times
patterns = df.breathing_pattern.to_list()
patterns = list(set([i for i in patterns if patterns.count(i) >= 6]))
df = df[df.breathing_pattern.isin(patterns)]
patterns_efficacy = df.groupby("breathing_pattern").mean(
['HRV','HR']
).astype(int).reset_index()
patterns_efficacy = patterns_efficacy.sort_values(by=['HRV'], ascending=False)
patterns_efficacy Поместил код через =PY( в ячейку А1 Excel
И он прекрасно справился с задачей и выдал таблицу с результатами:
|
breathing_pattern |
HRV |
HR |
8 |
physiological sighs moderate |
59 |
65 |
7 |
physiological sighs light |
57 |
62 |
1 |
4.4-6.6 |
56 |
59 |
6 |
following pulse |
55 |
61 |
0 |
4.2-0-6.4-0 |
53 |
62 |
3 |
6-6 |
53 |
61 |
4 |
calming breathing: inhale through nose, slow exhale through mouth |
53 |
61 |
2 |
5-5 |
52 |
63 |
5 |
count: 4 inhale nose, 6 exhale mouth |
52 |
63 |
Комбинация функций GROUPBY и XLOOKUP тоже отработала без изъянов:
Для группировки по HRV в ячейку E1
=GROUPBY(Table5[[#All],[breathing_pattern]],Table5[[#All],[HRV]], AVERAGE,3,0,-2)
Для группировки по пульсу в ячейку E13
=GROUPBY(Table5[[#All],[breathing_pattern]],Table5[[#All],[HR]], AVERAGE,3,0,-2)
И для объединения в одну общую таблицу в ячейку G2
=XLOOKUP(E2:E10,E14:E22,F14:F22,"not found") breathing_pattern |
HRV |
HR |
physiological sighs moderate |
59 |
65 |
physiological sighs light |
57 |
63 |
4.4-6.6 |
56 |
60 |
following pulse |
55 |
61 |
4.2-0-6.4-0 |
54 |
63 |
6-6 |
54 |
61 |
calming breathing: inhale through nose, slow exhale through mouth |
53 |
62 |
5-5 |
53 |
63 |
count: 4 inhale nose, 6 exhale mouth |
52 |
63 |
* Для внимательных: разница в данных между двумя таблицами -- плод Python-овского округления до целых чисел.
* В “нативном” подходе нет отсечки паттернов с количеством замеров менее шести.
Выводы и наблюдения по теме:
В Python итоговая таблица сама автоматически изменяет размеры при добавлении новых паттернов. GROUPBY by Excel ведет себя так же, а вот связка GROUPBY&XLOOKUP уже потребует редактирования формул: нужно изменять адреса диапазонов ячеек, к которым обращается XLOOKUP.
Вопреки большему размеру Python-код мне кажется проще и для написания, и для чтения-понимания. Хотя писать код в ячейке Excel -- весьма извращенное удовольствие.
Исполнение кода Python требует интернет-соединения.
Выводы и наблюдения не совсем по теме:
Для меня лично схема дыхания “physiological sighs light” (легкие физиогические вздохи) -- оптимальный выбор.
Более шести месяцев я придумывал, зачем мне может понадобиться живущий в Excel Python и наконец нашел.
Буду благодарен за советы и критику. Постараюсь ответить на вопросы.
Комментарии (23)
itGuevara
03.06.2024 14:31Исполнение кода Python требует интернет-соединения.
Off-line никак?
economist75
03.06.2024 14:31+1Уже 6 лет как библиотеки PyXLL и XLWIngs дают Python/Pandas внутри Excel 2007-10-13-16-19.
Kahelman
03.06.2024 14:31Можно накатать код на чистом VBA живет в excel,
Можно на powershell- есть модуль для работы с excel.
Ещё вариант выкинуть данные в csv- импортировать в SQLite. Где выполнить запрос и выдать результаты.
в любом из случаем не требуется интернет. Но запускать надо будет после обновления данных
Gonchar_POTT Автор
03.06.2024 14:31В данной ситуации (не умею ни в VBA, ни в SQLite, мизерный объем данных, постоянное Интернет-подключение, умею в Pandas) мне показалось рациональным задействовать именно Python в Excel. И, не скрою, очень хотелось найти ему применение.
Kahelman
03.06.2024 14:31+1ЧатГпт в помощь :)
Решение прикольное не нравится что все через сайт МС. Сегодня соединение есть, завтра нет.
Кстати скормите задачу Чату ГПТ и посмотрите что он вам находит. Конечно придётся доработать напильником.
Gonchar_POTT Автор
03.06.2024 14:31ChatGPT можно скормить сырые данные и он их обработает, это да. И несложный код написать сможет.
LexX80
03.06.2024 14:31Попробуйте использовать jupyter, не в составе google colab, а в установке на рабочий ПК(если зайдет, можно в северном исполнении сделать).
Я в свое время использовал jupyter для разбора финансовых показателей для отчёта, данных было очень много иЭксель плакал и звал маму, pandas тратил секунды на это.
Gonchar_POTT Автор
03.06.2024 14:31Я постоянно использую локальный Jupyter Notebook на РС, отличный инструмент для определеных (не всех) задач. Здесь же идея в том, что все действия - и записи новых данных, и их обработка - происходят внутри одного файла. Открыл Excel, внёс новые строки в таблицу и сразу видишь результаты.
Raevus
03.06.2024 14:31+1Так и раньше можно было использовать питон через Excel, причём все работает оффлайн. Для этого нужно написать макрос на VBA и вызвать питон скрипт, передав ему через параметры путь к данному Excel, название листа и все прочее.
smnmegatron
03.06.2024 14:31+2"необходимостью после каждого изменения таблицы-источника делать REFRESH" в pandas тоже нужно код перезапустить, если данные в источнике обновились. Частично проблема обновления решается ДВССЫЛ, именоваными диапазонами. Но проблема кнопки-обновить остается.
Gonchar_POTT Автор
03.06.2024 14:31Выглядит странно, но мне показалось, что в моем сценарии код запускается на исполнение сам, сразу же после обновления таблицы-источника данных, ничего нажимать/запускать вручную не надо.
smnmegatron
03.06.2024 14:31Возможно, я не понимаю как работает эта строка:
df = xl("Table5[#All]", headers=True)Gonchar_POTT Автор
03.06.2024 14:31df = xl("Table5[#All]", headers=True)Из таблицы Excel "Table5" создается Python-объект, DataFrame. [#All] значит, что таблица используется полностью -- и данные, и header.
smnmegatron
03.06.2024 14:31Функция
xlне является стандартной функцией Pandas или Python, поэтому предполагается, что это пользовательская функция или функция из сторонней библиотеки, которая возвращает DataFrame?Gonchar_POTT Автор
03.06.2024 14:31Функция xl — стандартная функция Excel, которая, принимая одним из аргументов объект Excel, конвертирует его в объект Python, не обязательно датафрейм. Что-то вроде моста между Excel на локальном компьютере и Python на сервере Microsoft. Хотя аналогия с мостом — не очень удачная, ничего другого мне в голову не приходит.
Gonchar_POTT Автор
03.06.2024 14:31
На всякий случай: этот Python-код "живет" в ячейке A1, а не в отдельном файле. Это я забыл (упустил) акцентировать. И не требует привычного запуска.
vtal007
Кажется это оверхед (или как это правильно называется)
может проще было "промежуточные итоги", а потом сортировка
Просто данных я не вижу и непонятно, что считалось
Паверквери - можно ознакомится на ютубе есть лекции от "товарищ эксель" , но кажется PQ не для Вашей задачи
а если уходить в питон. да сразу развернуть юпитер локально, закинуть туда файл эксель/csv и там уже отрабатывать
С другой стороны питон хорош для автоматизации
Gonchar_POTT Автор
Таблица вот такого вида. Подход через запуск отдельного исполняемого Python-файла или Jupyter Notebook мне кажется избыточным.
vtal007
да тут кажется просто сводная таблица и все. Не? (вот этот же код группирует и выводит средние по двум переменным "
groupby("breathing_pattern").mean( ['HRV','HR'] "258 строк это вообще ниачем, это даже не 258 тысяч (и их бы эксель прожевал спокойно, вот 2 миллиона уже без паверквери не получилось бы)
Так-то мне и питон тут излишним. питон в экселе это наверно скорее замена VBA, для современного поколения дата-аналитиков
Aizz
Ничего не хочу сказать, но вам достаточно было сделать дополнительную колонку со "Средним значением" и куда-нибудь отдельно вынести поиск максимального значения с INDEX+MATCH
Достаточно будет вносить новые значения и смотреть на результат.
https://i.imgur.com/n5Qj2PU.png
Gonchar_POTT Автор
За подсказку спасибо! Способов действительно много.