Автоматизировать чтение, запись, объединение и разделение нескольких файлов Excel с помощью R

Одно из достоинств области аналитики данных - это ее открытость для множества инструментов. Эта область уже показала, что знание одного-единственного инструмента является препятствием. Чтобы выжить и развиваться в этой сфере, вам необходимо:

Мастер на все руки и мастер своего дела. - неизвестный

Excel - один из основных инструментов при работе с данными в большинстве предприятий. Я сам имел опыт работы с Excel для анализа данных и обнаружил несколько недостатков, работая с ними. Проблемы, с которыми я столкнулся, не были моими собственными, но с ними сталкивается и большинство пользователей.

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

  1. Столбцы Excel не разграничивают входные и выходные данные.
  2. Определяемые пользователем формулы не интуитивно понятны, поскольку вместо имен переменных помечаются местоположения ячеек.
  3. Открытие / обработка файлов большого размера происходит медленно, и система часто дает сбой (бизнес не может часто обновлять компьютеры).
  4. Объединение разных файлов Excel в один файл является обременительным и аналогичным образом работает с разными листами в одном файле.
  5. Excel находится за платным доступом.

Как уже упоминалось, программирование с открытым исходным кодом является одним из вариантов, и здесь я буду использовать R для автоматизации простой обработки файлов Excel.

Обсуждаемые темы:

  1. Машиночитаемое именование файлов
  2. Чтение файлов Excel
  3. Слияние файлов Excel
  4. Разделение файлов Excel
  5. Создать несколько файлов Excel

Цель

Цель - два чтения трех файлов Excel с глобальными данными, касающимися продолжительности жизни, расходов ВВП, использования природного топлива и наличия воды. Объедините файлы в один файл Excel с тремя листами и сохраните их в рабочей папке. Объедините три листа в один, используя Outer Join, и сохраните данные для каждой страны в соответствующей папке континента.

Итак, приступим.

Машиночитаемое именование файлов

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

  1. Избегайте диакритических знаков, пробелов и текста с учетом регистра.
  2. Используйте разделители или разделители, такие как «-» или «_».
  3. Используйте машиночитаемые форматы даты, например ГГГГ-ММ-ДД.
  4. Добавьте контроль версий в конце для облегчения навигации (например, -01, -02,…, -10).

Пример наименования файла: 2021–07-15_Excel_Automation-01.xlsx

Чтение файлов Excel

Для чтения файлов используется пакет xlsx. Есть две функции: read.xlsx() и read.xlsx2() для чтения файлов в xlsx пакете. read.xlsx2() работает быстро и эффективно при работе с большими наборами данных. Здесь приведенный ниже код предназначен для чтения книг Excel с одним листом.

# reading individual files
fuel <- read.xlsx2("global-fuel-vs-gdp.xlsx",sheetIndex = 1)
life_exp <- read.xlsx2("global-life-expectancy.xlsx",sheetIndex = 1)
water <- read.xlsx2("global-water-share.xlsx",sheetIndex = 1)

Поскольку мы здесь для автоматизации, давайте рассмотрим это. Чтобы подойти к проблеме чтения нескольких файлов, используйте функцию list.files(), чтобы создать список, содержащий имена всех интересующих файлов. Сохраняйте данные Excel с отдельных листов во фреймах данных. Используйте правильное соглашение об именах для определенных объектов фрейма данных, так как это поможет в автоматизации. Назначение фреймов данных отдельным листам достигается двумя циклами For и функцией assign().

# set the working directory
setwd("C:/Users/amalasi/Documents/R/Blog post/Blog17-Excel1")

Слияние файлов Excel

После загрузки данных Excel данные готовы к обработке. Здесь обсуждаются два возможных способа объединения данных. Первый путь - создать одну книгу, содержащую несколько листов, соответствующих листам всех книг. Второй путь - объединить все листы в книге, созданной по первому пути, в один лист.

Создание одной книги с несколькими листами, соответствующих нескольким листам всех книг

Использование функции write.xlsx() внутри цикла for может помочь добавить несколько книг с несколькими листами в одну книгу в виде разных листов. Следует проявлять осторожность при использовании функции write.xlsx(), впервые убедитесь, что для параметра append установлено значение по умолчанию, которое равно FALSE. Это создает книгу, и для добавления отдельных листов в эту книгу просто установите параметр append=TRUE.

Создание одного листа путем объединения нескольких листов книги

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

Цель этого упражнения - объединить листы с помощью внешнего соединения. Для этого создайте список всех соответствующих фреймов данных, которые будут объединены для создания единого фрейма данных или листа. С помощью функций ls() и mget() создается список временных фреймов данных, который назначается объекту с именем local_list. Определив объект списка, можно легко управлять фреймами данных внутри цикла For. Этот фрагмент кода создаст объект с именем global_final со всеми данными, объединенными в один лист.

Разделение файлов Excel

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

Создавайте несколько файлов и папок

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

Первый шаг - создать папки, названные в честь континентов, что достигается с помощью функции dir.create().

Второй шаг - разбить лист на несколько фреймов данных с информацией по отдельным странам и сохранить их. Начните с индексации отдельных стран, так как это помогает в создании цикла For. Затем с помощью filter() извлеките информацию о стране и сохраните ее во временном объекте. Затем создайте книгу, определите лист и запишите данные на листы. Последний шаг - сохранить файлы в целевых папках.

Ссылка на полный код находится здесь.

Вывод

Статья наглядно демонстрирует, как потратить несколько часов на создание сценария кода на R и может сэкономить огромное количество времени в долгосрочной перспективе при слиянии и разделении нескольких файлов Excel.

Приведенный выше код не зависит от количества листов в книге и может обрабатывать листы с различными столбцами данных.

Ключевым обучением было: определение соглашений об именовании машиночитаемых файлов, а также чтение, запись, объединение и разделение нескольких листов и книг Excel.

Следующими шагами будет использование сводных таблиц, Vlookup и других функций Excel с использованием R.

Если меня интересуют другие блоги, связанные с автоматизацией, то обязательно загляните в этот блог:



Создайте свое резюме с помощью страничного пакета на R
Создание впечатляющего резюме за 1 час с помощью страничного пакета на R в сторонуdatascience.com



Другие блоги на R и построение графиков на R:



3 менее известных оператора конвейера в Tidyverse
Операторы конвейера тройника, представления и присваивания для написания чистого кода. todatascience.com





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





7 горячих клавиш, которые стоит знать для эффективного рабочего процесса в R
Удобные сочетания клавиш для обычного кодирования в RStudio в сторонуdatascience.com





Визуализация данных с помощью ggplot2: 5 функций, которые стоит знать
Советы по повышению эффективности использования пакета ggplot2 для dataviz в сторонуdatascience.com





Основная визуализация данных с помощью ggplot2: круговые диаграммы, паутинные диаграммы и гистограммы
Ваше руководство по созданию круговых диаграмм, паучьих диаграмм и круговых гистограмм в R. todatascience.com



Источник данных

  1. Https://ourworldindata.org/grapher/life-expectancy
  2. Https://ourworldindata.org/water-sanitation-2020-update
  3. Https://ourworldindata.org/energy-poverty-air-pollution

Ссылка на репозиторий Github.

Вы можете связаться со мной в LinkedIn и Twitter, чтобы следить за моим путешествием по науке о данных и визуализации данных.