Ecfun (version 0.1-7)

asNumericDF: Coerce to numeric dropping commas and info after a blank

Description

For asNumericChar, delete leading blanks and a leading dollar sign plus commas (thousand separators) and drop information after a blank (other than leadng blanks), then coerce to numeric or to factors, Dates, or POSIXct as desired.

For a data.frame, apply to all columns and drop non-numeric columns except those in ignore, factors, Dates, and POSIXct. Then order the rows by the orderBy column. Some Excel imports include commas as thousand separators; this replaces any commas with char(0), '' before trying to convert to numeric.

Similarly, if "%" is found as the last character in any field, drop the percent sign and divide the resulting numeric conversion by 100 to convert to proportion.

Also, some character data includes footnote references following the year.

Table F-1 from the US Census Bureau needs all three of these numeric conversion features: It needs orderBy, because the most recent year appears first, just the opposite of most other data sets where the most recent year appears last. It has footnote references following a character string indicating the year. And it includes commas as thousand separators.

Usage

asNumericChar(x)
asNumericDF(x, keep=function(x)any(!is.na(x)), 
    orderBy=NA, ignore=NULL, factors=NULL, 
    Dates=NULL, POSIX=NULL, format)

Arguments

x

For asNumericChar, this is a character vector to be converted to numeric after gsub(',', '', x). For asNumericDF, this is a data.frame with all character columns to be converted to numerics.

keep

something to indicate which columns to keep, in addition to columns specified in ignore, factors, Dates, and POSIX.

orderBy

Which columns to order the rows of x[, keep] by. Default is to keep the input order.

ignore

vector identifying columns of x to ignore, i.e., to keep and not attempt to convert to another data type.

factors

vector indicating columns of x to convert to factor

Dates

vector indicating columns of x to convert using as.Date(, format).

POSIX

vector indicating columns of x to convert using as.POSIXct(, format).

format

Character vector of length 1 to pass as argument format to as.Date and / or as.POSIXct for conversion from character.

For Dates, as.Date is first tried with format = '%Y-%m-%d', then with '%Y/%m/%d', '%m-%d-%Y', and '%m/%d/%Y'.

Value

a data.frame

Details

For asNumericChar:

1. Replace commas by nothing

2. strsplit on ' ' and take only the first part, thereby eliminating the footnote references.

3. Replace any blanks with NAs

4. as.numeric

for asNumericDF:

1. Copy x to X.

2. Confirm that ignore, factors, Dates, and POSIX all refer to columns of x and do not overlap.

3. Convert factors, Dates, and POSIX.

4. Apply asNumericChar to all columns not in ignore, factors, Dates, or POSIX.

5. Keep columns specified by keep.

6. return the result.

See Also

scan gsub Quotes stripBlanks as.numeric, factor, as.Date, as.POSIXct

Examples

# NOT RUN {
##
## 1.  an example 
##
xDate <- as.Date('1970-01-01')+c(0, 365)
xPOSIX <- as.POSIXct(xDate)+c(1, 99)
fakeF1 <- data.frame(yr=c('1948', '1947 (1)'),
            q1=c(' 1,234 ', ''), duh=rep(NA, 2), 
            dol=c('$1,234', ''), 
            pct=c('1%', '2%'), 
            xDate=as.character(xDate, format='%m-%d-%Y'), 
            xPOSIX=as.character(xPOSIX), 
            junk=c('this is','junk'))
# This converts the last 3 columns to NAs and drops them:   

str(nF1.1 <- asNumericChar(fakeF1$yr))
str(nF1.2 <- asNumericChar(fakeF1$q1))
str(nF1.3 <- asNumericChar(fakeF1$duh))

nF1 <- asNumericDF(fakeF1)
nF2 <- asNumericDF(fakeF1, Dates='xDate', POSIX='xPOSIX', 
        ignore='junk')

# check 
nF1. <- data.frame(yr=asNumericChar(fakeF1$yr),
                   q1=asNumericChar(fakeF1$q1), 
                   dol=asNumericChar(fakeF1$dol), 
                   pct=c(.01, .02))

nF1c <- data.frame(yr=1948:1947, q1=c(1234, NA), 
                   dol=c(1234, NA), pct=c(.01, .02))

# }
# NOT RUN {
all.equal(nF1, nF1.)
# }
# NOT RUN {
all.equal(nF1., nF1c)
# }
# NOT RUN {
nF2c <- data.frame(yr=1948:1947, q1=c(1234, NA), 
            dol=c(1234, NA), pct=c(.01, .02), 
            xDate=xDate, xPOSIX=xPOSIX, 
            junk=fakeF1$junk)
# }
# NOT RUN {
all.equal(nF2, nF2c)
# }
# NOT RUN {
##
## 2.  orderBy=1:2
##
nF. <- asNumericDF(fakeF1, orderBy=1:2)

# }
# NOT RUN {
all.equal(nF., nF1c[2:1,])
# }