Continued struggle with IPEDS data

As mentioned earlier, works in automate data importing take enormous amount of dedication and efforts. For those do not appreciate, there is really no need to share the knowledge with them.

Here is an example that demonstrate the kind of work and dedication is needed to solve just one problem that I run into while importing IPEDS data.

One problem format I run into in some IPEDS csv file is: 
...,""some text quoted with two double quote"", ...

As a human, we know this line break the csv convention and most likely any csv file importing program is going to fail.

As a data user, I got few resolutions to consider. If I am only dealing with this file, the fastest way is to just open the file in text editor and modify the line so that the csv file can be imported into my application. If you are thinking this way, most likely you are a data analyst and probably think this is how things should be handled. Since you are higher up in the data food chain, likely, have not appreciate the work and thought of IT professions.

IT professions are likely to view the situation from a much broad point of view and ask questions like: What if this is an error exist in ACS' csv file? - If you know the size of a general ACS' csv file, you will realize that there probably very few text editor can effectively open the file, let alone to locate the error line and fix it.

IT professions may also ask: What if there are other csv files also have this problem? How can I handle this automatically?

One tool a lot of IT profession know about is the sed program. To use the sed to fix this problem it is straight forward:
  sed 's/,"("[^"]{2,}")"/,\1/g; ' InCsv

Unfortunately, if you want to invoke this with VBA, the command become much more complicate:
  Cmd.exe /c ^"sed ^-r ^-n ^'^{s^/^,^"^(^"^[^^^^^"^]^{2^,^}^"^)^"^/^,^\1^/g^; p^;^}^' InCsv ^"

Let's just say this, if you have no clue what we are talking about here, you should appreciate the work of IT professions.

NCES IPEDS data for Database/IT professionals

Personally, I am an IT professional worked in an education agency. (Have) Been in this position long enough and dealing with social science researchers a lot. One of the interesting observation is that even though the social science researcher dealing with data all the time, without the IT background still limited their ability to handle large amount of data efficiently. A lot of time, these staffs relied on expensive commercial software and computer hardware to perform their tasks. When leading projects, often times, they are limited by their vision to provide and deliver efficient data products.

On the other hand, people with strong IT training can have better visions on how things work and know the real limits of things and set the goals that others can't - I love this Elon Musk story Simple math is why Elon Musk’s companies keep doing what others don’t even consider possible, where Physics is said to be applied first, which is the fundamental that dictated the limits. The value of a real STEM training is the vision and the know of limits. Apply to the data processing, the IT is the know.

The Integrated Postsecondary Education Data System (IPEDS) refers to a set of data collected from a large set of Postsecondary Education Institutions of United States. The survey is conducted by the National Center for Education Statistics. The data collected is available for anyone's use. For causal use, you can easily obtained the data you are interested in, manually. However, as we all know, the real power of data multiplied if you can have all the data in one place in a readily to be used state. Yes, most likely we are talking about a database.

Glance over the data retrieval option offered by NCES/IPEDS, the 'Complete data files' option seems to be the best way to retrieve the whole IPEDS data set. Practicing a bit manually, you soon realize that manually select and download will still take you a long time to even download the file let alone importing them.

With enough IT knowledge, a reasonable approach to this problem could be: 1) Save the download page; 2) Make minimal fixes to the page so that it conform to XML; 3) Device a short XSLT translation script; 4) Copy the translated page into database; 5) With the list of file to download on hand, wrote scripts to download files automatically.

In addition to the above implementation, to facilitate the continuity of time available for download, a scheduling mechanism is also implemented.
  

Population migration derived from ACS 2015 5-year PUMS dataset

Now that we have got all the data imported, let's have some fun.

For those of you who knows me, I have been an advocate for open source movement for a while now. The statistic software I preferred to use have been the R. However, I had not spent a lot of my time on R - I think we all understand that people got a lot of things to do and we revisit a tool when we needed to.

Couple months ago, I spent my spare time and wrote quite a bit of code in R and I thought that I will be right at home when I decided to take on this migration project. Boy, am I wrong about this... gosh. Well, spent almost whole day and end up fixing some of the bugs - well, not really a bug but because I have decided to include the NA definition into my definition database, it caused some problem when referencing these definitions from my old code. Anyway, got it fixed but did not really use the R.

Well - my IT training kicked in - I realized that instead of using the statistic software for this project, a few SQL statement will largely simplify the task to nothing. Come to think about this, the SQL not only easier, it actually run much faster - Database is designed to run from hard disk, it is not like most statistic software will load all the data into memory and tied up the computer resources. By the way, a while back I have this idea of using database as my statistic software. I actually check out MS SQL documentation on customer functions and, do you know what, it is totally possible. Now, the question is who is going to take on this project.

Anyway, I end up running few SQL statements and dumping it into Excel with a bunch of formula - sorry, I haven't really invested in the Open Office yet.

OK. Let's get back to the topic. American Community Survey is conducted by US Census Bureau in an annually basis. The PUMS file is sampled from the collected data and allows user to use these sample to derive results that weren't readily tabulated by the US Census Bureau.

Inside the ACS survey, there is a question that asked respondents where they lived a year ago. Based on this question, we can look into the PUMS data and derive some useful information from it. One of the interesting application of this question is when it is combined with the education attainment info of the respondents. This allowed data analysts to see that, for people moving out of a state, what kind of education these people acquired and, hence, the brain drain if highly educated people left a state.

Click here for the resulting file - please noted that for any result derived from sampling, there are associated errors - this file does not come with the 'margin of errors', which describes the range the real value may lie. In our case, with large enough margin of errors, the real value for an in-migration could end up in negative and, hence, associated with the idea of  an out-migration. So, the file is for references only. The author is working on consolidate some of the categories and, hopefully, can report some data with reasonable 'margin of error'.