What's the best way to access a 4GB csv file?
I would like get a 'cut' of this open data set: Full Replacement Monthly NPI File, available here.
Specifically, I want only the rows for hospitals; though, one might want the rows for healthcare clearing houses, or home health agencies, etc.
The unzipped NPI file is 4.8GB, which is too large for my desktop applications. It is an interesting barrier to open data.
Any suggestions?
This is the kind of thing that the csvkit was built for:
csvgrep -c "Healthcare Provider Taxonomy Code_1" -r '^282N' npidata_20050523-20131110.csv > hospitals.csv
csvkit is a suite of utilities for converting to and working with CSV, the king of tabular file formats.
A little more efficiently, you could do:
zcat NPPES_Data_Dissemination_Nov_2013.zip | grep 282N | csvgrep -c 48 -r '^282N' > hospitals.csv
On Windows, SweetScape 010 Editor is the best application I am aware of to open/edit large files (easily up to 25 GB). It took around 10 seconds on my computer to open your 4 GB file (SSD):
More such tools: Text editor to open big (giant, huge, large) text files
As you're only taking a portion of the file, you may be able to use simple tools to subset it before processing. That may get it down to a reasonable size to work with.
If you're working on a posix (ie, unix-like) system, you can use shell commands to reduce the file:
zcat -cfilename| grep(pattern to match hospitals only)>outputFile
This lets you extract the lines without uncompressing the larger file on disk. (with today's systems, drive speeds are often a limiting factor, so working with the data compressed can be more efficient if you have sufficient CPU).
If it were tab-deliminated, pipe-delimited, or fixed-width, you can also reduce the columns with the unix cut command ... but it's hit-or-miss with CSV, as it'll break horribly if there are commas in strings:
zcat -c(filename)| grep(pattern to match hospitals only) | cut -d, -f(list of fields) >outputFile
you can connect to the file with sql and run your analysis from there.
i have written extremely detailed r code (r is free and open source) about how to work with the nppes from your laptop here:
http://asdfree.com/national-plan-and-provider-enumeration-system-nppes.html
if you have never used r before, check out http://twotorials.com for a crash course
hope this helps :)
Others have mentioned way to pull apart this file incrementally. It seems to me like you are also commenting on use of resources for a large file. For some solutions you can incrementally read the compressed file uncompressing as you go and feed it through the csv module. For example in python with gzip'd input you would do this by:
import csv
import gzip
with gzip.open("test.csv.gz", "r") as f:
reader = csv.reader(f)
for row in reader:
pass # do something with the row
You could do the same thing for zip archives with zipfile.
If this data is coming from across a network you can pay very little or sometimes get performance improvements by not copying and then uncompressing it. It can cost you less to pull it across the network compressed (smaller) in pieces and operate on it then it does to pull it across in bulk, uncompress it, and then read it. It also doesn't use up local storage resources.
There are streaming CSV parsers, that only look at a small window of the file at a time.
Node is a particularly stream-friendly language and ecology, so here a few Node streaming CSV parsers:
https://github.com/voodootikigod/node-csv
https://github.com/koles/ya-csv
https://github.com/lbdremy/node-csv-stream
On Windows, there is also a software called Delimit ("Open data files up to 2 billion rows and 2 million columns large!") http://delimitware.com For instance it can split, sort and extract only some rows or columns.
Load the file into PostgreSQL database table with a Copy statement. This will give you the full capabilities of SQL syntax, plus the ability to index columns for faster access.
For complex queries you have a optimizer that a can figure out the fastest way to access the data. PostgreSQL has smarter I/O than most applications it will detect sequential read access and read-ahead to pre-load data into memory.
Results can be viewed in Excel or other spreadsheets by accessing the data via an ODBC driver.
You can access to PostgreSQL via several Cloud Services like Heroku or AWS. It would be good cloud project to create a new machine instance, install PostgreSQL and copy the data to the instance. Then use the Postgres COPY command to load the data and then access the data with a PostgreSQL client application from your desktop.
This answer is not really useful for non-programmers, but if could manage some programming in perl, the Parse::CSV module is especially designed for this task.
From the doc:
It provides a flexible and light-weight streaming parser for large, extremely large, or arbitrarily large CSV files.
Perl is usually very good for data mining tasks.
I have used utilities such as (g)awk to readlarge file such as this record by record. I the extract the required information from each line and write it to an output file. For windows users (g)awk is available in cygwin. I have also used python to achieve the same result. You could implement this process in most programming languages.
Well, in short, Talend Open Studio for Data Integration is an ETL.
It can be used for many use cases, including data migration, files processing, etc. You can easily build jobs using a visual editor to combine specialized connectors (read CSV files, select rows corresponding to your criteria, write result to one or more files or directly to a database, and more). The possibilities are endless because there are more than 800 connectors.
At the end, TOS generates a java application which can be launched from the designer or for the command line (Windows or Unix).
Hope this helps.
If you're on Windows, I can't sing the praises of LogParser high enough. It allows you to query files in a wide variety of formats (mostly log formats as that's what it was meant for, but XML and CSV are valid). You query the file with a surprisingly complete SQL syntax, and you can even use it to import an entire file directly into an SQL database very easily.
It's extremely fast, too. I've used it to process 5 GB of IIS log files and a query as complex as SELECT DISTINCT cs-username, cs(User-Agent) FROM .\*.log where cs-username is not null took about 2 minutes to complete running on my laptop.
If you are working with Python, there are a couple of options:
Something simple like
with open('in_filepath', 'r') as inf, open('out_filepath', 'w') as outf:
for line in inf:
# do some processing, e.g, .split(',')
if criterion: # check if you want this row
outf.write(line)
would work, since it reads the file iteratively, not all at once.
Alternatively, there is a csv module in the standard library of Python that works quite similarly:
import csv
with open('my.csv', 'r') as inf, , open('out_filepath', 'w') as outf:
csvreader = csv.reader(inf, delimiter=',')
for row in csvreader:
...
There is also a new nice Python library called blaze, which is has been designed to work with a large number of large CSV files. Here is a nice tutorial about how to use it for this purpose: http://nbviewer.ipython.org/url/blaze.pydata.org/notebooks/timings-csv.ipynb
I recently had to parse the 6GB NPPES file and here is how I did it:
$ wget http://download.cms.gov/nppes/NPPES_Data_Dissemination_July_2017.zip
$ unzip NPPES_Data_Dissemination_July_2017.zip
$ split -l 1000000 npidata_20050523-20170709.csv
$ add headers...
$ python parse.py
$ load *.tab files to the database
The code for the parse.py script used to extract some columns:
import os
import pandas as pd
import csv
files = [
'xaa.csv',
'xab.csv',
'xac.csv',
'xad.csv',
'xae.csv',
'xaf.csv']
usecols = [
"NPI",
"Provider Organization Name (Legal Business Name)",
"Provider Business Mailing Address City Name",
"Provider Business Mailing Address State Name",
"Provider Business Mailing Address Postal Code",
"Provider Business Mailing Address Country Code (If outside U.S.)"]
for f in files:
print("Parsing file: {}".format(f))
df = pd.read_csv(f,
engine='c',
dtype='object',
skipinitialspace=True,
quoting=csv.QUOTE_ALL,
usecols=usecols,
nrows=None)
df.rename(columns={
'NPI': 'npi',
'Provider Organization Name (Legal Business Name)': 'business',
'Provider Business Mailing Address City Name': 'adr_city',
'Provider Business Mailing Address State Name': 'adr_state',
'Provider Business Mailing Address Postal Code': 'adr_zip',
'Provider Business Mailing Address Country Code (If outside U.S.)': 'adr_country', # noqa
}, inplace=True)
out = os.path.join('out', "{}.tab".format(f))
df.to_csv(out, sep='\t', index=False)
I am a big fan of tad which crunches these kind of files easily.
Assuming that you can uncompress the online archive, your best approach might be to:
Depending on the OS you are using, there are a series of open source tools available to split / join large files or tools already installed.
MS Windows: you will have to install a tool or use a script (e.g python) to do the splitting. If you search Google for "splitting csv files" you will find quite a selection.
Alternatively you could also use a live CD/USB stick with e.g. Ubuntu on it (see the Ubuntu web site for how to create one) and then use the approach below.
Linux / Unix:
Use the split command:
e.g. split -d -l 10000 source.csv tempfile.part.
this will split source.csv into files containing 10'000 lines each and named tempfile.part.00, tempfile.part.01 ...
You can use the join command to join the multiple output files from your selections together into one csv file (either by naming the files and piping them into one file or by joining all files within a folder into one output file - please check the join manual pages or online how to do this in detail).
You can try nitroproc. It's currently a beta version I think, but allows to sort/summarize/filter/etc. It's free.
I've had good luck in R (without resorting to fancy packages) of reading CSVs up to and beyond that size. The nice thing about getting it in RAM, if you can swing it, is very fast manipulation.
If you only want to be able to read / query the data, you could use:
csvs-to-sqlite to convert import the CSV file into a SQLite database. You can do this as a mapping into a single database table (for example: csvs-to-sqlite myfile.csv mydatabase.db), or you can use the csvs-to-sqlite tool to split the data into separate tables automatically linked by foreign key relationships;datasette. For example, datasette serve -p 8899 mydatabase.db to serve the datasette browser on localhost port 8899.As an added benefit, the datasette service also provides an API that allows you to run arbitrary SQL queries on the data via a simple URL and retrieve the data as JSON.
A query can also be used to export just the data you want as a smaller CSV file (datasette - CSV export).
If you want to do some processing on a large csv file, the best option is to read the file as chunks, process them one by one, and save the output to disk (using pandas for example).
If you want to explore the file and you are looking for free tools, you can use Power Query add-in for Excel or glogg log explorer.
Gigasheet, which is relatively new, now allows you to upload and work with a 1 billion row CSV file in a browser-based, excel-like interface. The full details are here: https://www.gigasheet.co/post/open-large-csv-file, and nice video showing the interaction is here: https://www.youtube.com/watch?v=g0ZC73oi-WM.
It's pretty straight-forward. You'll need to create a (free) account at gigasheet.co, after which you can upload your file and interact with it immediately.
Disclosure: Since posting this answer, I joined the Gigasheet team
