Use data modeling to join varied datasets to provide a complete view of decision parameters

Published on August 10, 2016

“A comfortable old age is the reward of a well-spent youth.” – Maurice Chevalier

Martha and Paul spent their youth well. Now both are looking forward to spending their retirement years comfortably in a pleasant place where their children and grandchildren can visit them frequently.

Martha has retired from Long Island City High School after 35 years as head librarian. Her husband Paul worked for several large finance companies in New York City as the head of IT service. Their older daughter lives in the suburbs of Portland, Oregon with her husband and two sons. The younger daughter lives with her son in Irvine, California.

They need to sell their four-bedroom house in Staten Island before they can buy a retirement home. They’re also planning to downsize, buying a three-bedroom house and investing the money they save. For that, the city they move to should also have a lower cost of living. With these thoughts in mind, they consult their tech savvy real estate agent, Michelle, to help them choose the perfect place for them.

First, they want to know how much they can expect for their house in New York and if it’s a good time to sell. Then for deciding on where to move to, they have a list of priorities:

  • Proximity to family
  • Good air quality / low pollution
  • Access to public transportation
  • Access to health care providers

Understanding their requirements, Michelle gives them a couple of options to consider: Redding, CA and Reno, NV. Both have a reputation for being good cities in which to retire.

Considering their first priority, both cities are relatively convenient; Redding is 420 miles from Portland and 584 miles from Irvine while Reno is 534 miles from Portland and 495 miles from Irvine.

To address their complete list of priorities, Michelle creates an app in Arcadia Instant importing real estate data from Zillow and public transportation, healthcare, and pollution related data from government databases.

First, Michelle creates a single dataset in Arcadia Instant which is comprised of joining different tables. She selects columns that are common in the tables, e.g. state, city, etc.

1-join

2-join 3-join 4-joined-tables

After joining the data, Michelle creates a comprehensive app to help Martha and Paul understand various pertinent metrics through visuals so they can make an informed decision.

The first thing Martha and Paul want to know is if the timing is right to sell their house in New York and if it is, how much they can expect for their house. Michelle selects New York from the City Where Selling filter and Four Bedroom from the Type filter.

image05

The Bubble Chart shows the Zillow Home Value Index (ZHVI) variation for four bedroom houses in New York over the past two years. The chart shows the prices have been constantly going up. So, if they want to wait and watch if the prices go higher, they can. Otherwise, the time is still good to sell their house. The couple has decided to move as soon as possible.

7-new-york

To check on what they can expect for their house, Michelle scrolls down to the Expected Selling Price visual, which shows that the expected selling price of a four bedroom house in the outer boroughs of New York City is approximately $626,500.

8-expected-selling-price

Now, Martha and Paul have an idea of how much money they’ll have in hand after selling their house in New York and they want to see what the possibilities are in the cities suggested by Michelle. Michelle’s first suggestion is Redding, CA. From the filter City Where Buying she selects Redding and from the filter Type, three bedroom.

The ZHVI variation shows that three bedroom house prices in Redding increased substantially after January 2015, reached a peak in December 2015, and are somewhat stable now.

9-redding

Then they check the expected purchase prices for a three bedroom house in Redding. It’s around $341,900.

10-purchase-pruce

That means, after selling their four bedroom in New York City, if they buy a three bedroom in Redding, they will have nearly $285,000 still in hand. Michelle suggests that with that money, they might consider buying a second two bedroom house in Redding and renting it out to get monthly rent in addition to their pensions. To see how feasible that idea is, she sets the Type filter to Two Bedroom and the visual refreshes to show the expected purchase price of a two bedroom house in Redding. It’s indeed within their budget.

11-purchase-price-two-bedroom

Now they want to check the feasibility of their second option, which is Reno, NV. Michelle selects Reno on the City Where Buying filter and three bedroom from the Type filter. The first visual shows the ZHVI trend for the past five years.

12-reno

The data shows that prices of three bedroom houses in Reno have been increasing since January 2015 and after December 2015, they’ve continued to rise.

Michelle then checks the expected purchase price of a three bedroom in Reno and as expected, it’s less than in Redding.

13-reno-purchase-price

At a price as low as $196,400, they can actually purchase three three-bedroom houses in Reno and rent out two of them. However, Michelle also informs them that if the housing prices are low, the rent will likely be comparatively low.

Now, since they’ve confirmed that they can afford three-bedroom houses in either city, and can also expect to have extra income from rent, they want to compare other quality of life factors like pollution, health facilities, public transportation, etc. These factors are important for a good retirement.

To compare the pollution levels, Michelle directs their attention to the State – Metrics map. It’s a Choropleth Map colored by the level of chemicals released in each state. In the Select Metric for State Map filter, Chemical Releases is selected by default for this map.

14-state-pollution

Hovering over CA, NV, and NY on the map shows that New York has the lowest and Nevada has the highest pollution levels. California has more pollution compared to New York, but less than Nevada. Nevada, in fact, is one of the most polluted states in the nation. That is definitely not good for the couple’s health.

15-filter

On to the next life style metric. To compare the health facilities between the two cities, Michelle changes the metric for state map to Median Cost of Nursing Home in Percentage. This refreshes the app and changes the map’s colors to the following:

16-state-nursing-home-cost

Tooltips on hover shows that the percent median cost of nursing homes is quite high in New York; it’s the lowest in Nevada; and in California, it’s only a little higher than Nevada. Learning this makes the couple happy they are moving from New York.

The third aspect of a comfortable lifestyle for Martha and Paul is public transportation. To figure out how good public transportation is in a given city, Michelle wants them to consider two factors: how easily available public transportation is in a city and how much it costs in that city. To analyze that, Michelle has created a Bar Chart using the public transit data she imported into Arcadia Instant.

Arcadia Instant allows you to receive variable dimension parameters in Apps. So, for this chart, Michelle has enabled two filters – Select Metric for Bar Chart and Select Granularity for Bar Chart – for which she can select both the dimensions and measures dynamically. City and State are the dimensions; Average Price per Trip and Average Trips per Resident are the measures. She can also select multiple dimensions, i.e. cities or states.

image12

Selecting City and then the three pertinent cities shows the following two tables for these two measures.

combined-image-300dpi

The bar chart shows that trips per resident are the highest in New York City; in Reno and Redding, they’re quite low comparatively. Average trip price is almost the same in New York and Reno; it’s a little higher in Redding. This goes to show that public transportation is really good in New York; it’s similar overall between Reno and Redding.

The app makes it clear that property prices and health services are less expensive in Reno. However, pollution is really high in Nevada. So, with good air quality and proximity to family as top priorities on Martha and Paul’s list, Redding sounds like a good choice. Martha and Paul reached this decision and trust Michelle because she didn’t simply answer their queries but showed them visuals which helped them find the answers themselves. On top of that, they also approximated what they could expect to get paid for their house in New York, how much they could save out of it after purchasing a house in Reno, what they could do with it, and how much income they could make per month after investing that saving. Arcadia Data helps Michelle help her clients make educated decisions and gain their trust.