基于熊猫DF中的值返回日期时间

Question

我正在工作从具有“ anual_quarters”的数据创建数据时间列，作为每个季度每个数据点的列名和值。

我正在考虑创建自定义功能并使用.Apply以返回可以使用的数据。但是我似乎无法使用Iterrows（）（）和IterItems（）

Heres在数据的每一行/col上编写一个迭代的功能。

from datetime import datetime
def get_recent_orders(merged_data):
    for index, row in merged_data.iteritems():
        if row['q4_sales_2021'] > 0:
            return datetime(2021, 10, 1)
        elif row['q3_sales_2021'] > 0:
            return datetime(2021, 7, 1)
        elif row['q2_sales_2021'] > 0:
            return datetime(2021, 4, 1)
        elif row['q1_sales_2021'] > 0:
            return datetime(2021, 1, 1)
        elif row['q4_sales_2020'] > 0:
            return datetime(2020, 10, 1)
        elif row['q3_sales_2020'] > 0:
            return datetime(2020, 7, 1)
        elif row['q2_sales_2020'] > 0:
            return datetime(2020, 4, 1)
        elif row['q1_sales_2020'] > 0:
            return datetime(2020, 1, 1)
        else:
            return None
merged_data['last_order'] = merged_data.apply(get_recent_orders, axis=0)

Answer 1

使用numpy.select：

import numpy as np

conditions = [merged_data['q4_sales_2021'].gt(0), 
              merged_data['q3_sales_2021'].gt(0),
              merged_data['q2_sales_2021'].gt(0),
              merged_data['q1_sales_2021'].gt(0),
              merged_data['q4_sales_2020'].gt(0), 
              merged_data['q3_sales_2020'].gt(0),
              merged_data['q2_sales_2020'].gt(0),
              merged_data['q1_sales_2020'].gt(0)]

choices = [datetime(2021, 10, 1),
           datetime(2021, 7, 1),
           datetime(2021, 4, 1),
           datetime(2021, 1, 1),
           datetime(2020, 10, 1),
           datetime(2020, 7, 1),
           datetime(2020, 4, 1),
           datetime(2020, 1, 1)]

merged_data["last_order"] = np.select(conditions, choices, None)

Answer 2

使用np.select：

condlist = [df['q4_sales_2021'] > 0,
            df['q3_sales_2021'] > 0,
            df['q2_sales_2021'] > 0,
            df['q1_sales_2021'] > 0,
            df['q4_sales_2020'] > 0,
            df['q3_sales_2020'] > 0,
            df['q2_sales_2020'] > 0,
            df['q1_sales_2020'] > 0]

choicelist = [datetime(2021, 10, 1),
              datetime(2021, 7, 1),
              datetime(2021, 4, 1),
              datetime(2021, 1, 1),
              datetime(2020, 10, 1),
              datetime(2020, 7, 1),
              datetime(2020, 4, 1),
              datetime(2020, 1, 1)]

default = pd.NAT

merged_data['last_order'] = np.select(condlist, choicelist, default)

Answer 3

您可以做到这一点：

df = pd.DataFrame(data={"annual_quarters": ["q4_sales_2021", "q3_sales_2021", "q2_sales_2021", "q1_sales_2021",
                                            "q4_sales_2020", "q3_sales_2020", "q2_sales_2020", "q1_sales_2020"]})

import datetime as dt

def get_recent_orders(quarter):
    month_list = [1, 4, 7, 10]
    month = month_list[int(str(quarter)[1])-1]
    year = int(str(quarter)[-4:])
    
    return dt.date(year, month, 1)

df["last_order"] = df["annual_quarters"].apply(get_recent_orders)

根据Q1、2、3或4的规定，每个月将是1月，APR，JUL或OCT字符串字符，最近4个字符的一年，day = 1。这样，根本不需要迭代行。