Solving the Charts Challenge

Facebook ShareLinkedin ShareReddit ShareTwitter Share

In Eliot’s MongoDB World Weekly Challenge - Week Two, you were set a task which involved using MongoDB Charts to answer some questions around where to send and house MongoDB engineers. Charts is a great way to visualize data, so even if you didn't do the challenge (there's one every Wednesday up to MongoDB World), you can still be a winner because we're going to show you how to get the solution.

Question 1 - The Property Count Question

Remember we want our Engineers to stay in the most popular, populated market. To that end, use MongoDB Charts to analyze the data to determine which market (address.market field) has the largest number of properties listed in the sample dataset. Create a chart showing your analysis and provide a link to this chart using the embed code feature of Charts.

Well, for this and most of the other questions, we’ll use a horizontal bar graph. If we drag the address.market field over to the Y axis and set it to sort by value, we’ll bucket the results up into the separate markets. Now we want to count the number of properties; well each document represents a property, so let’s count the number of documents by counting the field that’s definitely going to be in the document, _id. Drag that field over to the X axis in Charts and set the aggregate function to count and you should see this.

Q1: The Property Count Solution

And we can see Istanbul topping the charts. This is the correct answer; in this sample data set, Istanbul has the most properties.

Question 2 - Treehouses or Castles?

MongoDB Engineers typically like to stay in either Tree Houses or Castles. On average, does it cost more to stay in a treehouse or a castle? To solve this, you will probably want to use the mean value of the property_type and price fields in your chart.

This should be fairly simple. For our X axis, we’ll drag over our price field and set the aggregation function to Mean. Then, we’ll drag the property_type over to the Y axis and sort that by Category. That gives us this:

Q2: Treehouses or Castles - First Solution

But that isn’t the answer to the question. If you hover over the chart in the right places you can find the answer, but that’s not a good visualization. We are only interested in Treehouses and Castles, so let’s filter the data using a MongoDB query in the Filters field. Specifically:

{ property_type: { $in: [ 'Treehouse', 'Castle'] } }

With this filter in place, things are a lot easier to read.

Q2: Treehouses or Castles - Solution

And we can see that Treehouses, with a mean price of 185 cost more than Castles with a mean price of 127.

Question 3 - Amiable Amenities?

We also like to ensure that our Engineers stay where they have the most amenities. In which market (address.market field) do properties have the highest average number of amenities (amenities)?

The first part of this goes back to the first question; we want to analyze by market so we drag address.market field over to the Y axis and set it to sort by value. Next we need the number of amenities for each property. Well, there’s no field with the number as amenities are in an array, but we can count the number of elements in the array. Drag amenities onto the X axis and you are offered “Array Reductions”, ways to extract data from the array. Select Array Length and then select the aggregate function Mean. This should give us this chart:

Q3: Amiable Amenities Solution

And we can conclude that Maui is the place with the highest average number of amenities to keep the engineers occupied.

Question 4 - Review Season

Of the properties with at least one review (number_of_reviews), what month and year had the highest number of first reviews (first_review field)?

For a change, let’s use a grouped Column view this time. This is another question where a filter helps; just a simple one:

{ number_of_reviews: { $gt: 0 } } 

This will pick up only properties where the number of reviews is greater than one. Now we can use the first_review field. Drag that to the X axis, and because we want this data by the month, turn binning on and set it to Monthly. We are now looking for a count of those first reviews, so let’s count the _id of the documents. Drag the _id field to the Y axis and set it to aggregate by Count. That gives us this chart:

Q4: Review Season Solution

And if we go to the peak of the chart, we see that the answer is July 2018.

Question 5 - Maximum Bedrooms?

We have a lot of Engineers these days and we want to try to get them all in the same home. Help us out and find out what is the maximum number of bedrooms (bedrooms field) of any property in the dataset?’

This is notionally a simple question. We’re interested in which property has the most bedrooms. Start with a bar chart and drag the _id field to the Y axis, sorting by value, and drag the bedrooms field to the X axis, aggregating it by Sum (or Max, it doesn’t matter, we’re dealing with one property each time). You’ll get a chart like this:

Q5: Maximum Bedrooms - Opening Solution

Well, there’s a result there. You can see that this outlier has 20 bedrooms. You can only deduce that from the axis though. There must be a better way to do this chart and there is. Flip the X axis’s aggregate to Max, we are going to look for the maximum of a category instead of individual properties. What category? One we used previously; property_type is suitably granular and should give up some extra insight. Drag the property_type field to the Y axis and now we have this chart.

Q5: Maximum Bedrooms - Clear Solution

We can hover over the leading bar and see the maximum bedrooms in the Boutique Hotel category is 20. Same answer but now with a much more interesting and useful visualization.

Displaying the Results

Most of the details for displaying the results in the Challenge are covered in the challenge instructions. The HTML page you would create would, at its simplest look something like this.

<html>
  <head>
    <title>
      Solving Charts
    </title>
  </head>
  <body>
    <h1>Solving Charts</h1>
    <h2>Q1</h2>
    <iframe style="border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640"
      height="480" src="https://charts.mongodb.com/charts-sampledata-kedqw/embed/charts?id=aa829192-a2f2-44a6-8140-f61d5be88a6d&tenant=b293159e-d17f-4ed9-bc83-70dcbdd8e7b6"></iframe>
    <h2>Q2</h2>
    <iframe style="border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640" height="480" src="https://charts.mongodb.com/charts-sampledata-kedqw/embed/charts?id=64aaf6e4-0a8a-4afb-a3a3-64ed64d11b1c&tenant=b293159e-d17f-4ed9-bc83-70dcbdd8e7b6"></iframe>
    <h2>Q3</h2>
    <iframe style="border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640" height="480" src="https://charts.mongodb.com/charts-sampledata-kedqw/embed/charts?id=fe50fb2b-5fd3-4b8e-99f4-c94c3398feef&tenant=b293159e-d17f-4ed9-bc83-70dcbdd8e7b6"></iframe>
    <h2>Q4</h2>
    <iframe style="border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640" height="480" src="https://charts.mongodb.com/charts-sampledata-kedqw/embed/charts?id=72cf805f-6946-4828-9d39-b26dddf564e5&tenant=b293159e-d17f-4ed9-bc83-70dcbdd8e7b6"></iframe>
    <h2>Q5</h2>
    <iframe style="border: none;border-radius: 2px;box-shadow: 0 2px 10px 0 rgba(70, 76, 79, .2);" width="640" height="480" src="https://charts.mongodb.com/charts-sampledata-kedqw/embed/charts?id=0c651118-7a8c-4547-a769-ee53708ca007&tenant=b293159e-d17f-4ed9-bc83-70dcbdd8e7b6"></iframe>
  </body>
</html>

The <iframe> data is simply copy and pasted from the MongoDB Charts application.

Wrapping Up

That's it for the solution to the Charts Challenge. As you can see, MongoDB Charts is a great way to explore your data visually. The next challenge in Eliot's Weekly MongoDB World Challenge is coming on Wednesday 15th of May and every Wednesday up to MongoDB World. Join in, up your MongoDB skills and you could win a prize.