Practice #6 Mask

1. Read a excel file : read_excel()

import pandas as pd

df1 = pd.read_excel("E06EXAMPLE.xlsx", sheet_name=1)
df1

	Name	Mark
0	Louis	98.0
1	Harvey	NaN
2	G-dragon	NaN
3	Lola	100.0
4	Jorge	82.0
5	Piona	76.0
6	Mitchy	90.0
7	Fibio	NaN
8	Kim	92.0
9	Stacy	91.0
10	Grace	57.0
11	TL	67.0
12	Sanchez	91.0
13	Zhen	89.0
14	James	59.0
15	Coline	86.0
16	Gorila	NaN
17	Sunny	86.0
18	Conner	77.0
19	Sally	58.0
20	Marry	56.0
21	Katy	54.0
22	Gerge	68.0
23	kipling	NaN
24	Guggi	91.0
25	Phil	58.0
26	CoolJ	58.0
27	Kara	77.0
28	Sally	86.0
29	Tiara	56.0
30	Kolon	79.0
31	SungGil	76.0

2. “If” function in excel : mask

cond1 = df1["Mark"].isnull()
df1["Attendancy"] = df1["Mark"].mask(cond1, "X").mask(-cond1, "O")
df1

	Name	Mark	Attendancy
0	Louis	98.0	O
1	Harvey	NaN	X
2	G-dragon	NaN	X
3	Lola	100.0	O
4	Jorge	82.0	O
5	Piona	76.0	O
6	Mitchy	90.0	O
7	Fibio	NaN	X
8	Kim	92.0	O
9	Stacy	91.0	O
10	Grace	57.0	O
11	TL	67.0	O
12	Sanchez	91.0	O
13	Zhen	89.0	O
14	James	59.0	O
15	Coline	86.0	O
16	Gorila	NaN	X
17	Sunny	86.0	O
18	Conner	77.0	O
19	Sally	58.0	O
20	Marry	56.0	O
21	Katy	54.0	O
22	Gerge	68.0	O
23	kipling	NaN	X
24	Guggi	91.0	O
25	Phil	58.0	O
26	CoolJ	58.0	O
27	Kara	77.0	O
28	Sally	86.0	O
29	Tiara	56.0	O
30	Kolon	79.0	O
31	SungGil	76.0	O

3. Multiple “if” in excel : multiple masks

cond2 = df1["Mark"]>70
cond3 = df1["Mark"]>75
cond4 = df1["Mark"]>80
cond5 = df1["Mark"]>85

df1["Grade"] = df1["Mark"].mask(-cond2, "C").mask(cond2,"B").mask(cond3,"B+").mask(cond4, "A").mask(cond5, "A+")
df1

	Name	Mark	Attendancy	Grade
0	Louis	98.0	O	A+
1	Harvey	NaN	X	C
2	G-dragon	NaN	X	C
3	Lola	100.0	O	A+
4	Jorge	82.0	O	A
5	Piona	76.0	O	B+
6	Mitchy	90.0	O	A+
7	Fibio	NaN	X	C
8	Kim	92.0	O	A+
9	Stacy	91.0	O	A+
10	Grace	57.0	O	C
11	TL	67.0	O	C
12	Sanchez	91.0	O	A+
13	Zhen	89.0	O	A+
14	James	59.0	O	C
15	Coline	86.0	O	A+
16	Gorila	NaN	X	C
17	Sunny	86.0	O	A+
18	Conner	77.0	O	B+
19	Sally	58.0	O	C
20	Marry	56.0	O	C
21	Katy	54.0	O	C
22	Gerge	68.0	O	C
23	kipling	NaN	X	C
24	Guggi	91.0	O	A+
25	Phil	58.0	O	C
26	CoolJ	58.0	O	C
27	Kara	77.0	O	B+
28	Sally	86.0	O	A+
29	Tiara	56.0	O	C
30	Kolon	79.0	O	B+
31	SungGil	76.0	O	B+

4. Copy to Clipboard : to_clipboard()

df1.to_clipboard(index=False)

5. Total Code

import pandas as pd

df1 = pd.read_excel("E06EXAMPLE.xlsx", sheet_name=1)

cond1 = df1["Mark"].isnull()

df1["Attendancy"] = df1["Mark"].mask(cond1, "X").mask(-cond1, "O")

cond2 = df1["Mark"]>70
cond3 = df1["Mark"]>75
cond4 = df1["Mark"]>80
cond5 = df1["Mark"]>85

df1["Grade"] = df1["Mark"].mask(-cond2, "C").mask(cond2,"B").mask(cond3,"B+").mask(cond4, "A").mask(cond5, "A+")

df1.to_clipboard(index=False)