一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

SQLを用いて重複レコードを全抽出(2回目以降出現のレコードを抽出するのではなく!)[R]

データには重複が含まれることがある

 重複データを抽出したり、削除したりを何回か話題に挙げました。
今回も、また重複データを取り上げるのは重複データに重要な意味があることが多いためです。
 例えば、1購入で1行というトランザクション形式の商品販売履歴データならば、リピーター様のデータは重複して出現します。

重複データの例

重複データの例
# 姓+名+購入品   
NameFam <-c("山田","山田","鈴本","山田","下山","下山","大林","浅井","下山","早川")
Name1st <-c("直子","太郎","次郎","直子","次郎","次郎","深見","沼男","次郎","分子")
Purchase<-c("花札","帽子","雨傘","日傘","眼鏡","下着","菓子","パン","牛乳","文庫")
Sample <-data.frame(NameFam,Name1st,Purchase)
Sample

   NameFam Name1st Purchase
1     山田    直子     花札
2     山田    太郎     帽子
3     鈴本    次郎     雨傘
4     山田    直子     日傘
5     下山    次郎     眼鏡
6     下山    次郎     下着
7     大林    深見     菓子
8     浅井    沼男     パン
9     下山    次郎     牛乳
10    早川    分子     文庫

duplicated だと困ったことが…

 Rにはduplicated関数というものがあります。この関数は、重複判定となる項目が同じものが2回目以降に現れたときにTRUEとなります。1回目はFalseなのです。重複しているデータを全て抽出しているわけではありません。

duplicatedの例
    # duplicated は一部の複数列を判定に用いることができないらしい   
 df<-subset(Sample,,c(NameFam,Name1st))
    # duplicatedでTRUE判定のものを表示
    Sample[duplicated(df),]


  NameFam Name1st Purchase
4    山田    直子     日傘
6    下山    次郎     下着
9    下山    次郎     牛乳

 duplicated は2回目以降に出現するデータを抽出します。
 そのため、山田直子さんが花札を買ったデータと、下山次郎さんがメガネを買ったデータが抽出されません。
 こういう抽出結果に意味がある場合もあるかもしれませんが、重複データ全体を把握するためにはこの方法は使えません。

重複とは出現回数が2以上ということ

出現回数を数える

 根本的なところで、重複データとは出現回数が2以上ということですから、出現回数を数えることからはじめます。

出現回数をカウントする
     require("sqldf") # sqldfパッケージを読み込む
 
# 姓+名 毎で 出現回数をカウントする
sqldf("select NameFam,Name1st,count(*) from Sample Group by Name1st,NameFam")

  NameFam Name1st count(*)
1    下山    次郎        3
2    鈴本    次郎        1
3    浅井    沼男        1
4    大林    深見        1
5    山田    太郎        1
6    山田    直子        2
7    早川    分子        1

 ここでは、sqldfを用いて SQL により、Name1st,NameFam をユニークキーとして出現回数を求めています。
 count(*) が出現回数であり、これが2以上であれば重複があるということです。
 下山次郎さんと山田直子さんに重複データがあります。

出現回数>1のデータのみ数える

   出現回数が2以上が重複データということですから、各レコードに出現回数を付与した後、出現回数>1に絞り込みます。

元データに出現回数を追加し、その出現回数により重複レコードを抽出する
     require("sqldf") # sqldfパッケージを読み込む
 
# 出現回数>1のみ抽出する
sqldf("select x.* from Sample as x,(select Name1st as name,NameFam as sei,count(*) as ct from Sample Group by Name1st,NameFam) 
 where Name1st=name and NameFam=sei and ct>1")

  NameFam Name1st Purchase
1    山田    直子     花札
2    山田    直子     日傘
3    下山    次郎     眼鏡
4    下山    次郎     下着
5    下山    次郎     牛乳

 ここでも sqldfを用いて SQL を活用します。
 元のデータに、副照会クエリを利用して出現回数を結合し、出現回数>1のみに絞り込んでいます。
 SQLを用いて重複レコードを抽出できました。

RではなくてもSQLなら同じこと

 ここまで R言語 の話としてきましたが、実際には SQL ならRでなくても同じことです。

SQLの書き方

select x.* from 
     Sample as x,
     (select Name1st as name,NameFam as sei,count(*) as ct from Sample Group by Name1st,NameFam) 
 where Name1st=name and NameFam=sei and ct>1