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

本文介绍了如何使用python创建4-4-5会计日历?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我希望有一个数据框，该数据框由相应的星期数和月份组成，每个日期从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

工作原理:

递归部分生成从 2021-01-01 到定义的结束日期的日期行
主查询生成具有正确字符串格式的列

Recursive part generate date rows from 2021-01-01 to defined end date
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');

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

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

重点是它是为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会计日历?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！