(5) 효율 100% 엑셀 사무 자동화, 함수 및 기능들 총정리 (파이썬, 판다스)

목차 (작성 예정)

(1) 100% 만족할 파이썬 엑셀 사무 자동화, 회사에서 안 된다면?

(2) 엑셀 보안 한 방에 뚫기

(3) 시간 50배 단축, 실무 엑셀 함수 구현 (vlookup, index match 등)

(4) 실무 엑셀 함수 응용

(5) 엑셀 실무용 유용한 함수 및 기능들 파이썬으로 해결

(6) 언제까지 수작업할래? 크롤링과 사진 자동으로 캡쳐, 스샷

---

https://0goodmorning.tistory.com/72

https://0goodmorning.tistory.com/73

 

지난번 글에서는 실무에서 무조건 쓰는 VLOOKUP, INDEX/MATCH 함수를 파이썬으로 구현하는 방법에 대해서 소개해드렸습니다.  몇 천개가 넘는 데이터를 INDEX/MATCH를 사용하여 여러 조건을 따졌을 때, 밑에 프로세스 계산중이라고 뜨면서 엑셀이 멈추거나 버벅거렸는데 파이썬으로는 30초 안에 해결된 거 같네요.

 

 

엑셀 함수 자동화 기능들

- 띄어쓰기 다른 문자열 비교 (조건 찾기 개선)

- 포함 여부 찾기

- 필터링 방법 (있을 때, 없을 때)

- 인덱스 찾기

- 모든 제품 소문자/ 대문자 바꾸기

- 찾는 조건 값 한 번에 바꾸기

- 여러 시트 합치기

 

 

 

띄어쓰기가 다른 문자열

VLOOKUP 기능을 사용하면서 느꼈던 점은 제품의 경우 대소문자가 달라도 같은 제품으로 인식하는데, 띄어쓰기나 문자가 조금만 다르면 조건에 걸리지 않은 치명적인 단점이 있었습니다.

 

예를 들어 'GALAXY Z FLIP3' = 'galaxy z flip3' 는 조건에서 인식을 하는데 'GALAXY ZFLIP3'나 'GALAXYZFLIP3'는 조건에 해당하지 않아서 #N/A 값이 나오는 황당한 케이스가 너무 많았습니다. 이는 파이썬에서 금방 해결이 가능합니다.

 

 

 

import pandas as pd
 
target = 'GALAXY Z FLIP3'
data = ['galaxy z flip3','GALAXY ZFLIP3', 'GALAXYZFLIP3']
 
for i in data : 
    if target == i :
        print(f'{target}은 {i}과 같습니다.')
    
    else :
        print(f'{target}은 {i}과 다릅니다.')

파이썬에서는 기본적으로 대소문자를 다르게 인식하고 있어서, 엑셀에서는 검색이 됐던 소문자도 다른 것으로 인식이 되고 있습니다. 해결방법은 간단합니다. 두 문자를 모두 소문자로 인식시키고, 공백을 제외해서 비교하면 됩니다.

 

 

 

import pandas as pd
 
target = 'GALAXY Z FLIP3'
data = ['galaxy z flip3','GALAXY ZFLIP3', 'GALAXYZFLIP3']
 
for i in data : 
    target_ = target.lower().replace(' ','')
    i_ = i.lower().replace(' ','')
    if target_ == i_ :
        print(f'{target}은 {i}과 같습니다.')
    
    else :
        print(f'{target}은 {i}과 다릅니다.')

단어를 .lower().replace(' ', '')을 사용하시면 간단하게 해결이 됩니다. 여기서 중요한 것은 변수 자체를 바꾸는 것이 아닌, 비교할 때만 소문자로 만들고 공백(띄어쓰기)를 임시로 제거해야 합니다. 본래 값을 바꾸게 되면, 나중에 입력하는 값에 수정된 값이 입력될 수 있습니다. 예를 들어 'galaxy z flip3'가 아닌 'galaxyzflip3'이 입력이 됩니다.

 

 

 

포함 여부 (문자열, 숫자) / 숫자만 뽑기

import pandas as pd
 
data = ['galaxy z flip3','GALAXY ZFLIP3', 'GALAXYZFLIP3']
 
for i in data : 
    if 'GALAXY' in i :
        print(f'GALAXY가 {i} 제품명에 포함되어 있습니다')

'galaxy z flip3'에 'GALAXY'가 포함되어 있는지 여부를 알려면 어떻게 해야할까요? 위에 예시와는 반대로 .lower()가 아닌 소문자를 대문자로 바꿔주는 .upper()를 사용하시면 됩니다. 

 

 

 

def contain_num(s):
    return any(i.isdigit() for i in s)
 
 
data = ['galaxy z flip','GALAXY ZFLIP2', 'GALAXYZFLIP3']
 
for i in data : 
    if contain_num(i) :
        print(f'{i} 제품명에 숫자가 포함되어 있습니다')

contain_num 함수는 문자열을 하나하나 분리해서 그 안에 숫자가 있는지 확인을 합니다. isdigit()이 숫자면 True, 아니면 False를 반환 하게 됩니다. 그래서 숫자가 포함이 되면 return 값이 True가 되기 때문에, if contain_num(i)를 실행하게 됩니다. 여기서 궁금한 것이 그렇다면 숫자만 찾고고 싶을 수도 있어서 추가로 팁을 드리겠습니다.

 

 

 

 

import re
 
def contain_num(s):
    return any(i.isdigit() for i in s)
 
data = ['galaxy z flip','GALAXY ZFLIP2', 'GALAXYZFLIP3']
 
for i in data : 
    if contain_num(i) :
        number = re.findall(r'\d+', i)[0]
        print(f'{i}는, 갤럭시 Z플립{number} 시리즈 입니다.')

이어져 있는 숫자를 다 가져오게 됩니다. 

 

 

 

re.findall(r'\d+', string)는 string에서 이어진 숫자를 찾아줘서 list를 만들어줍니다. 만약에 여러 숫자를 포함하고 있다면, [0]이 아닌 원하는 숫자가 포함된 인덱스를 사용하시면 됩니다.

 

 

 

엑셀 필터링 기능 - 필요한 데이터 추출, 포함 할 때 & 포함 안 할 때

코드 설명을 하고 밑에 한 번에 코드를 붙여놓을게요. 이러한 데이터가 있을 때 삼성 데이터만 보고싶다면 어떻게 해야할까요?

 

 

 

지난번 글에서 알려드렸던 것처럼 VLOOKUP 함수에서의 조건을 사용하시면 됩니다. phone[phone['Company'] ==  'Samsung'] 굳이 분리를 안 하셔도 되는데 이해하기 쉽게 사진을 찍었습니다.

 

 

 

찾아야할 조건이 여러개가 된다면 리스트를 만들어주고 isin 을 사용하시면 됩니다. 포함 유무를 확인하여 뽑아줍니다.

 

 

 

 

만약에 조건을 포함하지 않는 것을 찾는다면 어떻게 해야할까요? 방법은 의의로 간단합니다. df.loc[~조건 ] 조건 안에 물결 표시 '~'를 추가하면 됩니다. 

 

 

 

 

이 방법 또한 문자열을 포함하면 필터링을 해주는 기능인데, 위 방법과 무슨 차이가 있을 까요? 자세히 보시면 회사명이 정확히 apple과 samsung이 아닌데도 필터링이 정확하게 됐습니다. str.contains()는 부분 포함을 해도 찾게 됩니다.

 

 

 

 

인덱스를 구하는 것은 더 쉽습니다. 위에 함수에서 df.loc[조건]을 하지 않고 df[조건].index를 하시면 간단합니다.

 

 

 

import pandas as pd 
 
#데이터 읽기
phone = pd.read_clipboard(keep_default_na=False, sep="\t")
 
#원하는 데이터 필터링
condition = ( phone['Company'] == 'Samsung' )
samsung = phone[condition]
 
#여러 데이터 필터링
company_list = ['Apple','Samsung']
company_condition = phone['Company'].isin(company_list)
company = phone.loc[company_condition]
 
#원하는 데이터 포함하지 않는 값 필터링
company_list = ['Apple','Samsung']
company_condition = phone['Company'].isin(company_list)
company = phone.loc[~company_condition]
 
#문자열 포함 데이터 필터링
company_list = ['App','Sam']
condition2 = '|'.join(company_list)
company_condition = phone['Company'].str.contains(condition2,na=False)
company = phone.loc[company_condition]
 
#인덱스 구하기
company_condition = phone['Company'].str.contains('Apple',na=False)
company_index = phone[company_condition].index

 

 

모든 데이터 변경 대문자 / 소문자

 

for 문을 돌릴 필요 없이 원하는 컬럼에 str.lower()를 사용하면 모든 제품이 소문자로 변경된다. 반대는 str.upper() 사용!

 

 

 

 

 

특정 데이터 한 번에 변경

갤럭시 Z플립3 5G만 5만원씩 할인을 한다고 했을 때, 'df.loc[조건, 열] = 원하는 값' 을 입력해주시면 됩니다.

 

 

 

 

여러 데이터 합치기 

 

위와 같이 phone과 phone_price라는 시트가 있습니다. phone 시트에서 Product와 Price 열만 가지고 오려면 어떻게 할까요?

 

 

 

pd.concat([기존 시트, 추가 시트1, 추가 시트2], join = 'inner) concat과 inner join을 사용하시면 기존 컬럼을 유지하게 됩니다.

 

 

 

append 사용하기. 앞으로 관리를 할 때 Company와 Review 열을 추가로 가져오고 싶으면, 열에 맞게 값이 추가가 됩니다. 기존 시트에 열이 없으면 새로 가져오는 시트에서 열을 가져오게 됩니다. 그래서 기존 시트는 Company와 Review 열에 NaN 값이 들어가게 됩니다.

 

 

 

import pandas as pd 
 
#기존 시트
phone_price = pd.read_clipboard(keep_default_na=False, sep="\t")
 
#새로운 시트
phone = pd.read_clipboard(keep_default_na=False, sep="\t")
 
#기존 시트의 열만 참조
phone_update = pd.concat([phone_price, phone], join='inner')
 
#새로운 시트의 열까지 참조
phone_new = phone_price.append(phone, sort=False)

이외에도 중복 제거 drop_duplicates(), NaN 값 제거 dropna() 등 여러가지 함수들이 있는데, 이 부분은 찾으면 금방 나와서, 실무에서 자주 사용했던 기능들만 올렸습니다. 혹시 더 간단한 방법 있으면 공유해주시면 감사하겠습니다 ^^