エクセルで行っていた相対参照による計算、オートフィルをPythonでやりたい

目的：エクセルで行ってきた表計算的な作業をPythonでやりたい

今までエクセルで行ってきた「セルを相対参照して取得した計算の結果を新しいセルに書き込む」という作業をPythonのDataFrameで行いたいのですがとりあえず買ってきたオライリーの本やgoogle検索では上手く解決できませんでした。初歩的な操作だとは思うのですが、適切な質問の仕方もまだ分からないレベルなのでご容赦下さい。

やりたいこと

エクセルの時にif関数と相対参照で書いたものをすべての行に対してオートフィルで書き込んでいた処理をPythonのDataframeに対して行うことが目的です。
エクセルでは次のような式をオートフィルですべての行に書き込んでいました。
簡単にいうと「隣接する2つの行の日付とidが一致する場合時刻の差を下側の行に追加する、一致しない場合はNAとする」という処理をしています。

=if(AND(yyyymmdd1=yyyymmdd2,id1=id2),hhmm2-hhmm1,"")
#実際にはyyyymmdd1やid1の部分にはセル番号が相対参照で入っています

Pythonだとapply()を使えばDataFrameやSeries内のValueに関数を適用できるところまでは分かったのですが、エクセルでいう相対参照はどう記述すればいいのか、どのようなアプローチをとればいいのか分かりません。

###具体例
上記の式は日付、時刻、個人番号が記録されたログから各個人が1回の作業に消費した時間を算出する際に使用していました。
下の表は元々は時系列順に記録されていたものを個人番号>日付>時刻の順にソートしたものです。

yyyymmdd	hhmm	id
20190101	9:00	1111
20190101	9:15	1111
20190101	9:30	1111
20190101	9:45	1111
20190202	9:00	1111
20190202	9:15	1111
20190202	9:30	1111
20190202	9:45	1111
20190101	9:00	2222
20190101	9:10	2222
20190101	9:20	2222
20190101	9:30	2222
20190101	9:40	2222
20190202	9:00	2222
20190202	9:10	2222
20190202	9:20	2222
20190202	9:30	2222
20190202	9:40	2222

この表に対してmim_per_productという列を追加して先ほどの式をオートフィルしたものがこちらになります。

yyyymmdd	hhmm	id	min_per_product
20190101	9:00	1111	NaN
20190101	9:15	1111	15
20190101	9:30	1111	15
20190101	9:45	1111	15
20190202	9:00	1111	NaN
20190202	9:15	1111	15
20190202	9:30	1111	15
20190202	9:45	1111	15
20190101	9:00	2222	NaN
20190101	9:10	2222	10
20190101	9:20	2222	10
20190101	9:30	2222	10
20190101	9:40	2222	10
20190202	9:00	2222	NaN
20190202	9:10	2222	10
20190202	9:20	2222	10
20190202	9:30	2222	10
20190202	9:40	2222	10

このような処理をPython上で実現するにはどのようなアプローチをすれば良いのでしょうか。

試したこと

オライリーのPythonによるデータ分析入門、前処理大全などは買って読んでみたのですが適用できそうな処理が見つかりませんでした。
もし有用な書籍情報などありましたら教えていただけると幸いです。

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

エディタにはJupyter notebookを使用しています。

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

回答2件

ベストアンサー

CSVデータを

CSV
1yyyymmdd,hhmm,id
220190101,9:00,1111
320190101,9:15,1111
420190101,9:30,1111
520190101,9:45,1111
620190202,9:00,1111
720190202,9:15,1111
820190202,9:30,1111
920190202,9:45,1111
1020190101,9:00,2222
1120190101,9:10,2222
1220190101,9:20,2222
1320190101,9:30,2222
1420190101,9:40,2222
1520190202,9:00,2222
1620190202,9:10,2222
1720190202,9:20,2222
1820190202,9:30,2222
1920190202,9:40,2222

とすると、df.groupby(['id', 'yyyymmdd']) を行い、各グループの'hhmm'列に対してSeries.diff()を行うと良いかと思います。

Python
1import pandas as pd
2
3df = pd.read_csv('data.csv', parse_dates=['yyyymmdd', 'hhmm'])
4
5df['min_per_product'] = df.groupby(['id', 'yyyymmdd'])['hhmm'].apply(lambda d: d.diff()).astype('timedelta64[m]')
6print(df)
7#     yyyymmdd                hhmm    id  min_per_product
8#0  2019-01-01 2019-08-30 09:00:00  1111              NaN
9#1  2019-01-01 2019-08-30 09:15:00  1111             15.0
10#2  2019-01-01 2019-08-30 09:30:00  1111             15.0
11#3  2019-01-01 2019-08-30 09:45:00  1111             15.0
12#4  2019-02-02 2019-08-30 09:00:00  1111              NaN
13#5  2019-02-02 2019-08-30 09:15:00  1111             15.0
14#6  2019-02-02 2019-08-30 09:30:00  1111             15.0
15#7  2019-02-02 2019-08-30 09:45:00  1111             15.0
16#8  2019-01-01 2019-08-30 09:00:00  2222              NaN
17#9  2019-01-01 2019-08-30 09:10:00  2222             10.0
18#10 2019-01-01 2019-08-30 09:20:00  2222             10.0
19#11 2019-01-01 2019-08-30 09:30:00  2222             10.0
20#12 2019-01-01 2019-08-30 09:40:00  2222             10.0
21#13 2019-02-02 2019-08-30 09:00:00  2222              NaN
22#14 2019-02-02 2019-08-30 09:10:00  2222             10.0
23#15 2019-02-02 2019-08-30 09:20:00  2222             10.0
24#16 2019-02-02 2019-08-30 09:30:00  2222             10.0
25#17 2019-02-02 2019-08-30 09:40:00  2222             10.0

にて実現出来るかと思います。

astype() 以降はデータを分単位表記に変えているだけです

【追記】
休憩時間を処理するサンプル

Python
1import pandas as pd
2import datetime
3
4# 休憩時間（とりあえず適当）
5BREAK_START = datetime.time(9, 27)
6BREAK_END = datetime.time(9, 32)
7
8# datetime.time 型同士の差を求めるUtility関数
9def time_diff(start_time, end_time):
10    return datetime.datetime.combine(datetime.date.today(), end_time) - datetime.datetime.combine(datetime.date.today(), start_time)
11
12# Groupby.apply() にて呼ばれる関数（各行に時間を求める）
13def calc_product_time(data):
14    # 後の処理を行いやすくするために DataFrame化しておく
15    tmp_df = pd.DataFrame({'start_time': data.shift(1).dt.time,
16                           'end_time': data.dt.time,
17                           'total_time': data.diff()},
18                          index = data.index)
19
20    #print(tmp_df)
21
22    # 各行に対して休憩時間を計算する
23    for idx, row in tmp_df.iterrows():
24        # 範囲内に休憩開始・休憩終了時間が含まれる場合
25        if ((row.start_time <= BREAK_START) &
26            (BREAK_START < row.end_time) &
27            (row.start_time <= BREAK_END) &
28            (BREAK_END < row.end_time)):
29            
30            tmp_df.loc[idx, 'break_time'] = time_diff(BREAK_START, BREAK_END)
31            
32        # 範囲内に休憩開始時間のみ含まれる場合            
33        elif ((row.start_time <= BREAK_START) &
34              (BREAK_START < row.end_time) &
35              (BREAK_END >= row.end_time)):
36            
37            tmp_df.loc[idx, 'break_time'] = time_diff(BREAK_START, row.end_time)
38
39        # 範囲内に休憩終了時間のみ含まれる場合
40        elif ((row.start_time > BREAK_START) &
41              (row.start_time <= BREAK_END) &
42              (BREAK_END < row.end_time)):
43
44            tmp_df.loc[idx, 'break_time'] = time_diff(row.start_time, BREAK_END)
45
46        # 休憩時間内に、範囲がすべて含まれる場合
47        elif ((row.start_time > BREAK_START) &
48              (BREAK_END >= row.end_time)):
49            tmp_df.loc[idx, 'break_time'] = time_diff(row.start_time, row.end_time)
50
51        # その他（範囲内に休憩なし）
52        else:
53            tmp_df.loc[idx, 'break_time'] = datetime.timedelta(0)
54            
55    tmp_df['product_time'] = tmp_df['total_time'] - tmp_df['break_time']
56    #print(tmp_df)
57
58    return tmp_df['product_time']
59
60df = pd.read_csv('data.csv', parse_dates={'datetime': ['yyyymmdd', 'hhmm']})
61df['min_per_product'] = df.groupby(['id', df['datetime'].dt.date])['datetime'].apply(calc_product_time)
62print(df)
63

投稿2019/08/30 03:26

編集2019/09/02 02:15

magichan

総合スコア15898

Kio_Mutto

2019/08/30 06:16

素早い回答ありがとうございます！ df.groupby(['id',...)['hhmm']の部分が何を意味しているのか理解するのに手間取りましたが、.groupsで中身をみることができるとわかり、たぶん理解できたと思います。実は、さらにhhmm1とhhmm2が休憩時間をまたぐときはその分の時間を減算する条件式をif関数で記述していたのですが…教えていただいた方法の応用では実現が難しいみたいなのでもしよろしければそちらも教えていただけると幸いです。

magichan

2019/09/02 02:09

遅くなりました。とりあえず、現状 groupby().applay() で呼んでいる lambda の部分を関数化して、関数の中で処理をするとよいのですが・・結構面倒です。あまりスマートな方法が思いつかなかったので、とりあえず愚直に処理する方法でサンプルを書いてみましたので、参考にしてみてください

Kio_Mutto

2019/09/05 13:19

返信おそくなりましてすみません、追記ありがとうございます！時間の値の表し方を良く知らなかったのでじっくり読ませて頂きます…。 groupbyとapplyはとてもむずかしい(とオライリーの本にも書いてあった)ので頑張って読みます…。あと、ふと思ったんですが、各作業時間を長さを持った量の集合として休憩時間の集合と重ならない部分を求める、merge(作業時間,休憩時間,how='left')みたいな処理ができればifで場合分けしなくてもできそうな気がしてきました。とりあえずまだまだ幼稚園児レベルの語彙力なのでがんばります…。

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

ループ処理で実装

for文でエクセルでいう相対参照、if条件式、オートフィルみたいな処理を書いてみました。
roop処理は時間がかかるみたいですがエクセルに手動でコピペ&オートフィルするよりも断然速いのでいいんです…。
ぱそこん原始人に産業革命が起きました。

Python
1import pandas as pd
2import numpy as np
3
4#全ての値をobject形式で読み込み
5df = pd.read_csv('sampledata.csv',dtype='object')
6
7#hhを60倍しmmに合算した0:00時からの積算時間(分)mmmmを作成しdfに結合
8mmmm = []
9index_max = len(df.index)
10for roop_number in range(index_max):
11    mmmm_each = int(df.loc[roop_number,'hhmm'][0:2])*60+int(df.loc[roop_number,'hhmm'][2:4])
12    mmmm.append(mmmm_each)
13df['mmmm'] = mmmm
14
15#if条件付きの式でmmmmの差をとる
16min_per_tray = [np.nan] #1行目はNaNを代入
17index_max = len(df.index)
18for roop_number in range(index_max-1): #1行目はNaNを代入したのでループ回数を1減らず
19    if df.loc[roop_number+1,'id']==df.loc[roop_number,'id'] and df.loc[roop_number+1,'yyyymmdd']==df.loc[roop_number,'yyyymmdd'] and int(df.loc[roop_number+1,'mmmm'])<=1020: ##日付とID一致かつ17時以前に登録されたもののみ差分を計算
20        if df.loc[roop_number,'mmmm']<=590 and df.loc[roop_number+1,'mmmm']>=600: #休憩時間を挟む場合減産
21            mpt_each =  df.loc[roop_number+1,'mmmm']-df.loc[roop_number,'mmmm']-10
22        else:
23            mpt_each =  df.loc[roop_number+1,'mmmm']-df.loc[roop_number,'mmmm']
24    else:
25        mpt_each = np.nan
26    min_per_tray.append(mpt_each)
27df['min_per_tray'] = min_per_tray
28print(df)

###実際にやってみた
サンプルデータがこれ

csv
1yyyymmdd,hhmm,id
220190101,0900,1111
320190101,0915,1111
420190101,0930,1111
520190101,0945,1111
620190101,1010,1111
720190101,1730,1111
820190202,0900,1111
920190202,0915,1111
1020190202,0930,1111
1120190202,0945,1111
1220190202,1010,1111
1320190101,0900,2222
1420190101,0910,2222
1520190101,0920,2222
1620190101,0930,2222
1720190101,0940,2222
1820190202,0900,2222
1920190202,0910,2222
2020190202,0920,2222
2120190202,0930,2222
2220190202,0940,2222
2320190202,0950,2222
2420190202,1010,2222
25

できあがったものがこうなりました。
||yyyymmdd|hhmm|id|mmmm|min_per_tray
|:--|:--:|--:|
0|20190101|0900|1111|540|
1|20190101|0915|1111|555|15.0
2|20190101|0930|1111|570|15.0
3|20190101|0945|1111|585|15.0
4|20190101|1010|1111|610|15.0
5|20190101|1730|1111|1050|
6|20190202|0900|1111|540|
7|20190202|0915|1111|555|15.0
8|20190202|0930|1111|570|15.0
9|20190202|0945|1111|585|15.0
10|20190202|1010|1111|610|15.0
11|20190101|0900|2222|540|
12|20190101|0910|2222|550|10.0
13|20190101|0920|2222|560|10.0
14|20190101|0930|2222|570|10.0
15|20190101|0940|2222|580|10.0
16|20190202|0900|2222|540|
17|20190202|0910|2222|550|10.0
18|20190202|0920|2222|560|10.0
19|20190202|0930|2222|570|10.0
20|20190202|0940|2222|580|10.0
21|20190202|0950|2222|590|10.0
22|20190202|1010|2222|610|10.0

休憩時間(9:50-10:00)をはさむ4行目の減算や17時以降に登録された5行目、日付が変わる6行目、idが変わる11行目などがNaNとして処理されてるのでとりあえずできたはず…。

おぼえたこと

１．Pythonはいちいち変数を定義しなくても適当に察して型を適用してくれる(すごい…頭いい…)
２．x.methodは=func(x)みたいな意味(へー)
３．df.indexで行数の数列を取得できる
４．len()で数列の長さを取得できる(→ループ処理につかえる！)
５．df.loc[行番号,'列名']でDataFrame中の任意の値を取得できる(ここを参考にした)
６．ループ処理でprintの代わりにリスト変数xの末尾に値yを書き込んでいくにはx.append(y)を使う
７．mmmmへの変換はdf.apply()の方が速そう

投稿2019/09/01 05:43

編集2019/09/01 05:46

Kio_Mutto

総合スコア5