如何在 Pandas 使用 Excel 的 Match 函數

7/14/2021 PythonPandasExcelMatch

因疫情關係,到辦公室的人都須事先填寫問券,在搭配門禁系統的刷卡紀錄,就可比對出哪些人有到辦公室,卻沒有填寫問券
要達成這個目的,利用 Excel 的 Match 函數,在二個資料表中比對,就可完成

那在 Python 的 Pandas 中,該如何做到跟 Excel 的 Match 函數一樣的效果呢

不熟悉 Match 函數 的可參考這篇:
如何在 Excel 中跨分頁查找所需資料 (Match 函數介紹) (opens new window)

95-01

上面這二個資料表 (左邊是問券資料,右邊是門禁系統資料)

Pandas 中有個 pd.merge() 方法,可以合併二個資料表

# 讀取 Excel

import pandas as pd
import os

# Get desktop path
desktopPath = os.path.abspath(os.path.dirname(os.getcwd()))

userInput = input("Please type mmdd : ")

# Get file path
filePath = desktopPath + r'\Dairy Access Report\Match.xlsx'

# Read excel
ques = pd.read_excel(filePath, 'Question')
door = pd.read_excel(filePath, 'Door')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 問券資料表

先將資料表的欄位名稱,再來因為問券資料表內的資料是一個區間的,所以我們用 userInput 來讓 User 選擇要抓取哪天的問券資料,之後將那天的資料存成 ques

# Update Date format from 2021-07-07  -> 0707
def removeYear(series):
    series = series.apply(str).apply(lambda x : x[5 : 10]).str.strip()
    series = series.apply(lambda x : x.replace('-', ''))
    return series

# Rename columns for dataframe ques
ques = ques.rename(columns= {'Which date you will go to the office' : 'Date', 'Employee ID #' : 'EID' })

### Select the date we need
# Set Y in column Fill Ques
ques.loc[removeYear(ques['Date']) == userInput, 'Fill Ques']  = 'Y'

# Selected Fill Ques == Y as df ques
ques = ques[ques['Fill Ques'] == 'Y']

ques = ques[['EID', 'Fill Ques']]

# Setting EID as index
ques.set_index(['EID'], inplace=True)

print(ques)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

95-02

上圖可以看到在 07/08 這天只有 3 個人填寫問券

# 門禁資料表

門禁系統因為沒有單獨的 ID 欄位,所以須用字串切割的方式來取得 ID
且因為同一個人在門禁資料表中會有多次刷卡紀錄,因此要用 groupby() 來挑出每個人最早的刷卡時間

# Get employee ID
door['ID'] = door['Person Name'].apply(lambda x:x[x.index("(")-7 : x.index("(")]).str.strip()

# Get the first clock record by ID
door = door.groupby(['ID']).agg({'Date' : 'min'})
print(door)
1
2
3
4
5
6

95-03

門禁資料表(door)篩選出來會如上圖

# 合併資料表

二個資料表(ques & door) 都準備好後,就可以利用 merge 來將這二個表合併成一個(df)了

因為這二個表 index 都是 ID,因此我們可以用 left_index=True, right_index=True 作為合併的基準

TIP

這裡要注意:我們是將 ques 資料表(有填問券的人)拿來比對門禁系統(有進辦公室的人)
所以 door 要放在 ques 的前面(pd.merge(door, ques, how='left', left_index=True, right_index=True)

合併後, 門禁系統內的人若沒有填問券(也就是 door 有資料,而 ques 沒資料),則 Fill Ques 欄位會顯示 NAN(如下圖左)

95-04

所以我們可以用 df['Fill Ques'] = df['Fill Ques'].fillna('N') 將 NaN 改為 N(如上圖中)

最後再將 ID 從 index 移出來,並 reset index (如上圖右)

即可完成如同 Excel 的 Match 功能

# Merge door & ques based on index
df = pd.merge(door, ques, how='left', left_index=True, right_index=True)
print(df)

df['Fill Ques'] = df['Fill Ques'].fillna('N')
print(df)

# Move index to a column
df['ID'] = df.index

# Reset index
df.reset_index(drop=True, inplace=True)
print(df)

with pd.ExcelWriter(desktopPath  + r'\Dairy Access Report\MatchDemo_' + userInput + '.xlsx') as writer:
    df.to_excel(writer, sheet_name = "df", index=False)

print("Saved to Excel")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# Source Code

import pandas as pd
import os
from os import system
system('cls')


#####################################################
################# Read Excel Files ##################
#####################################################

desktopPath = os.path.abspath(os.path.dirname(os.getcwd()))
# get file
userInput = input("Please type mmdd : ")

filePath = desktopPath + r'\Dairy Access Report\Match.xlsx'

ques = pd.read_excel(filePath, 'Question')
door = pd.read_excel(filePath, 'Door')


# Update Date format from 2021-07-07  -> 0707
def removeYear(series):
    series = series.apply(str).apply(lambda x : x[5 : 10]).str.strip()
    series = series.apply(lambda x : x.replace('-', ''))
    return series

#####################################################
################### questionnaire ###################
#####################################################

# rename columns for dataframe ques
ques = ques.rename(columns= {'Which date you will go to the office' : 'Date', 'Employee ID #' : 'EID' })

### Select the date we need
# Set Y in column Fill Ques
ques.loc[removeYear(ques['Date']) == userInput, 'Fill Ques']  = 'Y'

# Selected Fill Ques == Y as df ques
ques = ques[ques['Fill Ques'] == 'Y']

ques = ques[['EID', 'Fill Ques']]

# Setting EID as index
ques.set_index(['EID'], inplace=True)


#####################################################
######################## Door #######################
#####################################################

# Get employee ID
door['ID'] = door['Person Name'].apply(lambda x:x[x.index("(")-7 : x.index("(")]).str.strip()

# Get the first clock record by ID
door = door.groupby(['ID']).agg({'Date' : 'min'})

#####################################################
####################### Merge #######################
#####################################################

# Merge door & ques based on index
df = pd.merge(door, ques, how='left', left_index=True, right_index=True)

df['Fill Ques'] = df['Fill Ques'].fillna('N')

# Move index to a column
df['ID'] = df.index

# Reset index
df.reset_index(drop=True, inplace=True)

#####################################################
############# Save dataframe to Excel ###############
#####################################################

with pd.ExcelWriter(desktopPath  + r'\Dairy Access Report\MatchDemo_' + userInput + '.xlsx') as writer:
    df.to_excel(writer, sheet_name = "df", index=False)

print("Saved to Excel")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
Last Updated: 6/30/2023, 1:40:10 AM

歡迎點擊追蹤:

(adsbygoogle = window.adsbygoogle || []).push({});