Do not load all your data into RAM
Pandas loads entire datasets into RAM at once. This is why Python crashes when you try to open a 20GB CSV file on a laptop with 16GB of RAM. Even with slightly larger datasets, your laptop freezes and the Jupyter Notebook kernel dies. You cannot keep sampling data or upgrading your hardware every time.
Instead, use DuckDB. It loads only the data it needs, piece by piece. It doesn't load the entire dataset into RAM; it scans only the required parts using SQL syntax. This is why it is gaining attention among data engineers as a powerful alternative to Pandas.
Transforming Pandas code in 3 steps
Changing your data analysis environment is simpler than you think. Keep your existing Pandas DataFrames and just swap out the engine.
- Data Preparation: Instead of reading files with Pandas, point DuckDB to the file path.
- Execute Queries: Write your required conditions and aggregations using the
duckdb.sql()function. No actual computation happens yet. - Extract Results: The actual calculation only runs when you call
.df()at the end.
This approach provides efficiency beyond just changing code. Operations that would crash your RAM when using groupby in Pandas run stably in DuckDB by utilizing the disk.
Preventing analysis crashes with a 4GB limit
When dealing with large-scale data, you will inevitably hit memory limits and cause programs to crash. To prevent this, you should install guardrails in your working environment.
Add the following code to the top of your script:
`python
import duckdb
con = duckdb.connect()
con.execute("SET memory_limit = '4GB'")
con.execute("SET temp_directory = './tmp'")
`
memory_limit defines how much RAM DuckDB is allowed to use. By setting it to 4GB, even on my 16GB laptop, the entire system won't freeze. If RAM runs low, DuckDB automatically writes data to temporary disk files. According to a 2023 benchmark, DuckDB processed a 140GB Parquet file using only 1.3GB of RAM—a task that would have consumed dozens of gigabytes of RAM and eventually crashed in Pandas.
Handling erroneous data
One of the most frustrating moments in data analysis is when parsing stops due to incorrect formatting. If types don't match when loading a CSV, your analysis cannot even begin.
Turn on these options when using the read_csv function:
`python
con.execute("""
SELECT * FROM read_csv('data.csv',
columns={'id': 'INTEGER', 'value': 'DOUBLE'},
store_rejects=true,
strict_mode=false)
""")
`
By doing this, the entire process won't die because of malformed data. The incorrect rows are isolated into a separate table. You can simply query the reject_errors table later to investigate why the data was corrupted.
Data analysis isn't about fixing tools; it's about extracting insights from data. Stop running your code dozens of times due to RAM issues, and secure your processing efficiency by swapping the engine first.
