0

I have a CSV that is supplied from a 3rd party every morning. That csv requires processing, and I'm looking for recommendations as to how those familiar with data science / big data management would approach the problem.

I more than likely need to pair two data sources based on a common field - in a database I would use a pair.

Speed is the main goal - though manageable scripting would also be beneficial. Postgres is not winning in the manageable script part in my books. More on that below.

Incoming data: CSV, Approx 12 columns, 11m rows.

Incoming Data Types: Text, Integer, Decimal, DateTime

Calculations: Add as new columns. Add, Subtract, Multiply, Exponential.

Sorting / Group_By: The ability to select the maximum value from sub groups. Eg: For Each GROUP_ID, select the 'row' of data (each row is a 1hr interval) that has the maximum intensity (numeric).

For example:

+----+------------------+-----------+
| ID |     TIMEDATE     | Intensity |
+----+------------------+-----------+
|  1 | 2019/01/01_12:00 |         1 |
|  1 | 2019/01/01_13:00 |         5 |
|  1 | 2019/01/01_14:00 |         3 |
|  1 | 2019/01/01_15:00 |         2 |
|  3 | 2019/01/01_12:00 |        10 |
|  3 | 2019/01/01_13:00 |        50 |
|  3 | 2019/01/01_14:00 |        30 |
|  3 | 2019/01/01_15:00 |        20 |
+----+------------------+-----------+

Can be calculated to:

+----+------------------+-----------+
| ID |     TIMEDATE     | Intensity |
+----+------------------+-----------+
|  1 | 2019/01/01_13:00 |         5 |
|  3 | 2019/01/01_13:00 |        50 |
+----+------------------+-----------+

Calculations are iterative: Column A * Column B = Column C. Column D = Column A + Column C. Column E = Column B ^ Column D.

Outgoing Data: Either to DB Table, or to CSV. Potentially 25 columns or more.

The options: I have available and am loosely familiar with in house are Python/Pandas/Anaconda/etc, PostgreSQL 12, MS MSQL (would prefer not to use if possible). I am, to some extent, able to use other software too.

Tests to date: Using Tableau to run the calculations - slow. Takes 40 mins. Using Postgres 10 to run 1/3 of the calculations - slow. Takes 15 mins. Both these products can handle pairing tables. Postgres does not support using variables in calculating column values for each row of data, meaning I wind up with a rats nest of a script.

Hardware/System: 128gb ram + 44 physical cores available on a single machine. Nvidia Cuda available. Microsoft Windows 10 Enterprise.

  • +1 for successfully measuring the time that processing needs currently. -1 because you don't specify an acceptable time. What are you looking for? Processing in 13 minutes ok? Or 1 minute? Or 5 seconds? How large is the CSV? "Text" can be huge. If it's 200 GB of text, it will definitely be slow, no matter how many cores you have. – Thomas Weller Oct 4 '19 at 6:30
  • Good point @ThomasWeller. 1gb of text. As for processing time, I'm not sure. Overnight I've been playing with Python/Pandas/Dask and am starting to see some success in reducing processing times, and also been able to perform calculated column on calculated column actions. After introducing 2 calculated columns the run time is 8-9 seconds, excluding a final disk write. I'm curious to see how that extends out when there's more columns and I introduce some joins. Sub 1 minute is acceptable. – anakaine Oct 5 '19 at 7:23
  • At 100 MB/s disk speed, that's 10 seconds read time and 10 seconds write time, leaving 40 seconds for the calculations, ok? I have 96 GB RAM, so I could perform some tests. Do you have sample data I could use? – Thomas Weller Oct 5 '19 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.