別のデータ条件にあう行を検索し、削除する。

実現したいこと

別のデータフレームから条件に合う行を検索し、該当する行を削除したいです。

レジの購買データを分析していて、返品などによりマイナス金額レコードが存在します。
マイナス金額を処理するために、該当のプラス金額レコードを検索し、相殺させたいです。

前提

<データフレーム>
index 店舗コード会員番号金額取引日時
1 1001 111 100 2022/1/10
2 1002 222 200 2022/1/30
3 1001 111 200 2022/2/5
4 1003 555 500 2022/3/10
5 1002 111 200 2022/4/6
6 1001 111 200 2022/5/1
7 1004 444 600 2022/5/4
8 1002 333 500 2022/8/10
9 1001 111 -200 2022/8/11
10 1002 222 -200 2022/10/1

上記のフレームで、index10と２、9と7を消したいです。
（同日取引日などもあるので、条件に合致するレコードが1レコード見つかったら処理終了のようにし、
1対1に紐づけたいです）

試したこと

マイナス金額のデータとプラス金額のデータにフレームを分け、
日付日時が直近の順にソートしました。

#マイナスデータのみ抽出
data_minus = data[data['金額'] < 0]

#マイナスデータの並び替え
data_minus = data_minus.sort_values('取引日時',ascending=False)

＃マイナス金額を絶対値に置き換え
data_minus['金額'] = data_minus['金額'].abs()

#プラスのデータのみ抽出し、並び替え
data = data[data['金額']>=0]
data = data.sort_values('取引日時',ascending=False)

data_minus['店舗コード'] == data['店舗コード']&
data_minus['会員番号'] == data['会員番号']&
data_minus['金額'] == data['会員番号'] &
data_minus['取引日時'] >= data['取引日時']
の条件に合致するレコードをdataのフレームから検索し、
最も日付の近い1レコードを削除したいと考えておりますが、
コードが分からずにいます。

補足情報（FW/ツールのバージョンなど）

for idx,row in data_minus.iterrows():

for文で繰り返し処理をする。iterrowsが使えそう？までは
調べたのですが、最終的にどのようにすればよいかが分からない状況です。

行動規範の内容に同意します

回答1件

ベストアンサー

index10と2、9と7を消したいです。

おそらく、10 と 2, 9 と 6 ではないかと思います。

python
1import pandas as pd
2import io
3from functools import reduce
4
5csv_data = '''
6index,店舗コード,会員番号,金額,取引日時
71,1001,111,100,2022/1/10
82,1002,222,200,2022/1/30
93,1001,111,200,2022/2/5
104,1003,555,500,2022/3/10
115,1002,111,200,2022/4/6
126,1001,111,200,2022/5/1
137,1004,444,600,2022/5/4
148,1002,333,500,2022/8/10
159,1001,111,-200,2022/8/11
1610,1002,222,-200,2022/10/1
17'''
18df = pd.read_csv(io.StringIO(csv_data), parse_dates=['取引日時'])
19df['取引日時'] = df['取引日時'].dt.date
20
21idx = df.sort_values('取引日時').assign(金額_abs=df['金額'].abs())\
22        .groupby(['店舗コード', '会員番号', '金額_abs'])\
23        .apply(lambda x: [l.index for l in x.rolling(2) if len(l)==2 and l['金額'].sum()==0][:1])\
24        .sum()
25dfx = df.drop(reduce(pd.Index.union, idx), errors='ignore')
26
27print(dfx)

index	店舗コード	会員番号	金額	取引日時
1	1001	111	100	2022-01-10
3	1001	111	200	2022-02-05
4	1003	555	500	2022-03-10
5	1002	111	200	2022-04-06
7	1004	444	600	2022-05-04
8	1002	333	500	2022-08-10

追記

ここの箇所の理解ができなかったのですが、どのようなことをされているのか

以下は groupby(['店舗コード', '会員番号', '金額_abs']) によってグループ化されたデータフレームの一つです。このデータフレームが apply() に指定した lambda 関数の引数(x)に渡されることになります。

python
1   index  店舗コード  会員番号  金額    取引日時  金額_abs
22      3        1001       111   200  2022-02-05       200
35      6        1001       111   200  2022-05-01       200
48      9        1001       111  -200  2022-08-11       200

次に、rolling(2) はスライドしながら2行づつまとめます。具体的には以下の様な中身になります。

python
1# rolling(2)[0]
2   index  店舗コード  会員番号  金額    取引日時  金額_abs
32      3        1001       111   200  2022-02-05       200
45      6        1001       111   200  2022-05-01       200
5
6# rolling(2)[1]
7   index  店舗コード  会員番号  金額    取引日時  金額_abs
85      6        1001       111   200  2022-05-01       200
98      9        1001       111  -200  2022-08-11       200

最初に index の値が 3 と 6 の行、次に 6, 9 の行になります。これをリスト内包表記で処理していきます。

python
1.apply(lambda x: [l.index for l in x.rolling(2) if len(l)==2 and l['金額'].sum()==0][:1])

「金額」列の合計が 0 になる(l['金額'].sum()==0)組み合わせを抽出して、そのインデックス(index 列の値ではなくデータフレームのインデックス)を返します。[:1] を付けているのは、合計金額が 0 になる最初の組み合わせだけを取り出すためですが、金額が全て 0 よりも大きい場合(入金データのみ)は空リストが返るので [0] を指定するとエラーになってしまうことを考慮しています。

投稿2023/02/17 14:08

編集2023/02/19 10:25

melian

総合スコア21118

minami_min

2023/02/19 08:03

melianさんご回答、ご指摘ありがとうございます。勉強不足で恐縮ですが、 apply(lambda x: [l.index for l in x.rolling(2) if len(l)==２ここの箇所の理解ができなかったのですが、どのようなことをされているのか補足いただくことは可能でしょうか。（処理はできました）お手数ですが、ご回答頂けますと幸甚です。