Analysis of hydrological data with correlation matrices: technical implementation and possible applications

For the examples shown herein, we obtained time series from the ehyd.gv.at platform, which provides the data of Austrian groundwater, surface water and precipitation measurement stations (BMLFUW 2016). A detailed description of this platform can be found in Haas and Birk (2017). As mentioned in the “Introduction” section, there are other possible sources for data and the tools provided herein can be adapted to other data sources or CSV files structured differently.

For the ehyd data, a module that reads in the CSV files is provided in the supplementary material. While the ehyd data are quality controlled (see, e.g., Godina 2000; Müller 2006; BMLFUW 2017), it is important to note that some of the time series do have gaps. Thus, we have build a simple error handling into the module.

Time series that are missing time information get discarded, as do time series that contain more than 10% of data flagged as erroneous in the original files and time series that contain more than three consecutive errors. In cases less severe than those, the module simply pads missing data with the previous water level or precipitation amount. We deem this an acceptable workaround for the low number of affected time series [less than 10% of the data used in Haas and Birk (2017) for example], but for larger issues it is upon the user to assess a more appropriate way to deal with missing data.

As can be seen in the files provided in the supplementary material, there is considerable preprocessing, to get the files into a format usable within python due to some intricacies of the German language, such as the replacement of umlauts. Thus, we also provide a simple CSV reader that can serve as a base for the user to adapt the code to their data at hand. After this preprocessing, the header can be used to obtain information about the time series and to store it in a way that can later be queried and used.

The pandas package (McKinney 2010) is used to import and export the data. For storage and handling of the data, we use a pandas dataframe, which is “a 2-dimensional labeled data structure with columns of potentially different types” (pydata.org 2017) and thus similar to a spreadsheet. This similarity also carries over to their ability to hold multiple index levels and the ability to select from these indices.

Thanks to its similarity with spreadsheets, dataframes can be easily exported to and imported from spreadsheets, enabling easy exchange with other programs, such as the ones mentioned in the “Introduction” section. Thus, we export the standardized dataframe to a CSV file, to have a human- and machine- readable intermediate result that can serve as a basis for further work. Also, we export a Hierarchical Data Format—HDF/H5 file, which is a much smaller, binary file, very well documented and standardized (see hdfgroup.org 2017) and with bindings to multiple tools and languages, ranging from Fortran to MATLAB. Compared to the above mentioned CSV file, however, reading a HDF file requires more involvement for users unfamiliar with it.

This intermediate step of exporting a dataframe could also be forgone by combining the provided python modules into one, and thus keeping the dataframe in memory, but we prefer this twofold approach, since this allows for an easier change or substitution of the means used to build or analyze said dataframe.

The comparison and interpretation of monitoring data from hydrometeorological gauging stations and observation wells situated in different regions need to account for the differences in climatological, hydrological or hydrogeological conditions. For the example of a weather station, the average precipitation for a particular month will be different from one location to another and from one month to another. It is thus of interest to observe if a particular month (or season) in one location is drier or wetter than normal. By standardizing the data, we can compare on the same scale two stations that may experience a monthly average of 100 and 200 mm of precipitation, respectively. For the example of a groundwater measurement well, the standardized data would allow us to compare on the same scale two wells that show an average groundwater level of 500 and 200 m asl, respectively. Therefore, comparisons can readily be made between both stations of the same type but at different locations. The standardization also allows comparisons between different types of stations, e.g., groundwater levels measured in meters and precipitation measured in millimeters on the same scale. However, it should be noted that this lack of real measurements can also be misleading. At first sight, one might assume that for example two stations with an SPI value of 0.5 do experience the same amount of precipitation, while the actual precipitation amounts observed could vary greatly.

To standardize the data, we use different variations on the same method corresponding to the different components of the water cycle. The precipitation data are standardized using the SPI (McKee et al. 1993), the groundwater levels are standardized using the SGI (Bloomfield and Marchant 2013), the river stages are standardized using the SRSI (Haas and Birk 2017, SGI applied to river stages) and the climatic water balance is standardized using the SPEI (Vicente-Serrano et al. 2010), in each case using the standardization procedure proposed in the original literature.

It has to be noted that there is some disagreement on the feasibility of the gamma distribution used for the SPI (e.g., Guttman 1999; Blain and Meschiatti 2015). In general, however, it is deemed as an acceptable distribution, especially regarding its use for European data (Stagge et al. 2015b). The authors are not aware of any discussions regarding the adequacy of the nonparametric normal scores transform used for the SGI and SRSI, which we assume to be due to the relative recentness of the indices and the “self fitting” quality of the used method. Regarding the SPEI, the used method defaults to the log-logistic distribution but according to Stagge et al. (2015b) the generalized extreme value distribution might be slightly preferable when assessing European data.

Other indices or standardizations such as the Palmer Drought Index—PDI (Palmer 1965), the Standardized Streamflow Index—SSI (Vicente-Serrano et al. 2012) or many of the indices listed in Svoboda and Fuchs (2016) can also be implemented.

In the supplementary material, we provide python modules for the SPI, the SGI and the SRSI and a python module to use the published R-implementation of the SPEI (https://​CRAN.​R-project.​org/​package=​SPEI, Beguería et al. 2014) as well as a very simple and general standardization module using the z-scores approach, to quickly visualize all kinds of additional time series.

As previously mentioned in the “Standardization” section the standardization of data makes time series from different locations and/or different types comparable on the same scale. Additionally, it enables temporal averaging of surpluses (positive standardized values) or deficits (negative standardized values).

A main feature of the dataframe is the ability to easily construct a correlation matrix from it. This way, a Pearson correlation coefficient is obtained for every possible pairing of time series. However, since a dataset of n wells results in \(n^2\) entries (half of which duplicates) this can be hard to readily interpret. Hence, we color code the correlation coefficients to enable a quick overview over what would otherwise be a difficult to read, huge text file or spreadsheet.

In contrast to single correlation coefficients and their map representations, as for example shown with state-wide SEDI9 versus SPI9 correlations in Kim and Rhee (2016), the correlation matrix visualization allows an easy, clutterless visualization of multiple groundwater wells, with the additional possibility to correlate and compare them with area or region wide indices such as SPI, SPEI and similar indices. Thus, we provide a python module in the supplementary material that plots these correlation matrices.

If a map visualization of data is needed, pythons matplotlib package provides the “basemap” module (see Hunter 2007 and https://​matplotlib.​org/​basemap/​) which enables the straightforward plotting of data on a variety of generic maps. Additionally, it is able to tie into ESRI’s ArcGIS REST API, allowing for the use of ESRI maps. In the supplementary material, we provide a python module that can plot a dataset on a map.

One aspect only demonstrated tangentially by Haas and Birk (2017) is the ability to use the correlation matrix as a tool for data quality control and for the identification of one or several time series showing a behavior deviating from the others. Figure 2 shows the correlation matrix for the Aichfeld region in the Styrian Mur catchment adapted from Haas and Birk (2017), which is used to illustrate this aspect in more detail. As can be seen, most of the correlation matrix shows similar colors, indicating high correlations between the SGIs of the various time series, and thus a similar behavior of the underlying groundwater wells. However, there is also a distinct set of 5 wells that show low to negative correlations with everything else, but very high correlations with each other.

As discussed in Haas and Birk (2017), the average depth of the wells in the Aichfeld dataset is 13.5 m below ground level with a very high standard deviation of 8.5 m. Closer inspection of the underlying dataset reveals that this high standard deviation is caused by the five wells shown in Fig. 2, which have an average depth of \(\sim 25\) m bgl, compared with an average of \(\sim 9.5\) m bgl for the remaining 15 wells in the dataset. This difference in depth results in a different “behavior” of the groundwater and thus in low correlations of the different depth levels, suggesting that the two groups actually represent two distinct aquifers.

This different behavior is shown in Fig. 2b, where the average SGIs for the two sets of wells and the average for the complete dataset is shown. As can be seen the subset of deep wells does not affect the average too much, but this is only due to the limited number of deep wells in this example. For larger datasets, being able to identify differing datasets quickly could provide a valuable addition to more efficient quality control. Additionally, this option to calculate a representative average over a certain subset of time series enables other analysis options, such as trend analysis or a further way to compare different regions.

Haas and Birk (2017) further use the example of the construction of hydraulic structures in the Mur valley to demonstrate that the matrix visualization supports the identification of human impacts affecting the groundwater levels of some wells.

As also shown in Fig. 2, where artificial outliers (time series full of random data) have been included into the real data, those are clearly identifiable by their low, but not strongly negative, correlation with all of the real time series and with each other. “Semi outliers” such as the Danube gauging stations added into Fig. 2 seem to behave different again. While they do differ from most of the dataset, they still show moderate correlations with the river gauges in the area and some of the wells as well as noticeably lower correlations with the deep wells, making it hard to draw any distinct conclusions about those time series.

This demonstrates that correlation matrices allow one to identify time series—and thus mostly groundwater monitoring wells in the highlighted case—that deviate strongly from the average behavior in a region. However, this does not necessarily give an information about the reason for this deviation. Judging from our examples, estimates about the nature of the time series and thus the reason for the deviation in correlation can be made, however. Random time series appear to simply show a correlation coefficient close to zero, as expected due to their random nature. Time series of similar nature and location as the majority (e.g., the two Danube gauges inserted into Fig. 2) may still show minor correlation with many of the time series.

Standardizing data and plotting them in a correlation matrix allow for a comparison of different types of data, e.g., groundwater levels (SGI) and precipitation (SPI). As Haas and Birk (2017) have shown, generally shallow groundwater is highest correlated with the 6 month SPI and the most so in a foreland basin, whereas highest correlations of groundwater with surface water are seen in a narrow valley.

This comparison can be extended to other types of indices and other types of data as mentioned in the “Standardization” section. In Fig. 2, we show the addition of the Standardized Precipitation Evapotranspiration Index—SPEI (Vicente-Serrano et al. 2010). While the SPI is a purely precipitation-based drought index, the SPEI additionally includes a temperature-based estimate of potential evapotranspiration. One may expect that this allows a more appropriate representation of drought, particular under climatic conditions where a substantial part of precipitation is lost by evapotranspiration. Indeed, Kingston et al. (2015) found that a higher number of European droughts was identified by the SPEI than by the SPI. Likewise, Bachmair et al. (2015) found higher correlations for SPEI than for SPI. The findings by Bachmair et al. (2015), Stagge et al. (2015a) and Blauhut et al. (2016) similarly suggest that SPEI tends to be a better predictor of drought impacts than SPI, but a combination of different aggregation periods and indices generally is considered most favorable. In the given case, however, the behavior of the SPEI only minimally deviates from the SPI, as can be seen from the high correlation between the two indices (see Fig. 2d). As a consequence, the SPI-SGI and the SPEI-SGI correlations for the Aichfeld are also very similar, and the SPI even exhibits a tendency toward slightly higher correlations with SGI, which suggests that the impact of evapotranspiration is minor in this humid Alpine setting. This finding agrees well with the results from a more comprehensive study for the Netherlands and Germany, where correlations between SGI and either SPI or SPEI were very similar (Van Loon et al. 2017). While the relationship between SPEI, SPI, and SGI thus deserves further investigation, particularly in more arid settings and on a regional level, as opposed to the country-wide assessment done in Stagge et al. (2015a), these examples demonstrate how the visualization of correlation matrices implemented in our Python code supports such comparisons of drought indices.

Correlation matrices can be used to visualize different regions in a catchment, which can then serve as a basis for comparison of the regions. Besides this one matrix per region approach, the sorting of the data in the correlation matrix can be used to convey some spatial information. The Haas and Birk (2017) paper mentioned throughout this work sorts the groundwater wells in their dataset by their distance to the main river in their regions, therefore showing the influence of the river on the groundwater. Other possible options would be for example sorting groundwater wells by their location along a stream (i.e., upstream to downstream), to sort surface water gauging stations by their location along the stream, or to sort groundwater wells by their depth or by the elevation of their locations. In the example shown in Fig. 3, the gauging stations are sorted by the size of their catchments, since this is provided as metadata in the ehyd dataset, and translates to their position along the stream. The other possible options mentioned above depend on the metadata provided with the dataset one uses, or have to be added by the user by hand. For this, two options are feasible: Adding a field with the desired data to the input data, or later adding an entry to the index of the large pandas dataframe described in the “Dataframe” section.

Also, contrary to the splitting between regions as shown by Haas and Birk (2017), regions can also be added together into single matrices (see Fig. 4), to show possible long distance correlations/teleconnections, or wells can be clustered together, for example into wells close to a know groundwater abstraction and wells deemed unaffected by human activities.

The dataframe can be split up into distinctive periods, so that a development over time can be visualized and the effects of extreme events such as the 2003 drought and the 2009 floods can be shown (see Fig. 4 and Haas and Birk 2017).

Building on this approach where time periods get selected due to outside information, we provide a short script in the supplementary material that uses this ability to split a dataset into single years that are then visualized separately for every year in the data. This turns a long-term dataset into a series of yearly snapshots that can be easily browsed through with an image viewer or turned into a movie that allows for a quick way to check for changes in correlations or patterns. In this way, one can observe whether the correlation patterns are more or less stable or change through time. Identifying the causes of changes in correlation patterns, however, is not straightforward. This is illustrated by the example of the Mur catchment, where a snow-rich year shows a correlation pattern similar to a drought year and a snow-poor year behaves similar to a flood year (Haas and Birk 2017).

In addition, linking drought indices such as SPI, SPEI, or SGI to the actual occurrence of drought impacts is a challenging task. Bachmair et al. (2016) for instance, found that the accumulation periods of SPI and SPEI best linked to drought impacts vary from one region to the other and also depend on the kind of impact considered. In the Aichfeld example, the SPI6 exhibits the highest correlation with the SGI, suggesting that it is a reasonable indicator for drought impacts on groundwater, in particular pointing to low groundwater levels and thus low aquifer storage and discharge, which might be associated with other adverse environmental or economic effects. Using the threshold SPI \(\le -\,2\), as suggested by the original McKee et al. (1993) paper, results in six extreme droughts within the time period from 1975 to 2011. The correlation matrices of these events show a tendency to higher correlations between the SGI values of the different wells, as shown for the drought year 2003 by Haas and Birk (2017). In contrast, no distinct correlation patterns (e.g., low correlations as found by Haas and Birk (2017) for the flood year 2009) are obvious from the matrices for the six events classified as “extreme floods” (SPI \(\ge +\,2\)) when adapting the original drought classification by McKee et al. (1993) to floods. Remarkably, the SPI6 of the year 2009, which is associated with extreme floods according to the local literature (see for example BMLFUW 2011; Hornich 2009; Schatzl 2009; Stromberger et al. 2009; Ruch et al. 2010), stays below + 2, thus only matching the category of “severe flood”.

These examples highlight the difficulties in interpreting the values of the drought indices, their correlation patterns and how to link them to real phenomena, such as the manifold possible drought impacts as for example discussed in Stahl et al. (2016). Thus, we suggest that the visual comparison and interpretation of correlation matrices should be complemented by independent, external information, such as reported drought or flood events, which can be used to select distinct time periods (e.g., years) that deserve closer investigation.

Another issue is the fact that very snow-rich years can produce similar patterns as the 2003 drought due to the snow cover cutting of the connection between precipitation and groundwater recharge. Likewise, very snow-poor years can produce patterns similar to the 2009 flood due to a direct connection of precipitation to groundwater recharge during the winter. However, this abundance or lack of snow does not show up in the SPI, or other such indices, thus making it impossible to tell a snow-rich year apart from a snow-poor one, using the matrix visualization alone. Related to this issue, it should be discussed what classifies as a drought or flood for a region in question, taking into account not only a certain index value, but also other factors.

In a region such as Austria, a local meteorological drought might not necessarily result in a groundwater drought. This is attributed to the fact that groundwater can still be replenished by naturally occurring infiltration of river water. Groundwater and precipitation time series are often the only data that are easily available for past times, which can make it difficult to identify past droughts/floods as solely a function of one or both of these in the absence of a hydrological model. A proper classification would best be done by either assessing droughts/floods from a calibrated hydrological model, by using databases such as the European Drought Impact Inventory—EDII (see Stahl et al. 2016) or in the case of lacking information in the EDII by going through local records, such as newspaper archives, government reports or even “crowdsourced” data from social media.

Splitting the time series into distinct periods can also support the quality control and classification of the data. In the case of the SGI values discussed in the “Quality control and data classification” section, the negative correlations of the deep wells with the shallow wells and their high correlations with themselves is found to be amplified in many years, making them much more obvious than shown in Fig. 2. Also, most of the wells closest to the river Mur, which show high correlations in Fig. 2, keep these high correlations in most years, whereas the wells further away show a more arbitrary behavior through time.

Another option easily enabled by python is the relative ease of the plotting of maps for regions with the SGI values of various wells. This “classic” approach can be a first step in going beyond correlations and investigating their causations, especially when combined with the option to split the dataset into arbitrary time periods (see “Time and event comparison” section).

As discussed in Stromberger et al. (2009) the flood year of 2009 was characterized by multiple large floods and heavy precipitation events of often only local extent and interrupted by periods of “nice” weather. One would assume that such local phenomena are easy to spot in a map, possibly with the topography already giving helpful hints as to their causes and extent. However, these phenomena are not clearly visible in the monthly data available (see Fig. 5). Here the month of July 2009 (subfigure c) which follows the main floods of June (Stromberger et al. 2009) shows mostly only moderately wet conditions in groundwater with some wells even indicating moderately dry conditions. While this does fit the idea of locally differing conditions, the SGI values shown (0–approx. + 1) would indicate a “mild flood”, using the definitions from McKee et al. (1993) for flood, are not fitting the reported severity of the floods, with the highest SGIs only seen in September 2009. In contrast, July of the drought year 2003 (subfigure b) does show very negative SGI values throughout the region shown in Fig. 5.

Regarding the possible identification of outliers, such as erroneous measurements or time series of differing nature (e.g., from different aquifers), and assessing their causation, a map view can provide both helpful hints and misleading marks, as demonstrated with the deep wells (brown highlights in Fig. 5) discussed in the “Quality control and data classification” section. These wells show a more muted behavior in the drought and flood years of 2003 and 2009, but can also behave very different, as highlighted for two cases in 1977 and 1984 (subfigure a). Thus, the map view can point toward the fact that this subset of wells behaves different. As to the causation of this difference, the first conclusion from the map view could be that this simply is due to their location on the northern bank of the river Mur. However, as discussed in the “Quality control and data classification” section, their differing behavior is presumed to be connected to their depth, instead of their location.