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 #2

This is a continuation of my previous blog on importing the NCES IPEDS data into database for IT/Database professionals.

As described in my last blog, I was able to apply few IT know-how and managed to download all IPEDS data automatically to my desktop. The next mission is to import those data automatically into database. 

One of major task to support social science research, as described in my previous articles about the ACS PUMS data, is the ability to import and provide the label information for both variables and values. In current case of NCES IPEDS data, these information can be obtained from various places.One possible place is to use the 'Dictionary zip file' download provided by the IPEDS. Browsing through few of the information provided by the dictionary zip file, you realized that is really not the idea place to get these kind of information. The zip file provided by IPEDS do not have consistent format. In some years and surveys, the information is provided in Excel format. In other years and surveys, these information were provided as web-page html formats.

After studying how IPEDS made their data available to statistics software, I, actually, think it is quite unique and workable - IPEDS provide a single, or 2, .csv file to support all statistics software. It then provide a 'program/script' for each statistics software. The program or the script, basically, provide instructions to import the .csv file, to assign variables labels to each column and to assign value labels to each values. So, basically, all the label information for both variables and values are embedded in the program/script file. The question, of cause, is how to extract those information - For programmers went through bachelor degree training, they understand that this is a topic under the compiler study.

Based on my work long time ago, I was able to modify the parser and parse the info in the .sps file into database. The code to pull data in the csv to database is largely the same as I did with the ACS PUMS excepting few reformatting and unzipping. Some of the IPEDS csv files using the dot '.' to mean NA and some of the quoting isn't very consistent. I decide to convert tabs in the file to space first, then transform the file to tab delimited and that seems to work fine.

Sam Barbett at IPEDS was also contact about the release of 'Final' data - IPEDS, in general, release data in two phases. The first one is called provisional while the second one is called final. According to Sam, when the final data released, the new/final csv file will have a '_rv' suffix added to the file name, even though the .sps file isn't updated.

The other problem I run into is line termination used in the downloaded .sps file. Most of my processes is run/executed under Windows environment which means that lines in a file are terminated by the Carriage-Return/Line-Feed pair instead of a single Line-Feed character used in most Unix systems. By modifying my code to accommodate that, I was currently be able to process couple years of data without run into errors.

Overall, the process is smooth and the next task would be how to use these data effectively and efficiently.

ACS 2015 5-year PUMS for database/IT professional

Continue with the ACS PUMS database project, the task is to import the 5-year ACS PUMS product of 2015.

Comparing Census's 2015 Data Dictionary file (PUMS_Data_Dictionary_2011-2015.txt) to that of 2014, we, again, noticed that Census' added leading spaces to a lot of lines to, possibly, make the file more readable for 'human' users. Following the step of processing the 2015 1-year file, I removed those leading spaces via sed before processing it with my definition processor.

Processing the Data Dictionary file with my program, it yields the following parsing errors. Some of them are clearly unintended errors, others may just because Census did not spend time and efforts to establish clear syntax rules so that their products can be machine friendly. Here are the parsing errors:


      ADJINC
        value 1001264, the blank after '1001264' is actually an A0h instead of
          20h.
      TEN
        just before TEN, a two line 'NOTE:'
      PERSON RECORD
        - no blank line after the 'PERSON RECORD' section mark
      ADJINC
        value 100264, the blank after '1001264' is actually an A0h instead of
          20h.
      GCL
        just before GCL, a two line 'NOTE:'
      FPINCP
        no empty line before FPINCP

The A0h one is really interesting. For those of interest, A0h is a NBSP character used in HTML. Without a good hex editor, it takes me a lot of efforts to figure out what is going wrong.