Steps to download and start using compressed data are outlined below.

step one (skip if Linux user):

1. Download Unix Utilities for Windows
2. Unzip Unix tools to a directory of your choice. I put them in my “Program Files” directory.
3. Browse through the directory you just created to find the folder where the executable files are. After extracting the files the executables were in:

   C:\Program Files\usr\local\wbin\
   

4. Add this directory to your system PATH variable. You do this so that you can call these commands from the terminal from any folder on your hard drive. In my case I right clicked My Computer, selected properties > Advanced settings > Environment Variables, then edit the “path” variable adding the text “;C:\Program Files\usr\local\wbin\” without quotation marks. Be careful not to add a space between the semi-colon and the directory name.

Step two:

Open Stata and run the following commands or add them to a do-file.

Change to a working directory (e.g. cd “C:\Temp”)

Download the file to a file called download.zip by issuing the command:

shell wget -Odownload.zip "http://example.com/download.zip"

This command will use Stata’s -shell- command to send the text following to your operating system’s shell. For the command above this will use the unix utility wget to download a compressed file.

Now that the file is downloaded we need to unzip the compressed archive. On Windows I prefer to use 7zip for compression, but you could also use gzip from the Unix Utils package or unzip if you’re on Linux. I’ve included all three of these options below so choose one. Again, we submit the commands to Stata with the prefix -shell- so it sends the command to the OS shell.

shell 7z x -o.\unZipped download.zip
shell gzip -d download.zip
shell unzip download.zip

Including these in a do-file is extra useful as it automates the download and unzipping process. Be careful using wget in a do-file though, especially if you are trying to debug a your code. Some webmasters won’t like you downloading files multiple times and using up their bandwidth and this can get you blocked.

I haven’t tried it yet, but, I imagine that -shell- could be used to call other handy command line tools like Python scripts or maybe even leverage some useful R packages. I’ll post again if I have any luck with these options.

Now it’s time to dive into the data contained in those compressed archives and unlock their secrets. Good luck!!

I also overheard the following conversation by a couple of teens in the store:

“This movie looks cool.”
“Yeah, but I was looking for something else.”
“Let’s just go home and watch something on the net. There’s more selection and we don’t have to return it.”

The movie industry seems to have an uphill battle if it continues with it’s current business model.

* hilarious for an economist == somewhat amusing for the other 99.8% of the population

The blog post explains how to connect to database running on Microsoft’s SQL server. Below I’ll describe the process using a database on the open-source MySQL platform.

Step 0: Download/Install the MySQL ODBC Driver
If you haven’t done this already, you must install an ODBC driver to connect to MySQL servers. You can download the latest version of the MySQL ODBC driver and install it by following the prompts.

Step 1: Load data from your MySQL database using Stata
The following example code will connect to a MySQL database (worldstats) running on my local machine (localhost) and extract a unique ID and name using a SQL statement (‘sql’) from a table of country attributes (countries). This database requires a username (UID=andrew) and password (PWD=pass) but these may not be needed depending on how the server you are connecting to is configured.

local db "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=worldstats;UID=andrew;PWD=pass;"
local sql "SELECT ID, CountryName FROM countries"

odbc load, exec("`sql'") conn("`db'") clear
des

For more info about using -odbc- in Stata search the help files by typing “help odbc”. Good luck!

If you use phpMyAdmin, or you’re a linux geek, you know that backing up your MySQL database to a .sql file is easy. There is a point-and-click interface for phpMyAdmin and you can use mysqldump in a terminal on linux. Turns out this also works on windows with a small tweak and can be called from a Python script as well if you are using that language for development….and if you’re not you should really consider it.

Steps to backup a MySQL database running on windows from Python

1. My default install of WAMP2 for Windows 7 didn’t set an environment variable for the MySQL folder so I added it by adding the text “;C:\wamp\bin\mysql\MySQL 5.1.36\bin” without quotes to the windows PATH by right clicking “My Computer” > Properties > Advanced settings > Environment variables.

2. Now you should be able to run mysql from the command line from any folder. Try it by opening a command window (win + r > cmd > OK) and typing: “mysql -u username”. Pretty cool.

3. Now you’re set to run MySQL commands from Python (or PHP, etc. as well I imagine). The following few lines of code will backup an entire database to a temporary directory in your root directory. Good luck!

import os
target_dir = 'C:\\TEMP\\'
os.system("mysqldump --add-drop-table -c -u username -ppassword database > "+target_dir+"database.bak.sql")

I’m new to using github for code but I have posted the code for this script up there to share with whoever can make something useful of it. You can download the Python script to convert CSV (with lat/long coordinates) to geoJSON or clone the .git repository.

Below I post the body of the Python script for comments if anyone should pass this way with suggestions for improvements.

import csv

# Read in raw data from csv
rawData = csv.reader(open('sample.csv', 'rb'), dialect='excel')

# the template. where data from the csv will be formatted to geojson
template = \
    ''' \
    { "type" : "Feature",
        "id" : %s,
            "geometry" : {
                "type" : "Point",
                "coordinates" : ["%s","%s"]},
        "properties" : { "name" : "%s", "value" : "%s"}
        },
    '''

# the head of the geojson file
output = \
    ''' \
{ "type" : "Feature Collection",
    {"features" : [
    '''

# loop through the csv by row skipping the first
iter = 0
for row in rawData:
    iter += 1
    if iter >= 2:
        id = row[0]
        lat = row[1]
        lon = row[2]
        name = row[3]
        pop = row[4]
        output += template % (row[0], row[2], row[1], row[3], row[4])

# the tail of the geojson file
output += \
    ''' \
    ]
}
    '''

# opens an geoJSON file to write the output to
outFileHandle = open("output.geojson", "w")
outFileHandle.write(output)
outFileHandle.close()

The procedure goes like this:

1. Write out the equations in your system
2. Re-write the system as two equations expressed as functions of exogenous variables only
3. Generate the variables in your software package of choice. I’ll be using Stata although this would be equally easy in R and in python you should be able to solve the systems and generate the data in the program and avoid the algebra.

1. Write out the system of equations:

Y1 = a0 + a1*Y2 + a2*X1 + e1
Y2 = g0 + g1*Y1 + e2

where a0,a1,a2,g0 and g1 are parameters that we specify explicitly while e1 and e2 are error terms.

2. We can re-write the system of equations like this using simple substitution:

Y1 = (a0 + a1*g0 + a1*e2 + a2*x1 + e1)/(1 – a1*g1)
Y2 = (g0 + g1*a0 + g1*a2*x1 + g1*e1 + e2)/(1 – g1*a1)

3. The following code will produce a dataset with simultaneously determined equations. Copy and paste into Stata and hit enter.

clear
set obs 1000

* These are the random variables
gen x1 = rnormal(0,1)
gen e1 = rnormal(0,1)
gen e2 = rnormal(0,1)</code>

* These are the parameters of our equations
* They can be any value you choose
local a0 = 1
local a1 = 2
local a2 = 3
local g0 = 4
local g1 = 5

* This generates our two dependent variables
gen y1 = (`a0' + `a1'*`g0' + `a1'*e2 + `a2'*x1 + e1)/(1-`a1'*`g1')
gen y2 = (`g0' + `g1'*`a0' + `g1'*`a2'*x1 + `g1'*e1 + e2)/(1-`g1'*`a1')

Done!

4. Now, since we know the data generating process (DGP) of this dataset, we should use it to test some of the assumptions of the 2SLS estimator. I’ll explore the 2SLS estimator in an upcoming post but to get you started you can try estimating the parameters using the -ivregress- command below.

ivregress 2sls y2 (y1 = x1)

Notice that the estimated coefficient on Y1 and intercept are 5.003 and 3.984 respectively. These are close to our input parameters so looks like the 2sls procedure works fairly well for this system. Now compare this to the results we’d find if we just used ordinary least squares (OLS).

regress y2 y1

This time we get an estimate for the coefficient on Y1 and intercept of 3.82 and 2.79 respectively. Not good, especially considering that Stata is reporting both of these estimates to be statistically significant.

The problem
I noticed that when I ran my Stata scripts on Ubuntu, random characters would be added to the data (especially string data) when running the reshape command. For example, I would load some data:


insheet using "/home/data/sample.csv", clear comma
tabulate string_variable


producing the output:


string_vari |
able | Freq. Percent Cum.
------------+-----------------------------------
A | 500 50.00 50.00
B | 500 50.00 100.00
------------+-----------------------------------
Total | 1,000 100.00


Then I reshape the data in some manner and tabulate once more and find that string_variable has some new additions.


string_vari |
able | Freq. Percent Cum.
------------+-----------------------------------
A | 498 49.80 49.80
B | 497 49.70 99.50
ôèA | 2 0.20 99.70
ñBúõ | 3 0.30 100.00
------------+-----------------------------------
Total | 1,000 100.00


Obviously it is a problem when random characters are being thrown into your data, especially if this is happening on a variable you use to index a reshape of the dataset. Now, on to how it’s fixed.

The solution
I spent a great deal of time checking and re-checking my code because I believed that this must be something wrong with what I’m doing. After several hours of debugging and web searches the best I came up with is from this thread on statalist that suggested to me that maybe Stata thought that my license was invalid. I contacted Stata tech support to ask if there was a problem with my license and they told me this was NOT the case. Rather, a known bug with Ubuntu 10.04 due to the behavior of a low-level call in a library in this distribution of Linux caused data in Stata’s memory to become corrupted. Since it is a known bug, Stata issued a patch in Stata 11.1 and all that I needed to do is update the software. To do this first save your dataset and do-files if you’re working on something and then enter:


update query
update executable, force
update swap


When Stata restarts enter:


update ado, force


Now you should be on your way. If problems continue you’ll likely need to contact Stata tech support for more info but this fixed it for me.

I much prefer to use a reference manager like Zotero which makes it unbelievably easy to get your references into. Most times, you can just drop a PDF in and then find the reference data based on info in the PDF. But, more about Zotero in a later post perhaps. For now, what do you do when you receive a huge list of references in APA/MLA/etc. format and want to add them to your reference manager?

For this task I’ve used a tool from Makino Takaki fairly successfully a few times now to convert a list of text references to BibTeX and then import into Zotero. It’s not foolproof and you’ll have to double-check in the import but it’s a start.

I’m hoping that someone with more time available than myself will take his source code and tweak it to improve this tool.

Also the javascript that I was originally working with used

<body onload="init()">

to start the javascript when the page loaded but this wouldn’t work from a WordPress blog entry. So I had to change my javascript from:

function init() {
something();
goes();
here();
}

to:

window.onload = function init() {
something();
goes();
here();
}

and then I call the init() function in my blog post using:

<script type="text/javascript">
init();
</script>

*Important*
I can to change my WordPress user settings to disable the visual text editor since it was adding/stripping various parts of the html and breaking it.