開始と終了レコードを結合するpythonスクリプトを最適化したい

Question

前提・実現したいこと

集計用にログの開始レコードと終了レコードを結合するスクリプトを作成しました。

もっと最適な実現方法があるのではないかと思うのですが
知識不足のため思いつきません。

みなさまのベストプラクティスを教えて頂ければ幸いです。
ライブラリなどの指定はありません。
よろしくお願いいたします。

該当のソースコード

## INPUT
    name status                dtime
0   siro     ST  2020-01-01 00:00:05
1   siro     ST  2020-01-01 00:00:10
2   siro     ED  2020-01-01 00:00:15
3   eiro     ST  2020-01-02 00:00:20
4   eiro     ST  2020-01-02 00:00:25
5   eiro     ED  2020-01-02 00:00:30
6   airo     ED  2020-01-03 00:00:35
7   kkkk     ST  2020-01-04 01:10:10
8   eiro     ST  2020-01-05 02:10:20
9   kkkk     ED  2020-01-05 01:10:30
10  eiro     ED  2020-01-05 02:20:30

##  OUTPUT
   name status1               dtime1 status2               dtime2
0  siro      ST  2020-01-01 00:00:05    None                 None
1  siro      ST  2020-01-01 00:00:10      ED  2020-01-01 00:00:15
2  eiro      ST  2020-01-02 00:00:20    None                 None
3  eiro      ST  2020-01-02 00:00:25      ED  2020-01-02 00:00:30
4  airo    None                 None      ED  2020-01-03 00:00:35
5  kkkk      ST  2020-01-04 01:10:10      ED  2020-01-05 01:10:30
6  eiro      ST  2020-01-05 02:10:20      ED  2020-01-05 02:20:30

## 現状のプログラム
import pandas as pd
data=[
    ['siro','ST','2020-01-01 00:00:05']
    ,['siro','ST','2020-01-01 00:00:10']
    ,['siro','ED','2020-01-01 00:00:15']
    ,['eiro','ST','2020-01-02 00:00:20']
    ,['eiro','ST','2020-01-02 00:00:25']
    ,['eiro','ED','2020-01-02 00:00:30']
    ,['airo','ED','2020-01-03 00:00:35']
    ,['kkkk','ST','2020-01-04 01:10:10']
    ,['eiro','ST','2020-01-05 02:10:20']
    ,['kkkk','ED','2020-01-05 01:10:30']
    ,['eiro','ED','2020-01-05 02:20:30']

]
cols = ['name', 'status1','dtime1','status2','dtime2']
odf = pd.DataFrame(index=[], columns=cols)

for i,row in df.iterrows():
    df.loc[i,'p']=False
    
for i,row in df.iterrows():

    if row.status == 'ST':
        df.loc[i,'p']=True
        
        for j,row2 in df[i+1:].iterrows():
            if row2.name==row2.name:
                df.loc[j,'p']=True
                if row2.status=='ST':
                    record = pd.Series([row.name, row.status, row.dtime,None,None], index=odf.columns)
                    odf = odf.append(record, ignore_index=True)
                    break
                
                if row2.status=='ED':
                    record = pd.Series([row.name, row.status, row.dtime,row2.status,row2.dtime], index=odf.columns)
                    odf = odf.append(record, ignore_index=True)
                    break

    if (row['p']==True):
        continue

    if row.status=='ED':
        df.loc[i,'p']=True
        record = pd.Series([row.name,None,None, row.status, row.dtime], index=odf.columns)
        odf = odf.append(record, ignore_index=True)

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

python 3.6
ライブラリなどの指定はありません。

Answer 1

nameでグループ化して、ST毎にカウントアップする列を作れば、その値を使ってSTに対応するEDを取り出せます。
この処理はgroupbyとcumsumでできます。
nameでグループ化して、statusをカウントアップして、EDの場合はそこから1ひく処理をすれば、その値を使ってSTに対応するEDを取り出せます。
(最初の回答だとEDが連続するケースがあった場合が違う出力になるので修正しました。そういうケースがないならどちらでもいいです。)

import pandas as pd

df = pd.DataFrame(data=[
    ['siro','ST','2020-01-01 00:00:05']
    ,['siro','ST','2020-01-01 00:00:10']
    ,['siro','ED','2020-01-01 00:00:15']
    ,['eiro','ST','2020-01-02 00:00:20']
    ,['eiro','ST','2020-01-02 00:00:25']
    ,['eiro','ED','2020-01-02 00:00:30']
    ,['airo','ED','2020-01-03 00:00:35']
    ,['kkkk','ST','2020-01-04 01:10:10']
    ,['eiro','ST','2020-01-05 02:10:20']
    ,['kkkk','ED','2020-01-05 01:10:30']
    ,['eiro','ED','2020-01-05 02:20:30']
], columns=['name', 'status','dtime'])

df['n'] = df.groupby('name')['status'].cumcount() - (df['status'] == 'ED')
#df['n'] = (df['status'] == 'ST').groupby(df['name']).cumsum()

あとは、これを使ってpivotかmergeをすればいいですが、欲しい形に近くなるのはmergeでしょう。
STだけのデータフレーム、EDだけのデータフレームを作って、nameと上で作った列でouter mergeします。

• STがないairoの行が下になってしまいます
• NoneではなくNaNが入っています

odf = pd.merge(
    df[df['status'] == 'ST'], df[df['status'] == 'ED'],
    on=['name', 'n'], how='outer', suffixes=('1', '2'))
odf = odf.drop(columns='n')

dtime1がない行をdtime2の値を使うようにしてソートします。

sort_idx = odf['dtime1'].fillna(odf['dtime2']).sort_values().index
odf = odf.reindex(sort_idx).reset_index(drop=True)
#   name status1               dtime1 status2               dtime2
#0  siri      ST  2020-01-01 00:00:05     NaN                  NaN
#1  siro      ST  2020-01-01 00:00:10      ED  2020-01-01 00:00:15
#2  eiro      ST  2020-01-02 00:00:20     NaN                  NaN
#3  eiro      ST  2020-01-02 00:00:25      ED  2020-01-02 00:00:30
#4  airo     NaN                  NaN      ED  2020-01-03 00:00:35
#5  kkkk      ST  2020-01-04 01:10:10      ED  2020-01-05 01:10:30
#6  eiro      ST  2020-01-05 02:10:20      ED  2020-01-05 02:20:30

Comments

[quinty]

ご返答ありがとうございます。
試させてもらいます。

[lehshell]

index まで合わせると気持ちいいと思います。
odf = odf.reset_index().drop(columns='index')

[lehshell]

odf = odf.reset_index(drop=True)
にすべきでした。m(__)m

[bsdfan]

lehshellさん
reset_index()はどっちでもいいかな、と思ったのですが、しておいたほうが気持ちはいいですね。

[quinty]

これ以上ないシンプルな解法かと思いました。

もしよろしければ結合キーにver項を加えた場合の解法を教えて頂けないでしょうか。
この辺でエラーが出ます。

df['n'] = (df['status'] == 'ST').groupby(df['name','ver']).cumsum()

[bsdfan]

これだけの情報ではちょっとわからないところですが、
groupby([df['name'], df['ver']])
でどうでしょう。
あとは、mergeのときのonにも'ver'を追加したらいいはずです。
これでダメなら別質問に挙げていただいた方がいいかも。

[quinty]

上手くいきました。ありがとうございました。

Answer 2

for文を使わないでやると、以下のようになります。
性能は良いと思いますが、理解しやすいかどうかは人によります。

df_sort = df.sort_values(['name', 'dtime'])
df_sort_prev = df_sort.shift(1)
df_sort['start_end'] = (df_sort['name']==df_sort_prev['name']) & (df_sort['status']=='ED') & (df_sort_prev['status']=='ST')
df_end = df_sort[['status','dtime']].where(df_sort['start_end']==True).shift(-1)
df_end[['status', 'dtime']] = df_sort[['status', 'dtime']].where((df_sort['start_end']==False) & (df_sort['status']=='ED'), df_end[['status', 'dtime']])
df_concat = pd.concat([df_sort, df_end], axis=1)
df_concat.columns = ['name', 'status1', 'dtime1', 'start_end', 'status2', 'dtime2']
df_result = df_concat[df_concat['start_end']==False].drop('start_end', axis=1).sort_index()

入力データ

>>> print(df)
    name status                dtime
0   siro     ST  2020-01-01 00:00:05
1   siro     ST  2020-01-01 00:00:10
2   siro     ED  2020-01-01 00:00:15
3   eiro     ST  2020-01-02 00:00:20
4   eiro     ST  2020-01-02 00:00:25
5   eiro     ED  2020-01-02 00:00:30
6   airo     ED  2020-01-03 00:00:35
7   kkkk     ST  2020-01-04 01:10:10
8   eiro     ST  2020-01-05 02:10:20
9   kkkk     ED  2020-01-05 01:10:30
10  eiro     ED  2020-01-05 02:20:30

実行結果

>>> print(df_result)
   name status1               dtime1 status2               dtime2
0  siro      ST  2020-01-01 00:00:05     NaN                  NaN
1  siro      ST  2020-01-01 00:00:10      ED  2020-01-01 00:00:15
3  eiro      ST  2020-01-02 00:00:20     NaN                  NaN
4  eiro      ST  2020-01-02 00:00:25      ED  2020-01-02 00:00:30
6  airo      ED  2020-01-03 00:00:35      ED  2020-01-03 00:00:35
7  kkkk      ST  2020-01-04 01:10:10      ED  2020-01-05 01:10:30
8  eiro      ST  2020-01-05 02:10:20      ED  2020-01-05 02:20:30

Comments

[quinty]

ご返答ありがとうございます。
試させてもらいます。

Answer 3

実行時間などは比較していないのでどれほど最適化できたかは不明ですが、できるだけforループを使わないようにするとよいかと思います。

import pandas as pd

data=[
     ['siro','ST','2020-01-01 00:00:05']
    ,['siro','ST','2020-01-01 00:00:10']
    ,['siro','ED','2020-01-01 00:00:15']
    ,['eiro','ST','2020-01-02 00:00:20']
    ,['eiro','ST','2020-01-02 00:00:25']
    ,['eiro','ED','2020-01-02 00:00:30']
    ,['airo','ED','2020-01-03 00:00:35']
    ,['kkkk','ST','2020-01-04 01:10:10']
    ,['eiro','ST','2020-01-05 02:10:20']
    ,['kkkk','ED','2020-01-05 01:10:30']
    ,['eiro','ED','2020-01-05 02:20:30']
]
cols = ['name', 'status','dtime']
df = pd.DataFrame(data, columns=cols)

last = {}
def func(row):
    idx = -1
    if row['status'] == 'ED':
        last[row['name']] = row.name
    else:
        idx = last.pop(row['name'], idx)
        # 終了行に、対応する開始行の位置をセット
        if idx >= 0:
            df.loc[idx, 'st'] = row.name

# 逆順に走査して終了行の直近の開始行を決定
df['st'] = -1
df.iloc[::-1].apply(func, axis=1)

# 開始、終了行同士を結合
df = pd.merge(df[df['status'] == 'ST'], df[df['status'] == 'ED'], left_index=True, right_on='st', how='outer', suffixes=('1','2'))

# 開始行のない終了行に必要な情報をセットして元レコード順に並び替え
df = df.reset_index()
rows = df['st'] < 0
df.loc[rows,'st'] = df.loc[rows,'index']
df.loc[rows,'name1'] = df.loc[rows,'name2']
df = df.sort_values(['st'])

# 必要な列のみ
df = df.loc[:,['name1','status1','dtime1','status2','dtime2']]
print(df)
"""
  name1 status1               dtime1 status2               dtime2
0  siro      ST  2020-01-01 00:00:05     NaN                  NaN
1  siro      ST  2020-01-01 00:00:10      ED  2020-01-01 00:00:15
2  eiro      ST  2020-01-02 00:00:20     NaN                  NaN
3  eiro      ST  2020-01-02 00:00:25      ED  2020-01-02 00:00:30
6  airo     NaN                  NaN      ED  2020-01-03 00:00:35
4  kkkk      ST  2020-01-04 01:10:10      ED  2020-01-05 01:10:30
5  eiro      ST  2020-01-05 02:10:20      ED  2020-01-05 02:20:30
"""

Comments

[quinty]

ご返答ありがとうございます。
試させてもらいます。