通过基于2组仅选择一个值并将其余值转换为0来对Pandas组进行选择 [英] Pandas groupby selecting only one value based on 2 groups and converting rest to 0

问题描述

我有一个熊猫数据框，它的日期时间索引看起来像这样:

I have a pandas data frame which has a datetime index which looks like this:

df =

           Fruit    Quantity
01/02/10    Apple   4
01/02/10    Apple   6
01/02/10    Pear    7
01/02/10    Grape   8
01/02/10    Grape   5
02/02/10    Apple   2
02/02/10    Fruit   6
02/02/10    Pear    8
02/02/10    Pear    5

现在对于每个日期和每个水果，我只希望一个值(最好是最上面的一个)，而其余的水果对于日期则保持为零.因此，期望的输出如下:

Now for each date and for each fruit I only want one value (preferably the top one) and the rest of the fruit for the date to remain zero. So desired output is as follows:

           Fruit    Quantity
01/02/10    Apple   4
01/02/10    Apple   0
01/02/10    Pear    7
01/02/10    Grape   8
01/02/10    Grape   0
02/02/10    Apple   2
02/02/10    Fruit   6
02/02/10    Pear    8
02/02/10    Pear    0

这只是一个小例子，但是我的主数据框有超过300万行，并且不一定按日期排列水果.

This is only a small example, but my main data frame has over 3 million rows and the fruit are not necessarily in order per date.

谢谢

推荐答案

您可以使用:

• set_index 用于索引名称
• groupby ，其中<用于计数器的a href ="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.cumcount.html" rel ="nofollow noreferrer"> cumcount 每组
• 通过
通过 where 和遮罩

• set_index for index name
• groupby with cumcount for Counter per groups
• get boolean mask for first groups by eq
• set 0 by where and mask

m = df.rename_axis('Date').groupby(['Date', 'Fruit']).cumcount().eq(0)
df['Quantity'] = df['Quantity'].where(m, 0)
print (df)
          Fruit  Quantity
01/02/10  Apple         4
01/02/10  Apple         0
01/02/10   Pear         7
01/02/10  Grape         8
01/02/10  Grape         0
02/02/10  Apple         2
02/02/10  Fruit         6
02/02/10   Pear         8
02/02/10   Pear         0

使用 reset_index ，但必须通过将boolen掩码转换为numpy数组values ，因为索引不同:

Another solution with reset_index, but is necessary convert boolen mask to numpy array by values, because different indices:

m = df.reset_index().groupby(['index', 'Fruit']).cumcount().eq(0)
df['Quantity'] = df['Quantity'].where(m.values, 0)
print (df)
          Fruit  Quantity
01/02/10  Apple         4
01/02/10  Apple         0
01/02/10   Pear         7
01/02/10  Grape         8
01/02/10  Grape         0
02/02/10  Apple         2
02/02/10  Fruit         6
02/02/10   Pear         8
02/02/10   Pear         0

时间:

np.random.seed(1235)

N = 10000
L = ['Apple','Pear','Grape','Fruit']
idx = np.repeat(pd.date_range('2017-010-01', periods=N/20).strftime('%d/%m/%y'), 20)
df = (pd.DataFrame({'Fruit': np.random.choice(L, N),
                   'Quantity':np.random.randint(100, size=N), 'idx':idx})
      .sort_values(['Fruit','idx'])
      .set_index('idx')
      .rename_axis(None))             

#print (df)

---

def jez1(df):
    m = df.rename_axis('Date').groupby(['Date', 'Fruit']).cumcount().eq(0)
    df['Quantity'] = df['Quantity'].where(m, 0)
    return df

def jez2(df):
    m = df.reset_index().groupby(['index', 'Fruit']).cumcount().eq(0)
    df['Quantity'] = df['Quantity'].where(m.values, 0)
    return df

def rnso(df):
    df['date_fruit'] = df.index+df.Fruit # new column with date and fruit merged
    dflist = pd.unique(df.date_fruit)    # find its unique values
    dfv = df.values                      # get rows as list of lists
    for i in dflist:                     # for each unique date-fruit combination
        done = False
        for c in range(len(dfv)): 
            if dfv[c][2] == i:           # check each row
                if done: 
                    dfv[c][1] = 0        # if not first, make quantity as 0
                else: 
                    done = True

    # create new dataframe with new data: 
    newdf = pd.DataFrame(data=dfv, columns=df.columns, index=df.index)
    return newdf.iloc[:,:2] 

---

print (jez1(df))      
print (jez2(df))      
print (rnso(df))      

In [189]: %timeit (rnso(df))
1 loop, best of 3: 6.27 s per loop

In [190]: %timeit (jez1(df))
100 loops, best of 3: 7.56 ms per loop

In [191]: %timeit (jez2(df))
100 loops, best of 3: 8.77 ms per loop

通过另一个答案进行

有一个问题，您需要按列Fruit和index重复的呼叫. 因此，有两种可能的解决方案:

There is problem you need call duplicated by column Fruit and index. So there are 2 possible solutions:

• 通过 reset_index 并调用 DataFrame.duplicated ，最后通过 values <将输出转换为numpy数组/a>
• 通过Fruit添加到index > set_index 并调用

• create column from index by reset_index and call DataFrame.duplicated, last convert output to numpy array by values
• add column Fruit to index by set_index and call Index.duplicated

#solution1
mask = df.reset_index().duplicated(['index','Fruit']).values
#solution2
#mask = df.set_index('Fruit', append=True).index.duplicated()
df.loc[mask, 'Quantity'] = 0

Timings1

def jez1(df):
    m = df.rename_axis('Date').groupby(['Date', 'Fruit']).cumcount().eq(0)
    df['Quantity'] = df['Quantity'].where(m, 0)
    return df

def jez3(df):
    mask = df.reset_index().duplicated(['index','Fruit']).values
    df.loc[mask, 'Quantity'] = 0
    return df

def jez4(df):
    mask = df.set_index('Fruit', append=True).index.duplicated()
    df.loc[mask, 'Quantity'] = 0
    return df

print (jez1(df))
print (jez3(df))
print (jez4(df))

In [268]: %timeit jez1(df)
100 loops, best of 3: 6.37 ms per loop

In [269]: %timeit jez3(df)
100 loops, best of 3: 3.82 ms per loop

In [270]: %timeit jez4(df)
100 loops, best of 3: 4.21 ms per loop

这篇关于通过基于2组仅选择一个值并将其余值转换为0来对Pandas组进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！