Repository: mie-lab/trackintel (python)

Issue:
How to use temporal_tracking_quality to delete bad tracking days
Hey, 
I have a GPS tracking dataset and want to filter days that have a low tracking coverage. I noticed that the (pretty nice) function `ti.analysis.tracking_quality.temporal_tracking_quality` does exactly what I want ;-)
Except that I don't know how I can use the results for filtering. The problem is that the function does not return a date but a relative count of days since the start of tracking (I assume). Therefore I can not use the result to identify bad tracking days in my original dataset. (see the screenshot below). 
I think it would be better if the function would return a datetime index instead of a relativ day count. This is done automatically when using a pandas datetime grouper as in the modal split function [here](https://github.com/mie-lab/trackintel/blob/master/trackintel/analysis/modal_split.py#L69). Maybe using that would be an option.

![image](https://user-images.githubusercontent.com/31069329/122799480-13a4fc00-d2c2-11eb-9948-98a6d2b07717.png)


Pull request description:
ENH: use pd.Grouper for tracking quality
closes #258

main changes:
- We use pd.Grouper to group days or weeks and return the datatime object. The granularity == "day" and "week" now only returns the absolute date. 
- As we have no relative days, we no longer fill in quality == 0 records (_get_all_quality() is now unused).
- Description added to the docstrings

Patch:
diff --git a/README.md b/README.md
index 25d3f4b..2f1efa6 100644
--- a/README.md
+++ b/README.md
@@ -101,7 +101,7 @@ You should then be able to run the examples in the `examples` folder or import t
 ```{python}
 import trackintel as ti
 
-ti.print_version()
+ti.print_version() 
 ```
 
 ## Requirements and dependencies
diff --git a/docs/modules/io.rst b/docs/modules/io.rst
index 09083d7..0c2d291 100644
--- a/docs/modules/io.rst
+++ b/docs/modules/io.rst
@@ -61,6 +61,8 @@ PostGIS Import
 
 .. autofunction:: trackintel.io.postgis.read_trips_postgis
 
+.. autofunction:: trackintel.io.postgis.read_tours_postgis
+
 CSV File Export
 ===============
 
@@ -89,6 +91,8 @@ PostGIS Export
 
 .. autofunction:: trackintel.io.postgis.write_trips_postgis
 
+.. autofunction:: trackintel.io.postgis.write_tours_postgis
+
 Predefined dataset readers
 ==========================
 We also provide functionality to parse well-known datasets directly into the trackintel framework.
diff --git a/trackintel/analysis/tracking_quality.py b/trackintel/analysis/tracking_quality.py
index d2b7a41..fe0781b 100644
--- a/trackintel/analysis/tracking_quality.py
+++ b/trackintel/analysis/tracking_quality.py
@@ -40,17 +40,33 @@ def temporal_tracking_quality(source, granularity="all", max_iter=60):
     datamodels and their combinations (e.g., staypoints and triplegs sequence).
 
     The temporal tracking quality is the ratio of tracking time and the total time extent. It is
-    calculated and returned per-user in the defined ``granularity``. The possible time extents of
-    the different granularities are different:
+    calculated and returned per-user in the defined ``granularity``. The time extents
+    and the columns for the returned ``quality`` df for different ``granularity`` are:
 
-    - ``all`` considers the time between the latest "finished_at" and the earliest "started_at";
-    - ``week`` considers the whole week (604800 sec)
-    - ``day`` and ``weekday`` consider the whole day (86400 sec)
-    - ``hour`` considers the whole hour (3600 sec).
+    - ``all``:
 
-    The tracking quality of each user is calculated based on his or her own tracking extent.
-    For granularity = ``day`` or ``week``, the quality["day"] or quality["week"] column displays the
-    time relative to the first record in the entire dataset.
+        - time extent: between the latest "finished_at" and the earliest "started_at" for each user.
+        - columns: ``['user_id', 'quality']``.
+
+    - ``week``:
+
+        - time extent: the whole week (604800 sec) for each user.
+        - columns: ``['user_id', 'week_monday', 'quality']``.
+
+    - ``day``:
+
+        - time extent: the whole day (86400 sec) for each user
+        - columns: ``['user_id', 'day', 'quality']``
+
+    - ``weekday``
+
+        - time extent: the whole day (86400 sec) * number of tracked weeks for each user for each user
+        - columns: ``['user_id', 'weekday', 'quality']``
+
+    - ``hour``:
+
+        - time extent: the whole hour (3600 sec) * number of tracked days for each user
+        - columns: ``['user_id', 'hour', 'quality']``
 
     Examples
     --------
@@ -80,67 +96,47 @@ def temporal_tracking_quality(source, granularity="all", max_iter=60):
 
     if granularity == "all":
         quality = df.groupby("user_id", as_index=False).apply(_get_tracking_quality_user, granularity)
+        return quality
 
-    elif granularity == "day":
-        # split records that span several days
-        df = _split_overlaps(df, granularity=granularity, max_iter=max_iter)
-        # get the tracked day relative to the first day
-        start_date = df["started_at"].min().date()
-        df["day"] = df["started_at"].apply(lambda x: (x.date() - start_date).days)
-
-        # calculate per-user per-day raw tracking quality
-        raw_quality = df.groupby(["user_id", "day"], as_index=False).apply(_get_tracking_quality_user, granularity)
-        # add quality = 0 records
-        quality = _get_all_quality(df, raw_quality, granularity)
+    # split records that span several days
+    df = _split_overlaps(df, granularity="day", max_iter=max_iter)
+    if granularity == "day":
+        grouper = pd.Grouper(key="started_at", freq="D")
+        column_name = "day"
 
     elif granularity == "week":
-        # split records that span several days
-        df = _split_overlaps(df, granularity="day", max_iter=max_iter)
-        # get the tracked week relative to the first day
-        start_date = df["started_at"].min().date()
-        df["week"] = df["started_at"].apply(lambda x: (x.date() - start_date).days // 7)
-
-        # calculate per-user per-week raw tracking quality
-        raw_quality = df.groupby(["user_id", "week"], as_index=False).apply(_get_tracking_quality_user, granularity)
-        # add quality = 0 records
-        quality = _get_all_quality(df, raw_quality, granularity)
+        grouper = pd.Grouper(key="started_at", freq="W")
+        column_name = "week_monday"
 
     elif granularity == "weekday":
-        # split records that span several days
-        df = _split_overlaps(df, granularity="day", max_iter=max_iter)
-
         # get the tracked week relative to the first day
         start_date = df["started_at"].min().date()
         df["week"] = df["started_at"].apply(lambda x: (x.date() - start_date).days // 7)
-        # get the weekday
-        df["weekday"] = df["started_at"].dt.weekday
 
-        # calculate per-user per-weekday raw tracking quality
-        raw_quality = df.groupby(["user_id", "weekday"], as_index=False).apply(_get_tracking_quality_user, granularity)
-        # add quality = 0 records
-        quality = _get_all_quality(df, raw_quality, granularity)
+        grouper = df["started_at"].dt.weekday
+        column_name = "weekday"
 
     elif granularity == "hour":
-        # first do a day split to speed up the hour split
-        df = _split_overlaps(df, granularity="day", max_iter=max_iter)
-        df = _split_overlaps(df, granularity=granularity)
-
+        df = _split_overlaps(df, granularity="hour")
         # get the tracked day relative to the first day
         start_date = df["started_at"].min().date()
         df["day"] = df["started_at"].apply(lambda x: (x.date() - start_date).days)
-        # get the hour
-        df["hour"] = df["started_at"].dt.hour
 
-        # calculate per-user per-hour raw tracking quality
-        raw_quality = df.groupby(["user_id", "hour"], as_index=False).apply(_get_tracking_quality_user, granularity)
-        # add quality = 0 records
-        quality = _get_all_quality(df, raw_quality, granularity)
+        grouper = df["started_at"].dt.hour
+        column_name = "hour"
 
     else:
         raise AttributeError(
             f"granularity unknown. We only support ['all', 'day', 'week', 'weekday', 'hour']. You passed {granularity}"
         )
 
+    # calculate per-user per-grouper tracking quality
+    quality = df.groupby(["user_id", grouper]).apply(_get_tracking_quality_user, granularity).reset_index()
+
+    # rename and reorder
+    quality.rename(columns={"started_at": column_name}, inplace=True)
+    quality = quality[["user_id", column_name, "quality"]]
+
     return quality
 
 
diff --git a/trackintel/io/__init__.py b/trackintel/io/__init__.py
index bc8260f..540233e 100644
--- a/trackintel/io/__init__.py
+++ b/trackintel/io/__init__.py
@@ -30,6 +30,9 @@ from .from_geopandas import read_trips_gpd
 
 from .file import read_tours_csv
 from .file import write_tours_csv
+from .postgis import read_tours_postgis
+from .postgis import write_tours_postgis
+from .from_geopandas import read_tours_gpd
 
 from .dataset_reader import read_geolife
 from .dataset_reader import geolife_add_modes_to_triplegs
diff --git a/trackintel/io/dataset_reader.py b/trackintel/io/dataset_reader.py
index ebb3f2e..5311452 100644
--- a/trackintel/io/dataset_reader.py
+++ b/trackintel/io/dataset_reader.py
@@ -39,6 +39,7 @@ def read_geolife(geolife_path, print_progress=False):
 
     labels: dict
         Dictionary with the available mode labels.
+        Keys are user ids of users that have a "labels.txt" in their folder.
 
     Notes
     -----
@@ -137,10 +138,7 @@ def _get_labels(geolife_path, uids):
     No further checks are done on user ids, they must be convertable to ints.
     """
     labels_rename = {"Start Time": "started_at", "End Time": "finished_at", "Transportation Mode": "mode"}
-    label_dict = defaultdict(
-        partial(pd.DataFrame, columns=["started_at", "finished_at", "mode"])
-    )  # output dict for the labels
-    # TODO: change geolife_add_modes_to_triplegs so that we can use a dict instead.
+    label_dict = {}  # output dict for the labels
 
     # get paths to all "labels.txt" files.
     possible_label_paths = ((os.path.join(geolife_path, user_id, "labels.txt"), user_id) for user_id in uids)
@@ -242,16 +240,13 @@ def geolife_add_modes_to_triplegs(
     # temp time fields for nn query
     tpls["started_at_s"] = (tpls["started_at"] - pd.Timestamp("1970-01-01", tz="utc")) // pd.Timedelta("1s")
     tpls["finished_at_s"] = (tpls["finished_at"] - pd.Timestamp("1970-01-01", tz="utc")) // pd.Timedelta("1s")
-    all_users = tpls["user_id"].unique()
     # tpls_id_mode_list is used to collect tripleg-mode matches. It will be filled with dictionaries with the
     # following keys: [id', 'label_id', 'mode']
     tpls_id_mode_list = list()
 
-    for user_this in all_users:
+    for user_this in labels.keys():
         tpls_this = tpls[tpls["user_id"] == user_this]
         labels_this = labels[user_this]
-        if labels_this.empty:
-            continue
 
         labels_this["started_at_s"] = (
             labels_this["started_at"] - pd.Timestamp("1970-01-01", tz="utc")
@@ -292,10 +287,7 @@ def geolife_add_modes_to_triplegs(
         tpls = tpls.join(tpls_id_mode)
         tpls = tpls.astype({"label_id": "Int64"})
 
-    try:
-        tpls.drop(["started_at_s", "finished_at_s"], axis=1, inplace=True)
-    except KeyError:
-        pass
+    tpls.drop(["started_at_s", "finished_at_s"], axis=1, inplace=True)
 
     try:
         tpls.drop(["ratio"], axis=1, inplace=True)
diff --git a/trackintel/io/from_geopandas.py b/trackintel/io/from_geopandas.py
index 5e101c1..1bb34a1 100644
--- a/trackintel/io/from_geopandas.py
+++ b/trackintel/io/from_geopandas.py
@@ -401,7 +401,12 @@ def _trackintel_model(gdf, set_names=None, geom_col=None, crs=None, tz_cols=None
     if tz_cols is not None:
         for col in tz_cols:
             if not pd.api.types.is_datetime64tz_dtype(gdf[col]):
-                gdf[col] = _localize_timestamp(dt_series=gdf[col], pytz_tzinfo=tz, col_name=col)
+                try:
+                    gdf[col] = _localize_timestamp(dt_series=gdf[col], pytz_tzinfo=tz, col_name=col)
+                except ValueError:
+                    # Taken if column contains datetimes with different timezone informations.
+                    # Cast them to UTC in this case.
+                    gdf[col] = pd.to_datetime(gdf[col], utc=True)
 
     # If is not GeoDataFrame and no geom_col is set end early.
     # That allows us to handle DataFrames and GeoDataFrames in one function.
diff --git a/trackintel/io/postgis.py b/trackintel/io/postgis.py
index 7e2a9de..0746889 100644
--- a/trackintel/io/postgis.py
+++ b/trackintel/io/postgis.py
@@ -579,6 +579,118 @@ def write_trips_postgis(
         )
 
 
+@_handle_con_string
+def read_tours_postgis(
+    sql,
+    con,
+    geom_col=None,
+    crs=None,
+    index_col=None,
+    coerce_float=True,
+    parse_dates=None,
+    params=None,
+    chunksize=None,
+    **kwargs
+):
+    """Read tours from a PostGIS database.
+
+    Parameters
+    ----------
+    sql : str
+        SQL query e.g. "SELECT * FROM tours"
+
+    con : str, sqlalchemy.engine.Connection or sqlalchemy.engine.Engine
+        Connection string or active connection to PostGIS database.
+
+    geom_col : str, optional
+        The geometry column of the table (if exists).
+
+    crs : optional
+        Coordinate reference system if table has geometry.
+
+    index_col : string or list of strings, optional
+        Column(s) to set as index(MultiIndex)
+
+    coerce_float : boolean, default True
+        Attempt to convert values of non-string, non-numeric objects (like
+        decimal.Decimal) to floating point, useful for SQL result sets
+
+    parse_dates : list or dict, default None
+        - List of column names to parse as dates.
+        - Dict of ``{column_name: format string}`` where format string is
+            strftime compatible in case of parsing string times, or is one of
+            (D, s, ns, ms, us) in case of parsing integer timestamps.
+        - Dict of ``{column_name: arg dict}``, where the arg dict
+            corresponds to the keyword arguments of
+            :func:`pandas.to_datetime`. Especially useful with databases
+            without native Datetime support, such as SQLite.
+
+    params : list, tuple or dict, optional, default None
+        List of parameters to pass to execute method.
+
+    chunksize : int, default None
+        If specified, return an iterator where chunksize is the number
+        of rows to include in each chunk.
+
+    **kwargs
+        Further keyword arguments as available in trackintels trackintel.io.read_tours_gpd().
+        Especially useful to rename column names from the SQL table to trackintel conform column names.
+        See second example how to use it in code.
+
+    Returns
+    -------
+    GeoDataFrame (as trackintel tours)
+        A GeoDataFrame containing the tours.
+
+    Examples
+    --------
+    >>> tours = ti.io.read_tours_postgis("SELECT * FROM tours", con)
+    >>> tours = ti.io.read_tours_postgis("SELECT * FROM tours", con, index_col="id", started_at="start_time",
+    ...                                  finished_at="end_time", user_id="USER")
+    """
+    if geom_col is None:
+        tours = pd.read_sql(
+            sql,
+            con,
+            index_col=index_col,
+            coerce_float=coerce_float,
+            params=params,
+            parse_dates=parse_dates,
+            chunksize=chunksize,
+        )
+    else:
+        tours = gpd.GeoDataFrame.from_postgis(
+            sql,
+            con,
+            geom_col=geom_col,
+            crs=crs,
+            index_col=index_col,
+            coerce_float=coerce_float,
+            parse_dates=parse_dates,
+            params=params,
+            chunksize=chunksize,
+        )
+
+    return ti.io.read_tours_gpd(tours, **kwargs)
+
+
+@_handle_con_string
+def write_tours_postgis(
+    tours, name, con, schema=None, if_exists="fail", index=True, index_label=None, chunksize=None, dtype=None
+):
+    write_trips_postgis(
+        tours,
+        name=name,
+        con=con,
+        schema=schema,
+        if_exists=if_exists,
+        index=index,
+        index_label=index_label,
+        chunksize=chunksize,
+        dtype=dtype,
+    )
+
+
 # helper docstring to change __doc__ of all write functions conveniently in one place
 __doc = """Stores {long} to PostGIS. Usually, this is directly called on a {long}
     DataFrame (see example below).
@@ -628,3 +740,4 @@ write_triplegs_postgis.__doc__ = __doc.format(long="triplegs", short="tpls")
 write_staypoints_postgis.__doc__ = __doc.format(long="staypoints", short="sp")
 write_locations_postgis.__doc__ = __doc.format(long="locations", short="locs")
 write_trips_postgis.__doc__ = __doc.format(long="trips", short="trips")
+write_tours_postgis.__doc__ = __doc.format(long="tours", short="tours")
diff --git a/trackintel/model/tours.py b/trackintel/model/tours.py
index bc11abd..f36eabc 100644
--- a/trackintel/model/tours.py
+++ b/trackintel/model/tours.py
@@ -57,6 +57,16 @@ class ToursAccessor(object):
         """
         ti.io.file.write_tours_csv(self._obj, filename, *args, **kwargs)
 
+    def to_postgis(
+        self, name, con, schema=None, if_exists="fail", index=True, index_label=None, chunksize=None, dtype=None
+    ):
+        """
+        Store this collection of tours to PostGIS.
+
+        See :func:`trackintel.io.postgis.write_tours_postgis`.
+        """
+        ti.io.postgis.write_tours_postgis(self._obj, name, con, schema, if_exists, index, index_label, chunksize, dtype)
+
     def plot(self, *args, **kwargs):
         """
         Plot this collection of tours.


Test patch:
diff --git a/tests/analysis/test_tracking_quality.py b/tests/analysis/test_tracking_quality.py
index 19ab2af..ff508a9 100644
--- a/tests/analysis/test_tracking_quality.py
+++ b/tests/analysis/test_tracking_quality.py
@@ -34,6 +34,22 @@ def testdata_all_geolife_long():
     return sp, tpls, trips
 
 
+def get_test_sp(start_time, duration):
+    """Generate test staypoints for tracking duration given start_time and duration."""
+    p1 = Point(8.5067847, 47.4)
+
+    # we generate three records, duration = 0, negative duration and positive duration
+    list_dict = [
+        {"user_id": 0, "started_at": start_time, "finished_at": start_time, "geom": p1},
+        {"user_id": 0, "started_at": start_time, "finished_at": start_time - duration, "geom": p1},
+        {"user_id": 0, "started_at": start_time, "finished_at": start_time + duration, "geom": p1},
+    ]
+    sp = gpd.GeoDataFrame(data=list_dict, geometry="geom", crs="EPSG:4326")
+    sp.index.name = "id"
+
+    return sp
+
+
 class TestTemporal_tracking_quality:
     """Tests for the temporal_tracking_quality() function."""
 
@@ -69,8 +85,8 @@ class TestTemporal_tracking_quality:
         # test if the result of the user agrees
         quality = ti.analysis.tracking_quality.temporal_tracking_quality(sp_tpls, granularity="day")
 
-        assert quality_manual == quality.loc[(quality["user_id"] == 0) & (quality["day"] == 0), "quality"].values[0]
-        assert (quality["quality"] < 1).all()
+        assert quality_manual == quality.iloc[0]["quality"]
+        assert (quality["quality"] <= 1).all()
 
     def test_tracking_quality_week(self, testdata_sp_tpls_geolife_long):
         """Test if the calculated tracking quality per week is correct."""
@@ -92,7 +108,7 @@ class TestTemporal_tracking_quality:
         quality = ti.analysis.tracking_quality.temporal_tracking_quality(sp_tpls, granularity="week")
 
         assert quality_manual == quality.loc[(quality["user_id"] == 0), "quality"].values[0]
-        assert (quality["quality"] < 1).all()
+        assert (quality["quality"] <= 1).all()
 
     def test_tracking_quality_weekday(self, testdata_sp_tpls_geolife_long):
         """Test if the calculated tracking quality per weekday is correct."""
@@ -116,7 +132,7 @@ class TestTemporal_tracking_quality:
         quality = ti.analysis.tracking_quality.temporal_tracking_quality(sp_tpls, granularity="weekday")
 
         assert quality_manual == quality.loc[(quality["user_id"] == 0) & (quality["weekday"] == 3), "quality"].values[0]
-        assert (quality["quality"] < 1).all()
+        assert (quality["quality"] <= 1).all()
 
     def test_tracking_quality_hour(self, testdata_sp_tpls_geolife_long):
         """Test if the calculated tracking quality per hour is correct."""
@@ -206,36 +222,37 @@ class TestTemporal_tracking_quality:
 
     def test_non_positive_duration_warning(self):
         """Test the function can handle non positive duration records without running into infinite loop."""
-        p1 = Point(8.5067847, 47.4)
-        t1 = pd.Timestamp("1971-01-01 00:00:00", tz="utc")
-
-        one_hour = datetime.timedelta(hours=1)
-
-        list_dict = [
-            {"user_id": 0, "started_at": t1, "finished_at": t1, "geom": p1},  # duration 0 at midnight
-            {"user_id": 0, "started_at": t1, "finished_at": t1 - one_hour, "geom": p1},  # negative duration
-        ]
-        sp = gpd.GeoDataFrame(data=list_dict, geometry="geom", crs="EPSG:4326")
-        sp.index.name = "id"
-
-        with pytest.warns(UserWarning):
-            ti.analysis.tracking_quality.temporal_tracking_quality(sp)
+        t = pd.Timestamp("1971-01-01 00:00:00", tz="utc")  # duration 0 at midnight
+        negative_one_hour = datetime.timedelta(hours=1)  # negative duration
+        sp = get_test_sp(t, negative_one_hour)
+
+        warn_string = "The input dataframe does not contain any record with positive duration. Please check."
+        with pytest.warns(UserWarning, match=warn_string):
+            ti.analysis.tracking_quality.temporal_tracking_quality(sp.iloc[:-1])
+
+    def test_absolute_extent(self):
+        """Test the absolute date is correctly generated for both granularity day and week."""
+        # we test two examples: one at midnight the other at midday
+        t_ls = [pd.Timestamp("1971-01-01 00:00:00", tz="utc"), pd.Timestamp("1971-01-01 12:00:00", tz="utc")]
+        ten_days = pd.Timedelta(days=10)
+        # the midnight record loses one day after the split
+        last_start_date_after_split_ls = [t_ls[0] + ten_days - pd.Timedelta(days=1), t_ls[0] + ten_days]
+        for t, last_start_date_after_split in zip(t_ls, last_start_date_after_split_ls):
+            sp = get_test_sp(t, ten_days)
+
+            quality = ti.analysis.tracking_quality.temporal_tracking_quality(sp, granularity="day")
+            # get the "date" of the last record and compare to the last "date" in data
+            assert quality.values[-1][-2].day == last_start_date_after_split.day
+
+            quality = ti.analysis.tracking_quality.temporal_tracking_quality(sp, granularity="week")
+            # get the "week" of the last record and compare to the last "week" in data
+            assert quality.values[-1][-2].week == last_start_date_after_split.week
 
     def test_non_positive_duration_filtered(self):
         """Test the non positive duration records are filtered and do not affect the result."""
-        p1 = Point(8.5067847, 47.4)
-        t1 = pd.Timestamp("1971-01-01 00:00:00", tz="utc")
-
+        t = pd.Timestamp("1971-01-01 00:00:00", tz="utc")  # duration 0 at midnight
         one_hour = datetime.timedelta(hours=1)
-
-        list_dict = [
-            {"user_id": 0, "started_at": t1, "finished_at": t1, "geom": p1},  # duration 0 at midnight
-            {"user_id": 0, "started_at": t1, "finished_at": t1 - one_hour, "geom": p1},  # negative duration
-            {"user_id": 0, "started_at": t1, "finished_at": t1 + one_hour, "geom": p1},  # positive duration
-        ]
-
-        sp = gpd.GeoDataFrame(data=list_dict, geometry="geom", crs="EPSG:4326")
-        sp.index.name = "id"
+        sp = get_test_sp(t, one_hour)
 
         granularity_ls = ["all", "day", "week", "weekday", "hour"]
         correct_quality_ls = [1, 1 / 24, 1 / 24 / 7, 1 / 24, 1]
diff --git a/tests/io/test_dataset_reader.py b/tests/io/test_dataset_reader.py
index 165fdce..3e9f0f9 100644
--- a/tests/io/test_dataset_reader.py
+++ b/tests/io/test_dataset_reader.py
@@ -35,8 +35,8 @@ def matching_data():
         Tripleg_2 overlaps and extents to the right but is almost not covered by label_0
         Tripleg_3 overlaps label_1 to the right and the left but is almost fully covered by it.
     """
-    one_hour = datetime.timedelta(hours=1)
-    one_min = datetime.timedelta(minutes=1)
+    one_hour = pd.Timedelta("1h")
+    one_min = pd.Timedelta("1min")
     time_1 = pd.Timestamp("1970-01-01", tz="utc")
 
     triplegs = [
@@ -146,14 +146,6 @@ class Test_GetLabels:
         assert len(labels[20]) == 223
         assert all(df.columns.tolist() == ["started_at", "finished_at", "mode"] for df in labels.values())
 
-    def test_defaultdict(self):
-        """Test if non existing entries return a correct empty pd.DataFrame"""
-        geolife_path = os.path.join("tests", "data", "geolife_modes")
-        uids = ["010", "020", "178"]
-        labels = _get_labels(geolife_path, uids)
-        empty_df = pd.DataFrame(columns=["started_at", "finished_at", "mode"])
-        assert_frame_equal(labels[-1], empty_df)
-
 
 class Test_GetDf:
     def test_example_data(self):
@@ -242,8 +234,7 @@ class TestGeolife_add_modes_to_triplegs:
 
     def test_mode_matching_multi_user(self, matching_data):
         tpls, labels_raw = matching_data
-        # we add an empty DataFrame with labels in the end
-        labels = {0: labels_raw, 1: pd.DataFrame(columns=labels_raw.columns)}
+        labels = {0: labels_raw}
         # explicitly change the user_id of the second record
         tpls.loc[1, "user_id"] = 1
 
diff --git a/tests/io/test_from_geopandas.py b/tests/io/test_from_geopandas.py
index c04a724..3dc0edd 100644
--- a/tests/io/test_from_geopandas.py
+++ b/tests/io/test_from_geopandas.py
@@ -91,6 +91,17 @@ class Test_Trackintel_Model:
         pfs = _trackintel_model(pfs, tz_cols=["tracked_at"], tz="UTC")
         assert_geodataframe_equal(pfs, example_positionfixes)
 
+    def test_multiple_timezones_in_col(self, example_positionfixes):
+        """Test if datetimes in column don't have the same timezone get casted to UTC."""
+        example_positionfixes["tracked_at"] = [
+            pd.Timestamp("2021-08-01 16:00:00", tz="Europe/Amsterdam"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Asia/Muscat"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Pacific/Niue"),
+        ]
+        pfs = _trackintel_model(example_positionfixes, tz_cols=["tracked_at"])
+        example_positionfixes["tracked_at"] = pd.to_datetime(example_positionfixes["tracked_at"], utc=True)
+        assert_geodataframe_equal(pfs, example_positionfixes)
+
 
 class TestRead_Positionfixes_Gpd:
     """Test `read_positionfixes_gpd()` function."""
@@ -296,6 +307,17 @@ class TestRead_Trips_Gpd:
         example_trips.rename(columns=mapper, inplace=True)
         assert_geodataframe_equal(trips, example_trips)
 
+    def test_multiple_timezones_in_col(self, example_trips):
+        """Test if datetimes in column don't have the same timezone get casted to UTC."""
+        example_trips["started_at"] = [
+            pd.Timestamp("2021-08-01 16:00:00", tz="Europe/Amsterdam"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Asia/Muscat"),
+            pd.Timestamp("2021-08-01 16:00:00", tz="Pacific/Niue"),
+        ]
+        trips = read_trips_gpd(example_trips)
+        example_trips["started_at"] = pd.to_datetime(example_trips["started_at"], utc=True)
+        assert_geodataframe_equal(example_trips, trips)
+
 
 @pytest.fixture
 def example_tours():
diff --git a/tests/io/test_postgis.py b/tests/io/test_postgis.py
index 6b9f03a..3791d46 100644
--- a/tests/io/test_postgis.py
+++ b/tests/io/test_postgis.py
@@ -160,6 +160,25 @@ def example_trips():
     return trips
 
 
+@pytest.fixture
+def example_tours():
+    """Tours to load into the database."""
+    t1 = pd.Timestamp("1971-01-01 00:00:00", tz="utc")
+    t2 = pd.Timestamp("1971-01-01 05:00:00", tz="utc")
+    t3 = pd.Timestamp("1971-01-02 07:00:00", tz="utc")
+    h = datetime.timedelta(hours=1)
+
+    list_dict = [
+        {"user_id": 0, "started_at": t1, "finished_at": t1 + h},
+        {"user_id": 0, "started_at": t2, "finished_at": t2 + h},
+        {"user_id": 1, "started_at": t3, "finished_at": t3 + h},
+    ]
+    tours = pd.DataFrame(data=list_dict)
+    tours.index.name = "id"
+    assert tours.as_tours
+    return tours
+
+
 def del_table(con, table):
     """Delete table in con."""
     try:
@@ -291,6 +310,28 @@ class TestPositionfixes:
         finally:
             del_table(conn, table)
 
+    def test_daylight_saving_tz(self, example_positionfixes, conn_postgis):
+        """Test if function can handle different tz informations in one column.
+
+        PostgreSQL saves all its datetimes in UTC and then on exports them to the local timezone.
+        That all works fine except when the local timezone changed in the past for example with daylight saving.
+        """
+        pfs = example_positionfixes.copy()
+        conn_string, conn = conn_postgis
+        table = "positionfixes"
+        sql = f"SELECT * FROM {table}"
+        t1 = pd.Timestamp("2021-08-01 16:00:00", tz="utc")  # summer time
+        t2 = pd.Timestamp("2021-08-01 15:00:00", tz="utc")  # summer time
+        t3 = pd.Timestamp("2021-02-01 14:00:00", tz="utc")  # winter time
+        pfs["tracked_at"] = [t1, t2, t3]
+        geom_col = pfs.geometry.name
+        try:
+            pfs.as_positionfixes.to_postgis(table, conn_string)
+            pfs_db = ti.io.read_positionfixes_postgis(sql, conn_string, geom_col, index_col="id")
+            assert_geodataframe_equal(pfs, pfs_db)
+        finally:
+            del_table(conn, table)
+
 
 class TestTriplegs:
     def test_write(self, example_triplegs, conn_postgis):
@@ -582,6 +623,51 @@ class TestTrips:
             del_table(conn, table)
 
 
+class TestTours:
+    """Test of postgis functions for tours."""
+
+    def test_write(self, example_tours, conn_postgis):
+        """Test if write of tours create correct schema in database."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            columns_db, dtypes = get_table_schema(conn, table)
+            columns = tours.columns.tolist() + [tours.index.name]
+            assert len(columns_db) == len(columns)
+            assert set(columns_db) == set(columns)
+        finally:
+            del_table(conn, table)
+
+    def test_read(self, example_tours, conn_postgis):
+        """Test if tours written to and read back from database are the same."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        sql = f"SELECT * FROM {table}"
+
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            tours_db = ti.io.read_tours_postgis(sql, conn_string, index_col="id")
+            assert_frame_equal(tours, tours_db)
+        finally:
+            del_table(conn, table)
+
+    def test_no_crs(self, example_tours, conn_postgis):
+        """Test if writing reading to postgis also works correctly without CRS."""
+        tours = example_tours
+        conn_string, conn = conn_postgis
+        table = "tours"
+        sql = f"SELECT * FROM {table}"
+        try:
+            tours.as_tours.to_postgis(table, conn_string)
+            tours_db = ti.io.read_tours_postgis(sql, conn_string, index_col="id")
+            assert_frame_equal(tours, tours_db)
+        finally:
+            del_table(conn, table)
+
+
 class TestGetSrid:
     def test_srid(self, example_positionfixes):
         """Test if `_get_srid` returns the correct srid."""