61
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....
26
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
22
I'd suggest using JSON Table Schema: http://www.dataprotocols.org/en/latest/json-table-schema.html
It's:
JSON-based
Super simple
Extendable
Here's a rough outline:
# fields is an ordered list of field descriptors
# one for each field (column) in the data
"fields": [
# a field-descriptor
{
"id": "field unique name / id",
"label": ...
17
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 ...
14
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
...
13
If you already have a nicely formatted CSV then why not publish that? You can publish in both formats if you really want to do the RDFa too.
12
While definitely not yet an established practice, another proposal to consider might be Jeni Tennison's Linked CSV (see examples), which adds "prolog lines" to describe the schema used in CSV files.
11
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 ...
10
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
9
The W3C offers a collection of tools that convert from CSV to RDF. However, there is no explicit mention of RDFa in any of these CSV converters. Personally, I'd give the RDF Refine plugin for OpenRefine a try.
Once you have your open data in an RDF format, you could use the RDF Translator to turn it into RDFa.
9
Well my comment received a number of up votes which I take as a signal of quality and I am posting the links here so they are more visible to future visitors:
opendata.socrata.com - you can upload a number of different file types here, create visualizations, link to them, and take advantage of a very mature set of APIs for data consumption and publishing
...
8
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.
8
Turns out there is a function daylength in the geosphere package in R that calculates day length for any latitude and date.
7
While SKOS certainly might be the best way to represent this information it does require more effort than I believe most will be willing to provide. Couldn't we start with a simple, practical, form for providing a data dictionary with another CSV file with 4 mandatory columns. DatasetName, FieldName, FieldValue or Code, ValueDefinition and an optional ...
7
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 ...
6
You might want to consider maintaining your cleanups as a set of operations or diffs which get applied to the source data. This would help isolate you from changes to the source and allow you to reapply them to a new dataset.
OpenRefine maintains a history of operations, but you could do something similar with a set of version controlled scripts in your ...
6
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.
6
There is not a lot of good nation-wide data on LGBT topics. Here are a few I was able to find for you. Some of them are already in CSV/TSV and/or Excel format (and therefore trivial to convert to CSV) while some are PDF reports with tables embedded (which, given some effort, could be converted to CSV/TSV tables):
XLS Census/ACS data on same-sex couples: ...
5
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.
5
I wouldn't dismiss XML so lightly. In the first place, given the natural vagaries of data transmission (especially when considered world-wide), information should be put in XML to simplify error-catching -- the start- and end-point of every datum is unambiguously identified. In the second place, if your XML is self-documenting, as the XML spec intends, it ...
5
The National Information Exchange Model (NIEM) is an XML-based system for defining "data in motion" (i.e., an on-the-wire format.) What distinguishes NIEM is 2 things: it is for standardizing the semantics of the exchange, not just the syntax; and it as much a process model as it is a technical model. That is, it (the NIEM organization) has developed a ...
5
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 ...
5
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 ...
4
If your CSV has newlines inside 'cells' then this will screw-up command-line diff. You'd be better off parsing it in python to either remove them, or to feed into the python diff.
Hashing each line will tell you track a row that stays the same. It is no good for keeping track of a row that changes a bit. Maybe you don't need to do this. Row IDs have been ...
4
Git will work reasonably well for CSVs, given that they are just flat files with text.
A slightly more robust workflow might look something like this:
For each row in in the CSV (call it SOURCE_DATA), hash the entire line and store the hash value in a separate table (call it HASHES)
Do your data processing on the raw input data, and store the output in a ...
4
I have recently started using the Linked CSV proposed standard for generating CSV files from plural data sources. Below is a vocabulary definition for the columns/data types I am using. Perhaps others will find this useful/interesting approach:
http://www.opengeocode.org/cude1.1/LinkedCSV-Vocab.php
Update: the above link throws a 404, however it is ...
4
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 ...
4
I am a big fan of tad which crunches these kind of files easily.
4
A hopeful candidate is Dat, which aspires to become what Git (and GitHub) is for code. With this in mind, searching for "git for data" yields a lot of interesting results. In that context, the article We Don't Need a GitHub for Data is an good read which stresses the thought that it's (almost) never data that needs versioning, but data transformations - ...
Only top voted, non community-wiki answers of a minimum length are eligible
