使用多个条件np.wh公司

Question

我试图将一些相对简单的条件组合成一个np.哪里子句，但我很难把逻辑的语法记下来。你知道吗

我当前的数据帧看起来像下面的df，有四列。我想添加两列，命名如下，条件如下：

所需输出低于-dfdf\u so\u v2

1. 活动后天数 *查找具有相同ID的最近在先行，然后减去“日期”列 *如果没有最新值，则返回NA

2. 更改。平均值 条件1：如果计数=0，则不适用 条件2：如果计数！=0，查找具有相同ID和计数的最近的前一行！=0，然后在“平均值”列中找到差值。

然而，我是建立在简单的np.哪里像下面这样的查询，不知道如何组合本例中所需的多个条件。你知道吗

df['CASH'] = np.where(df['CASH'] != 0, df['CASH'] + commission , df['CASH'])

非常感谢你在这方面的帮助。你知道吗

df_dict={'DateOf': ['2017-08-07','2017-08-07','2017-08-07','2017-08-04','2017-08-04','2017-08-04'
                , '2017-08-03','2017-08-03','2017-08-03','2017-08-02','2017-08-02','2017-08-02','2017-08-01','2017-08-01','2017-08-01'],
    'ID': ['553','559','914','553','559','914','553','559','914','553','559','914','553','559','914'], 'Count': [0, 4, 5, 0, 11, 10, 3, 9, 0,1,0,2,4,4,0],
    'Avg. Value': [0,3.5,2.2,0,4.2,3.3,5.3,5,0,3,0,2,4.4,6.4,0]}
df_so=pd.DataFrame(df_dict)

df_dict_v2={'DateOf': ['2017-08-07','2017-08-07','2017-08-07','2017-08-04','2017-08-04','2017-08-04'
                , '2017-08-03','2017-08-03','2017-08-03','2017-08-02','2017-08-02','2017-08-02','2017-08-01','2017-08-01','2017-08-01'],
    'ID': ['553','559','914','553','559','914','553','559','914','553','559','914','553','559','914'], 'Count': [0, 4, 5, 0, 11, 10, 3, 9, 0,1,0,2,4,4,0],
    'Avg. Value': [0,3.5,2.2,0,4.2,3.3,5.3,5,0,3,0,2,4.4,6.4,0],
    'Days_since_activity': [4,3,1,1,1,2,1,2,1,1,1,1,'NA','NA','NA'],
    'Chg. Avg Value': ['NA',-0.7,-1.1,'NA',-0.8,1.3,2.3,-1.4,'NA',-1.4,'NA','NA','NA','NA','NA']
    }

df_so_v2=pd.DataFrame(df_dict_v2)

Answer 1

这是这部分问题的答案。我需要更多关于2的条件的澄清。你知道吗

1）活动后的天数*查找具有相同ID的前一行，然后减去日期列*如果没有最新值，则返回NA

首先需要将字符串转换为datetime，然后按升序对日期进行排序。最后用.transform来找出差异。你知道吗

df_dict={'DateOf': ['2017-08-07','2017-08-07','2017-08-07','2017-08-04','2017-08-04','2017-08-04'
                , '2017-08-03','2017-08-03','2017-08-03','2017-08-02','2017-08-02','2017-08-02','2017-08-01','2017-08-01','2017-08-01'],
    'ID': ['553','559','914','553','559','914','553','559','914','553','559','914','553','559','914'], 'Count': [0, 4, 5, 0, 11, 10, 3, 9, 0,1,0,2,4,4,0],
    'Avg. Value': [0,3.5,2.2,0,4.2,3.3,5.3,5,0,3,0,2,4.4,6.4,0]}
df_so = pd.DataFrame(df_dict)
df_so['DateOf'] = pd.to_datetime(df_so['DateOf'])

df_so.sort_values('DateOf', inplace=True)
df_so['Days_since_activity'] = df_so.groupby(['ID'])['DateOf'].transform(pd.Series.diff)
df_so.sort_index()

根据您的评论编辑： 找到前一天没有计数为零的最近一天，然后计算差值。你知道吗

df_dict={'DateOf': ['2017-08-07','2017-08-07','2017-08-07','2017-08-04','2017-08-04','2017-08-04'
                , '2017-08-03','2017-08-03','2017-08-03','2017-08-02','2017-08-02','2017-08-02','2017-08-01','2017-08-01','2017-08-01'],
    'ID': ['553','559','914','553','559','914','553','559','914','553','559','914','553','559','914'], 'Count': [0, 4, 5, 0, 11, 10, 3, 9, 0,1,0,2,4,4,0],
    'Avg. Value': [0,3.5,2.2,0,4.2,3.3,5.3,5,0,3,0,2,4.4,6.4,0]}

df = pd.DataFrame(df_dict)
df['DateOf'] = pd.to_datetime(df['DateOf'], format='%Y-%m-%d')

df.sort_values(['ID','DateOf'], inplace=True)
df['Days_since_activity'] = df.groupby(['ID'])['DateOf'].diff()

mask = df.ID != df.ID.shift(1)
mask2 = df.groupby('ID').Count.shift(1) == 0

df['Days_since_activity'][mask] = np.nan
df['Days_since_activity'][mask2] = df.groupby(['ID'])['DateOf'].diff(2)

df['Chg. Avg Value'] = df.groupby(['ID'])['Avg. Value'].diff()
df['Chg. Avg Value'][mask2] = df.groupby(['ID'])['Avg. Value'].diff(2)

conditions = [((df['Count'] == 0)),]
choices = [np.nan,]
df['Chg. Avg Value'] = np.select(conditions, choices, default = df['Chg. Avg Value'])

# df = df.sort_index()
df

新的未排序输出便于比较：

    DateOf  ID  Count   Avg. Value  Days_since_activity Chg. Avg Value
12  2017-08-01  553 4   4.4      NaT        NaN
9   2017-08-02  553 1   3.0      1 days     -1.4
6   2017-08-03  553 3   5.3      1 days     2.3
3   2017-08-04  553 0   0.0      1 days     NaN
0   2017-08-07  553 0   0.0      4 days     NaN
13  2017-08-01  559 4   6.4      NaT        NaN
10  2017-08-02  559 0   0.0      1 days     NaN
7   2017-08-03  559 9   5.0      2 days     -1.4
4   2017-08-04  559 11  4.2      1 days     -0.8
1   2017-08-07  559 4   3.5      3 days     -0.7
14  2017-08-01  914 0   0.0      NaT        NaN
11  2017-08-02  914 2   2.0      NaT        NaN
8   2017-08-03  914 0   0.0      1 days     NaN
5   2017-08-04  914 10  3.3      2 days     1.3
2   2017-08-07  914 5   2.2      3 days     -1.1

索引11应该是NaT，因为最新的前一行的计数为零，没有其他可以比较的