如何优雅的把CSMAR(国泰安)数据导入R和Python

2019-01-08 本文已影响0人王新路Sean

前言

CSMAR(国泰安)数据库是经济金融相关的科研工作者用到的最多的数据库之一。它提供了丰富全面的上市公司财务及金融数据，以及一些行业宏观层面的数据。但是，它并没有像WRDS(沃顿研究数据服务)等数据库提供丰富接口(如SAS，R等)供下载，只能在网页上下载好数据然后导入到相应的分析软件进行分析。

我在最近使用该数据库时发现，虽然CSMAR(国泰安)数据库可以提供CSV格式下载，但限制每次只能有300,000条查询，而且在导入R的过程中总是报错。另外还有其他的可以导入R的txt格式，但无一例外都出现错误，无法导入成功。最终无奈选择xls格式，而xls格式由于自身的限制，每个文件只有65,000条数据。

因此把CSMAR数据导入R就变成了两个问题：

批量导入excel数据文件。
为每个变量添加标签。(因为许多变量名并不能直观的知道它的意思，而Rstudio里面显示每个变量标签就方便多了)

完成之后，在Rstudio里面的样式是：

一步一步来

安装两个packages:

install.packages(c("tidyverse", "readxl"))

下载数据并解压在一个文件夹里，我们以CSMAR里面基金表现数据为例：

如图，每个子文件夹里面又有很多xls文件，和标签说明的txt文件。

读取xls文件的list：

library(tidyverse)
library(readxl)
filepath <- "~/基金表现/"
list <- list.files(filepath, 
                    full.names = TRUE, # 读取完整路径
                    recursive = T, # 读取所有的子文件夹
                    pattern = "\\.xls" # 只读取 .xls文件，也可以改成 .xlsx
                    )

## 读取该文件夹下面所有子文件夹的xls文件

每个excel的文件如下：

这个excel里面第一行是变量名，第二行是标签，第三行是单位，数据从第四行开始，那么读取所有excel表的数据：

先读取数据

# 数据部分
data <- list %>% as_tibble() %>%
    mutate(DF = map(value,
                    ~ read_excel(., skip = 3, 
                                            col_names = F))) %>%
    select(-value) %>%
    unnest()

# 从一个文件中提取变量名称

names <- read_excel(list[1],col_names = T) %>% names()

# 从文件夹的txt文件里面提取变量名

labels <- read_excel(list[1],skip = 1) %>% names()

最后把变量名和labels加入data中：

# 先写一个加label的函数 

addlabel <- function(dat, vlist, labellist) {
    for (v in vlist) {
        attr(dat[[v]], 'label') <- labellist[which(vlist == v)]
    }
    dat
}

# 给数据加变量名字
names(data) <- names

# 给变量加labels
data <- addlabel(data, names(data), labels)

其实你只需要下面这个函数！

当然你看完这篇文章就不需要这么麻烦了，直接复制下面的函数就可以了！

# 读取文件夹里的，包括子文件夹的所有xls 

CSMARxlsDF <- function(filepath){ 
    library(tidyverse) 
    library(readxl)
    
    list <- list.files(filepath,full.names = TRUE, recursive = T,
                       pattern = "\\.xls")

    names <- read_excel(list[1],col_names = T) %>% names()

    # generate labels
    
    labels <- read_excel(list[1],skip = 1) %>% names() 

    data <- list %>% as_tibble() %>%
        mutate(DF = map(value,
                        ~ read_excel(., skip = 3, 
                                                col_names = F))) %>%
        select(-value) %>%
        unnest()
    
    # 先写一个加label的函数 
    addlabel <- function(dat, vlist, labellist) {
        for (v in vlist) {
        attr(dat[[v]], 'label') <- labellist[which(vlist == v)]
            }
            dat
        }
    # 给数据加变量名字
    names(data) <- names

    # 给变量加labels
    data <- addlabel(data, names(data), labels)
    return(data)
}

如果只有一个excel文件，那就更简单了

# 读取文件夹里的，包括子文件夹的所有xls 
CSMARxlsDFone <- function(onefilepath){ 
    library(tidyverse) 
    library(readxl)
    names <- read_excel(onefilepath,col_names = T) %>% names()
    labels <- read_excel(onefilepath,skip = 1) %>% names() 
    data <- read_excel(onefilepath, skip = 3, col_names = F)
    
    # 先写一个加label的函数 
    addlabel <- function(dat, vlist, labellist) {
        for (v in vlist) {
        attr(dat[[v]], 'label') <- labellist[which(vlist == v)]
            }
            dat
        }
        
    # 给数据加变量名字
    names(data) <- names

    # 给变量加labels
    data <- addlabel(data, names(data), labels)
    return(data)
}

以后只要把下载的数据放在一个文件夹里，并提供该文件夹的路径，就可以得到一个well-organized dataset!

导入python

其实，如果用Rstudio的话可以直接在里面运行python代码会更快速:

在R里面readxl package里面的read_excel( )只能选择skip 几行，比如skip = 3 表示忽略前三行。但是，在python里面的pandas提供的read_excel() 里面的skiprows可以给出一个list作为参数比如skiprows = [1,2] 表示忽略第二行和第三行，(python里面0表示第一个) 但仍然保留第一样的变量名称，对我们这个例子非常有用！

在Rstudio里面添加python chunk就可以直接运行了

python和R之间进行数据沟通的时候，一个好用的格式是feather, python里面安装可以通过 pip install feather-format 完成。


path = "～/基金表现/"

import pandas as pd 
import os
import 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')