如何使用python创建4-4-5会计日历? [英] How to create a 4-4-5 fiscal calendar using python?

问题描述

我希望有一个数据框，该数据框由相应的星期数和月份组成，每个日期从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 ，但是，我不确定如何使用此类达到我的要求.预先感谢您提供的所有帮助:)

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< fiddle演示

db<>fiddle demo

工作原理:

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.

我还找到了4-4-5财政年度PostgreSQL的链接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&f; fiddle演示

db<>fiddle demo

这篇关于如何使用python创建4-4-5会计日历?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！