Working with dates¶

Added in version 1.8: Date columns and functions

Dates can be a very useful datatype to work with. We can use crandas to deal with date data, regardless of the format. In this section we will learn how to upload dates to the VDL and the date specific functions we have that allow us to extract properties from the date data that we have.

Uploading Dates¶

When uploading dates as strings we have to be more explicit than when uploading integers or strings. In order for crandas to know that the column that you are trying to upload contains dates, you must specify the ctype.

import crandas as cd
import pandas as pd

# We generally don't recommend attempting to upload dates in different formats, as it might lead to issues
dates = ["19910204", "30/08/23", "2023-09-06", "March 14, 2016"]
# We need to add the ctype or it won't work
tab =  cd.DataFrame({"dates" : dates}, ctype={"dates": "date"})

There are certain data formats that are ambiguous. For example, it is not clear whether "01/02/2000" refers to the first of February or the second of January. When using ambiguous formats, you can add the following format specification to the ctype:

# We need to add the ctype or it won't work
tab =  cd.DataFrame({"ambiguous_dates" : ["01/02/2000", "02/04/1991", "03/12/1993"]}, ctype={"ambiguous_dates": "date[format=d/m/Y]"})

The accepted options follow the python conventions except that the percentage signs % should not be added. The accepted options can be found in here. Currently, only -, /, / and `` `` are accepted as separators (not ,).

Python has multiple ways to represent dates, including datetime.date, pandas.timestamp and numpy.datetime64. When uploading an existing DataFrame with either representation, the resulting CDataFrames will have the relevant column as a date.

pydates = pd.to_datetime(["19910204", "10/08/23", "2023-09-06", "March 14, 2016", "19010101", "20991231"])
tab = cd.DataFrame({"dates" : pydates})

Warning

Crandas assumes time-zone agnostic datetimes are uploaded. When uploading datetime objects, make sure that they share this format or there might be of-by-one errors.

Date functions¶

Given a column of dates, we might be interested on which of them happen in the same year or month. Crandas contains functions that allow the extraction of year, month, day and weekday (starting on Monday) from any date.

tab = tab.assign(weekday=tab["dates"].weekday())
tab = tab.assign(day=tab["dates"].day())
tab = tab.assign(month=tab["dates"].month())
tab = tab.assign(year=tab["dates"].year())

>>> tab.open()
     dates  weekday  day  month  year
1991-02-04        0    4      2  1991
2023-10-08        6    8     10  2023
2023-09-06        2    6      9  2023
2016-03-14        0   14      3  2016
1901-01-01        1    1      1  1901
2099-12-31        3   31     12  2099

When using this operation, the resulting column is an integer column and no longer has a date format, but acts like any other integer column.

Note

We also allow the typical pandas notation tab["dates"].dt.year (also for day_of_week, dayofweek, day, and month)

Calculations over dates¶

We can also use crandas to find how many days happened between two dates or finding the date after a determined amount of days. To do this, we can simply add and subtract columns as normal.

dates = ["19910204", "30/08/23", "2023-09-06", "March 14, 2016"]

tab = cd.DataFrame({“dates” : dates, “days_for_next_app” : [12, 7, 20, 60]}, ctype={“dates”: “date”})

tab = tab.assign(next_app=tab[“dates”]+tab[“days_for_next_app”]) tab = tab.assign(day_diff=tab[“next_app”]-tab[“dates”])

>>> tab.open()
       dates  days_for_next_app   next_app  day_diff
0 1991-02-04                 12 1991-02-16        12
1 2023-08-30                  7 2023-09-06         7
2 2023-09-06                 20 2023-09-26        20
3 2016-03-14                 60 2016-05-13        60

Of course, some operations are not allowed, like adding two dates together or subtracting a date from an integer.

We can also add an integer directly or a python date:

>>> (tab["dates"]+30).as_table().open()
1991-03-06
2023-09-29
2023-10-06
2016-04-13

>>> (pd.to_datetime("20230906") - tab["dates"]).as_table().open()
11902
    7
    0
 2732

Note

In crandas, we can only add and subtract days. It is not possible to add months or years.

Filtering through dates¶

We can also use crandas to filter over a table, we can choose a specific date range or choose any date after a particular one. Similar to the arithmetic operations, we can treat them as any column type, as long as we provide python date objects.

>>> tab[tab["dates"] >= filter_date]
       dates
0 2023-08-30
1 2023-09-06
2 2016-03-14

Warning

Due to technical limitations, crandas can only handle dates between 1901-01-01 and 2099-12-31.

After learning about dates, we will see what happens when we are working with missing data in the next section.