How to Handle Large Datasets
You double-click a data file and Excel spins, freezes, or tells you it cannot open it. Real-world datasets are often far bigger than a spreadsheet can hold, and every beginner hits this wall the first time they grab a serious dataset. This guide is what to do next. It covers how to recognize a "large" dataset, and why you move it into a database. It then lists the handful of real-world quirks…
When working with large datasets that exceed a spreadsheet's capabilities, it's crucial to recognize when a dataset is too big for Excel or Google Sheets. A dataset is considered large when it contains more than about a million rows or when opening the file causes the program to crash or become unresponsive. For example, a dataset of Steam game reviews, recommendations.csv, is about 2 GB in size and contains 41 million rows, which Excel cannot handle.
To manage large datasets effectively, follow these steps:
1. Move the large dataset into a database, preferably SQLite, which has no practical row limit. Import the CSV file into the database and verify the import. This step is similar to the process described in the SQL database guide, but it may take longer due to the file size.
2. Expect the dataset to be downloaded as a zipped file containing several files. In the case of the Steam game reviews dataset, the zip file contained four files, but only one was needed. Extract the zip file and open the relevant folder, then import only the file you require, in this case, recommendations.csv, ignoring the rest.
3. Importing a large CSV file is a slow process and will take several minutes. The database file will also grow in size, potentially reaching several gigabytes. Make sure you have enough disk space before starting the import, as the original CSV and the resulting database can total 4 to 6 GB.
4. Build an index on the column you will use most frequently for joining or filtering. An index acts as a lookup shortcut, allowing the database to find specific rows without scanning through all 41 million rows. For the Steam game reviews dataset, an index was created on the app_id column to speed up searches and joins. Building this index took about 3 minutes, after which joins that would have taken a long time ran instantly.
5. When writing queries against large datasets, write them against a small sample first to avoid waiting for the database to process the entire 41 million rows. Use the LIMIT clause to return a smaller set of rows while you test and refine your query. Once the query is correct on the sample, remove the LIMIT and run it on the entire dataset.
6. Aggregate data early to avoid displaying millions of raw rows. Instead of displaying all the raw data, use GROUP BY to summarize and aggregate the data as needed. This approach ensures that you only retrieve what is necessary, improving performance and usability.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — it may contain errors, so check the original before relying on it.
