Flat rent price prediction in Berlin with web scraping

The production of modern official statistics is based on a system of scientific methods, regulations, codes, practices, ethical principles, and institutional settings that was developed in a pre-digital world, where surveys and administrative registers were the most important data sources for decades. Data in those times were a luxury and its acquisition was very costly. This means that most of the system was designed with this kind of data source in mind (Ricciato et al. 2020).

Nowadays, with the digitalization of society, there are new digital data sources available for official statistics that offer improved timeliness, accuracy, finer temporal and spatial resolution, more detail, increased relevance, possibly lower production costs, optimal statistics production and also competition with private players that provide statistics from a different outlook using this kind of data. This implies a challenge for official statistics since it has to adapt to these “new digital data”.

The ESSnet project Trusted Smart Statistics – Web Intelligence Network is an evolution of activities from the former ESSnet Big Data I and II projects (2016–2020), aiming to generate multi-purpose statistics enhancing the integration of web data into official statistics through a comprehensive, coordinated approach rather than isolated efforts by individual National Statistical Institutes. Key objectives include increasing knowledge and competencies in web intelligence, advancing developments in specific domains for statistical production integration, identifying new web data sources for potential integration, and developing a robust business architecture, along with methodological and quality frameworks equipped with quantitative quality indicators for producing statistics with web data. Work Package 3 of the project focuses on investigating the potential of novel web data sources and the generation of experimental statistics derived from these sources (European Commission 2024). This article presents findings from a subsidiary research effort within the framework of Work Package 3 of the project.

While the importance of the real estate market on macro-economic indicators is well researched (Agnello and Schuknecht 2011; Catte et al. 2004; André and Girouard 2009; Leamer 2007; Bergenstrahle 2016), the rental market has received less attention. Despite this fact, its importance and relevance in the macro- and microeconomic context has been pointed out by some researchers (Arregui et al. 2009; Arce and López-Salido 2011; Caldera and Andrews 2011; Kofner 2014; Bergenstrahle 2016; Czerniak and Rubaszek 2018; Rubaszek and Rubio 2020) which is highly related to the objectives of official statistics where the rent prices are covered in the consumer price index analysis. Recently Steorts et al. (2020) have used online flat prices to analyze regional price differentials at the level of neighborhoods. In this context, data from official statistics could not deliver the regional information at this low regional level. In Germany the only source from official statistics with detailed information on flats is the Microcensus subsample on accommodations which is repeated every four years, see Frink and Rendtel (2019). Schmandt (2021) used this information on housing together with information on income for an analysis of the affordability of flat prices. As the share of the flat rate on the disposable household income can amount up to 50 percent (Schmandt 2021) flat rates are of general public interest. In Germany there are official local rent indices, the so-called “Mietspiegel”, which measure regional flat rates at a city level, see Sebastian and Memis (2021). Based on survey selection they are updated every two years. The quality of the Berlin rent index has been reviewed by Frink and Rendtel (2019).

However, the rent indices mainly address existing rent contracts and exclude newly built accommodations by law. Thus, there is no up-to-date data source that monitors the market for new contracts. Here online advertisements of flats offer a chance to fill this gap.

In this study, we will investigate this source for the Berlin rental market. We will scrape online offers from two online portals. Our aim is to predict the price per square meter given the relevant characteristics of the accommodation, including its location in the town. We will compare these flat rates with the data from the actual Berlin rent index.

In this article, we document the necessary steps to achieve this goal: The scraping of online offers from the two portals (Sect. 2), the preprocessing of the rent data (Sect. 3), the identification of flat offers that appear on both platforms and the necessity to de-duplicate these offers (Sect. 4), the preparation of the merged data set (Sect. 5), the treatment of missing values by multiple imputation (Sect. 6) and, finally, the presentation of the semi-parametric regression model for the flat rate per square meter (Sect. 7). In the conclusions (Sect. 8) we summarize and point to some remaining problems, like the representativeness of data from the portals, and possible measurement problems that may occur by confusing the different components of flat costs.

Readers who are interested in the details of our work and the programming code will find a link to a Jupyter Notebook of our analyses in the appendix.

Two of the largest real estate portals in Germany were selected to be scraped: Immowelt and Immonet. A web scraper for each portal was developed using Scrapy which is a Python application framework for writing web spiders that crawl websites and extract structured data from them (Scrapy developers 2022a). For this task, configuring and defining spiders that navigate through the portals is necessary. Spiders are programs, called classes, that define how a certain page or group of pages will be scraped, how to follow the links (crawl), and how to extract and parse structured data from their pages. In other words, the whole process is set up by them.

The general logic of the procedure is as follows: Suppose a data analyst is in front of their computer and wants to explore and extract offers from an online real estate portal. The first task is to start their search on the portal by fixing all the criteria for the search in the search menu. Then they are redirected to a page that shows a list of online offers according to their search query. This URL is the first page the scraper will visit. Then they start to scroll the web page and create a table where they store the ID of the offer, the name, some general information, and the URL which leads to the description of each specific offer, the so-called exposé. After scrolling down the whole page, the analyst notices that there are still more offers available on the following pages. Therefore, they proceed to the next page and repeat the same procedure until they have read the last offer on the last page. This is the job of the first group of spiders, which is in charge of extracting and exporting all the available offers to a list in CSV format. Additionally, a check for repeated offers that should be excluded from the list is performed.

At this stage, the analyst has a list with each offer and the corresponding URLs to the corresponding exposés. Now, their next step is to extract the specific information they need from each exposé. Here, they explore the description of the offer and collect the available relevant information. Then they select and copy this information, which is then added to a new table that will contain the specific information of all offers. They clean and format the obtained data according to their needs. Some basic examples and a step-by-step guide for beginners can be found in the official tutorial of the Scrapy package (Scrapy developers 2022b).

Since both portals are well structured, it was relatively easy to use selectors that locate and extract the relevant information. For this purpose, the inspector view of the web browser Google Chrome allows one to check and inspect the chunks of code and their hierarchy. Immowelt offers the advantage by granting access to an API that retrieves the whole information of the exposé as a JSON file through the source code of the page. This was not the case for Immonet, where each attribute had to be localized separately. This also necessitated a preliminary removal of some symbols embedded in the HTML code.

The data were scraped between May and October of 2022. While the information from Immonet was scraped on a weekly basis, the offers from Immowelt were extracted on a daily basis. In our analysis, we did not consider the time stamp of the offer. However, if available, this kind of information can be helpful to identify duplicates of offers on different portals.

The scraped data must be prepared to be used for later analysis. Each dataset representing a real estate portal was preprocessed separately. At this stage, the biggest challenge was to establish the comparability of the data across the portals.

The two portals present their offers in their own specific manner. This implies that the structure, data presentation, and method of data input can vary between different portals. While one portal has standardized the information rigidly, the other allows more flexibility at the moment a new offer is published.

While Immonet is more strict with the type and values that variables can take, Immowelt is the opposite case. The latter portal did not restrict the phrasing of some string variables. As a consequence, different string combinations could have the same meaning. For example, the deposit for the flat rate was reported in some cases as three times the monthly rent price, while in other cases, it was reported in Euro.

A list of the scraped variables can be seen in Table 12. Additionally, the corresponding step-by-step preprocessing of each portal with an overall diagnostic analysis can be found in the internet supplement (Jupyter Notebook, Appendix A and B). Here, the empirical distributions of the two portals are quite similar to each other.

Even if there was a relatively high number of offers, the number of offers that were useful for the final model was as low as 43% (Immowelt) or 66% (Immonet) of the total available cases.

Duplicates were also considered at this stage. In this context, there are two types of duplicates. The first kind of duplicates are flats that are published on the portal repeatedly with the same information under unique identification numbers. The second type are offers that are published with minor changes and updates of their attributes while the rest remains constant. However, for some reason, they receive a new identification number. The first type of duplicate is easy to filter. Hence, these were removed, and only the latest duplicate offer was retained. However, the second type is not easy to identify. Using record linkage (See Sect. 4 below) in one of the datasets, it was observed that the proportion of these cases is not higher than 5% of the number of offers. Keeping these offers in the dataset and regarding them as new offers should not have a major impact on the estimation of statistical models and their inference.

The different portals may attract different flat offers. As we are interested in an analysis of the flat rate, we compare the average of the flat rates across the two portals. Table 1 states a substantial difference in the mean flat rate of the portals. Here the Immonet flats are on average 3.13 Euro more expensive than the flats on the Immowelt portal. We conclude that one should scrape from both portals in order to avoid selective effects with respect to the flat rate.

As we collect offers from the two portals, it may happen that one flat is offered on both platforms. If we want to analyze the data from both portals, we have to merge the separate datasets and determine the duplicates that occur in both portals. At this step, it is important to identify and de-duplicate the offers from the joint database. The identification of duplicates is relevant for better estimations of the size of the online market as well as to reduce a potential bias that might appear in the statistical analysis of rent prices. Here we conjecture that offers above average need more advertising. Because of the high methodological impact of de-duplication, which is documented in the subsequent sections, we already present the empirical result of the de-duplication in Table 2. Approximately one-third of the 2950 analyzed offers appear in both portals, and the duplicate offers are significantly more expensive, averaging 14.52 Euros per \(\text{m}^{2}\) compared to 13.14 Euros per \(\text{m}^{2}\) for offers on only one platform.

Therefore deduplication is a necessary step to achieve more reliable results for the online flat market. In the subsequent sections, we use record linkage techniques to identify identical flats in different portals.

The term “record linkage” refers to the procedure of linking records of two or more different sources that are believed to correspond to each other. It can also be used to identify duplicates. The idea behind this concept is to use attributes that characterize an entity. These attributes are then used to link units in different records. The linkage procedure can be represented as a workflow (Christen 2012) that consists of the following phases: Preprocessing, Indexing, Comparing, Classification, and Evaluation. The Record Linkage Toolkit is a Python package that has been developed with the essential tools for record linkage and de-duplication and also allows for customized procedures (de Bruin 2022c).

The output of the classification model is a list in which each element represents a comparison pair that was labeled as a match. Therefore, each element contains the IDs of the two offers forming the comparison pair. This is the basis of a consolidated dataset of the two portals.

Each real estate portal has its own set of variables, and those are defined according to the rules and formats of the respective online portals. Some variables have different names and attributes, and some that are present in one portal are missing in the other. Hence, if a merged dataset is going to be built, it should not be dominated by the information from only one of the two data sources. Furthermore, variables not common between the portals may lead to missing values, depending on the information available from the other portal. Additionally, common variables need to be synchronized. The common variables were identified, and renamed, and their attributes were standardized to ensure they conveyed the same meaning. In both datasets, there were groups of dummy variables related to specific features of the flat. For example, the presence of a garden, a balcony, or a terrace describes outdoor facilities that the flat might have. For these nominal variables with differing values, specific dummy variables were created to have identical meanings across both datasets. For example, a portal may mention the presence of a balcony and a loggia while the other just mentions the presence of a balcony. Thus, they can be combined into a single variable indicating the presence of either a balcony or a loggia.

After such synchronization, we have to decide which observation enters the merged dataset. For this purpose, we compute for each pair the number of variables with valid information. The offer from the portal with the higher number of valid information is chosen. However, before dismissing the less informative offer, we checked the possibility of augmenting information on some variables from the less informative offer to the more informative offer. We had also to consider the case that an offer appears in more than one pair. Here we used an ad hoc rule by keeping the newest offer.

The merged data frame contained originally 74 variables (See Table 12 and Juypter Notebook Appendix C.2 and D.2). To be able to come up with a smaller set of plausible and relevant variables some preprocessing steps were performed. In this procedure all variables with more than 50% of missingness, unbalanced categorical variables, and variables with possible multi-collinearity problems were removed.

Specifically, we mention the case of the variable “Deposit for the Rent”. The variable is an excellent predictor for the rent. So, every automatic variable search routine would use the Deposit variable to predict the rent. However, from subject matter knowledge it is known that the deposit is approximately equal to two to three times the monthly rent. This creates a substantial collinearity in the estimation of the regression coefficients for the flat rate per \(\text{m}^{2}\). Thus results of automatic variable selection routines should be checked against subject matter knowledge.

Our routine left only eight variables for further analysis: Utility costs, Heating costs, number of rooms, living area, construction year, central heating, use of gas, and use of district heating. The whole process of variable screening can be seen in detail in the internet supplement (Jupyter Notebook Appendix C.2 and D.2).

As the flat offers are based on free-formulated texts, there occurs non-response with a higher rate than in a structured questionnaire. This missingness in the data must be accounted for and corrected to yield valid inferences from the observed data. Here, we use multiple imputation to cope with missingness (van Buuren 2018).

Multiple imputation creates \(m\) versions of imputed datasets by replacing the missing values with possible plausible values. The plausible values are drawn from a modeled distribution for each specific entry or variable. Each imputed dataset is different concerning the imputed values reflecting the uncertainty inherent to them. After the imputation, we estimate the parameters of interest from each imputed dataset and pool them into a single estimate using Rubin’s rules (Little and Rubin 2019). The variance of the estimate combines the sampling variance (within-imputation variance) and the additional variance caused by the missing data (between-imputation variance). Under appropriate conditions, the pooled estimators are unbiased and have the correct statistical properties (Little and Rubin 2019).

The MICE (Multiple Imputation Conditional Expectation) Package generates sequentially multiple imputations for missing values of the basic variables and their transformations, see van Buuren (2018). It starts with a prediction of the variable with the lowest rate of missingness. Its missing values are predicted using the variables that are complete. The type of prediction depends on the type of the variable. At this stage single imputation methods are used, see Table 10.

So step by step advancing to variables with increasing rate of missingness all missing values are imputed which marks the end of the initial phase of the MICE algorithm. Then the procedure is repeated by using the most recent imputations as predictors. These steps are repeated 50 times. Every 10th realization is then used for imputation, leading to a total of m=5 multiple imputations.

To check the quality of the imputations among the five imputed datasets, a density plot was generated for each variable as shown in Fig. 9. The blue lines represent the distribution of the observed data for each variable and the red lines represent the distribution of the imputed data for each version of the dataset. It can be seen that the distribution of the imputed data closely represents the distribution of the observed data in most cases. However, in the case of flat rates with only 6 percent of missing values, the distribution of the imputed data is shifted to the right of the observed values. We conclude that missing values appear more frequently for flats with above-average prices.

Bild vergrößern

Here we first start with a variable selection procedure in a standard regression setting, an approach that is straightforward to implement. With the selected variables we switch to more demanding regression models that include geographical information.

Web scraping has proven to be a powerful tool for the collection of a huge amount of data from different internet portals. However, one should be careful with the obtained data. Depending on the source, the information can be structured or unstructured which can cause further effort to develop an adequate scraper.

The production of a prediction model for flat rates using web scraping from different information sources was possible thanks to an assembly of a set of interconnected steps with a specific objective along the whole process: A record linkage model to consolidate information from different sources, a multiple imputation model to account for the uncertainty caused by the missing data, statistical methods for variable selection that are included in the final model and finally, a structured additive model that allows accounting for the spatial effect of the postal codes of the offers and gives the flexibility of a semi-parametric model. This is a long lane that started from very technical issues of web scraping and ended with the use of advanced statistical routines. Needless to say a pure technical approach or an automatic treatment will not succeed in a satisfactory result.

Besides, we have encountered some problems with the representativeness of our results which are worth mentioning.

We could show that scraping data from the two portals, Immowelt and Immonet, leads to a substantial over-estimation of the number of offers. Even more intriguing is the fact that the offers that appear in both portals are more expensive than offers that appear only in one portal.

This might advocate for the use of only one portal. In this case, one could save the de-duplication via statistical matching. This might also reduce the missing data problem to some extent which results from different rules to document the flat offer. On the other hand, the omission of an important portal will under-count the number of flat offers. A major point of concern is whether the restriction to one portal is selective with respect to the estimation of a model for the flat rate. In the case of the two portals of our analysis, we found considerable differences with respect to the average flat rate. Hence we recommend scraping from both portals and also de-duplication of joint offers.

Such issues open the general question of the representativeness of results from online portals. In the context of flat offers, there will be probably low-price offers that do not appear in the Internet. Or they are offered in the internet but on portals that are small or not open for scraping. On the other hand, if we scrape more than two portals, the de-duplication or even de-triplification can become a challenging task.

Nevertheless, similar problems occur also in other contexts, for example, the scraping of job offers. As long as the population frame is not fixed, the problem of representativeness cannot be addressed. Since the entire market of local flat offers is hard to observe it might be regarded as sufficient to restrict the population frame to “scrapable” online portals.

More attention should be also spent on the sampling scheme from the portal. If we sample all offers at a given point in time then we will miss those offers which stay only for a short time interval on the Internet. This is mainly due to the fact that low-cost offers quickly find a tenant and were removed from the portal. Thus the more expensive offers remain on the Internet longer and are therefore over-represented by scraping the stock of offers, see Kauermann et al. (2021) on page 308 for an example. Such selective effects can be only avoided by a daily scraping of new offers. Still, with respect to the before-mentioned difficulties of de-duplication, the daily selection of new offers may be too tedious.

A topic that was not discussed here is the presence of measurement errors in data from online portals. The paid rent consists of three components: the costs for heating, the utility costs, and the share for the landlord (In German: “Nettokaltmiete”). Only the last component is relevant for rent indices. Nonetheless, it may happen that the total rent or the rent plus the utility costs are given in the exposé. It depends on the instructions of the portal and how well these components are separated. Thus, there is the tendency that the flat rates from the online exposés over-estimate the true rent. Here the questionnaire-based surveys of the local rent index and the microcensus may deliver more accurate results.

Our prediction model indicates substantial and plausible regional effects on the flat rate. These effects are in line with corresponding Berlin results from the German microcensus in Frink and Rendtel (2019).

As we have shown, the online market for flats is quite different from the stock of all rent contracts which is the focus of local rent indices. The online offers are concentrated on newly-built houses in outskirt areas of Berlin. Also, the price level is far above the Berlin rent index. This raises doubts about the ability of the local rent index to judge a flat rate of a new contract as “fair”.

Despite these problems, rent price models derived from online offers can be a helpful tool for official statistics to monitor the temporal development of flat rates. Here, the 4‑year time interval of the German microcensus is too long to keep track of the actual rapid increase of flat rates in Germany.