如何替换组中的空值？

Question

我创建了这个数据框架，我计算了我正在寻找的差距，但问题是，一些公寓的价格是相同的，我得到的差价是0。我怎么能用同一组的最后一个较低价格的差值替换0呢？

例如：

neighboorhood:a, bed:1, bath:1, price:5

neighboorhood:a, bed:1, bath:1, price:5

neighboorhood:a, bed:1, bath:1, price:3

neighboorhood:a, bed:1, bath:1, price:2

我得到的差价是0,2,1，nan，我正在寻找2,2,1，nan (简单地说，我不想比较2个相同价格的公寓)

提前感谢，祝您日安。

data=[
    [1,'a',1,1,5],[2,'a',1,1,5],[3,'a',1,1,4],[4,'a',1,1,2],[5,'b',1,2,6],[6,'b',1,2,6],[7,'b',1,2,3]
]
df = pd.DataFrame(data, columns = ['id','neighborhoodname', 'beds', 'baths', 'price']) 

df['difference_price'] = ( df.dropna()
                             .sort_values('price',ascending=False)
                             .groupby(['city','beds','baths'])['price'].diff(-1) )

Answer 1

我认为你可以先用diff删除所有用于groupby的列的重复项，然后在过滤后的数据中创建新列，最后使用merge with left join to original：

df1 = (df.dropna()
         .sort_values('price',ascending=False)
         .drop_duplicates(['neighborhoodname','beds','baths', 'price']))

df1['difference_price']  = df1.groupby(['neighborhoodname','beds','baths'])['price'].diff(-1)

df = df.merge(df1[['neighborhoodname','beds','baths','price', 'difference_price']], how='left')
print (df)
   id neighborhoodname  beds  baths  price  difference_price
0   1                a     1      1      5               1.0
1   2                a     1      1      5               1.0
2   3                a     1      1      4               2.0
3   4                a     1      1      2               NaN
4   5                b     1      2      6               3.0
5   6                b     1      2      6               3.0
6   7                b     1      2      3               NaN

或者您可以使用lambda函数来回填每个组的0值，以避免在一行分组(数据从另一个组中移动)时出现错误输出：

df['difference_price'] = (df.sort_values('price',ascending=False)
                            .groupby(['neighborhoodname','beds','baths'])['price']
                            .apply(lambda x: x.diff(-1).replace(0, np.nan).bfill()))

print (df)     
   id neighborhoodname  beds  baths  price  difference_price
0   1                a     1      1      5               1.0
1   2                a     1      1      5               1.0
2   3                a     1      1      4               2.0
3   4                a     1      1      2               NaN
4   5                b     1      2      6               3.0
5   6                b     1      2      6               3.0
6   7                b     1      2      3               NaN