0

I have a df in R that I have loaded using:

data <- fread("Data/LuminateDataExport_UTDP2_011818.csv", colClasses = 'character', stringsAsFactors = FALSE)

I did this because I had to perform certain operations like stripping "$", etc.

Now, I am trying to convert the columns into the appropriate formats without having to as._ each column individually...

The structure of the current df is:

> str(data)
Classes ‘data.table’ and 'data.frame':  196879 obs. of  32 variables:
 $ city             : chr  "" "" "" "" ...
 $ company_goal     : chr  "" "" "" "" ...
 $ company_name     : chr  "" "" "" "" ...
 $ event_date       : chr  "5/14/2016" "9/26/2015" "9/12/2015" "6/3/2017" ...
 $ event_year       : chr  "FY 2016" "FY 2016" "FY 2016" "FY 2017" ...
 $ fundraising_goal : chr  "250" "200" "350" "0" ...
 $ name             : chr  "Heart Walk 2015-2016 St. Louis MO" "Heart Walk 2015-2016 Canton, OH" "Heart Walk 2015-2016 Dallas, TX" "FDA HW 2016-2017 Albany, NY WO-65355" ...
 $ participant_id   : chr  "2323216" "2273391" "2419569" "4088558" ...
 $ state            : chr  "" "OH" "TX" "" ...
 $ street           : chr  "" "" "" "" ...
 $ team_average     : chr  "176" "123" "306" "47" ...
 $ team_captain     : chr  "No" "No" "Yes" "No" ...
 $ team_count       : chr  "7" "6" "4" "46" ...
 $ team_id          : chr  "152788" "127127" "45273" "179207" ...
 $ team_member_goal : chr  "0" "0" "0" "0" ...
 $ team_name        : chr  "Team Clayton" "Cardiac Crusaders" "BIS - Team Myers" "Independent Walkers" ...
 $ team_total_gifts : chr  "1,230 " "738" "1,225 " "2,145 " ...
 $ zip              : chr  "" "" "" "" ...
 $ gifts_count      : chr  "2" "1" "2" "1" ...
 $ registration_gift: chr  "No" "No" "No" "No" ...
 $ participant_gifts: chr  "236" "218" "225" "0" ...
 $ personal_gift    : chr  "0" "0" "0" "250" ...
 $ total_gifts      : chr  "236" "218" "225" "250" ...
 $ match_code       : chr  "UX000" "UX000" "UX000" "UX000" ...
 $ tap_level        : chr  "X" "X" "X" "X" ...
 $ tap_desc         : chr  "" "" "" "" ...
 $ tap_lifed        : chr  "" "" "" "" ...
 $ medage_cy        : chr  "0" "0" "0" "0" ...
 $ divindx_cy       : chr  "0" "0" "0" "0" ...
 $ medhinc_cy       : chr  "0" "0" "0" "0" ...
 $ meddi_cy         : chr  "0" "0" "0" "0" ...
 $ mednw_cy         : chr  "0" "0" "0" "0" ...
 - attr(*, ".internal.selfref")=<externalptr>

Now, as a first step- I am trying to convert all of the numbers to_numeric.

I have tried every one of the solutions found here but none of them have worked.

The errors I keep getting are:

Error in [.data.table(data, , cols) : j (the 2nd argument inside [...]) is a single symbol but column name 'cols' is not found. Perhaps you intended DT[,..cols] or DT[,cols,with=FALSE]. This difference to data.frame is deliberate and explained in FAQ 1.1.

AND

Error in [.data.table(data, cols) : When i is a data.table (or character vector), the columns to join by must be specified either using 'on=' argument (see ?data.table) or by keying x (i.e. sorted, and, marked as sorted, see ?setkey). Keyed joins might have further speed benefits on very large data due to x being sorted in RAM.

Here some more info on the data:

> dput(data[1:6, 1:11])
structure(list(city = c("", "", "", "", "", ""), company_goal = c("", 
"", "", "", "", ""), company_name = c("", "", "", "", "", ""), 
    event_date = c("5/14/2016", "9/26/2015", "9/12/2015", "6/3/2017", 
    "5/6/2017", "10/17/2015"), event_year = c("FY 2016", "FY 2016", 
    "FY 2016", "FY 2017", "FY 2017", "FY 2016"), fundraising_goal = c("250", 
    "200", "350", "0", "0", "100"), name = c("Heart Walk 2015-2016 St. Louis MO", 
    "Heart Walk 2015-2016 Canton, OH", "Heart Walk 2015-2016 Dallas, TX", 
    "FDA HW 2016-2017 Albany, NY WO-65355", "FDA HW 2016-2017 New Haven, CT WO-66497", 
    "Heart Walk 2015-2016 Puget Sound, WA"), participant_id = c("2323216", 
    "2273391", "2419569", "4088558", "4527010", "2424207"), state = c("", 
    "OH", "TX", "", "", "WA"), street = c("", "", "", "", "", 
    ""), team_average = c("176", "123", "306", "47", "0", "97"
    )), .Names = c("city", "company_goal", "company_name", "event_date", 
"event_year", "fundraising_goal", "name", "participant_id", "state", 
"street", "team_average"), class = c("data.table", "data.frame"
), row.names = c(NA, -6L), .internal.selfref = <pointer: 0x10200c378>)

Suggestions please?

(Once I do this, I will also have to convert different columns to factors, etc)

zsad512
  • 861
  • 3
  • 15
  • 41
  • 1
    Please (a) share a small, reproducible example. Something copy/pasteable, like `dput(data[1:6, 1:11])`. Then also please show some of your attempts. Are you hoping to auto-detect classes? Are you hand-picking the numeric columns? By name or index? If so, can you share that as well so we don't have to guess at it? – Gregor Thomas Jan 25 '18 at 17:12
  • 1
    The errors you see are probably fixable, but it's hard to help when you don't share the code that produced them. – Gregor Thomas Jan 25 '18 at 17:45
  • @Gregor I have added the information you requested...I am looking for a general method of manually classifying columns...auto-detection will not work- the point is to classify multiple columns in one call instead of doing each manually. I can pick the columns numerically or by name- it doesnt really matter. – zsad512 Jan 26 '18 at 21:10
  • I see the data. I still don't see (a) the code you used that's generating the errors you get, or (b) anything that tells me (or could tell R) which columns you want to be numeric. I'm also not sure why you think auto-detection won't work - are you worried R will do too much of your job? – Gregor Thomas Jan 26 '18 at 21:24
  • @Gregor- auto detection wont work because of special characters, thats why i had to read the data in as characters...as for which solutions have i tried, i used every single one of the solutions provided in the link provided in the question...they all produce the same error – zsad512 Jan 26 '18 at 21:26

1 Answers1

3

I realize this is an older question that you're probably not working on anymore, but since it's one of the first questions that comes up when people search for simultaneously formatting multiple columns as numeric in R, I thought I'd add a thought.

Regarding the first part of your question--how to identify which columns are numeric, which columns are dates, which columns are factors, etc.--I do not have a good answer, particularly because factors can originally be character or otherwise, but then be designated as factor instead. Deciding which ones to convert is largely up to the designer. If there are no entries that are acceptably NA, you could use the logic here to determine which columns should be formatted numeric. Once you have decided which columns to convert . . .

I'm guessing that your second error appears because you're using data tables slightly differently than the current syntax requires. You can find instructions for changing a selection of columns using data table syntax in one of the later answers on this post:

Coerce multiple columns to factors at once

In that post, they coerce a set of columns to factor; the same process works for coercing to numeric.

To keep it simple, you specify the columns you want (using numeric values or column names or otherwise--In your case, this value will be programmatically assigned using whatever logic and rules you apply to divide your data into groups). E.g.,

colsToConvert <- c(6,11,13)

or

colsToConvert <- c("fundraising_goal","team_average","team_count")

Then you use an lapply command and use the SDcols subset specification:

data[, (colsToConvert) := lapply(.SD, as.numeric), .SDcols = colsToConvert]

That should do your conversion. Repeat this process for as many data types as you prefer, changing the formatting from as.numeric to whichever type is appropriate.

Bradley Mecham
  • 41
  • 5