ログデータのセッション開始時間とセッション終了時間の差を計算できるようなデータ加工をしたい。

Question

前提

Pandasでログデータの分析のためのデータ加工をしています。
ログデータは元データ(添付左側)のように時間、IN/OUT、USER単位の行で格納されており、
基本的にはINと対になるOUTがあるはずですが、まれに対になるものがない状態となっています。

実現したいこと

• 加工後データ(添付右側)のように、INと対になるOUTのデータを同じ行で持ち、列同士の計算で滞在時間(列:diff)を出したいです。
• 1行ごとにループして総当たりをかければ実現できるのですが、データ量が膨大で全行ループでの処理は現実問題実現不可です。
• Pythonでループ処理以外（Pnadas,Numpy等）の計算が早い方法で実現したいと思っています。<br>

　　→最悪ループ処理でもよいのですが極力計算コストが少なくなる処理（コード）にしたいです。

• 加工後データ(添付右側)のように黄色ハッチング部分は処理の中で変数として渡せるようにしておきたいです。

元データ

No	datetime	in_out	user	service
1	2023/1/1 16:33:19	IN	A	α
2	2023/1/1 16:33:05	IN	A	α
3	2023/1/1 16:24:43	IN	A	α
4	2023/1/1 16:23:42	IN	A	α
5	2023/1/1 16:22:59	IN	A	α
6	2023/1/1 15:48:54	IN	A	α
8	2023/1/1 17:17:36	OUT	A	α
9	2023/1/1 16:31:21	OUT	A	α
10	2023/1/1 16:00:28	OUT	A	α

Comments

[can110]

同一userにおけるINとOUTの対応関係（ルール）がよく分かりません。
以下、元データを日付順に並び替えて左端に行番号列を追加して例にて

11 2023-02-03 15:40:00 IN B
12 2023-02-04 16:00:00 IN B
16 2023-02-04 17:20:00 OUT B
において、16のOUTに対応するのが12ではなく11である理由

13 2023-02-05 16:20:00 IN B
15 2023-02-07 17:00:00 OUT B
17 2023-02-10 18:00:00 OUT B
において、13のINに対応するのが15ではなく17である理由

とくに２つ目、13のINに対応するのは15であるのが自然な気がしますが。

[nrnrdsa]

＞とくに２つ目、13のINに対応するのは15であるのが自然な気がしますが。
サンプルデータの誤りでした。更新したうえで情報の追記をしました。

＞同一userにおけるINとOUTの対応関係（ルール）がよく分かりません。
明確はルールは存在せず、もっともらしさで自分で定義する必要があります。
考えられる定義としてはIN,OUTのuser,service,時間の誤差が最小になるレコードで取得しもっともらしいIN-OUTのペアを作るということです。
この時、ループしてすべての組み合わせとの最小時間を利用するという方法が考えられますが、総当たりで時間がかかりすぎるためより良い方法はないかということで本投稿をしております。

Answer 1

質問が変更されて、「誤差が最大となるように」に合わせてみました。
(INとOUTのペアリングのところ以外は修正前と同じです)

下記は、直前がINでないINと、直後がOUTでないOUTをマッチさせるようにしたものです。
これで、だいたい最大っぽい感じになると思いますが、もし合わないケースがあれば、ご自身で条件を変えてみてください。

import pandas as pd
import numpy as np

# df = ...

# 日付ソート
df = df.sort_values(['datetime'])

# INとOUTのペアリング
def pair_number(x):
    valid_in = (x == 'IN') & (x.shift() != 'IN')
    valid_out = (x == 'OUT') & (x.shift(-1) != 'OUT')
    num = np.arange(len(x))
    num_in = np.maximum.accumulate(np.where(valid_in, num, -1))
    return np.where(valid_out, num_in, num)

df['key'] = df.groupby(['user', 'service'])['in_out'].transform(pair_number)

# ピボット
df2 = pd.pivot(
    df,
    index=['user', 'service', 'key'],
    columns='in_out',
    values=['No', 'datetime'])

# カラム名の整理
df2.columns = [c2 + '_' + c1 for (c1, c2) in df2.columns]
df2 = df2.reset_index().drop(columns='key')

print(df2)

修正前の回答

datetime でソートして、user, service で groupby した上で、
in_out に連番をつけて、'OUT'のところは -1 すると、
IN → OUT と連続するところだけが同じ数字になります。
あとは、それを使って pivot すれば、だいたい望みの形になると思います。

import pandas as pd
import numpy as np

#df = ...

# 日付ソート
df = df.sort_values(['datetime'])

# INとOUTのペアリング
df['key'] = df.groupby(['user', 'service'])['in_out'].apply(
    lambda x: np.arange(len(x)) - (x == 'OUT'))

# ピボット
df2 = pd.pivot(
    df,
    index=['user', 'service', 'key'],
    columns='in_out',
    values=['No', 'datetime'])

# カラム名の整理
df2.columns = [c2 + '_' + c1 for (c1, c2) in df2.columns]
df2 = df2.reset_index().drop(columns='key')

print(df2)
#  user service IN_No OUT_No         IN_datetime        OUT_datetime
#0    A       α     1      7 2023-01-01 09:00:00 2023-01-01 09:10:00
#1    A       α   NaN      9                 NaT 2023-01-01 15:20:00
#2    A       β     2      8 2023-01-01 09:25:00 2023-01-01 12:00:00
#3    A       γ     3    NaN 2023-01-01 12:00:00                 NaT
#4    A       δ     4    NaN 2023-01-01 12:30:00                 NaT
#5    A       ε     5     10 2023-01-01 15:10:00 2023-01-01 15:20:00
#6    A       ζ     6     11 2023-01-01 12:12:00 2023-01-01 15:20:00

Comments

[nrnrdsa]

こちらのコードでほとんどやりたいことは実現可能でした。
ですが、仕様の漏れがありこのコードだとIN-OUTが正確に取れない場合は誤差が最小になるようにしていますが、正確には誤差が最大となるようにする必要がありました。

修正版のデータと加工後データのキャプチャを差し替えましたのでこちらに対して処理できるようにできないでしょうか？

[bsdfan]

回答を更新しましたが、とりあえず参考までにということで。
別の回答者さんの指摘にもあるように、「誤差が最大」と書くのは簡単ですが、まじめに考えると、いろんなケースを考えて、こういうときはこうするというのがいくつか出てくるはずなので、そのあたりをちゃんと考えてみてください。
そうすると、条件が明確になって、おのずとコードが書けるようになると思います。

Answer 2

まずはIN行毎に対応するOUT行番号を取得します。
これは全レコード１回の走査で得ることができます。

ルールとしては最も近くに出現した行を対応させるものとします。
すなわち

• 1(IN), 2(IN), 3(OUT) なら 2と3が対応します。
• 1(IN), 2(OUT), 3(OUT) なら1と2が対応します。

次にこの番号をもとにしたmergeにて

• IN行と対応するOUT行を結合したテーブル
• IN行に対応しないOUT行を抽出したテーブル

を求め、この２つのテーブルを行方向に結合します。

最後に適当に並び替えます。

import pandas as pd
from io import StringIO

# テストデータ
s = """No,datetime,in_out,user,service
1,2023/1/1 9:00,IN,A,α
2,2023/1/1 9:25,IN,A,β
3,2023/1/1 12:00,IN,A,γ
4,2023/1/1 12:30,IN,A,δ
5,2023/1/1 15:10,IN,A,ε
6,2023/1/1 12:12,IN,A,ζ
7,2023/1/1 9:10,OUT,A,α
8,2023/1/1 12:00,OUT,A,β
9,2023/1/1 15:20,OUT,A,α
10,2023/1/1 15:20,OUT,A,ε
11,2023/1/1 15:20,OUT,A,ζ"""
df = pd.read_csv(StringIO(s), parse_dates=['datetime'])

# 末尾から処理するため、日付とIN/OUTの逆順にソート 
df = df.sort_values(['datetime','in_out'], ascending=False)
print(df)

# IN行に対応するOUT行番号を取得
link = {} # キー:userとserviceの組み合わせ, 値:対応するOUT行番号
def find_out(row):
    key = (row['user'], row['service'])
    io = row['in_out']
    no = row['No']
    out_no = -1
    if io == 'OUT':
        link[key] = no
    else:
        if key in link:
            out_no = link[key]
            link[key] = -1
    return out_no
df['out_no'] = df.apply(find_out, axis=1)
print(df)

# IN行にOUT行をくっつける
df_i = df[df['in_out']=='IN'].merge(df, left_on='out_no', right_on='No', how='left', suffixes=['', '_o'])
print(df_i)

# IN行に対応しないOUT行のみを抽出する
df_o = df[df['in_out']=='IN'].merge(df, left_on='out_no', right_on='No', how='right', suffixes=['', '_o'])
print(df_o)
df_o = df_o[(df_o['in_out_o']=='OUT') & (df_o['No'].isna())]
df_o[['No','user','service']] = df_o[['No_o','user_o','service_o']] # 日付を除き、OUT行のものを採用
print(df_o)

# 両方を行方向にまとめる
df_ret = pd.concat([df_i,df_o], axis=0)

# 不要な列の削除と並び替え
df_ret = df_ret.loc[:,['No','datetime','user','service','datetime_o']].sort_values(['No'])
print(df_ret)
"""
   No            datetime user service          datetime_o
5   1 2023-01-01 09:00:00    A       α 2023-01-01 09:10:00
4   2 2023-01-01 09:25:00    A       β 2023-01-01 12:00:00
3   3 2023-01-01 12:00:00    A       γ                 NaT
1   4 2023-01-01 12:30:00    A       δ                 NaT
0   5 2023-01-01 15:10:00    A       ε 2023-01-01 15:20:00
2   6 2023-01-01 12:12:00    A       ζ 2023-01-01 15:20:00
0   9                 NaT    A       α 2023-01-01 15:20:00
"""

Comments

[nrnrdsa]

こちらのコードでほとんどやりたいことは実現可能でした。
ですが、仕様の漏れがありこのコードだとIN-OUTが正確に取れない場合は誤差が最小になるようにしていますが、正確には誤差が最大となるようにする必要がありました。

修正版のデータと加工後データのキャプチャを差し替えましたのでこちらに対して処理できるようにできないでしょうか？

[can110]

私の回答は私が回答に記した（決めた）ルールにしたがっています。
それと異なるのであれば、まずはその仕様（ルール）を明文化して質問に追記ください。
とくに
- その「誤差」というのが何を基準として何との差として求められるのか
- それを「最大となるようにする」とは具体的にどのような計算（処理）を行うのか
といった部分を明確にする必要があるかと思います。

Answer 3

1行ごとにループしなくても、全行を1回ずつ処理するだけで実現できませんか

• 特定時刻のINが現れたら時刻をキーとして連想配列に追加する
• INに対応するOUTが現れたらそれをOUT時間とする

Comments

[nrnrdsa]

＞特定時刻のINが現れたら
特定時刻のINはどのように設定（処理）すればよいのでしょうか？
本件の詰まりどころ（＝解決ポイント）はIN-OUTのロジックの設計とそのロジックをPythonでどのように描くべきかということと認識しております。

＞連想配列に追加する
あまり本質と関係ないことに思いますが、連想配列＝辞書型と理解しております。
辞書にする際のKey,valuesはそれぞれ何になるのでしょうか？
連想配列で解決するという意味だと思いますがあまり意味を理解できていません。