4
$\begingroup$

I have data in 2 tables in Sql server. First table has around 10 million rows and 8 columns. Second table has 6 million rows and 60 columns.

I want to import those tables into a Python notebook using pandas ( I am importing in "chunksize") and then merge them and then run analysis on resulting table.

I am unable to import the data due to possible hardware constraints. System hardware configuration is as follows:

RAM: 4GB
Storage: 160 GB, 
CPU : Dual core CPU. 

Even if the import goes through (which seems difficult), my resulting table after the merge will have 5 million rows and 40 columns.

Is it feasible to perform analysis/visualization in python notebook using pandas, seaborn on columns on the resulting table?

Would love to understand:

What should be my next steps to solve the problem?

Is installation of Anaconda on Windows 2012 server feasible? (This server has more memory)

$\endgroup$
1
$\begingroup$

Pandas load everything into memory before it starts working and that is why your code is failing as you are running out of memory. One way to deal with this issue is to scale your system i.e. have more RAM but this is not a good solution as this method will eventually fail to scale.

Other option will be to use big data libraries like spark or flink for this on a distributed system. You can try the installation on windows sever but that also wont scale well for you.

$\endgroup$
5
  • $\begingroup$ Thanks Rajat. If i have good configuration of Server, then can try? Also if i create resulting combined table in Sql, then i just will import it in pandas. But still it will be a table of 6 million rows. So, can i analyse/visualize it in pandas? Or i can aggregate few rows into summary rows like sum, average n reduce rows size to less as .1 million, n then run analysis/visualization on that? Kindly suggest. $\endgroup$ May 31 '19 at 18:22
  • $\begingroup$ I am not sure what will be to config of your server, but if you can load data into memory then it will work. Pandas is not distributed and will always store data locally. SQL wont help here because in the end you are importing in Pandas. If data inside a file is independent from other files, then you can generate separate stats for every file and combine them to plot information. Another parallel library will be Dask, which uses pandas like interface. $\endgroup$
    – secretive
    May 31 '19 at 18:58
  • $\begingroup$ Store data into memory. So, here memory means local hard disk memory you are telling right? So, if i get those tables available on my PC hard disk. Then do you mean, i should import them into my python notebook from hard disk n run analysis on it? I don't know, what happens behind the scene, when pandas import records or when it processes records? Where it keeps records? What is the role of RAM here n storage(hard drive) here. I am new to this,so please bear with me. Any good link, you can suggest to understand, what i mentioned above, will be greatly appreciated. Thanks a lot for giving time. $\endgroup$ May 31 '19 at 19:51
  • $\begingroup$ In programming, memory means RAM. Your external storage / hard disk doesnt matter. $\endgroup$
    – secretive
    May 31 '19 at 19:54
  • $\begingroup$ Thanks for explaining $\endgroup$ May 31 '19 at 20:14
0
$\begingroup$

Use Pandas chunksize option to load. And also you can use Dask, Koalas with Numba and ray for parallel computation.

$\endgroup$
1
  • $\begingroup$ Even if you use chunksize, the data will still be loaded into memory and you cannot performs stats on chunks if data is interdependent or it will be complicated. $\endgroup$
    – secretive
    Jun 1 '19 at 15:10

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