![\"\"](\"https:\/\/i0.wp.com\/aurimas.eu\/a\/wp-content\/uploads\/Screenshot-from-2021-04-29-23-26-00.png\")
<\/a>What exactly is a football field chart? It effectively is a column\/bar chart where the individual bars are used to indicate range (and thus start\/end in arbitrary places). Most examples on the internet relate to valuation ranges. In our case, the idea was to display contracts over time as discreet bars, varying the bar width with contract value changes.<\/p>\n\n\n\n We were planning to deliver our insights via Power BI reports, so my immediate reaction was to look whether we could create these sort of charts using Power BI’s R visuals<\/a> and ggplot2<\/a>. Turns out, ggplot2 includes exactly what we needed in this scenario. Its geom_tile() \/ geom_rect()<\/kbd><\/a> allow drawing arbitrary rectangles. If you prefer Python, the ggplot2-inspired plotnine package<\/a> supports the same geoms, and, admittedly has way more impressive examples.<\/p>\n\n\n\n For the purpose of this blog post, I generated some synthetic data representing a real estate portfolio in Germany. In case you are interested in how it was created, check out the python script with details<\/a> (it was a lot of fun to play around with various probability distributions, I may write up a blog post about that separately one day). <\/p>\n\n\n\n We effectively had: 1) a list of contracts 2) a list of terms associated with each contract and 3) a monthly rental portfolio data set derived from the contract\/term information. <\/p>\n\n\n\n Getting to the desired visualization was surprisingly easy. First, let’s select top 10 contracts by their future value (you don’t really want to visualize all contracts at once unless you intend to print it on A0 size paper..)<\/p>\n\n\n Then, we can proceed to get the first iteration of the chart. As geom_tile() requires “central” positions and width\/height of the rectangles, let’s calculate the mid-period of each term to be used as the x-axis position, width will be equal to number of days, and height to monthly rent. Let’s try with one contract.<\/p>\n\n\n Seems like we’re on the right track, even if not beautiful. We can fix that later. Let’s try with 10 contracts.<\/p>\n\n\n\n Right. That’s not that great. While initially I thought that R, for some reason, places each series on top of each other (i.e. ignores Y-axis parameter), I later realized the real issue was bar heights. They needed to be re-scaled. Here’s a fixed version where each bar height is scaled to maximum of 1 (and some formatting tweaks).<\/p>\n\n\n Almost perfect. Just need to add labels that indicate total value and sort the contracts from the highest value to the lowest. Here’s the final code that achieves all that.<\/p>\n\n\n Edit: I<\/em> received a comment (thanks Q!) that the chart may look more intuitive if there is a shadow behind each bar indicating the maximum monthly value on the chart for reference. Here is how that could look like (code-wise, that is just another geom_tile() layer).<\/p>\n\n\n\n Power BI’s R visual integration is incredibly simple. Effectively, all you need to do is to use their built-in R visual, drop in the fields that you want to have available as part of the dataframe in R (Power BI does all the aggregation for you, it’s effectively passing the same dataset as if it was a matrix visual) and then adjust R code accordingly (some of the data transforms were no longer required). <\/p>\n\n\n\n While not applicable in this case, a couple of caveats about R Visuals in Power BI:<\/p>\n\n\n\n Combine it with a dynamic TopN selection<\/a>, and voil\u00e0. Click to see it in action.<\/p>\n\n\n\n<\/a><\/a>
Credit: Plotnine geom_tile() documentation<\/a><\/figcaption><\/figure>\n\n\n\nGetting to the right visual in R<\/h3>\n\n\n\n
<\/a><\/figure><\/li><\/a><\/figure><\/li><\/ul>\nlibrary(tidyverse)\nlibrary(data.table)\nlibrary(RColorBrewer)\n\n#read in the table that contains individual contract terms with associated start\/end dates\nterms = fread("~\/coding\/real-estate\/terms.csv")\n\n#read in the table that contains monthly information for each term (SQM \/ rent income in a month)\n#add proper year\/month columns to the dataframe\nPL = fread("~\/coding\/real-estate\/monthly_data.csv") %>% \n mutate(date = as.Date(strptime(paste0(month,"-01"), format="%Y-%m-%d"))) %>%\n mutate(year = year(date), month = month(date))\n\n#calculate future value per contract (assuming we're at the end of 2020)\n# filter it down to top10 contracts only\ntop_10_contracts = PL %>% filter(date > "2020-12-31") %>% \n inner_join(terms, by='term_id') %>%\n inner_join(contracts, by="contract_id") %>% \n group_by(contract_id) %>% summarize(future_value = sum(rent), .groups='drop_last') %>% \n arrange(desc(future_value)) %>% head(10)\n<\/pre><\/div>\n\n\n\nchart_df = \n #filter to 1 contract\n top_10_contracts[1,] %>% \n #get terms of contract\n inner_join(terms, by="contract_id") %>% \n #calculate # of days of each term\n mutate(width = as.numeric(difftime(end, start, units="days"))) %>% \n #calculate the mid-point of each term\n mutate(mid_point = start + width\/2) %>% \n #extensions are numeric, make sure they are treated as discrete variables\n mutate(extension = as.factor(extension)) \n\nggplot(chart_df) + \n geom_tile(aes(\n x=mid_point, \n y=contract_id, \n width=width, \n height=monthly_rent, \n fill=extension\n ))\n<\/pre><\/div>\n\n\n
<\/a><\/a>\nggplot(chart_df) + \n geom_tile(aes(\n x=mid_point, \n y=contract_id, \n width=width, \n height=monthly_rent\/max(monthly_rent), #that's the fix\n fill=extension)\n ) +\n ylab("") + xlab("Year") + theme_bw() + \n theme(panel.grid.minor.y = element_blank(), panel.grid.major.y = element_blank())\n<\/pre><\/div>\n\n\n<\/a>\nchart_df = \n top_10_contracts %>%\n #get terms of top 10 contracts\n inner_join(terms, by="contract_id") %>% \n #calculate # of days of each term\n mutate(width = as.numeric(difftime(end, start, units="days"))) %>% \n #calculate the mid-point of each term\n mutate(mid_point = start + width\/2) %>% \n #extensions are numeric, make sure they are treated as discrete variables\n mutate(extension = as.factor(extension)) %>% \n #reorder contracts\n mutate(contract_id = reorder(as.character(contract_id), future_value, mean)) \n\nlabels = \n #group by contract\n chart_df %>% group_by(contract_id) %>% \n #calculate total value and get end date\n summarize(end_date = max(end), value = max(future_value), .groups="drop_last") %>% \n #format total value\n mutate(label = paste0(round(value \/ 1000000, 1), "M")) \n\nggplot(chart_df) + \n #create rectangle geoms\n geom_tile(aes(\n x=mid_point, \n y=contract_id, \n width=width, \n height=monthly_rent\/max(monthly_rent), \n fill=extension\n )) + \n #deal with axis labels\n ylab("") + xlab("Year") + theme_bw() + \n #remove gridlines on Y-axis\n theme(panel.grid.minor.y = element_blank(), panel.grid.major.y = element_blank()) +\n #add labels at the end of each contract bar\n geom_label(data = labels, mapping=aes(end_date + 400, contract_id, label=label)) +\n #apply a better color palette\n scale_fill_brewer(palette = "RdYlGn")\n<\/pre><\/div>\n\n\n<\/a><\/figure>\n\n\n\nPutting it all together in Power BI<\/h3>\n\n\n\n
<\/a>