patrick.net

Could you add overlay lines representing:
annual rent = price*3%,
annual rent = price*6%,
annual rent = price*9%

I think that would put these values in context.

Dunno how to easily do a median in MySQL. Here's what I did to extract from my giant forsale and forrent tables:

create table price_by_zip select avg(price) as price, zip from forsale group by zip;
create table rent_by_zip select avg(rent) as rent, zip from forrent group by zip;
CREATE INDEX zip_index ON price_by_zip (zip);
CREATE INDEX zip_index ON rent_by_zip (zip);
create table price_to_rent select price_by_zip.zip, price, rent from price_by_zip, rent_by_zip where price_by_zip.zip=rent_by_zip.zip;
echo "select * from price_to_rent" | m > price_to_rent.txt
("m" is an alias for command-line mysql)

Wish they had a median function.

SFace says

Completely uesless.

Expand your mind a bit. This graph kicks ass.

Sure, a for sale listing is a single family home, and a for rent is an apartment-- but this would basically be similar in each zip code. So, you clearly can't tell what rent to expect out of every 100k in real estate investment.

This graph does do a great job of directing an investor's attention towards a zip code with comparatively higher rents to purchase price though. This data could save a real estate investment trust fund manager a lot of dirty work time.

Sure, that would be fun! Here is the data:

The data source leaves a lot to be desired. It doesn't let you distinguish between types of housing or even house size. There's not too much you can infer from that particular data set. Best to get something more detailed and calculate the averages yourself in SQL.

Dunno how to easily do a median in MySQL.

Off the top of my head and without the benefit of a script IDE, but the following TSQL (mostly SQL, maybe a little specific to MS SQL Server). The semicolons are optional, but I like to use them as they make intent clearer.

declare @Temp table (Row int identity(0, 1), Zip int, Price float, Rent float);

insert into @Temp (Zip, Price, Rent)
select Zip, Price, Rent
from PriceRentByZip
order by Price;

declare @MedianPrice float;

select @MedianPrice = price
from @Temp as Temp
where Temp.Row = count(Temp.Row) / 2;

-------------------------------------------

declare @Temp2 table (Row int identity(0, 1), Zip int, Price float, Rent float);

insert into @Temp2 (Zip, Price, Rent)
select zip, price, rent
from PriceRentByZip
order by Rent;

declare @MedianRent float;

select @MedianRent = price
from @Temp2 as Temp
where Temp.Row = count(Temp.Row) / 2;

There's usually a way to reset the seed value of auto-identity columns, in which case you could just delete form @Temp and wouldn't need @Temp2.

Note, you can select other columns from the row as necessary.

CBOEtrader says

Expand your mind a bit. This graph kicks ass.

Thanks! This is just the beginning. I have a list of other graphs I plan to make. Suggestions appreciated too.

Dan8267 says

The data source leaves a lot to be desired. It doesn't let you distinguish between types of housing or even house size. There's not too much you can infer from that particular data set. Best to get something more detailed and calculate the averages yourself in SQL.

I do have more detailed data of course. I just didn't want to give it away after all the work and time it took to collect it. Still trying to figure out a business model for Patrick.net that might actually work.

Dan8267 says

insert into @Temp (Zip, Price, Rent) select Zip, Price, Rent from PriceRentByZip order by Price; declare @MedianPrice float; select @MedianPrice = price from @Temp as Temp where Temp.Row = count(Temp.Row) / 2;

Oy. Why doesn't MySQL just have a median() function the way they have avg()? It would not be hard for them. Maybe the latest MySQL does have it...

Why doesn't MySQL just have a median() function the way they have avg()?

In order to calculate the median of a set, you have to order the set. In order to calculate the average, you don't.

Technically, you could write a function to calculate the median, but that function would just be sorting the list and picking the middle item. In SQL Server Studio, you could write a CLR function in C# and call that from your TSQL code. There's probably similar functionality in MySQL, but I don't know what it is off the top of my head.

In any case, most DB providers won't give you a function that requires allocating a heck of a lot of memory to do a sort because the caller would expect the function to take little time and memory to execute. By making you do the sort yourself, the DB provider knows you know what your doing.

I've played around with the data you posted. I decided that the most useful way to visualize the data is using Google Earth. Basically, I combined your data with latitude and longitude and produced the following. Red is a zip code with a high price/rent ratio (buying is bad, renting is good) and green is the opposite (buying good, renting bad).

Of course, the best way to view this data is to load it in Google Earth. To do that just click on one of the files in the following .zip file.

http://patrick.net/uploads/2012/02/price_to_rent_enhanced.zip

Now the data set leaves much to be desired. Since I can't really compare apples to apples with it, the results are only so-so. Of course, with more detailed data, you could put together a far more meaningful and useful graph. So I'll explain what I did in detail next. But first I want to make sure my uploads worked because they were acting a bit funny. I hope they aren't too big. And I'm not sure the .zip file took.

Looks like there's a file size limit to the image upload function, but I got all the images up now.

The .zip file didn't take. I guess you can only upload images and if the web server can't parse the image and resize it, the web server drops the file.

No matter. I'll just show you how I produced the file.

So I downloaded your price_to_rent.txt file and a file containing geo-zip info zipcode.csv from http://www.boutell.com/zipcodes/

The format of zipcode.csv is

"zip","city","state","latitude","longitude","timezone","dst"
"00210","Portsmouth","NH","43.005895","-71.013202","-5","1"
"00211","Portsmouth","NH","43.005895","-71.013202","-5","1"
"00606","Maricao","PR","18.182151","-66.9588","-4","0"
"00607","Aguas Buenas","PR","18.256995","-66.104657","-4","0"

"00609","Aibonito","PR","18.142002","-66.273278","-4","0"
"00610","Anasco","PR","18.288319","-67.13604","-4","0"
"00611","Angeles","PR","18.279531","-66.80217","-4","0"

The file has a header, a bunch of useless quotes, and blank lines. The zip codes are padded with leading zeros and it includes zip codes not contained in your data file (mainly non-state zips).

So I wrote a little program to combine your data with this filtering out unwanted stuff. Here's the code in C#.

Code goes here

As you can see, I've modified it a bit and reran it a few times to produce the different .kml files.

So what's a KML file? It's a markup language for adding objects to Google's Earth data model. In this case I'm just adding points of interests, but you can make arbitrary shapes and a whole lot of other things.

So here's an abbreviated chunk of what the KML files look like.

KML goes here

When you double-click a KML file, it loads up in Google Earth, assuming you've installed GE.

Well, that's about it. Let me know if you have any questions. It's fun to look at detailed maps of price:rent ratios, but as I said the data source is a bit misleading because you're comparing small rental condos and large mansions. That's why the outliers are so extreme in the data, and I had to cap the max in the source.

I couldn't paste the code or KML file as text, so I'm trying images. Evidently some of the characters cause the posting to get rejected.

Good, the code is readable if you zoom in. Now for the KML snippet.

OK, you can now get the .zip file containing the KML files at http://www.filedropper.com/pricetorentenhanced

You only need to load one KML file. A created a bunch for convenience since Google Earth doesn't let you turn labels on/off for points of interests and since you may performance issues with the labels on.

Fri, 3 Feb 2012 at 12:12 pm Quote Like Flag Permalink Share

bmwman91 says

What is your input data comprised of? Is it 3 columns (zip code, avg sales price, avg rent price)?

Also, I have some hefty statistical analysis software at my disposal. If you can package up the data or get me access to the database, I can import it & run some analyses on it over one of my lunch breaks.

Sure, that would be fun! Here is the data:

http://patrick.net/contrib/price_to_rent.txt

It's about 400KB in size.

Is this the rawest data you have access to, or is this just some particular query that you did?

What I think would be interesting is for you to compute a price/rent ratio for *like* houses. (?Not sure if you have this data or not) In other words get a price to rent ratio for 1 BR condos, 2 BR condos, 3 BR SFH, etc.. You'd end up with data that you could plot (for a given zip code or region) the price to rent ratio on the Y axis, and (say) the number of bedrooms on the X...or the rent (or price) on the X axis...

Again, that takes more data than the 3 columns, so maybe not possible...

Dan8267 says

Befriend or Ignore
Friends: 8
Threads: 279
Comments: 1,982
Boca Raton, FL
Fri, 3 Feb 2012 at 9:03 pm Quote Like Flag Permalink Share

I've played around with the data you posted. I decided that the most useful way to visualize the data is using Google Earth.

Wow. That's great. So I have to have GE running on my computer to use the data? (no web-acessible app that can ingest the KML file and then "zoom in" to 80206?)

It's probably time for me to figure out if there is a GE client for OS X.

swebb says

So I have to have GE running on my computer to use the data?

Technically, no. You can always use data however you want. But if you want to see the data on the world map, yeah, you'll need to install Google Earth. Google doesn't have an interface to view KML files via Google Maps online.

You can get Google Earth from http://www.google.com/earth/download/ge/

And yes, there is a Mac version. When you click download, the web server will automatically give you the version of GE that matches your OS.

Once you install GE, just click on a KML file to see the results launched in GE.

So what should I plot exactly?

Why do you give a rats' ass what someone who self-identifies with an American Express black card thinks?

It is a fascinating plot.

There may be some meaning of the extrapolation to zero.

It looks like your plot extrapolates to average rent of $200 to $300 if the property is worth zero.

But maybe, the rent, which is for the most part related to wages, should be the independent axis, and the average price, which is affected by wages AND other factors, should be the dependent axis. In that case, it looks like zero rent would be correlated to negative $100,000, whatever that might mean, who knows?

Taking a look at the south Florida map, I think there is so much green simply because of the tiny apartments/condos that you can rent. If only I had the sq ft. of each rental, I'd make a calculated column that was the price/sq.ft. or rent/sq.ft. Although not perfect, that would make for a better analysis.

Wow Dan, you're a mapping maniac!

Do you know how to do boundaries in Google maps with KML?

I've been wanting to plot all the boundaries of school districts and then make a map you can click on or enter an address to say for any given address what school district it's in. Or just view school district boundaries.

Here's the raw data (census "shapefiles") for non-unified elementary and secondary school districts:

ftp://ftp2.census.gov/geo/tiger/TIGER2010/ELSD/2010/
ftp://ftp2.census.gov/geo/tiger/TIGER2010/SCSD/2010/

And there's the data for unified school districts (which have all school levels together in one district):

ftp://ftp2.census.gov/geo/tiger/TIGER2010/UNSD/2010/

This is all very cool.

Do you know how to do boundaries in Google maps with KML?

I would use polygons to make a rectangular fence around the borders. Here's an example using the Pentagon. In the example, I've replaced angle brackets with square brackets so I can paste the text on the forum. Also note that the XML tags are case sensitive even though the XSD is inconsistent in which casing convention it uses.

The Style node is optional, but if you don't include it, you get a solid black polygon. The inner boundaries are also optional, but if you omit them you get a solid shape (no hole). Finally, note that you repeat the first coordinates in order to close the path of the polygon.

The KML reference is available at http://code.google.com/apis/kml/documentation/kmlreference.html.

[?xml version="1.0" encoding="UTF-8"?]
[kml xmlns="http://www.opengis.net/kml/2.2"]
  [Placemark]
    [name]The Pentagon[/name]
    [Style id="s0"]
        [PolyStyle]
            [!-- Color is in the format AABBGGRR, 0 alpha is transparent --]
            [color]80FF0000[/color]
        [/PolyStyle]
    [/Style]
    [Polygon]
      [!-- Extrude 1 means make the shape go to the ground --]
      [extrude]1[/extrude]
      [altitudeMode]relativeToGround[/altitudeMode]
      [!-- Outer boundaries make the pentagon shape --]
      [outerBoundaryIs]
        [LinearRing]
          [!-- Each line is longitude, latitude, altitude --]
          [coordinates]
            -77.05788457660967,38.87253259892824,100
            -77.05465973756702,38.87291016281703,100
            -77.05315536854791,38.87053267794386,100
            -77.05552622493516,38.868757801256,100
            -77.05844056290393,38.86996206506943,100
            -77.05788457660967,38.87253259892824,100
          [/coordinates]
        [/LinearRing]
      [/outerBoundaryIs]
      [!-- Inner boundaries makes the hole in the center --]
      [innerBoundaryIs]
        [LinearRing]
          [coordinates]
            -77.05668055019126,38.87154239798456,100
            -77.05542625960818,38.87167890344077,100
            -77.05485125901024,38.87076535397792,100
            -77.05577677433152,38.87008686581446,100
            -77.05691162017543,38.87054446963351,100
            -77.05668055019126,38.87154239798456,100
          [/coordinates]
        [/LinearRing]
      [/innerBoundaryIs]
    [/Polygon]
  [/Placemark]
[/kml]

Here's the result:

B.A.C.A.H. says

Why do you give a rats' ass what someone who self-identifies with an American Express black card thinks?

HA! I understand why the AmEx Black card image is a turn off, but that's not enough reason to dismiss what the guy has to say...I take his input and try to ignore the picture...something about babies and bathwater...

Price per square foot is how you would normalize across units, if you have that data.

I'm going to set to work on that data set.

Wacking Hut says

Price per square foot is how you would normalize across units, if you have that data.

That's better than nothing, for sure, but in my world square footage is a secondary issue. Number of bedrooms and type of dwelling (SFH vs duplex vs high-rise) are both more important than square footage...so price to rent normalized by square footage is great if you first make sure you are comparing like units.

It would be helpful to filter based on anomalous square footages, too...really small, vacant lot, etc.

Square footage is tough to get for millions of addresses.

Maybe I should ask people to input square footage into my calculator if they have it. That might get them a better rent-vs-buy estimate too.

Okay you statistics wonks - you do know that Linear regression is not usually a good idea if the data set are heteroscedastic!! Also appears to be a high degree of multicollinearity in the model.....

No idea what you're saying.

swebb says

HA! I understand why the AmEx Black card image is a turn off, but that's not enough reason to dismiss what the guy has to say...I take his input and try to ignore the picture...something about babies and bathwater...

I don't think so.

It's not like SFAce has a birthmark on his face that resembles a Black Card. It is a Choice.

If my kids, when they were babies, flouted their self-identity with Black Card, I'd have thought about throwing them out with their bath water.

StoutFiles says

This graph isn't linear. It's y = square_root(x).

Somehow most of this thread missed StoutFiles point, which seems like the most interesting point of visualizing this data. What if analysis of this data fits an upside down hockey stick?

I just don't see how that could be.

Rents and prices seem to have a pretty clear linear relationship to each other.

This is great. Combining my favorite things in life; computer geek stuff and the housing crash!

Hey Patrick, want to break the news on the next property bubble? Check out these prices:
http://www.extension.iastate.edu/agdm/wholefarm/html/c2-09.html
Farmland prices are going exponential and decoupling from rents, just like residential real estate in 2004-2005.

I blame Michael Burry.

What about the graph with only SFH in it. Did that graph get made?

I just don't see how that could be.
Rents and prices seem to have a pretty clear linear relationship to each other.

I thought so too, but, when it was suggested, could also see a bit of a lower grouping to the right. Use the data analysis tools in Excel and have it draw a best fit curve.

It does look pretty linear. A droop to the right would suggest a thought I've often had, which is that the higher up you go on the sale value of the property, the more you rent for less. For example, going from a studio to a one bedroom isn't twice as much rent. Going from a one bedroom to a two bedroom is less of a rent increase still. Going to a SFH can be a very small increase annually, over renting a two-bedroom apartment in a mult-dwelling building.

OK Patrick, I finally played around with the data a little. It isn't going to reveal anything new, but it is sort of fun to fart around with the data & see what can be determined (or more importantly, what CAN'T be determined) from it.

So, first of all it is a good idea to look at the distribution of the data sets and get a feel for them. At first glance they might look like moderately-skewed normal distributions with some really high outliers. Well, that isn't the case, and they are VERY much log-normally distributed. The top of the plots shows a bunch of stuff (confidence interval of mean, quantile bars, some other stuff we won't worry about), but most important are the clouds of black dots. Those are the outliers...as-calculated for a normally distributed data set. This is not one, so they aren't the actual outliers.

So, I took the log10 of price & rent values and plotted the distribution of those, since they are log-normally distributed. When you do that, suddenly your data set will give a far better resemblance to a normal distribution. This is confirmed by the fact that a normal distribution line is fit to the data and that it fits very well. The goodness of fit part might seem a little confusing or contradictory, and that seems to be an issue in this software (SAS JMP 9.0). Anyway, if the "Prob>D" value is small & has a "*" next to it, then the fit line is very well suited to the data.

Now we can select the outliers and exclude them from the analysis. Since the two data sets do overlap, it makes things a little messy and with the new mean/stdev it looks like there are still outliers. Those are ignored at this point since we don't want to go mixing variables up during sorting.

So, now it is time to plot something that probably look familiar in this thread. Rent vs. Price. Is there a statistically significant link between them?

NO! Here is a brief explanation of what you are looking at.

- The magenta ellipse contains 95% of the data points.
- The blue ellipse contains 50% of the data points. These are just to give a feel for where the points mostly lie since there is this giant cloud.

- The red stuff pertains to a linear fit to the data.
- The green stuff pertains to a log(price) fit to the data.

- The darker, narrow shaded regions by the lines are the 95% confidence-of-fit intervals. These basically say, "For the true population of sales prices and rents, we are confident that the fit line will be within this region 95% of the time. The other 5% of the time, we don't know where it will be."
- The lighter, wide shaded regions are the 95% confidence intervals for the correlation between price and rent. These basically say, "For a given sales price, we are confident that the true population of rent costs lie within this colored band 95% of the time. The other 5% of the time, we don't know what they will be."

Discussion:
Being that the CIs for the price-rent correlation are gigantic, we can basically infer that there is no really solid link between prices and rent nationwide, at least given a sampling of the averages of both. I added the log(price) fit line in there because my intuition tells me that something like that is at play. People are willing to may more per month as "owners" than they are as "renters." We see this all over the Bay Area. Low-tier properties rent for cash flow-positive amounts, while higher-end areas rent for a lot less than it would cost to buy with a mortgage. Then again, that is super location-specific, and perhaps other places don't follow this. According to the limited data set, it varies wildly across the US since no fit line really has any significance here. There just isn't a correlation between price & rent, across the US, if we are using the averages of both as the inputs.

Now, I would like to re-run this using the MEDIAN of both to see if that can tease any correlation out. Averages are flawed for these types of analysis for reasons that we have all read on here before.

If nothing else, just look at the r^2 values. They are pitifully low, which is really all the information anyone needs to know that the fit is really meaningless! There isn't a fit, just a line drawn over a cloud of uncorrelated data!

So, Patrick:
a) Can we get the same data, but with the medians of both?
b) Would it be possible to have an additional column with the 2-letter state code corresponding to the zip code?
c) Do you have some monstrous database with data for every rent listing and recorded sale that you pull this from?

EDIT:

Also, the log-distribution of prices & rents makes perfect sense. People are generally seeking low rents and low house prices, and most market activity falls around these "affordable" levels. There is VERY little available below this since people are seeking the low-end to begin with. Then you see the long tail tapering off into the higher & higher price points. That also makes perfect sense since there are a number of people out there that can afford more expensive stuff. As prices go up, the number of participants goes down, and you see the tapering of prices and rents as they increase. I guess I am a huge nerd, but I love it when data agrees with intuition, and particularly when it seems to explain human behavior.

Also, trying to plot price, rent or price/rent versus zip code isn't useful at all. Zip codes are nominal data, while price and rent are continuous. They can be analysed, and I tossed them into JMP, which treats nominal-X axis + continuous-Y axis data as a One-Way analysis. Nothing useful popped out of that, which is sort of to be expected. It "looks" like there is a bunch of expensive real estate on the west coast, but no statistically significant fits could be applied to the data set to back that up.

I took statistics in German in Munich and didn't really absorb much.

bmwman91 says

So, Patrick:
a) Can we get the same data, but with the medians of both?
b) Would it be possible to have an additional column with the 2-letter state code corresponding to the zip code?
c) Do you have some monstrous database with data for every rent listing and recorded sale that you pull this from?

I don't know an easy way to get medians from my database.

I could pretty easily put the 2-letter state code because I can just join with my zip code table.

Yes, I have a monstrous database with data for every rent and asking price from Craigslist for about a year.

SFace says

Then there is still the issue of median rent and median sale is not the same thing.

This is true. Really, it seems like the only proper data set to work with would be one comprised of raw sales prices and rent values. Sampling methodology is a huge factor, and unless EVERY single sale & rental are taken, all sorts of sampling-related issues could mask trends and cause false ones to appear.

I have the x64 version of JMP & 16GB of RAM...I probably COULD work on the entire set of raw data. My gut feeling is that there is going to be some sort of correlation between price and rent without the masking effects of averaging or median filtering. Oh yeah, confirmation bias...about that, lol.

I could pretty easily put the 2-letter state code because I can just join with my zip code table.

Yes, I have a monstrous database with data for every rent and asking price from Craigslist for about a year.

Shoot me an email if you want to see what we can glean from the data. I know that this is sort of Patrick-proprietary stuff since you make a living off the site, so maybe we could make something interesting to share with readers without giving everyone all of your data.

JMP can access databases and stuff, so we wouldn't necessarily need to email monstrous text files.