59
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....
54
Here is a guide for each file format from the Open data handbook and a suggestion with a Python library to use.
JSON is a simple file format that is very easy for any programming language to read. Its simplicity means that it is generally easier for computers to process than others, such as XML. Working with JSON in Python is almost the same such as working ...
27
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
OpenRefine can be used to parse semi-structured data into a table like structure, where it can be operated on in a manner similar to a spread sheet and exported.
The site features a tutorial on converting a list on wikipedia into a table which may be a good starting point.
The operations involved can also be exported incase you need to perform the same ...
17
APIs are often offered by websites so that developers can use the web-based data for apps, without having the uncertainty and difficulty of scraping the HTML. But it's not necessary to use the data to build apps, and this means that APIs can be a great source of data for research and analysis. Just to name a few types of API data: weather forecasts, ...
16
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 ...
15
Python is the tool I would use.
import csv
from itertools import izip_longest
def grouper(iterable, n, fillvalue=None):
'''Collect data into fixed-length chunks or blocks
grouper('ABCDEFG', 3, 'x') --> ABC DEF Gxx'''
args = [iter(iterable)] * n
return izip_longest(fillvalue=fillvalue, *args)
with open("raw_data.txt", 'r') as f:
...
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
...
12
I surprised no-one mentioned HDF5 yet. This is a documented format with freely available C libraries for using it. It has traditionally been used for high-performance persistence of numerical scientific data. HDF5 files are self-describing and very easy to work with using python (using either PyTables or H5Py). More importantly, HDF5 is platform agnostic and ...
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
10
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 ...
9
The databases at the International Labour Organization (specifically ILOSTAT and LABORSTA) are tantalisingly close to what you're after. For example, go to LABORSTA and select Employment, then select Employment for detailed occupational groups by sex (SEGREGAT). This allows you to select a country and view a breakdown of detailed occupational groups which ...
8
Once you've decided on a data format, you will need to decide the following additional issues before publishing your open dataset:
Data Layout
Name/Data type (Vocabulary) of data fields.
Language and Language Script.
Data Layout
Tabular Data Format (TDF) - http://dataprotocols.org/tabular-data-package/
The key features of this format are the following:
•...
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.
7
As said in the question, the format used depends mainly on which project/domain the data comes from. But most of the time the data is useless without metadata information, and when the dataset starts to get big you need a way to extract the metadata from it automatically.
In astronomy, where most of the data has been open for decades, the International ...
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
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
One resource I have sent many people to (and they always find value in it) is Socrata's* "hackathon in a box" guide: http://hackathon-in-a-box.org/guide/
The Open Knowledge Foundation also has a guide which I have not read as closely at http://blog.okfn.org/2012/10/26/hackathons-the-how-to-guide/.
And finally, please at least skim ChallengePost's blog post ...
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
My currently favoured text editor jEdit has a simple yet effective word completion feature (Menu Edit > Complete word; default shortcut Ctrl+B). It takes its word list from the opened document and includes keywords of the file's programming language (in case it is code). Word-delimiting characters can be user-defined as described on the page Working with ...
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 ...
5
I am a big fan of tad which crunches these kind of files easily.
5
It has nothing to do with GET vs POST, you seem to be using two different API versions. In the first case you are using version 1 of the CKAN API (http://catalog.data.gov/api/search/dataset), which is deprecated. This version returns a list of dataset ids.
In the second case you are using version 3 of the CKAN API (http://catalog.data.gov/api/3/action/...
5
The POST syntax is fine - you get the same results using the web front end:
http://catalog.data.gov/dataset?q=sea_water_temperature&sort=score+desc%2C+name+asc&ext_location=&ext_bbox=&ext_prev_extent=-254.53125%2C-75.84516854027044%2C54.84375%2C83.19489563661588
However the underscores are being treated as spaces, so effectively your query ...
5
People from ScraperWiki and OpenKnowledge Foundation sure will like it! They develop and maintain a software called pdftables which extracts tabular data from PDFs.
There is also an article on ScraperWiki's blog about research in identifying tabular data in PDFs (since PDFs do not have information about data semantic, only positions, font etc.).
To contact ...
5
I found this website (http://www.datasciencetoolkit.org/). There is an API collection in there with great tools, especially to "clean" and prepare your open datasets for analysis and visualization.
A list of the tools:
Street Address to Coordinates
Street Address to Location calculates the latitude/longitude
coordinates for a postal address. ...
5
Including to other answers, here is one that may help to create a database.
Project Euler is a series of challenging mathematical/computer
programming problems that will require more than just mathematical
insights to solve. Although mathematics will help you arrive at
elegant and efficient methods, the use of a computer and programming
skills ...
5
The pdftotext library (man page) that comes standard with most linux distros and can be installed on Windows contains a -layout flag that preserves table structure.
pdftotext -layout input.pdf output.txt
After that, you can easily parse with any language into your desired JSON structure.
There is a python wrapper for pdftotext, but as far as I know, it ...
5
You can use python, R or other code language as you prefer:
Example for python: https://stackoverflow.com/questions/6325216/parse-html-table-to-python-list
Example for R: https://stackoverflow.com/questions/1395528/scraping-html-tables-into-r-data-frames-using-the-xml-package
Here just two short examples from stackoverflow. If you want to iterate the ...
Only top voted, non community-wiki answers of a minimum length are eligible
