如何使用 python 创建 4-4-5 会计日历?

2022-01-11 00:00:00 python pandas sql calendar

问题描述

我想要一个数据框，其中包含从 1/1/21 到 31/12/50 属于的每个日期的相应周数和月份.例如，

I would like to have a dataframe consisting of the corresponding week numbers and month every date from 1/1/21 to 31/12/50 would belong to. For instance,

  date  |   day   | week_no | yyyy-mm | quarter
01/01/21| Friday  |    40   | 2021-01 | Q4 2021
02/01/21| Saturday|    40   | 2021-01 | Q4 2021
.
.
24/01/21| Sunday  |    44   | 2021-02 | Q4 2021
.
.
01/04/21| Thursday|    53   | 2021-03 | Q4 2021
.
04/04/21| Sunday  |    1    | 2021-04 | Q1 2022

我在 Pandas 中发现了一个名为 FY5253Quarter 的包装类 和 fiscal445 在 python 中，但是，我不确定如何使用这个类达到我的要求.提前感谢所有帮助:)

I found a wrapper class in Pandas called FY5253Quarter and fiscal445 in python, however, I am not sure how I can use this class to achieve what I require. Thanks in advance for all the help :)

解决方案

创建日历表可以用SQL完成.这里使用 PostgreSQL 的例子:

Creating calendar table could be done with SQL. Here example using PostgreSQL:

WITH RECURSIVE cte AS (
   SELECT CAST('2021-01-01' AS date) d
   UNION ALL
   SELECT d+1
   FROM cte
   WHERE d < CAST('2030-12-31' AS DATE)
)
SELECT 
  d AS "date",
  to_char(d, 'DAY') AS day,
  to_char(d, 'iw') AS week_no,
  to_char(d, 'yyyy-mm') AS "yyyy-mm",
  to_char(d, '"Q"q yyyy') AS "quarter"
FROM cte;

db<>小提琴演示

它是如何工作的:

  1. 递归部分生成从2021-01-01到定义结束日期的日期行
  2. 主查询生成具有正确字符串格式的列
  1. Recursive part generate date rows from 2021-01-01 to defined end date
  2. Main query generate columns with proper string formatting

现在通过查询，如果需要，您可以使用 CREATE TABLE myCalendar AS ... 实现它.

Now by having the query you could materialize it with CREATE TABLE myCalendar AS ... if needed.

如果您使用不同的 RDBMS，则模式基本相同 - 唯一的区别是使用方言特定的功能.

If you are using different RDBMS, the pattern is basically the same - the only difference is using dialect specific functions.

例如PostgreSQL有自己的函数generate_series:

For instance PostgreSQL has its own function generate_series:

SELECT * /* format functions here */
FROM generate_series(date '2021-01-01', date '2030-12-31', '1 day');

我如何指定我想要一个 4-4-5 日历，因为据我了解，您似乎正在创建一个普通日历?

How can I specify that I want a 4-4-5 calendar because from what I understand it seems you're creating a normal calendar?

是的，这是一个普通的日历.逻辑可以重写，但您已经找到了可行的解决方案.

Yes, here it is a normal calendar. The logic could be rewritten, but you already found a working solution.

我还找到了 PostgreSQL 的 4-4-5 财政年度 github.com/sqlsunday/calendar/blob/boss/CalendarFunctions.sql 的链接，但我在运行 Calendar.Fiscal_4_4_5 函数的脚本时总是遇到错误

Also I found a link to PostgreSQL for fiscal 4-4-5 github.com/sqlsunday/calendar/blob/boss/CalendarFunctions.sql but I always meet with an error running the script for function Calendar.Fiscal_4_4_5

关键是它是在 TSQL 中作为表值函数为 SQL Server 编写的.调用它使用:

The point is it's written for SQL Server in TSQL as table-valued function. To call it use:

SELECT *
FROM  Calendar.Fiscal_4_4_5 ('2021-01-01', '2030-12-31', '2021-01-01','445',1);

db<>小提琴演示

相关文章