# Converting Strings to Reals in a List

I am new to Mathematica and working with streamed data:

 str = OpenRead["https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-03.csv"]


I'd now like to convert the records from the streamed file into a new list, by converting all numbers to reals. It seems like the default storage method of the data is string for any entry. Correct? In particular, I would need to convert the following variables to reals: Vendor ID, passenger_count, trip_distance, RatecodeID, PULocationID, DOLocationID, payment_type, fare_amount, extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge, total_amount and congestion_surcharge.

Unfortunately, I am struggling to do this - any help would be greatly appreciated!

• Your dataset is 700MB, much too big for tests. Please make a small test data set. Commented Feb 28, 2021 at 9:38
• Hi Daniel, thanks for the hint. I have updated my question now Commented Feb 28, 2021 at 15:50

For a test, I only read the first 5 records:

str = OpenRead[
"https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-03.csv"]


Now we extract the header and the data and split them into the corresponding table elements:

header = StringSplit[mem[[1]], ","]
dat = StringSplit[Rest[mem], ","];


The rows of dat consists of strings that represent a mixture of numbers and strings. To transform the strings representing numbers we need to to pick out the corresponding columns:

(dat[[All, #]] = ToExpression[dat[[All, #]]]) & /@ {1, 4 ;; 6, 8 ;;}


To get the final table we prepend the header to the data:

PrependTo[dat, header]


If in addition you want also to change the date strings into numeric lists, you may say:

dat[[All, 2]] = DateList /@ dat[[All, 2]]
dat[[All, 3]] = DateList /@ dat[[All, 3]]


Or with DateObject:

  dat[[All, 2]] = DateObject /@ dat[[All, 2]]


• Thank you for your help! One last question: Instead of a DateList object, I would need to convert my date strings into a DateObject. Simply exchanging DateList with DateObject /@dat[[All, 3]] in your code yields an error message. Any hint on a different approach? Thanks again! Commented Feb 28, 2021 at 17:31
• Should work with DateObject too (at least in version 12.1. I made an add in my answer. Commented Feb 28, 2021 at 19:47
• Cool, thanks a lot! Commented Feb 28, 2021 at 20:35

The third argument according to documentation is for:

reads only the first n objects of the specified types.

This code will run on 3 record (first row is column):

records = ReadList[str, Record, 3];


the output is a list of strings, use SemanticImportString to convert strings to their data types (Reals, ...).SemanticImportString needs a string with \n as the record separator.

Use StringRiffle to concatenate records with \n in between.

SemanticImportString[#, Automatic, "Rows", Delimiters -> ","] &@
StringJoin@StringRiffle[records, "\n"]


Output:

Using "Dataset" form instead of "Rows":

SemanticImportString[#, Automatic, "Dataset", Delimiters -> ","] &@
StringJoin@StringRiffle[records, "\n"]


Output:

The output is a list of records with different types (String, Reals, ...). See SemanticImport documentation to read more about other output forms.

• Hey, thanks for the response! I have just checked and it seems that all elements of the list are strings by default already, correct? How would I now go about converting all the list elements that are numbers to reals? These would be the following columns: Vendor ID, passenger_count, trip_distance, RatecodeID, PULocationID, DOLocationID, payment_type, fare_amount, extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge, total_amount and congestion_surcharge. Commented Feb 28, 2021 at 16:10