[Python]データフレームにおける時刻差分と差分合計の列追加

位置情報データを扱っています。
データ量は約数千万レコードです。

"千代田区"に滞在したユーザを対象に、
2つの列を追加したいのですが、苦戦しています。

　・千代田区の滞在時間列（時刻差分）
・千代田区の滞在時間（時刻差分）の合計列

時刻差分を計算する際、千代田区の起時を都度、0にしたいのですが、うまくいきません。
知見のある方、ご教示いただけないでしょうか。
宜しくお願い致します。

【df】
Index｜　 id　｜　Timestamp　　　｜　citycode(=市区町村コード)　※適当です
0　　 AA1　　2022/06/10 11:07　　001(千代田区)
1　　 AA1　　2022/06/10 11:22　　001(千代田区)
2　　 AA1　　2022/06/10 12:10　　003(新宿区)
3　　 AA1　　2022/06/10 14:04　　001(千代田区)
4　　 AA1　　2022/06/10 14:41　　001(千代田区)
5　　 AA1　　2022/06/10 16:24　　005(渋谷区)
6　　 AA1　　2022/06/10 19:54　　001(千代田区)
7 　 BB4　　2022/06/11 06:06　　001(千代田区)
8 　 BB4　　2022/06/11 07:58　　006(目黒区)
9 　 BB4　　2022/06/11 08:37　　001(千代田区)
10　　BB4　　2022/06/11 17:50　　001(千代田区)
11　　BB4　　2022/06/11 20:25　　001(千代田区)
12　　BB4　　2022/06/11 22:33　　006(目黒区)

　　　　　　　　 ↓
【列追加後（イメージ）】
Index｜　 id　｜　Timestamp　　　｜　citycode　　　　｜　diff　｜　diff_sum
0　　 AA1　　2022/06/10 11:07　　001(千代田区)　　　　０　　　　112
1　　 AA1　　2022/06/10 11:22　　001(千代田区)　　　　15　　　 112
2　　 AA1　　2022/06/10 12:10　　003(新宿区)　　　　　0　　　　 112
3　　 AA1　　2022/06/10 14:04　　001(千代田区)　　　　0　　　　 112
4　　 AA1　　2022/06/10 15:41　　001(千代田区)　　　　97　　　　112
5　　 AA1　　2022/06/10 16:24　　005(渋谷区)　　　　　0　　　　 112
6　　 AA1　　2022/06/10 19:54　　001(千代田区)　　　　0　　　　 112
7 　 BB4　　2022/06/11 06:06　　001(千代田区)　　　　0　　　　 747
8 　 BB4　　2022/06/11 07:58　　006(目黒区)　　　　　0　　　　 747
9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　39　　　 747
10　　BB4　　2022/06/11 17:50　　001(千代田区)　　　　553　　　747
11　　BB4　　2022/06/11 20:25　　001(千代田区)　　　　155　　　747
12　　BB4　　2022/06/11 22:33　　006(目黒区)　　　　　0　　　　 747

can110

2022/07/09 09:11

追加したい列の値がなぜそうなるのか（ルール）を説明ください。

meg_

2022/07/09 13:18

> 時刻差分を計算する際、千代田区の起時を都度、0にしたいのですが、うまくいきません。その「うまくい」かないコードを掲載ください。

sanfuji

2022/07/10 08:57

質問・回答いただいた方、ありがとうございます。説明不足で恐縮です。いくつか訂正と追記します。【訂正】訂正箇所①　列追加後（イメージ）のIndex9 誤）9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　39　　　 747 正）9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 訂正箇所②　id:BB4のdiff_sum　※Index9/diff:39→0により誤）747 正）708 改【列追加後（イメージ）】 7 　 BB4　　2022/06/11 06:06　　001(千代田区)　　　　0　　　　 708 8 　 BB4　　2022/06/11 07:58　　006(目黒区)　　　　　0　　　　 708 9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 10　　BB4　　2022/06/11 17:50　　001(千代田区)　　　553　　　708 11　　BB4　　2022/06/11 20:25　　001(千代田区)　　　155　　　708 12　　BB4　　2022/06/11 22:33　　006(目黒区)　　　　　0　　　　708 【追記】 diff列のルールについて滞在時間の算出は、同じcitycode(=千代田区)の位置情報2点間で算出することを前提とします。 id：BB4で説明します。ーーーー 7 　 BB4　　2022/06/11 06:06　　001(千代田区)　　　　0　　　　 708 8 　 BB4　　2022/06/11 07:58　　006(目黒区)　　　　　0　　　　 708 9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 10　　BB4　　2022/06/11 17:50　　001(千代田区)　　　　553　　　708 11　　BB4　　2022/06/11 20:25　　001(千代田区)　　　　155　　　708 12　　BB4　　2022/06/11 22:33　　006(目黒区)　　　　　0　　　　 708 ーーーー Index7-9では、千代田区を出た後、目黒区に行き、また千代田区に戻る、という行動をしています。この場合、同じcitycode（Index7と9）で差分を取ると、151分になりますが、 151分の間に目黒区に行っているので、千代田区に151分滞在というのは正確ではなく、千代田区に何分滞在していたのか判定できないため、滞在時間は"わからない"とし、0としたいです。（位置情報1点のみでは、何時にその場にいたのはわかるのですが、滞在時間まではわかりません。）上記の考えで、千代田区の一つ前のレコードが千代田区以外の場合（Index8,9）、その千代田区レコード（Index9）は起時0としたいですが、diff()だけではうまくいません。スキル不足で対応できず、知見のある方、教示いただけないでしょうか。宜しくお願い致します。

can110

2022/07/11 01:52

この欄は依頼欄で、他の人の目につかないので、質問本文を修正・追記ください。

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

回答2件

ベストアンサー

以下のように差分値のうち「前行＝現在行＝千代田区」の行の値だけ採用すればよいかと思います。

Python
1import pandas as pd
2from io import StringIO
3pd.set_option('display.unicode.east_asian_width', True)
4
5s = """id,Timestamp,citycode
6AA1,2022/06/10 11:07,001(千代田区)
7AA1,2022/06/10 11:22,001(千代田区)
8AA1,2022/06/10 12:10,003(新宿区)
9AA1,2022/06/10 14:04,001(千代田区)
10AA1,2022/06/10 14:41,001(千代田区)
11AA1,2022/06/10 16:24,005(渋谷区)
12AA1,2022/06/10 19:54,001(千代田区)
13BB4,2022/06/11 06:06,001(千代田区)
14BB4,2022/06/11 07:58,006(目黒区)
15BB4,2022/06/11 08:37,001(千代田区)
16BB4,2022/06/11 17:50,001(千代田区)
17BB4,2022/06/11 20:25,001(千代田区)
18BB4,2022/06/11 22:33,006(目黒区)"""
19df = pd.read_csv(StringIO(s), parse_dates=['Timestamp'])
20
21def func(dfg):
22    # 前行との差分を計算
23    ts = dfg['Timestamp']
24    diff = (ts - ts.shift()).dt.total_seconds().fillna(0) // 60
25
26    # 「前行＝現在行＝千代田区」
27    cc = dfg['citycode']
28    filter = (cc == cc.shift()) & (cc == '001(千代田区)')
29    diff = (diff * filter).astype(int) # 乗算すると対象外の行は０になる
30
31    dfg['diff'] = diff
32    dfg['diff_sum'] = sum(diff)
33    return dfg
34
35df = df.groupby('id').apply(func)
36print(df)
37"""
38     id           Timestamp       citycode  diff  diff_sum
390   AA1 2022-06-10 11:07:00  001(千代田区)     0        52
401   AA1 2022-06-10 11:22:00  001(千代田区)    15        52
412   AA1 2022-06-10 12:10:00    003(新宿区)     0        52
423   AA1 2022-06-10 14:04:00  001(千代田区)     0        52
434   AA1 2022-06-10 14:41:00  001(千代田区)    37        52
445   AA1 2022-06-10 16:24:00    005(渋谷区)     0        52
456   AA1 2022-06-10 19:54:00  001(千代田区)     0        52
467   BB4 2022-06-11 06:06:00  001(千代田区)     0       708
478   BB4 2022-06-11 07:58:00    006(目黒区)     0       708
489   BB4 2022-06-11 08:37:00  001(千代田区)     0       708
4910  BB4 2022-06-11 17:50:00  001(千代田区)   553       708
5011  BB4 2022-06-11 20:25:00  001(千代田区)   155       708
5112  BB4 2022-06-11 22:33:00    006(目黒区)     0       708
52"""

投稿2022/07/11 06:14

can110

総合スコア38266

sanfuji

2022/07/11 10:01

回答ありがとうございます。記載いただいたコードの実行にて、イメージ通りのデータと列追加を確認できました。

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

python
1import pandas as pd
2import io
3
4pd.set_option('display.unicode.east_asian_width', True)
5
6csv_data = '''
7id,Timestamp,citycode
8A1,2022/06/10 11:07,001(千代田区)
9A1,2022/06/10 11:22,001(千代田区)
10A1,2022/06/10 12:10,003(新宿区)
11A1,2022/06/10 14:04,001(千代田区)
12A1,2022/06/10 15:41,001(千代田区)
13A1,2022/06/10 16:24,005(渋谷区)
14A1,2022/06/10 19:54,001(千代田区)
15BB4,2022/06/11 06:06,001(千代田区)
16BB4,2022/06/11 07:58,006(目黒区)
17BB4,2022/06/11 08:37,001(千代田区)
18BB4,2022/06/11 17:50,001(千代田区)
19BB4,2022/06/11 20:25,001(千代田区)
20BB4,2022/06/11 22:33,006(目黒区)
21'''
22df = pd.read_csv(io.StringIO(csv_data), parse_dates=['Timestamp'])
23
24#
25grp = df.query('citycode == "001(千代田区)"').groupby('id', group_keys=False)\
26        .apply(lambda x: x.index.to_series().diff().ne(1).cumsum())
27df['diff'] = df.groupby(['id', grp])['Timestamp'].diff().dt.seconds.fillna(0) / 60.0
28df['diff'] = df['diff'].fillna(0)
29df = df.groupby('id').apply(lambda x: x.assign(diff_sum = x['diff'].sum()))
30
31print(df)

	id	Timestamp	citycode	diff	diff_sum
0	A1	2022-06-10 11:07:00	001(千代田区)	0	112
1	A1	2022-06-10 11:22:00	001(千代田区)	15	112
2	A1	2022-06-10 12:10:00	003(新宿区)	0	112
3	A1	2022-06-10 14:04:00	001(千代田区)	0	112
4	A1	2022-06-10 15:41:00	001(千代田区)	97	112
5	A1	2022-06-10 16:24:00	005(渋谷区)	0	112
6	A1	2022-06-10 19:54:00	001(千代田区)	0	112
7	BB4	2022-06-11 06:06:00	001(千代田区)	0	708
8	BB4	2022-06-11 07:58:00	006(目黒区)	0	708
9	BB4	2022-06-11 08:37:00	001(千代田区)	0	708
10	BB4	2022-06-11 17:50:00	001(千代田区)	553	708
11	BB4	2022-06-11 20:25:00	001(千代田区)	155	708
12	BB4	2022-06-11 22:33:00	006(目黒区)	0	708

投稿2022/07/09 09:20

編集2022/07/11 04:11

melian

総合スコア19805

sanfuji

2022/07/10 09:41

回答ありがとうございます。いくつか訂正と追記します。【訂正】訂正箇所①　列追加後（イメージ）のIndex9 誤）9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　39　　　 747 正）9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 訂正箇所②　id:BB4のdiff_sum　※Index9/diff:39→0により誤）747 正）708 改【列追加後（イメージ）】 7 　 BB4　　2022/06/11 06:06　　001(千代田区)　　　　0　　　　 708 8 　 BB4　　2022/06/11 07:58　　006(目黒区)　　　　　0　　　　 708 9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 10　　BB4　　2022/06/11 17:50　　001(千代田区)　　　553　　　708 11　　BB4　　2022/06/11 20:25　　001(千代田区)　　　155　　　708 12　　BB4　　2022/06/11 22:33　　006(目黒区)　　　　　0　　　　708 【追記】 diff列のルールについて滞在時間の算出は、同じcitycode(=千代田区)の位置情報2点間で算出することを前提とします。 id：BB4で説明します。ーーーー 7 　 BB4　　2022/06/11 06:06　　001(千代田区)　　　　0　　　　 708 8 　 BB4　　2022/06/11 07:58　　006(目黒区)　　　　　0　　　　 708 9 　 BB4　　2022/06/11 08:37　　001(千代田区)　　　　0　　　 708 10　　BB4　　2022/06/11 17:50　　001(千代田区)　　　　553　　　708 11　　BB4　　2022/06/11 20:25　　001(千代田区)　　　　155　　　708 12　　BB4　　2022/06/11 22:33　　006(目黒区)　　　　　0　　　　 708 ーーーー Index7-9では、千代田区を出た後、目黒区に行き、また千代田区に戻る、という行動をしています。この場合、同じcitycode（Index7と9）で差分を取ると、151分になりますが、 151分の間に目黒区に行っているので、千代田区に151分滞在というのは正確ではなく、千代田区に何分滞在していたのか判定できないため、滞在時間は"わからない"とし、0としたいです。（位置情報1点のみでは、何時にその場にいたのはわかるのですが、滞在時間まではわかりません。）上記の考えで、千代田区の一つ前のレコードが千代田区以外の場合（Index8,9）、その千代田区レコード（Index9）は起時0としたいです。教示いただけないでしょうか。宜しくお願い致します。