קריאת קובצי Excel ב-R (readxl)

R צריכה חבילה כדי לקרוא קובצי Excel

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 ל-data frame. לכל מה שתעשו כמתחילים הוא מתנהג בדיוק כמו data frame (הוא אכן כזה, עם תוספות): $ שולף עמודות, nrow() סופרת שורות, והוא זורם לתוך הפעלים של dplyr. ההבדלים הנראים לעין הם קוסמטיים ונעימים: הוא מדפיס רק את עשר השורות הראשונות עם סוגי העמודות מתחת לשמות, במקום לשפוך הכול. אם פונקציה ישנה כלשהי מתעקשת על data frame רגיל, 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" יחיד, הערה שהוקלדה בין המספרים, או רווח בתא "ריק" גורמים לכל העמודה לחזור כ-character. str() מיד אחרי הקריאה תופסת את זה; הארגומנט col_types (למשל col_types = c("text", "numeric", "date")) מכריע את העניין כשהניחוש ממשיך לטעות.

השורה התחתונה: גיליון אלקטרוני הוא קנבס שאנשים מציירים עליו, לא טבלה. קראו אותו בחשדנות, הריצו str(), ובדקו כמה ערכים מול המקור לפני שאתם סומכים על הייבוא.

מה לקחת מכאן

• R הבסיסית לא יכולה לקרוא .xlsx: התקינו את readxl, ואז read_excel("file.xlsx").
• excel_sheets() מציגה מה יש בחוברת עבודה; sheet = (העדיפו שמות) בוחר אחד; range = "B4:E20" חותך בדיוק את הטבלה שרוצים.
• מקבלים בחזרה tibble: data frame עם הדפסה נחמדה יותר.
• כתיבה עוברת דרך חבילה אחרת: writexl::write_xlsx() לפלט פשוט, openxlsx לחוברות עבודה מעוצבות.
• למקרים חד-פעמיים, ייצוא CSV מ-Excel ושימוש ב-read.csv() הם קיצור דרך מכובד לגמרי.
• שימו לב לשלוש הקלאסיקות: תאריכים כמספרים סידוריים (נקודת אפס 1899-12-30), תאים ממוזגים שהופכים לריקים, ותא רע אחד שגורר עמודה לטקסט.

הבא בתור: הכיוון השני. כתיבת ה-data frames שלכם לקובצי CSV ו-RDS.