日期维度表是数据仓库设计中的重要部分,在之前一篇MySQL 制作日期表有介绍使用SQL语言制作,本文来一波如何使用Python生成日期维度表。
- 导入必要的库
需要导入datetime模块,以便在Python中处理日期;导入pandas模块,便于展示,如果遇到没有次模块问题,可前往终端进行pip/conda install 模块;
from datetime import date, timedelta
import pandas as pd
- 进行测试,定义生成日期维度表的函数
日期信息变量可以进行打印测试,待测试没有问题后,进行封装,定义一个名为generate_pub_calendar的函数,该函数接受起始日期和结束日期作为参数,并返回日期相关信息。
def generate_pub_calendar(start_date, end_date):
date_data_info = []
current_date = start_date
# 使用循环生成日期区间的信息
while current_date <= end_date:
calendar_year = current_date.year
year_start_dt = date(current_date.year, 1, 1)
year_end_dt = date(current_date.year, 12, 31)
calendar_week = current_date.strftime('%W')
# calendar_year_week 年份和年中第几周拼接,乘出位数相加,用作排序和定位年周
calendar_year_week = current_date.year * 100 + current_date.isocalendar()[1]
calendar_weekday = current_date.weekday()+1
calendar_weekday_name = current_date.strftime('%A')
week_begin_dt = current_date - timedelta(days=current_date.weekday())
week_end_dt = week_begin_dt + timedelta(days=6)
calendar_month = current_date.month
calendar_month_name = current_date.strftime('%B')
month_begin_dt = date(current_date.year, current_date.month, 1)
# month_end_dt 先得出下一个月第一天 减去一天 即可
month_end_dt = date(current_date.year + int(current_date.month/12), current_date.month % 12 + 1, 1) - timedelta(days=1)
calendar_year_month = current_date.year*100 + current_date.month
# calendar_quarter //(向下取整除,它会返回整除结果的整数部分)
calendar_quarter = (current_date.month - 1)//3 + 1
# quarter_begin_dt 根据当前季度数*3月份数再加上1个月
quarter_begin_dt = date(current_date.year, ((current_date.month - 1) // 3) * 3 + 1, 1)
# quarter_end_dt 根据 month_end_dt 得出逻辑 和 calendar_quarter 配合即可得出
quarter_end_dt = date(current_date.year + int(((current_date.month - 1)//3 + 1) * 3/12),(((current_date.month - 1)//3 + 1) * 3) % 12 + 1, 1) - timedelta(days=1)
# calendar_year_quarter 根据 calendar_year、calendar_quarter 做拼接即可
calendar_year_quarter = current_date.year * 100 + ((current_date.month - 1)//3 + 1)
# week_in_calendar_quarter 根据 calendar_week 减去 quarter_begin_dt 所在的年周 即可得出
# week_in_calendar_quarter = current_date.isocalendar()[1] - date(current_date.year, ((current_date.month - 1) // 3) * 3 + 1, 1).isocalendar()[1]
week_in_calendar_quarter = int(current_date.strftime('%W')) - int(date(current_date.year, ((current_date.month - 1) // 3) * 3 + 1, 1).strftime('%W'))
# calendar_year_quarter_week 根据 calendar_year、calendar_quarter 、 week_in_calendar_quarter 做拼接即可
calendar_year_quarter_week = (current_date.year * 100 + ((current_date.month - 1)//3 + 1)) * 100 + (int(current_date.strftime('%W')) - int(date(current_date.year, ((current_date.month - 1) // 3) * 3 + 1, 1).strftime('%W')))
# day_in_calendar_year 当前日期 减 year_start_dt 即可
day_in_calendar_year = (current_date - date(current_date.year, 1, 1)).days
day_in_calendar_quarter = (current_date - date(current_date.year, ((current_date.month - 1) // 3) * 3 + 1, 1)).days
day_in_calendar_month = current_date.day
# 追加日期区间的信息数据
date_data_info.append({
'calendar_date': current_date,
'calendar_year': calendar_year,
'year_start_dt': year_start_dt,
'year_end_dt': year_end_dt,
'calendar_week': calendar_week,
'calendar_year_week': calendar_year_week,
'calendar_weekday': calendar_weekday,
'calendar_weekday_name': calendar_weekday_name,
'week_begin_dt': week_begin_dt,
'week_end_dt': week_end_dt,
'calendar_month': calendar_month,
'calendar_month_name': calendar_month_name,
'month_begin_dt': month_begin_dt,
'month_end_dt': month_end_dt,
'calendar_year_month': calendar_year_month,
'calendar_quarter': calendar_quarter,
'quarter_begin_dt': quarter_begin_dt,
'quarter_end_dt': quarter_end_dt,
'calendar_year_quarter': calendar_year_quarter,
'week_in_calendar_quarter': week_in_calendar_quarter,
'calendar_year_quarter_week': calendar_year_quarter_week,
'day_in_calendar_year': day_in_calendar_year,
'day_in_calendar_quarter': day_in_calendar_quarter,
'day_in_calendar_month': day_in_calendar_month
})
current_date += timedelta(days=1)
return date_data_info
在这个函数中,我们使用循环和datetime模块提供的功能来计算相关日期信息,最后将这些信息存储为字典。
- 调用函数并生成日期维度表
start_date = date(2023, 1, 1)
end_date = date(2023, 12, 31)
pub_calendar_data = generate_pub_calendar(start_date, end_date)
df = pd.DataFrame(pub_calendar_data)
print(df.tail(10).to_markdown(index=False))
calendar_date | calendar_year | year_start_dt | year_end_dt | calendar_week | calendar_year_week | calendar_weekday | calendar_weekday_name | week_begin_dt | week_end_dt | calendar_month | calendar_month_name | month_begin_dt | month_end_dt | calendar_year_month | calendar_quarter | quarter_begin_dt | quarter_end_dt | calendar_year_quarter | week_in_calendar_quarter | calendar_year_quarter_week | day_in_calendar_year | day_in_calendar_quarter | day_in_calendar_month |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2023-12-22 | 2023 | 2023-01-01 | 2023-12-31 | 51 | 202351 | 5 | Friday | 2023-12-18 | 2023-12-24 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 12 | 20230412 | 355 | 82 | 22 |
2023-12-23 | 2023 | 2023-01-01 | 2023-12-31 | 51 | 202351 | 6 | Saturday | 2023-12-18 | 2023-12-24 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 12 | 20230412 | 356 | 83 | 23 |
2023-12-24 | 2023 | 2023-01-01 | 2023-12-31 | 51 | 202351 | 7 | Sunday | 2023-12-18 | 2023-12-24 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 12 | 20230412 | 357 | 84 | 24 |
2023-12-25 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 1 | Monday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 358 | 85 | 25 |
2023-12-26 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 2 | Tuesday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 359 | 86 | 26 |
2023-12-27 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 3 | Wednesday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 360 | 87 | 27 |
2023-12-28 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 4 | Thursday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 361 | 88 | 28 |
2023-12-29 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 5 | Friday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 362 | 89 | 29 |
2023-12-30 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 6 | Saturday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 363 | 90 | 30 |
2023-12-31 | 2023 | 2023-01-01 | 2023-12-31 | 52 | 202352 | 7 | Sunday | 2023-12-25 | 2023-12-31 | 12 | December | 2023-12-01 | 2023-12-31 | 202312 | 4 | 2023-10-01 | 2023-12-31 | 202304 | 13 | 20230413 | 364 | 91 | 31 |
通过调用这个函数,我们可以生成包含各种日期维度信息的日期维度表。
日期维度表为数据分析和业务智能提供了强大的基础,生成日期维度表是数据分析工作中的基础步骤之一,其实实现思路基本一致,只要你理清思路了,无论使用何种语言和工具,都可实现,大家可以实操起来,看看都掌握了几种工具。