如何透過googlesheets4連結R與google試算表

-
王俞才


你可能跟我一樣，經常透過Google表單來做一些簡單的訪問調查。但在調查期間裡，你的主管或指導老師可能會希望你能每天即時回報最新的調查訪問情形。於是你就必須在R與google兩者間做著以下重複的事情，完成後，再把資料檔或報表存回雲端硬碟裡。

step1. 從Google表單中轉出試算表
step2. > dataset <- read.csv="" span="">"some_surveydata.csv")
step3. 在R軟體中進行資料清理與分析的工作
step4. > write.csv(dataset, "surveydata.csv")
step5. 繼續在Google試算表中完成後續的工作

這一連串看似簡單卻繁瑣的工作，一直都沒有什麼工具可以幫助你簡化流程。不過現在googlesheets4套件可以協助你解決這樣的問題！

googlesheets4提供了一個經由Sheets API v4連結Google試算表的界面。透過googlesheets4你可以直接讀取Google試算表中的資料，並且在雲端硬碟裡更新表單[1]。你可能會問：「為什麼要叫googlesheets4？我是不是錯過了googlesheets1到3呢？」其實並沒有喔！googlesheets4之所以命名為4，只是因為Sheets API為第4版！我們就直接來看googlesheets4是如何進行操作的。

一、                安裝套件

你可以透過CRAN來下載最新釋出的googlesheets4。

> install.packages("googlesheets4")
> library(googlesheets4)

或這經由GitHub下載開發版的googlesheets4。

> install.packages("devtools")
> devtools::install_github("tidyverse/googlesheets4")

二、                連結你的Google帳戶

在安裝完成後，接著就要連結到你的Google帳戶。輸入下列程式碼後，R就會要求你的Google帳戶授權，讓你可以透過R對Google表單進行修改或查詢等操作。

> sheets_auth()

此時瀏覽器會跳出一個視窗，請選擇你要使用的帳戶並輸入密碼。



完成後，Google會向你確認是否允許授權給Tidyverse API Packages，以查看、編輯、建立及刪除雲端硬碟中的試算表。若沒有其他問題，按下「允許」即可。


接著，Google會再一次確認你要使用的帳戶及相關授權內容。如果一樣沒問題，按下「允許」即可。


此時，在R的console裡會顯示”Enter authorization code”，你只需要把Google給你的那組授權碼複製貼上即可。


三、                讀取Google試算表

read_sheet()是googlesheets4套件裡讀取資料的主要指令，目標是讀取Google試算表。就如同readr::read_csv()是用來讀取csv文件，而readxl::read_excel()是用來讀取Excel表格一樣。在googlesheets4套件裡，大部分的指令都是以sheet_為開頭，因此sheets_read()也有同樣的功能。簡單來說，read_sheet()不過就是sheets_read()的別名罷了。

你可利用試算表的URL、ID或googledrive套件所產生的物件dribble來讀取資料，都可以得到相同的結果。

# 利用試算表的URL
> read_sheet("https://docs.google.com/spreadsheets/d/1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU/edit#gid=273006016")
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows
 
# 利用試算表的ID
> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU")
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows
 
# 利用物件dribble
> library(googledrive)
> drive_get("srda2020")%>%
+ sheets_read()
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows

至於試算表的URL或ID該如何取的呢？你只要在瀏覽器中打開試算表，就可以在網址列當中取得URL。以上述為例，該試算表的URL即為：
https://docs.google.com/spreadsheets/d/1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU/edit#gid=273006016。
這裡要提醒大家，這串URL包含了試算表(spread Sheet) ID和工作表(work sheet)ID，我們使用的是試算表ID。如果你的試算表包含了2個以上的工作表，那麼你可以透過參數sheet來選取要讀取的工作表。

> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU", sheet=2)
Reading from 'srda2020'
Range "'srda2020_2'"
 
# A tibble: 103 x 6
      id    b6    b7    b8    b9   b10
        
 1     3     2     2     5     7     9
 2    19     9     9     1     1     3
 3    42     9     9     5     2     3
 4    91     2     1     5     6     9
 5   112     9     9     5     7     9
 6   123     2     1     5     4     3
 7   246     2     1     2     7     9
 8   363     2     1     1     1     3
 9   415     9     9     3     6     9
10   415     9     9     3     6     9
# ... with 93 more rows
 
> read_sheet("1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU",
               sheet= "srda2020_2" )
Reading from 'srda2020'
Range "'srda2020_2'"
 
# A tibble: 103 x 6
      id    b6    b7    b8    b9   b10
        
 1     3     2     2     5     7     9
 2    19     9     9     1     1     3
 3    42     9     9     5     2     3
 4    91     2     1     5     6     9
 5   112     9     9     5     7     9
 6   123     2     1     5     4     3
 7   246     2     1     2     7     9
 8   363     2     1     1     1     3
 9   415     9     9     3     6     9
10   415     9     9     3     6     9
# ... with 93 more rows

四、                合併googledrive套件一起用

為什麼要建議你同時使用googlesheets4和googledrive套件呢？文章開頭有提到，googlesheets4是透過Sheets API v4.來讀取Google試算表中的資料。但Sheets API比較著重在試算表的資料內容及其詮釋資料的操作，無法提供文件級的操作，例如：刪除、複製或重新命名試算表等。因此，我們就必須透過Drive API來處理，而googledrive套件是目前最好的選擇。

大家是否有發現，我們在利用googlesheets4讀取試算表的資料時，是利用試算表的ID（URL也是由ID所組成的），而不是它的檔名。這是因為該API只接受試算表的ID。但googledrive套件就比較人性化，能接受檔名及ID。這也是為什麼建議你合併googledrive套件一起用的另一個原因。

最後，舉一個例子來幫助大家理解googledrive套件的應用。首先是下載及載入套件。

> install.packages("googledrive")
> library(googlesheets4)
> library(googledrive)

接著是請求Google帳戶授權。

> drive_auth()

完成授權後，我們就可以利用googledrive套件裡的函數，例如：drive_find()來檢視或搜尋google雲端硬碟中所有檔案，並利用drive_get()來啟動檔案，最後再透過googlesheets4將資料載入即可。

> drive_find("srda")
Items so far: 
172 
# A tibble: 2 x 3
  name        id                                                drive_resource   
*                                                               
1 srda2020    1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU 
2 srda2.csv   1_l9cyf5u5CJojvhxTOG2MFCLYYwr4tja             
 
> ss <- drive_get="" span="">"srda2020")
> sheets_get(ss)
Auto-refreshing stale OAuth token.
  Spreadsheet name: srda2020
                ID: 1LSTNRGhsnnXhHVmFRcK_CvoD4W2J5btOM_AcQA2QUWU
            Locale: zh_TW
         Time zone: America/Los_Angeles
       # of sheets: 2
 
(Sheet name): (Nominal extent in rows x columns)
  srda2020_1: 104 x 6
  srda2020_2: 104 x 6
 
> read_sheet(ss)
Reading from 'srda2020'
 
# A tibble: 103 x 6
      id    a1    a2    a3    a4    a5
        
 1     3     2    61     3     2     7
 2    19     9    21    10     1     6
 3    42     2    61     3     2     3
 4    91     2    59     3     2     4
 5   112     1    51     4     3     1
 6   123     2    46    13     2    10
 7   246     1    36     9     2     5
 8   363     1    37    13     2     1
 9   415     2    33     7     2     4
10   415     2    33     7     2     4
# ... with 93 more rows

在完成資料整理及分析後，最後一個步驟就是利用drive_upload()將本機檔案夾裡的資料檔上傳到Google雲端硬碟。

> write.csv(srda2020, "surveydat.csv")
> drive_upload("surveydat.csv ", "uploadname.csv")

其他函數的用法，可以參考下方參考文獻的內容。同時也再次提醒大家，在做資料整理或分析的過程當中，記得要先保留一份原始檔，以防資料修改錯誤導致無法回復喔！

五、                參考文獻

1.          googlesheets4. (2020-06-01). https://googlesheets4.tidyverse.org/index.html.
2.          googledrive. (2020-06-01). https://googledrive.tidyverse.org/index.html.


[1] 部分功能仍處於開發階段，必須使用「開發版」的套件才能使用。

留言

張貼留言

這個網誌中的熱門文章

使用Python進行資料整理 – 初探Pandas

-
圖片
李孟諵 資料整理是資料分析和建模前的準備工作，包括資料的讀取、檢核、修改、重新編碼和合併等。市場上有不少專為量化研究所設計的商業軟體，例如： SPSS 、 SAS 和 Stata 等，都能進行這項任務，但共通問題是價格所貲不菲。因此，有些人轉向使用開源軟體，其中，具資料整理功能的代表軟體就是「 R 」。 近年來，隨著資料科學 [1] （ Data Science ）一詞廣泛被討論，另一套追求開源精神的程式語言 Python ，因第三方函式庫 [2] （ library ） Pandas 日漸成熟，使其在資料整理的功能上也逐漸受到關注。因此，為了使大家更容易了解 Pandas 在調查資料的運用，本文以一個符合調查資料結構的小資料表，藉由調查資料的整理過程，來介紹所使用的函式以及說明程式碼和輸出結果。 一、 Python 簡介 Python 一個開源、簡潔、易讀的程式語言──由 Guido van Rossum 創建，於 1991 年首次發布，設計哲學為「優雅」、「明確」、「簡單」，其直譯式 [3] 、易擴充和跨平台的特性，加上豐富多元的第三方函式庫和社群，使其在各種程式應用上都能看見它的身影，例如：網頁開發、系統管理、資料處理、科學計算，以及近年熱門的機器學習和深度學習等。在近期的 TIOBE 指數 [4] 和 KDnuggets 調查 [5] 都有不錯的熱門度。 二、 Python 環境安裝 由於跨平台的特性， Windows 、 Mac 和 Linux 系統都能安裝。一般從 Python 原生環境加上所需的函式庫到整合開發環境（ Integrated Development Environment ，簡稱 IDE ） [6] ，對初學者來說安裝就是一個挑戰，因此建議直接下載「 Anaconda 」（號稱 Python 資料科學懶人包）來安裝，優點是安裝簡單，只需點選「下一步」即可，缺點是有些函式庫可能用不到，會佔據太多磁碟空間。安裝步驟可參考 官網 說明 ，惟補充兩個安裝時會遇到的問題（以 Windows 為例），第一個是環境變數（ PATH environment variable ）的選擇（如圖一），主要作用在於透過命令提示字元（ cmd.exe ）呼叫 Python 或相關程式時，是否需指引到絕對路徑。 Anacond
閱讀完整內容

SAS、SPSS、STATA 統計軟體檔案格式轉換介紹

-
圖片
陳家玉 [原文刊載於SRDA學術調查研究資料庫通訊第41期，2012.6] 一、   前言 市面上統計套裝軟體不勝枚舉，目前最常被使用的仍以 SAS 、 SPSS 、 STATA 為主流且各有所長。當好不容易申請或取得的資料檔，並非平時熟悉的統計軟體檔案格式時，如何將檔案進行讀取或轉換，是多數人常面臨到的問題。統計軟體檔案格式的轉換，除了利用統計套裝軟體內建的匯入及匯出功能外， Stat/Transfer 是另一個快速且便利的檔案格式轉換工具。本文將統計套裝軟體的匯入 / 匯出功能分為二部分介紹：第一部分以各軟體資料檔間的轉換為主；第二部分介紹匯入 / 匯出最通用的純文字檔及普遍用於數據資料整理的 EXCEL 檔，於下以 SAS 9.2 版、 SPSS 20 版、 STATA 12.1 版逐步分述之。 Stat/Transfer 統計數據轉換軟體則針對介面功能操作進行介紹。 二、   匯入 / 匯出統計軟體資料檔功能 在說明如何轉檔前，先瞭解此三種軟體資料檔的差異性（表 1 ），其中 SPSS 、 STATA 資料檔可以包含變項說明（ variable label ）與選項數值說明（ value label ）兩者，但 SAS 資料檔僅包含變項說明，選項數值說明則是另以格式檔儲存，需套用 format 指令才能取得。 表 1 ： SAS 、 SPSS 、 STATA 資料檔之差異   軟體 差異性 SAS SPSS STATA 說   明 資料與格式分別儲存 資料與格式儲存在同檔案中 資料與格式儲存在同檔案中 資料檔 *.sas7bdat *.sav *.dta 格式檔 *.sas7bcat 數據資料因受限於統計軟體資料檔支援性的問題，較常以純文字檔，搭配語法（ syntax ）撰寫進行檔案讀取。隨著套裝軟體視窗版的不斷發展、更新且越具易用性，可支援多種檔案類型的匯入與匯出，使得程式語法不再是絕對必要，讓使用者省去自行撰寫程式的麻煩，能快速又方便地讀取或轉換資料。 （一）      SAS 軟體匯入及匯出 SPSS/STATA 資料檔 1
閱讀完整內容

資料整理與檢誤經驗談—以SPSS程式進行邏輯檢查

-
圖片
蘇婉雯 [原文刊載於SRDA學術調查研究資料庫通訊第3期，2002.12] 邏輯檢誤相較於不合理值檢誤要來的更複雜、費事，也更需要研究者的專業知識。邏輯檢誤，主要是檢查受訪者在通篇問卷的答案是否前後一致、是否合理。藉由這道檢查，找到數據資料的可疑之處，進而修改之。例如：某人回答目前的信仰為「佛教」，但回答最常求助的神職人員為「神父」；某人回答年齡為「 25 歲」，回答親生子女就讀學校卻為「 XX 大學」；某人性別為「男」，應跳過生育經驗不需作答，卻回答了生育第一胎的年齡為 30 歲。像這些狀況，都需要翻查原始問卷，查看是否因鍵入錯誤而產生的不合理現象。 一般而言，邏輯檢誤所發現的錯誤答案，可能源自： 1.        問卷設計不明確，未將跳題或續答之相關敘述標明清楚，而導致訪員或受訪者答題時失誤。 2.        受訪者的失誤。如：誤解題意、記憶模糊、精神狀態不佳、蓄意隱瞞等。 3.        訪員的失誤。如：弄錯限答對象、應跳問卻沒有跳問、填錯格子、寫錯答案等。 4.        過錄員的失誤。如：誤填跳答碼、填錯過錄格、寫錯過錄碼等。 5.        輸入員的失誤。如：看錯格子或代碼、按錯輸入鍵 ( 通常以正確鍵前後左右四個鍵最常誤觸 ) …等。 進行「邏輯檢誤」時，覆查問卷的工作相當費神，查核者往往需要前後對照數道題目。如果能盡量減少這個階段需要查驗的變項，將會是比較有效率的作法。因此， 檢誤工作比較理想的方式是先做「 不合理值 檢誤」，再做「邏輯檢誤」 ( 「不合理值的檢誤」請參考第二期內容 ) 。 不合理值檢誤能夠先確立每一個變項的數據皆落在合理範圍內；也就是說，理想上，可以去除大半的書寫及鍵入錯誤；此時，再做邏輯檢誤，可以比較專注於真正邏輯問題的查驗，較有效率。 邏輯檢誤通常需要同時考量兩題以上的答題情形，以判斷合理的邏輯；但為了簡化複雜的情形，最常見的做法是兩兩考量，如果還需要第三題合併考量，再慢慢加進來。需要邏輯檢誤的情形包羅萬象，最重要的是，檢誤者需要先對問卷結構與內涵、受訪者特性有深入瞭解之後，再依照需要設計程式檢誤。職是之故，唯有原問卷設計的研究人員最清楚該檢誤的邏輯，二手資料蒐集者及使用者是很難進行這部份的工作。 在此將邏輯檢核分成三類型來說明。第一種類型是跳 /
閱讀完整內容