Louis Woo

Code Everyday!

2 minute read

1. Read a excel file : read_excel()

import pandas as pd

df1 = pd.read_excel("E07EXAMPLE.xlsx", sheet_name=1)
df1
Subject Name Mark
0 Economics Louis 99.0
1 Economics Harvey NaN
2 Economics G-dragon 95.0
3 Economics Lola 74.0
4 Economics Jorge 75.0
... ... ... ...
150 Math CoolJ 95.0
151 Math Kara 92.0
152 Math Tiara 74.0
153 Math Kolon NaN
154 Math SungGil NaN

155 rows × 3 columns

2. Restructure the chart : pivot_table()

pdf1 = df1.pivot_table("Mark", index="Name", columns="Subject", aggfunc="count")
pdf1
Subject Economics English Math Science Sociology
Name
Coline 1 1 1 1 1
Conner 1 1 0 1 1
CoolJ 1 1 1 1 1
Fibio 1 0 1 1 1
G-dragon 1 1 1 1 1
Gerge 1 1 1 1 1
Gorila 1 0 1 1 0
Grace 1 1 1 1 1
Guggi 1 0 1 1 1
Harvey 0 1 1 1 1
James 1 1 1 1 1
Jorge 1 1 1 1 1
Kara 1 1 1 0 1
Katy 1 1 1 1 1
Kim 1 1 0 1 1
Kolon 1 1 0 1 1
Lola 1 1 1 1 1
Louis 1 1 1 1 1
Marry 1 1 1 1 1
Mitchy 1 1 0 1 1
Phil 1 0 1 1 1
Piona 1 1 1 1 1
Sally 1 1 1 1 1
Sanchez 1 1 1 1 1
Stacy 0 1 1 1 1
SungGil 1 1 0 1 0
Sunny 1 1 1 0 1
TL 1 1 1 1 1
Tiara 1 1 1 1 1
Zhen 1 1 1 0 1
kipling 0 1 1 1 1

3. “If” function in Excel : mask()

cond1 = pdf1 >= 1
pdf1 = pdf1.mask(cond1, "O").mask(~cond1, "X")
pdf1
Subject Economics English Math Science Sociology
Name
Coline O O O O O
Conner O O X O O
CoolJ O O O O O
Fibio O X O O O
G-dragon O O O O O
Gerge O O O O O
Gorila O X O O X
Grace O O O O O
Guggi O X O O O
Harvey X O O O O
James O O O O O
Jorge O O O O O
Kara O O O X O
Katy O O O O O
Kim O O X O O
Kolon O O X O O
Lola O O O O O
Louis O O O O O
Marry O O O O O
Mitchy O O X O O
Phil O X O O O
Piona O O O O O
Sally O O O O O
Sanchez O O O O O
Stacy X O O O O
SungGil O O X O X
Sunny O O O X O
TL O O O O O
Tiara O O O O O
Zhen O O O X O
kipling X O O O O

4. Copy to the clipboard : to_clipboard()

pdf1.to_clipboard()

5. Total Code


import pandas as pd

df1 = pd.read_excel("E07EXAMPLE.xlsx", sheet_name=1)
pdf1 = df1.pivot_table("Mark", index="Name", columns="Subject", aggfunc="count")
cond1 = pdf1==1
pdf1 = pdf1.mask(cond1, "O").mask(~cond1, "X")
pdf1.to_clipboard()

Updated:

You may also enjoy