파이썬 업무 자동화 과제 rev.2

homework2.py

#-*- encoding=utf-8 -*-

import pandas as pd
import random
from faker import Faker

faker = Faker('ko_KR')

df =pd.DataFrame(columns=[
    "Name",
    "Address",
    "Phonenumber",
    "Math Score",
    "Science Score",
])

for new_row_index in range(10000):
    phonenumber = "010" + "".join([str(random.randint(0, 9)) for x in range(8)])

    # 10% 확률에 대해서 대한민국 국가번호를 붙인 핸드폰 번호가 존재한다.
    if random.randint(0, 100) < 10:
        phonenumber = "8210" + phonenumber[3:]

    new_row = {
        'Name': faker.name(),
        'Address': faker.address(),
        'Math Score': random.randint(0, 100),
        'Science Score': random.randint(0, 100),
        'Phonenumber': phonenumber,
    }

    df.loc[new_row_index] = new_row

df.to_excel("student.xlsx", index=False)

def numberChange(number):
    if len(str(number)) == 11 :
        return str(number)[:3] + "-" + str(number)[3:7] +"-" + str(number)[7:]
    else:
        return "010" +  "-" + str(number)[4:8] +"-" + str(number)[8:]


df["Preprocessed Phonenumber"] = ""
df["Preprocessed Phonenumber"] = df["Phonenumber"].apply(numberChange)  # 전처리된 휴대폰 번호


# 점수에 따라 Grade 부여
def makeGrade(ranking):
    if ranking <= 2000:
        return "A"
    elif 2000 < ranking <= 4000:
        return "B"
    else:
        return "C"

#Math Score를 토대로 rank 생성 / rank에 따라 등급결정
df["Math Grade"] = ""
df["Math Grade"] = df["Math Score"].rank(ascending=False,method='max')
df["Math Grade"] = df["Math Grade"].apply(makeGrade)


#Science Score를 토대로 rank 생성 / rank에 따라 등급결정
df["Science Grade"] = ""
df["Science Grade"] = df["Science Score"].rank(ascending=False,method='max')
df["Science Grade"] = df["Science Grade"].apply(makeGrade)


df["Rejected"] = ""

# def reject(mg,sg):
#     if (mg == "C") & (sg == "C"):
#         return "False"
#     else:
#         return ""

#Science와 Math의 등급이 모두 C이면, False 표기 with for 문
# for row in range(0,10000):
#     df["Rejected"][row] = reject(mg=df["Math Grade"].get_value(row),sg=df["Science Grade"].get_value(row))


#Science와 Math의 등급이 모두 C이면, False 표기 with apply
def reject2(row):
    if (row["Math Grade"] == "C") & (row["Science Grade"] == "C"):
        return "False"
    else:
        return ""

df["Rejected"] = df.apply(reject2,axis=1)


df.to_excel("homeWork.xlsx")