Pandas:excel days360 equivalent

oknrviil  于 2023-04-18  发布在  其他
关注(0)|答案(2)|浏览(90)

我正在将一些excel模型转换为python,其中一些计算使用了excel中的days360函数,该函数基于一年360天计算两天之间的天数。
我一直在查看pandas.tseries.offsets,但什么也没看到。
例如在Excel中

d1 = 2/2/2015
d2 = 1/1/2018

=days(d1, d2) # 1064
=days360(d1, d2) # 1049
cvxl0en2

cvxl0en21#

在Pandas的days360上有a Reddit thread-这里是发布在那里的函数(似乎与您的示例输入一起工作):

def days360(start_date, end_date, method_eu=False):
    start_day = start_date.day
    start_month = start_date.month
    start_year = start_date.year
    end_day = end_date.day
    end_month = end_date.month
    end_year = end_date.year

    if (
        start_day == 31 or
        (
            method_eu is False and
            start_month == 2 and (
                start_day == 29 or (
                    start_day == 28 and
                    start_date.is_leap_year is False
                )
            )
        )
    ):
        start_day = 30

    if end_day == 31:
        if method_eu is False and start_day != 30:
            end_day = 1

            if end_month == 12:
                end_year += 1
                end_month = 1
            else:
                end_month += 1
        else:
            end_day = 30

    return (
        end_day + end_month * 30 + end_year * 360 -
        start_day - start_month * 30 - start_year * 360)

OP数据测试:

d1 = pd.to_datetime("2/2/2015")
d2 = pd.to_datetime("1/1/2018")

days360(d1, d2) # 1049
42fyovps

42fyovps2#

对于Spark:

def days360(df, start_date, end_date, method_eu=False):
  def is_leap_year(year):
    return F.when((year%4==0) & (~(year%100==0) | (year%400==0)), True).otherwise(False)

  start_day = F.dayofmonth(start_date)
  start_month =F.month(start_date)
  start_year = F.year(start_date)

  end_day = F.dayofmonth(end_date)
  end_month =F.month(end_date)
  end_year = F.year(end_date)

  is_method_eu = F.lit(method_eu==False)

  condition_1 = (start_day==31) | (is_method_eu & ((start_month==2) & ((start_day == 29) | ((start_day == 28) & (is_leap_year(start_year)==False)))))
  start_day = F.when(condition_1, 30).otherwise(start_day)

  condition_2 = (end_day==31)
  condition_3 = (is_method_eu) & (start_day != 30)
  condition_4 = (end_month == 12)

  end_day = F.when(condition_2, F.when(condition_2 & condition_3, 1).otherwise(30)).otherwise(end_day)
  end_year =  F.when(condition_2 & condition_3 & condition_4, end_year + 1).otherwise(end_year)
  end_month =  F.when(condition_2 & condition_3 & condition_4,  1).otherwise(F.when(condition_2 & condition_3, end_month+1).otherwise(end_month))

  return (
        end_day + end_month * 30 + end_year * 360 -
        start_day - start_month * 30 - start_year * 360)

# df.select(days360(df, df['start_date'], df['end_date'])).show()

相关问题