Многие компании сейчас ведут расчеты не в Excel, а в Google Sheets (Гугл таблицы). Это удобный и главное бесплатный способ ведения учета и расчетов. Таблицы Google Sheets позволяют работать над одной таблицей нескольким пользователям, онлайн, из любой точки мира, нужен только доступ в интернет.
Еще такой способ сбора и обработки данных интересен тем, что один раз настроив загрузку и обработку таблицы в Google Sheets, при каждом обновлении данных нет необходимости копировать и сохранять файл с новыми данными. При внесении онлайн изменений в таблицу, из Google Sheets будут загружаться обновленные данные.
Есть несколько способов загрузки данных из Google Sheets, включая настройку импорта данных в Pandas по API, в данной статье будет рассмотрен наиболее простой и понятный даже новичку метод загрузки.
Подготовка таблицы
Для того чтобы таблица правильно скачалась и данные правильно определились, необходимо проделать с таблицей ряд операций.
Во-первых, необходимо избавиться от запятых разделяющих целые и десятичные разряды чисел. Числовые данные с запятыми вместо точки, будут определяться как простой текст. Pandas не сможет его сконвертировать. Для изменения нужно зайти в “Файл” > “Настройки” > “Региональные настройки” и поменять страну на “Соединенные штаты” либо “Великобритания”. После этой смены все ваши запятые в разрядах чисел должны поменяться на точки.
Во-вторых, нужно выбрать правильный числовой формат без разделителей - пробелов. Заходим “Формат” > “Числа” > “Другие форматы чисел” и выбираем формат в котором числа отображаются без пробелов разделяющих тысячи.
Определение адреса таблицы Google Sheets
Для начала найдем в HTTP адресе таблицы нужные для загрузки элементы. Адрес всегда можно посмотреть и скопировать в адресной строке браузера
Типичный адрес таблицы:
https://docs.google.com/spreadsheets/d/16ZJ5UWRwshEL3e1wddUWBuz3qxuQJtrJknVnV/edit?gid=13026139#gid=13026139
Для дальнейшей работы нам нужен текст между https://docs.google.com/spreadsheets/d/ и /edit?gid=13026139#gid=13026139
Этот блок адреса 16ZJ5UWRwshEL3e1wddUWBuz3qxuQJtrJknVnV это уникальный идентификатор файла таблицы, который может содержать несколько листов. У каждого файла он свой. Вам просто нужно скопировать кусок адреса между двумя слэшами. Скопировать нужно точно, иначе вы получите ошибку и ваш запрос к таблице не найдет нужный адрес.
Загрузка таблицы
На первом этапе формируем две переменные и присваиваем им нужные значения.
Первая переменная googleSheetId = '16ZJ5UWRwshEL3e1wddUWBuz3qxuQJtrJknVnV' это наш идентификатор файла таблицы. Это текстовая переменная, поэтому она записывается в кавычках.
Вторая переменная workSheetName = 'base1' . Это имя листа (base1) на котором расположена нужная для загрузки таблица.
На втором этапе формируем необходимый для запроса URL адрес.
URL = f'https://docs.google.com/spreadsheets/d/{googleSheetId}/gviz/tq?tqx=out:csv&sheet={workSheetName}'
Обратите внимание на особенность синтаксиса Python. Если перед кавычками поставить символ f”...” то в кавычках у вас получится форматируемый текст. В чем его фишка, вы видите, что внутри этого текста у нас стоят не текстовые значения идентификатора листа и имени листа, а внутри вот таких символов {} наименования наших переменных. Это очень удобный способ менять значения внутри текста, не прибегая к изменения текстового блока.
Таким образом, этот текстовый блок, с такими значениями переменных полностью равнозначен вот такому тексту:
'https://docs.google.com/spreadsheets/d/16ZJ5UWRwshEL3e1wddUWBuz3qxuQJtrJknVnV/gviz/tq?tqx=out:csv&sheet=base1'.
На третьем этапе мы используем для загрузки файла метод Pandas read_csv() указав в нем в качестве параметра пути, сформированный URL адрес. Загружаемой таблице присвоим имя датафрейма - demo_df.
Для того чтобы наш файл загрузился с правильным типом данных мы используем параметр dtype, в который передадим словарь из заголовков столбцов и необходимых типов данных в этом столбце. Более подробно про параметр dtype, читайте в статье про загрузку данных из CSV.
Код загрузки таблицы Google Sheets.
# Идентификатор файла
googleSheetId = '16ZJ5UWRwshEL3e1wddUWBuz3qxuQJtrJknVnV'
# Имя листа нужной таблицы
workSheetName = 'base1'
# Формирование URL адреса для скачивания
URL = f'https://docs.google.com/spreadsheets/d/{googleSheetId}/gviz/tq?tqx=out:csv&sheet={workSheetName}'
# Загрузка таблицы в датафрейм demo_df
demo_df = pd.read_csv(URL, dtype={'№ п/п': 'int', 'Годы': 'object', '2017': 'float64', '2018': 'float64',
'2019': 'float64', '2020': 'float64', '2021': 'float64', '2022': 'object',})
# Просмотр загруженной таблицы
demo_df.head()
Проверка типов данных
Проверим типы данных методом info()
В результате того что мы применили параметр dtype, столбцы таблицы получили нужные типы данных. Теперь с таблицей можно работать осуществлять необходимые манипуляции данных.
Такой вид загрузки данных, является частным случаем загрузки данных из CSV файла при использовании метода read_csv(). Подробнее о данном методе, читайте в статье по загрузке данных с использованием этого метода.
Если вам интересно больше читать статьи на эту тему ставьте лайк и подписывайтесь на канал, здесь будет много интересного про Pandas и анализ данных.
Также смотрите статьи:
Pandas. Загрузка данных из CSV.
Pandas. Загрузка данных из Excel.