如何優雅的把CSMAR(國泰安)數據導入R

前言

CSMAR(國泰安)資料庫是經濟金融相關的科研工作者用到的最多的資料庫之一。它提供了豐富全面的上市公司財務及金融數據，以及一些行業宏觀層面的數據。但是，它並沒有像WRDS(沃頓研究數據服務)等資料庫提供豐富介面(如SAS，R等)供下載，只能在網頁上下載好數據然後導入到相應的分析軟體進行分析。

我在最近使用該資料庫時發現，雖然CSMAR(國泰安)資料庫可以提供CSV格式下載，但限制每次只能有300,000條查詢，而且在導入R的過程中總是報錯。另外還有其他的可以導入R的txt格式，但無一例外都出現錯誤，無法導入成功。最終無奈選擇xls格式，而xls格式由於自身的限制，每個文件只有65,000條數據。

因此把CSMAR數據導入R就變成了兩個問題：

1. 批量導入excel數據文件。
2. 為每個變數添加標籤。(因為許多變數名並不能直觀的知道它的意思，而Rstudio裡面利用`Hmisc`的`label( )`函數給每個變數添加標籤就方便多了。)

完成之後，在Rstudio裡面的樣式是：

一步一步來

1. 下載數據並解壓在一個文件夾里，我們以CSMAR裡面基金表現數據為例：

如圖，每個子文件夾裡面又有很多xls文件，和標籤說明的txt文件。

2. 讀取xls文件的list：

filepath <- "~/基金表現/"list <- list.files(filepath, full.names = TRUE, recursive = T, pattern = "\.xls")## 讀取該文件夾下面所有子文件夾的xls文件

每個excel的文件如下：

library(foreach)library(doParallel)# 採用parallel，提高讀取效率registerDoParallel(makeCluster(detectCores())) # 先從每個xls文件第四行讀取數據，不包含變數名data <- foreach(i =1:length(list), .combine = rbind, .packages = c("readxl", "readr")) %dopar% read_excel(list[i], skip = 3, col_names = F)# 從一個文件中提取變數名稱names <- read_excel(list[1],col_names = T) %>% names()# 從文件夾的txt文件裡面提取變數名labels <- list.files(filepath, full.names = TRUE, recursive = T, pattern = "\.txt")[1]labels <- readLines(labels) %>% as_data_frame() %>% separate(value, into = c("var", "label","definition"), sep = "\[|\]", extra = "merge") %>% select(label) labels <- labels[[1]]

最後把變數名和labels 加入data 中：

names(data) <- names Hmisc::label(data, self=FALSE) <- labels

函數化整個過程：

CSMARxlsDF <- function(filepath){ list <- list.files(filepath,full.names = TRUE, recursive = T, pattern = "\.xls") names <- read_excel(list[1],col_names = T) %>% names() # generate labels labels <- list.files(filepath,full.names = TRUE, recursive = T, pattern = "\.txt")[1] labels <- readLines(labels) %>% as_data_frame() %>% separate(value, into = c("var", "label","definition"), sep = "\[|\]", extra = "merge") %>% select(label) labels <- labels[[1]] data <- foreach(i =1:length(list), .combine = rbind, .packages = c("readxl", "readr")) %dopar% read_excel(list[i], skip = 3, col_names = F) names(data) <- names Hmisc::label(data, self=FALSE) <- labels return(data)}

以後只要把下載的數據放在一個文件夾里，並提供該文件夾的路徑，就可以得到一個well-organized dataset.

注意：保存後重新載入帶有label的dataset，必須載入`library(Hmisc)`, 否則會出現錯誤！

其實，如果用Rstudio的話可以直接在裡面運行python代碼會更快速:

在R裡面readxl裡面的read_excel( ) 只能選擇skip 幾行，比如skip =3 表示忽略前三行。但是，在python裡面的pandas 提供的read_excel( ) 裡面的skiprows 可以給出一個list作為參數比如skiprows = [1,2] 表示忽略第二行和第三行，但仍然保留第一樣的變數名稱，對我們這個例子非常有用！

# 在Rstudio裡面添加python chunk就可以直接運行了，非常方便！ ```{python, engine.path=/Library/Frameworks/Python.framework/Versions/3.5/bin/python3.5}path = "Users/Sean/基金表現/"import pandas as pd import osimport feather filesxls = [os.path.join(root, name) for root, dirs, files in os.walk(path) for name in files if name.endswith((".xls"))]data = [pd.read_excel(f,skiprows = [1,2]) for f in filesxls]all_data = pd.concat(data)labels = pd.read_excel(filesxls[0], skiprows = [0]).columns.values.tolist()labels = pd.DataFrame(labels)feather.write_dataframe(labels, /Users/Sean/labels.feather)feather.write_dataframe(all_data, /Users/Sean/data.feather)```

推薦閱讀：