قراءة ملفات Excel في R (readxl)

يحتاج R إلى حزمة لقراءة ملفات Excel

يقرأ base R الصيغ النصية الصرفة جاهزًا، لكن .xlsx ليست نصًا، بل حزمة XML مضغوطة، وقبلها كانت .xls صيغة ثنائية. فلا توجد دالة read.xlsx() مدمجة تنتظرك. ولفتح ملفات Excel تثبّت حزمة، والخيار المعياري هو readxl: تقرأ كلتا الصيغتين .xlsx و.xls، وليست لها اعتماديات خارجية (لا Java ولا حاجة إلى تثبيت Excel)، وتؤدي مهمة واحدة على أكمل وجه.

install.packages("readxl")   # once per machine
library(readxl)              # once per session

تحتاج مقتطفات هذه الصفحة إلى تثبيت readxl محليًا، لذا تُعرض كشيفرة ثابتة لا ككتل قابلة للتشغيل، فشغّلها في جلسة R الخاصة بك. وإذا كانت الحزم جديدة عليك فالخلاصة القصيرة: تنزّلها install.packages() مرة واحدة، وتحمّلها library() في كل مرة تشغّل فيها R.

‏read_excel(): الأساسيات

استدعاء واحد يقرأ الورقة الأولى من مصنف إلى R:

library(readxl)

sales <- read_excel("sales.xlsx")
head(sales)
str(sales)

العادات نفسها المتبعة في أي استيراد: ‏str() لفحص نوع كل عمود، وhead() لإلقاء نظرة على الصفوف الأولى. وإذا لم يُعثر على الملف فالسبب دليل العمل في الغالب الأعم، وتخبرك file.exists("sales.xlsx") بذلك خلال ثانية، والإصلاح هو نفسه المتبع مع ملفات CSV: استخدم مسارًا كاملًا أو اضبط دليل العمل على مجلد الملف.

وما يعود إليك هو tibble، أي صيغة tidyverse من إطار البيانات. ولكل ما ستفعله كمبتدئ يتصرف تمامًا كإطار بيانات (فهو كذلك فعلًا مع إضافات): يستخرج $ الأعمدة، وتعدّ nrow() الصفوف، وينساب إلى أفعال dplyr. أما الفروق الظاهرة فتجميلية ولطيفة، إذ يطبع الصفوف العشرة الأولى فقط مع أنواع الأعمدة تحت الأسماء بدل إغراقك بكل شيء. وإذا أصرّت دالة قديمة على إطار بيانات عادي، فإن as.data.frame(sales) تحوّله.

اختيار الأوراق والنطاقات وتخطي الفوضى

نادرًا ما تكون المصنفات الحقيقية جدولًا نظيفًا واحدًا يبدأ عند الخلية A1. ووسائط readxl تتعامل مع الفوضى المعتادة.

ما الأوراق الموجودة؟ اسأل قبل أن تقرأ:

excel_sheets("report.xlsx")
# [1] "Summary"  "Q1"  "Q2"  "Q3"  "Raw data"

sheet = تختار ورقة بالاسم أو بالموضع:

q3 <- read_excel("report.xlsx", sheet = "Q3")
raw <- read_excel("report.xlsx", sheet = 5)

فضّل الاسم، فـ sheet = 5 تقرأ الورقة الخطأ بصمت يوم يعيد أحدهم ترتيب الألسنة.

range = تقرأ مستطيلًا محددًا بترقيم Excel نفسه. وهذه أنظف طريقة لتخطي صفوف الشعارات وصفوف العناوين والملاحظات المتناثرة حول الجدول الفعلي:

budget <- read_excel("budget.xlsx", range = "B4:E20")
budget <- read_excel("budget.xlsx", sheet = "Plan", range = "B4:E20")

skip = وcol_names = هما البديل الأكثر مرونة حين تعرف عدد الأسطر الزائدة فوق البيانات لكنك لا تعرف أين تنتهي:

# Data starts after 3 title rows, first real row is the header:
df <- read_excel("export.xlsx", skip = 3)

# No header row at all - supply names yourself:
df <- read_excel("export.xlsx", col_names = c("id", "region", "amount"))

تستخدم col_names = TRUE (القيمة الافتراضية) الصف الأول أسماءً، بينما تولّد FALSE الأسماء ...1 و...2 وتعامل الصف الأول بيانات.

الكتابة إلى Excel تحتاج حزمة مختلفة

صُممت readxl للقراءة فقط. وحين يريد زميل نتائجك على هيئة جدول بيانات، فأسرع طريق هو writexl، وهو سطر واحد بلا اعتماديات:

install.packages("writexl")
writexl::write_xlsx(results, "results.xlsx")

وإذا احتجت إلى أكثر من القيم الخام، كترويسات عريضة وأجزاء مجمّدة وألوان خلايا وعدة أوراق منسّقة في مصنف واحد، فذلك مجال openxlsx:

library(openxlsx)
write.xlsx(list(Summary = summary_df, Detail = detail_df), "report.xlsx")

تصبح القائمة المسماة ورقة لكل عنصر. وتستطيع openxlsx قراءة ملفات Excel أيضًا، لذا ستراها مستخدمة في الاتجاهين على أرض الواقع، أما للقراءة المجردة فتبقى readxl الأداة الأبسط.

البديل العملي: صدّر ملف CSV

أحيانًا يفوز الحل الأقل ذكاءً. فإذا كان المصنف حالة فردية، كأن يرسل إليك أحدهم جدول بيانات وتحتاج إلى الأرقام الآن، فافتحه في Excel واستخدم "حفظ باسم" لتصدير الورقة بصيغة CSV، ثم اقرأها بالأداة التي تعرفها أصلًا:

df <- read.csv("sales.csv")

لا حزمة ولا أسماء أوراق ولا خلايا مدمجة. وسير العمل الكامل موجود في قراءة CSV. أما المقايضات فهي أنك تفقد بقية الأوراق، وتسطّح المعلومات القائمة على التنسيق (كألوان الخلايا التي "تعني" شيئًا، وهي رائحة سيئة في تصميم البيانات أصلًا)، كما أن التصدير خطوة يدوية سينساها أحدهم حين يُحدَّث الملف المصدر. فلمسار متكرر اقرأ ملف .xlsx مباشرة، ولاستيراد لمرة واحدة يكون CSV خيارًا محترمًا بصدق.

مصائد استيراد Excel الكلاسيكية

تحمل ملفات Excel مشكلات لا تحملها ملفات CSV، لأن جداول البيانات تتيح للبشر فعل أشياء لا ينبغي للجداول أن تفعلها.

تصل التواريخ أرقامًا. يخزّن Excel التواريخ عدّادًا تسلسليًا للأيام، وإذا خلط عمود بين الأنواع أو نُسِّق بشكل غريب فقد ينتهي بك الأمر إلى 44688 حيث توقعت تاريخًا. وتحوّل readxl خلايا التاريخ الحقيقية تحويلًا صحيحًا عادةً، لكن حين تحصل على الرقم الخام فحوّله باستخدام بداية حقبة Excel، وهي 1899-12-30 لا عام 1970:

as.Date(44688, origin = "1899-12-30")
# [1] "2022-05-07"

الخلايا المدمجة تتفكك إلى فراغات. فترويسة مدمجة عبر ثلاثة أعمدة تعود قيمة واحدة وخليتين فارغتين، وتسمية فئة مدمجة عبر عشرة صفوف تصبح قيمة واحدة وتسع قيم مفقودة. ولا تستطيع readxl استرجاع قصد لم يوجد إلا بصريًا، فتوقع أن تملأ تلك الفجوات بنفسك بعد الاستيراد.

خلية شاردة واحدة تحوّل العمود إلى نص. فأنواع الأعمدة مخمَّنة من البيانات، لذا فإن "n/a" واحدة، أو ملاحظة مكتوبة وسط الأرقام، أو مسافة في خلية "فارغة"، تجعل العمود كله يعود نصيًا. وتلتقط str() مباشرة بعد القراءة هذه المشكلة، ويحسم الوسيط col_types (مثل col_types = c("text", "numeric", "date")) الأمر حين يستمر التخمين في الخطأ.

والخلاصة الجامعة: جدول البيانات لوحة يرسم عليها الناس، لا جدول بيانات منظم. فاقرأه بعين متشككة، وشغّل str()، وتحقق من بضع قيم مقابل الأصل قبل أن تثق بالاستيراد.

الخلاصة

• لا يستطيع base R قراءة .xlsx، فثبّت readxl ثم استخدم read_excel("file.xlsx").
• تسرد excel_sheets() محتويات المصنف، ويختار sheet = (فضّل الأسماء) ورقة واحدة، ويقتطع range = "B4:E20" الجدول الذي تريده بالضبط.
• تحصل على tibble، وهو إطار بيانات بطباعة أجمل.
• تمر الكتابة عبر حزمة مختلفة: ‏writexl::write_xlsx() للمخرجات البسيطة، وopenxlsx للمصنفات المنسّقة.
• للحالات الفردية، يُعد تصدير CSV من Excel واستخدام read.csv() اختصارًا محترمًا تمامًا.
• انتبه إلى الثلاثة الكلاسيكية: التواريخ كأرقام تسلسلية (بأصل 1899-12-30)، والخلايا المدمجة التي تصبح فراغات، والخلية السيئة الواحدة التي تجرّ العمود إلى النص.

في المستند التالي: الاتجاه الآخر، أي كتابة أطر بياناتك إلى ملفات CSV وRDS.