2
$\begingroup$

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"]
memory=ReadList[str, Record,500];

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!

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

2 Answers 2

2
$\begingroup$

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"]
mem = ReadList[str, Record, 5];

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]

enter image description here

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]]

enter image description here

$\endgroup$
3
  • $\begingroup$ 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! $\endgroup$
    – noob3000
    Feb 28, 2021 at 17:31
  • $\begingroup$ Should work with DateObject too (at least in version 12.1. I made an add in my answer. $\endgroup$ Feb 28, 2021 at 19:47
  • $\begingroup$ Cool, thanks a lot! $\endgroup$
    – noob3000
    Feb 28, 2021 at 20:35
1
$\begingroup$

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:

enter image description here

Using "Dataset" form instead of "Rows":

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

Output:

enter image description here

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

$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – noob3000
    Feb 28, 2021 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.