How to create a calendar table (date dimension) in pandas

user_4050925 0
Viewed 17652

A table of dates with primary keys is sometimes used in databse design.

| date_id |     Date       |    Record_timestamp |  Day      |  Week |  Month |     Quarter |   Year_half |     Year |
|---------+----------------+---------------------+-----------+-------+--------+-------------+-------------+----------|
|       0 |     2000-01-01 |    NaN              |  Saturday |  52   |  1     |     1       |   1         |     2000 |
|       1 |     2000-01-02 |    NaN              |  Sunday   |  52   |  1     |     1       |   1         |     2000 |
|       2 |     2000-01-03 |    NaN              |  Monday   |  1    |  1     |     1       |   1         |     2000 |

How to do it in pandas?

pandas
3 Answers

This is a little cleaner with the dt accessor:

In [11]: def create_date_table2(start='2000-01-01', end='2050-12-31'):
    ...:     df = pd.DataFrame({"Date": pd.date_range(start, end)})
    ...:     df["Day"] = df.Date.dt.weekday_name
    ...:     df["Week"] = df.Date.dt.weekofyear
    ...:     df["Quarter"] = df.Date.dt.quarter
    ...:     df["Year"] = df.Date.dt.year
    ...:     df["Year_half"] = (df.Quarter + 1) // 2
    ...:     return df

In [12]: create_date_table2().head()
Out[12]:
        Date        Day  Week  Quarter  Year  Year_half
0 2000-01-01   Saturday    52        1  2000          1
1 2000-01-02     Sunday    52        1  2000          1
2 2000-01-03     Monday     1        1  2000          1
3 2000-01-04    Tuesday     1        1  2000          1
4 2000-01-05  Wednesday     1        1  2000          1

In [13]: create_date_table2().tail()
Out[13]:
            Date        Day  Week  Quarter  Year  Year_half
18623 2050-12-27    Tuesday    52        4  2050          2
18624 2050-12-28  Wednesday    52        4  2050          2
18625 2050-12-29   Thursday    52        4  2050          2
18626 2050-12-30     Friday    52        4  2050          2
18627 2050-12-31   Saturday    52        4  2050          2

Note: you may like to calculate these on the fly rather than store them as columns!

user_12402680

Use this function

def create_date_table(start='2000-01-01', end='2050-12-31'):
    start_ts = pd.to_datetime(start).date()

    end_ts = pd.to_datetime(end).date()

    # record timetsamp is empty for now
    dates =  pd.DataFrame(columns=['Record_timestamp'],
        index=pd.date_range(start_ts, end_ts))
    dates.index.name = 'Date'

    days_names = {
        i: name
        for i, name
        in enumerate(['Monday', 'Tuesday', 'Wednesday',
                      'Thursday', 'Friday', 'Saturday', 
                      'Sunday'])
    }

    dates['Day'] = dates.index.dayofweek.map(days_names.get)
    dates['Week'] = dates.index.week
    dates['Month'] = dates.index.month
    dates['Quarter'] = dates.index.quarter
    dates['Year_half'] = dates.index.month.map(lambda mth: 1 if mth <7 else 2)
    dates['Year'] = dates.index.year
    dates.reset_index(inplace=True)
    dates.index.name = 'date_id'
    return dates
user_40509250
Related