从一个可恶的当地政府网站下载了一堆数据.有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会更好地工作,只需要弄清楚如何.谢谢
解决方法
我编写了一个非常通用的解析函数,可以处理分隔线和字段值分隔符的任何模式,指定为参数化正则表达式.它还可以选择从字段值中删除尾随空格,并将可变参数传递给构建结果data.frame的单个data.frame()调用.
- 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参数以准确选择要提取的字段:
我已经尽力使其尽可能健壮.它仔细处理可能的冗余前导和尾随分隔线,并正确处理节之间不一致字段的情况.
值得强调的是最后一点.所提供的所有其他解决方案对输入数据做出了非常脆弱的假设,要么每个部分恰好有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