0
$\begingroup$

I'm completely new to data science and I have a problem that I need help in resolving. I have time series data (with 87 million rows currently, though that will grow) with x, y coordinates, a timestamp (using date_trunc('hour') to enable better comparisons), and the value, stored in a Postgresql table. The analyses I need to perform on this data (find the value with the closest timestamp for one or more x,y coordinates, average the values in different ways, etc.) do not perform in Postgres at the speed I need (ideally sub 5 second response time). So I'm investigating using a multidimensional Numpy array. My problem is multiple: first, that I have no idea if this is a good idea, but I'm willing to try it and find out. More importantly, and the specific issue I need help with, is how to convert the data from the 2D tabular Postgres format to a 3D numpy array.

The data looks similar to this:

x_id    y_id    approx_time           value
 4       26     2022-10-14 08:00:00    0.01
 4       26     2022-09-03 08:00:00    0.02
...

Any suggestions on how to convert this to an array that will allow me to perform the analyses I need to perform, and secondarily, any suggestions on better paths forward if an array won't get me where I need to be? Thanks in advance

$\endgroup$

1 Answer 1

0
$\begingroup$

You could export the data from the database (for example to a csv file) and load that into memory using numpy (or pandas, which may be easier as it gives you extra functions and allows you to refer to the columns using the column names instead of indices). I am not sure how much memory you have available but 87 millions rows might be than what can fit into memory at once. In this case you could use chunking, or try to optimize the performance of your SQL query (which might be a good first step nevertheless depending on the exact logic you're looking for). Other python libraries that might be interesting to look at when working with large datasets are polars and dask.

$\endgroup$
4
  • $\begingroup$ Thanks, but that loads it into a 2D format, no? pd.DataFrame is a 2D array, is it not? How do I get it into a 3D format? $\endgroup$
    – Shawn
    Oct 19, 2022 at 15:34
  • $\begingroup$ Based on the snippet you provided the data seems to be 2D, is there a specific reason you want to have the data in a 3D format? You can add an extra dimension to a numpy array by using numpy.expand_dims. $\endgroup$
    – Oxbowerce
    Oct 19, 2022 at 15:46
  • $\begingroup$ I'm not sure, but I need to find all the cells of the matrix that match the third dimension, approx_time. In other words, I need to query all or a subset of x_id and y_id, but only the cells that match the approx_time. 2D doesn't perform, with a fully optimized query in Postgres, using indices, etc.; I'm looking for alternative implementations that might. $\endgroup$
    – Shawn
    Oct 19, 2022 at 18:21
  • $\begingroup$ If you are experimenting for yourself try R data.table. For production there are better solutions, for example, column-oriented DBMSes, of which there are many. Even Postgres might work if it is set up properly. $\endgroup$
    – Valentas
    Jun 11, 2023 at 15:16

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.