将文本文件中隐藏格式的数据导入R

从一个可恶的当地政府网站下载了一堆数据.有77,000个项目条目看起来与以下内容完全相同,包含在纯文本文件中.我需要将这堆粪便导入R作为数据框：

Instrument: 201301240005447
Recorded: 01/24/2013
Consideration: $150,125.00
Document Type: MORTGAGES 
Pages: 17
Grantor: BYRES,CONNIE R / BYRES,SCOTT
Grantee: MORTGAGE ELECTRONIC REGISTRATION SYSTEMS INC / QUICKEN LOANS INC
Legal Description: * St:5495 MCNAMARA LN City:FLINT PrpId:1135532002 CC:11 T:8 R:7 S:35 ext:PT OF NE4 
 * 
---------------------------------/---------------------------------
Instrument: 201301240005408
Recorded: 01/24/2013
Consideration: $65,124.00
Document Type: MORTGAGES 
Pages: 17
Grantor: SANNE,BETTY LOU / SANNE,KENNETH D
Grantee: JPMORGAN CHASE BANK NA
Legal Description: Sub:WOODCROFT NO 1 Lt:188 St:2213 RADCLIFFE AVE City:FLINT PrpId:4024106003 CC:54 
 * 
---------------------------------/---------------------------------

有一些常用的字符向量,如“Instrument”,“Grantor”和“PrpId”.我究竟如何将其导入R？这会涉及解析或刮取某种类型吗？

不用说,我试图将此文件导入Excel但无法正常工作.我认为R会更好地工作,只需要弄清楚如何.谢谢

解决方法

sectionedFieldLinesToFrame <- function(lines,divRE,sepRE,select,rtw=T,...) {
    divLineIndexes <- grep(perl=T,lines);
    ## remove possible leading and trailing divs,for robustness
    if (length(divLineIndexes)>0L && divLineIndexes[1L]==1L) {
        leadDivCount <- match(T,c(diff(divLineIndexes)!=1L,T));
        lines <- lines[-seq_len(leadDivCount)];
        divLineIndexes <- divLineIndexes[-seq_len(leadDivCount)]-leadDivCount;
    }; ## end if
    if (length(divLineIndexes)>0L && divLineIndexes[length(divLineIndexes)]==length(lines)) {
        trailDivCount <- match(T,c(rev(diff(divLineIndexes)!=1L),T));
        lines <- lines[-seq(to=length(lines),len=trailDivCount)];
        divLineIndexes <- divLineIndexes[-seq(to=length(divLineIndexes),len=trailDivCount)];
    }; ## end if
    ## get fields to extract
    if (missing(select)) {
        allFieldLineIndexes <- grep(perl=T,lines);
        fields <- unique(sub(perl=T,paste0(sepRE,'.*'),'',lines[allFieldLineIndexes]));
    } else {
        fields <- select;
    }; ## end if
    ## extract each field vector and build the data.frame
    do.call(data.frame,c(setNames(lapply(fields,function(field) {
        fieldLineIndexes <- grep(perl=T,paste0('^Q',field,'E',sepRE),lines);
        sectionIndexes <- findInterval(fieldLineIndexes,divLineIndexes); ## 0-based
        values <- sub(perl=T,paste0('^.*?',lines[fieldLineIndexes]);
        if (rtw) values <- sub(perl=T,'s+$',values);
        values[match(seq(0L,length(divLineIndexes)),sectionIndexes)];
    }),fields),...));
}; ## end sectionedFieldLinesToFrame()

以下是如何使用它：

fileName <- 'data.txt';
divRE <- '^-+/-+$';
sepRE <- ':s*';
df <- sectionedFieldLinesToFrame(readLines(fileName),stringsAsFactors=F);
str(df);
## 'data.frame':   2 obs. of  8 variables:
##  $Instrument       : chr  "201301240005447" "201301240005408"
##  $Recorded         : chr  "01/24/2013" "01/24/2013"
##  $Consideration    : chr  "$150,125.00" "$65,124.00"
##  $Document.Type    : chr  "MORTGAGES" "MORTGAGES"
##  $Pages            : chr  "17" "17"
##  $Grantor          : chr  "BYRES,SCOTT" "SANNE,KENNETH D"
##  $Grantee          : chr  "MORTGAGE ELECTRONIC REGISTRATION SYSTEMS INC / QUICKEN LOANS INC" "JPMORGAN CHASE BANK NA"
##  $Legal.Description: chr  "* St:5495 MCNAMARA LN City:FLINT PrpId:1135532002 CC:11 T:8 R:7 S:35 ext:PT OF NE4" "Sub:WOODCROFT NO 1 Lt:188 St:2213 RADCLIFFE AVE City:FLINT PrpId:4024106003 CC:54"

您还可以指定select参数以准确选择要提取的字段：

select <- c('Instrument','Pages','Grantor');
df <- sectionedFieldLinesToFrame(readLines(fileName),stringsAsFactors=F);
df;
##        Instrument Pages                             Grantor
## 1 201301240005447    17      BYRES,SCOTT
## 2 201301240005408    17 SANNE,KENNETH D

我已经尽力使其尽可能健壮.它仔细处理可能的冗余前导和尾随分隔线,并正确处理节之间不一致字段的情况.

值得强调的是最后一点.所提供的所有其他解决方案对输入数据做出了非常脆弱的假设,要么每个部分恰好有8个字段始终以相同的顺序,要么每个部分都出现每个(可能是硬编码的)字段名称.如果违反了这个假设,那些解决方案就变得毫无用处.我的函数不对字段编号,名称或一致性做出任何假设.它动态检索任何部分中存在的所有字段名称,并构建每个字段的正确向量,生成NA元素,其中字段不存在于给定部分中.

这里有些例子：

sectionedFieldLinesToFrame(character(),'^-$',':');
## data frame with 0 columns and 0 rows
sectionedFieldLinesToFrame(rep('-',2L),':');
## data frame with 0 columns and 0 rows
sectionedFieldLinesToFrame(c('A:a','-'),':');
##   A
## 1 a
sectionedFieldLinesToFrame(c('A:a','-','B:b',':');
##      A    B
## 1    a <NA>
## 2 <NA>    b
sectionedFieldLinesToFrame(c('A:a','B:c',':');
##      A B
## 1    a b
## 2 <NA> c
sectionedFieldLinesToFrame(c('A:a','A:d'),':');
##      A    B
## 1    a    b
## 2 <NA>    c
## 3    d <NA>
sectionedFieldLinesToFrame(c('-','A:a','A:d','C:e',':');
##      A    B    C
## 1    a    b <NA>
## 2 <NA>    c <NA>
## 3    d <NA>    e
sectionedFieldLinesToFrame(c('-',':');
##      A    B    C
## 1    a    b <NA>
## 2 <NA> <NA> <NA>
## 3 <NA>    c <NA>
## 4    d <NA>    e

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!