2つのDataframeで期間重複するIDを抽出したい


python3とpandasを使っています。  
df1,df2の2つのDataframeのうち同じIDの人物の日付を比較し、期間重複がある行のIDを取り出したいです。  
実際には10万行ほどあります。

[code]

    data1 = [[1, "2010-01-01", "2010-01-20"], [1, "2010-03-20", "2010-03-30"],
            [2, "2010-02-01", "2010-04-20"], [3, "2010-06-10", "2010-06-15"],
            [3, "2010-06-20", "2010-06-30"], [3, "2010-07-10", "2010-06-20"]]
    
    data2 = [[1, "2010-01-15", "2010-01-30"], [1, "2010-04-20", "2010-04-30"],
            [2, "2010-05-01", "2010-05-10"], [2, "2010-05-20", "2010-05-25"],
            [3, "2010-02-01", "2010-02-15"], [3, "2010-06-15", "2010-06-25"]]
    
    columns1 = ["ID", "start_date", "end_date"]
    
    df1 = pd.DataFrame(data=data1,columns=columns)
    df2 = pd.DataFrame(data=data2,columns=columns)
    
[/code]

これをIDで比較して、下記のようなリストがほしいです。

result_list = [1,3]

[code]

    result_list = []
    for index,row1 in df1.iterrows():
        if not row1["ID"] in result_list:
            for index,row2 in df2.iterrows():
                if row1["ID"] == row2["ID"] and row2["start_date"] <= row1["end_date"] and row2["end_date"] >= row1["start_date"]:
                    result_list.append(row1["ID"])
                    break
    result_list = list(set(result_list))
    result_list
    
[/code]

試行錯誤して，求める結果を得ることができましたが  
効率が悪いような気がしてなりません…多重for文を書かずに済む方法はないものでしょうか．  
アドバイス宜しくお願い致します.




For 文を pandas で使いたくない派なので、ひたすらデータフレーム処理で重複を求めていくと、次のようになると思います。

[code]

    In [1]: import pandas as pd
    
    In [2]: data1 = [[1, "2010-01-01", "2010-01-20"], [1, "2010-03-20", "2010-03-30"],
          :         [2, "2010-02-01", "2010-04-20"], [3, "2010-06-10", "2010-06-15"],
          :         [3, "2010-06-20", "2010-06-30"], [3, "2010-07-10", "2010-07-20"]]
          :
          : data2 = [[1, "2010-01-15", "2010-01-30"], [1, "2010-04-20", "2010-04-30"],
          :         [2, "2010-05-01", "2010-05-10"], [2, "2010-05-20", "2010-05-25"],
          :         [3, "2010-02-01", "2010-02-15"], [3, "2010-06-15", "2010-06-25"]]
          :
          : columns = ["ID", "start_date", "end_date"]
          :
          : df1 = pd.DataFrame(data=data1,columns=columns)
          : df2 = pd.DataFrame(data=data2,columns=columns)
    
    In [3]: df = pd.concat([df1, df2], keys=["df1", "df2"])
          : df
    Out[3]:
           ID  start_date    end_date
    df1 0   1  2010-01-01  2010-01-20
        1   1  2010-03-20  2010-03-30
        2   2  2010-02-01  2010-04-20
        3   3  2010-06-10  2010-06-15
        4   3  2010-06-20  2010-06-30
        5   3  2010-07-10  2010-07-20
    df2 0   1  2010-01-15  2010-01-30
        1   1  2010-04-20  2010-04-30
        2   2  2010-05-01  2010-05-10
        3   2  2010-05-20  2010-05-25
        4   3  2010-02-01  2010-02-15
        5   3  2010-06-15  2010-06-25
    
    In [4]: stacked = df.set_index("ID", append=True).stack().to_frame()
          : stacked
    Out[4]:
                                  0
          ID
    df1 0 1  start_date  2010-01-01
             end_date    2010-01-20
        1 1  start_date  2010-03-20
             end_date    2010-03-30
        2 2  start_date  2010-02-01
             end_date    2010-04-20
        3 3  start_date  2010-06-10
             end_date    2010-06-15
        4 3  start_date  2010-06-20
             end_date    2010-06-30
        5 3  start_date  2010-07-10
             end_date    2010-07-20
    df2 0 1  start_date  2010-01-15
             end_date    2010-01-30
        1 1  start_date  2010-04-20
             end_date    2010-04-30
        2 2  start_date  2010-05-01
             end_date    2010-05-10
        3 2  start_date  2010-05-20
             end_date    2010-05-25
        4 3  start_date  2010-02-01
             end_date    2010-02-15
        5 3  start_date  2010-06-15
             end_date    2010-06-25
    
    In [5]: ordered = stacked.reset_index(level=2).sort_values(['ID', 0])
          : ordered
    Out[5]:
                      ID           0
    df1 0 start_date   1  2010-01-01
    df2 0 start_date   1  2010-01-15
    df1 0 end_date     1  2010-01-20
    df2 0 end_date     1  2010-01-30
    df1 1 start_date   1  2010-03-20
          end_date     1  2010-03-30
    df2 1 start_date   1  2010-04-20
          end_date     1  2010-04-30
    df1 2 start_date   2  2010-02-01
          end_date     2  2010-04-20
    df2 2 start_date   2  2010-05-01
          end_date     2  2010-05-10
        3 start_date   2  2010-05-20
          end_date     2  2010-05-25
        4 start_date   3  2010-02-01
          end_date     3  2010-02-15
    df1 3 start_date   3  2010-06-10
          end_date     3  2010-06-15
    df2 5 start_date   3  2010-06-15
    df1 4 start_date   3  2010-06-20
    df2 5 end_date     3  2010-06-25
    df1 4 end_date     3  2010-06-30
        5 start_date   3  2010-07-10
          end_date     3  2010-07-20
    
    In [14]: seq_df = ordered[["ID"]].reset_index()[["level_2", "ID"]].pipe(
           :     lambda df:
           :     df.assign(
           :         prev_level_2=df.groupby("ID").shift()
           :     )
           : )
           : seq_df
    Out[14]:
           level_2  ID prev_level_2
    0   start_date   1          NaN
    1   start_date   1   start_date
    2     end_date   1   start_date
    3     end_date   1     end_date
    4   start_date   1     end_date
    5     end_date   1   start_date
    6   start_date   1     end_date
    7     end_date   1   start_date
    8   start_date   2          NaN
    9     end_date   2   start_date
    10  start_date   2     end_date
    11    end_date   2   start_date
    12  start_date   2     end_date
    13    end_date   2   start_date
    14  start_date   3          NaN
    15    end_date   3   start_date
    16  start_date   3     end_date
    17    end_date   3   start_date
    18  start_date   3     end_date
    19  start_date   3   start_date
    20    end_date   3   start_date
    21    end_date   3     end_date
    22  start_date   3     end_date
    23    end_date   3   start_date
    
    In [15]: overlap_df = seq_df.pipe(
           :     lambda df:
           :     df[df["level_2"] == df["prev_level_2"]]
           : )
           : overlap_df
    Out[15]:
           level_2  ID prev_level_2
    1   start_date   1   start_date
    3     end_date   1     end_date
    19  start_date   3   start_date
    21    end_date   3     end_date
    
    In [16]: overlap_df["ID"].unique()
    Out[16]: array([1, 3])
    
[/code]

