✔굿모닝 IT ✔

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

오랜만에 글을 썼는데.. 사무자동화 마지막 글이네요. 우선 생각했던건 여기까지고 추가로 필요한 부분 있으면 제가 또 만들지 않을까.. bs4나 셀레니움(selenium)으로 크롤링까지는 금방 완성이 되는데, 자동 캡쳐나 스크린샷을 찍는 방법은 처음으로 시도했다. 검색을 해보면 전체화면 캡쳐를 하는게 대부분이고, 내가 원하는 element나 특정 부분 캡쳐는 설명이 별로 없었다.

복잡하게 좌표값이나 height, witdh를 다 구해서 전체화면 스크린샷을 찍고 원하는 부분만 가져오는 것도 많았다. 물론 크롬에서 쉽게 특정 element를 캡쳐하는 방법도 있었다. 약간 신세계를 경험하는 것 같았는데 자동화가 아니다 보니 패스.. 그래도 짧게 소개해보자면

GSMARENA라는 싸이트에서 빨간색 박스를 친 부분의 사진만 가져오고 싶을 때, 우선 F12 개발자 도구를 켠다. 그 다음에 Elements에서 저 부분을 포함하는 class나 id를 찾고 클릭을 한다. 이후에 shift + crtl + p 를 누른다.

그러면 개발자용 명령창이 뜨게 되는데 약자로 cnod를 검색. capture node screenshot이 나오게 되는데, 클릭하면 바로 캡쳐가 된다. 심지어 결과도 깔끔하고 화질도 나쁘지 않다. 그러면 뭐하나.. 자동이 아니면 매번 이 작업을 반복해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
 
chrome_options = Options()
driver = webdriver.Chrome(options=chrome_options)
 
element = driver.find_element_by_id('specs-list')
element.screenshot('screenshot.png')
 
element_png = element.screenshot_as_png
with open('screenshot.png', "wb") as file:
    file.write(element_png)

cs

다행히 셀레니움 기능중에 특정 element 값만 캡쳐하는 기능이 있었다. screenshot으로 저장하는 기능과 screenshot_as_png로 저장하는 기능인데 차이점은 거의 없는 것 같다. 이렇게 수월하게 되면 개발이 재미가 없지...

해결해야할 문제들

- 특정부분이 화면에 나오지 않으면 짤려서 찍혔다.

- 크롭옵션에 따라 캡쳐가 안 되기도 했다.

- 듀얼모니터에서 실행이 안 됐다.

- 화면 축소시 찍히지 않았다.

최대한 덜 잘려나오게 하려면 셀레니움이 노트북 화면이 아닌, 듀얼 모니터 화면에서 실행 되어야 하는데 안 됐다. 좌표값을 옮겨서 확대를 하라고 해서 여러 방법 끝에 됐다. 그리고 headless의 경우 어떤 화면에서는 잘 캡쳐가 됐지만, 안 되는 경우도 있어서 뺐다.

1
2
3
4
5
6
7
8
9
10

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
 
chrome_options = Options()
 
# chrome_options.headless = True #싸이트마다 다르게 캡쳐가 됨
# chrome_options.add_argument("--kiosk") #F11 눌러진 효과
chrome_options.add_argument("--window-position=2000,0") #듀얼모니터에서 보기
driver = webdriver.Chrome(options=chrome_options)
driver.maximize_window() 
Colored by Color Scripter

cs

듀얼 모니터 화면에서 캡쳐가 됐다!! 하지만 화면이 잘리지 않으려면 화면을 축소해서 찍어야 했다. 사람들마다 셀레니움 화면을 축소하는 다양한 방법을 소개시켜줬는데 잘 안 됐다. 기본 설정에 들어가서 바꾸라는 사람부터 zoom out을 하라는 것도 있었지만, 값을 바꾸게 되면 css에도 영향을 미치는 것 같았다.

1
2
3
4
5

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
 
# driver.execute_script("document.body.style.zoom='50%'")
driver.execute_script("document.body.style.transform = 'scale(0.50)'")

cs

document.body.style.zoom을 활용하게 되면 안 된다. document.body.style.transform을 사용해야 한다. 원하는 요소가 한 페이지에 나오면 좋겠지만, 아닌 경우도 있으니 쓰시면 될 것 같습니다. 문제는 어느정도 해결한 듯 보였으나 원하는 이미지가 서로 다른 element에 있어서 이미지 병합까지 하게 됐다.

밑에 과정은 gsmarena에서 기본적인 정보를 크롤링하고 이미지 캡쳐, 병합하는 과정입니다.

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
80
81
82
83
84
85
86
87
88
89

from bs4 import BeautifulSoup
import pandas as pd
import requests
import re
 
#환율
def exchange_rate(today_rate, price,r) : #r은 반올림 자리 숫자
    p_price = round(today_rate * int(price),r)
    return str(p_price)
 
#월
def month_string_to_number(string):
    m = { 'jan': 1, 'feb': 2, 'mar': 3, 'apr':4,  'may':5,  'jun':6,  'jul':7,  'aug':8,  'sep':9,  'oct':10,  'nov':11,  'dec':12 }
    s = string.strip()[:3].lower()    
    try:
        month = m[s]
        return month
    except:
        return '확인 바람'
 
#이름
def crawl_gsmarena(j,today_rate1,today_rate2) :
    
    global phone_name
    url = j
    
    headers = {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36"}
    res = requests.get(url, headers=headers)
    soup = BeautifulSoup(res.text, "html.parser") 
 
    tds= soup.find_all('td', attrs={'class': 'nfo'})
    
    try :    
        phone_name = soup.select('h1.specs-phone-name-title')[0].text
        
    except :
        phone_name = 0
        return phone_name
 
    for td in tds:
        
        global s_year, s_mon, phone_price, exchange
        
        if 'status' in str(td) :
 
        # <td class="nfo" data-spec="status">Available. Released 2021, August 27</td>
            try :
                td = td.text
                s_year = re.findall('\d+', td)[0]
                s_mon = td.split(',')[1]
                s_mon = re.sub('[^a-zA-Z]', '', s_mon)
                s_mon = month_string_to_number(s_mon)
                
            except :
                s_year = 'comming_soon'
                s_mon = 'comming_soon'
                
        elif 'price' in str(td) :
            td = td.text 
            # p = td.split()[1]
            
            if '$' in td :
                p = td.split('$')[1].split()[0]
                phone_price = p
                exchange = ''
                    
            elif '€' in td :
                p = td.split('€')[1]
                p = p.split()[0].replace(',','').split('.')[0]
                phone_price = exchange_rate(today_rate1, p,2)  # today_rate 환율 입력
                exchange = today_rate1
                
            elif '₹' in td :
                p = td.split('₹')[1]
                p = p.split()[0].replace(',','').split('.')[0]
                phone_price = exchange_rate(today_rate2, p,3)  # today_rate 환율 입력
                exchange = today_rate2
                            
            elif 'EUR' in td :
                p = td.replace(',','')
                p = re.findall('\d+', p)[0]
                phone_price = exchange_rate(today_rate1, p,2)
                exchange = today_rate1    
 
            else :
                phone_price = td
                exchange = ''
                
    return phone_name, s_year, s_mon, phone_price, exchange
Colored by Color Scripter

cs

핸드폰 이름, 생산 년도, 월, 핸드폰 가격 등을 가져옵니다.

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
80
81
82
83
84
85
86
87
88
89
90
91

import requests
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import time
import os 
import glob
from PIL import Image
import glob
import shutil
 
#이미지 병합
def merge_img(name):
    files = glob.glob('crawling_temp/*.png')
 
    full_width, full_height = 364, 0 #364로 고정 / 너비가 2배로 찍혀서 반은 없애야 함
 
    for f in files :
        image = Image.open(f)
        _, height = image.size
 
        # full_width = max(full_width,width)
        full_height += height
 
 
    canvas = Image.new('RGB', (full_width, full_height), 'white')
    output_height = 0
    
    for i in files:
        with Image.open(i) as image :
            _, height = image.size
            canvas.paste(image, (0, output_height))
            output_height += height
 
    canvas.save(f'crawling/{name}.png')
    
    shutil.rmtree('crawling_temp')
    
    return 
 
#이미지 리사이징
def resize_img():
    im = Image.open(f'crawling_temp/a.png')
    half = 0.5
    out = im.resize( [int(half * s) for s in im.size] )
    out.save(f'crawling_temp/a.png')
    im.close()
    return 
 
chrome_options = Options()
chrome_options.add_argument('--profile-directory=Default')
chrome_options.add_argument("--incognito")
chrome_options.add_argument("--disable-plugins-discovery")
chrome_options.add_argument("--window-position=2000,0") #듀얼모니터에서 보기
 
driver = webdriver.Chrome(options=chrome_options)
driver.maximize_window() 
 
 
    
urls = 'https://www.gsmarena.com/samsung_galaxy_s21_fe_5g-10954.php' # 찾고자 하는 url 리스트
names = 'samsung galaxy s21' # 검색키워드 리스트
 
for i in range(len(urls)) :
    url = urls[i]
    driver.get(url)
    
    element = driver.find_element_by_class_name("article-info")
 
    if os.path.exists('crawling_temp') :
        pass
    else :
        os.mkdir('crawling_temp')
    
    element.screenshot(f'crawling_temp/a.png')
    resize_img()
    
    driver.execute_script("document.body.style.transform = 'scale(0.50)'") #축소
 
    element2 = driver.find_element_by_id('specs-list')
    element2.screenshot(f'crawling_temp/b.png')
 
    name = names[i]
    if '/' in name :
        name = name.replace('/','')
    merge_img(name)
    
    time.sleep(2) # 빠르면 봇으로 인식
    
# 이미 사용중이라고 에러가 뜨면 vs코드 껐다가 다시 실행
print('완료')
    
Colored by Color Scripter

cs

이미지 병합, 리사이징까지의 과정이 있습니다. 화면을 축소하니까 사진을 잘못 찍는 경우가 있어서, 사진 1장은 원래 크기에서 찍고, 다른 1장은 축소해서 찍었네요. 그리고 리사이징을 해서 병합하는 복잡한 과정을 거쳤습니다. 여러분들이 도전하는 크롤링과 자동 캡쳐 스크린샷 코드는 문제가 없길 바랍니다 ㅠㅠ

'할 수 있다. 파이썬' 카테고리의 다른 글

(5) 효율 100% 엑셀 사무 자동화, 함수 및 기능들 총정리 (파이썬, 판다스) (0)	2022.01.23
(4) 실무 엑셀 함수 TOP3 자동화 - VLOOKUP 다중조건, INDEX/MATCH 파이썬으로 (0)	2022.01.22
(3) 실무 엑셀 함수 VLOOKUP, INDEX MATCH 시간 50배 단축, 파이썬으로 한 방에 잡자 (0)	2022.01.21
(2) 파이썬 엑셀 사무 자동화 : 보안 걸린 엑셀 한 번에 뚫기 openpyxl? xlwings? (2)	2022.01.20
(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신... (0)	2022.01.19

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

C.L.O.W.N 2022. 1. 23. 19:30

2022. 1. 23. 19:30

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

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 값이 나오는 황당한 케이스가 너무 많았습니다. 이는 파이썬에서 금방 해결이 가능합니다.

1
2
3
4
5
6
7
8
9
10
11

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}과 다릅니다.')

cs

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

1
2
3
4
5
6
7
8
9
10
11
12
13

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}과 다릅니다.')

cs

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

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

1
2
3
4
5
6
7

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

cs

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

1
2
3
4
5
6
7
8
9

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} 제품명에 숫자가 포함되어 있습니다')

cs

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

1
2
3
4
5
6
7
8
9
10
11

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} 시리즈 입니다.')

cs

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

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

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

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

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

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

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

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

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

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

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

cs

 

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

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 값이 들어가게 됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13

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)

cs

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

'할 수 있다. 파이썬' 카테고리의 다른 글

(6) 사무 자동화 - 웹 검색 후 특정 부분 자동 캡쳐/ 스크린샷, 확대 축소 (0)	2022.01.24
(4) 실무 엑셀 함수 TOP3 자동화 - VLOOKUP 다중조건, INDEX/MATCH 파이썬으로 (0)	2022.01.22
(3) 실무 엑셀 함수 VLOOKUP, INDEX MATCH 시간 50배 단축, 파이썬으로 한 방에 잡자 (0)	2022.01.21
(2) 파이썬 엑셀 사무 자동화 : 보안 걸린 엑셀 한 번에 뚫기 openpyxl? xlwings? (2)	2022.01.20
(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신... (0)	2022.01.19

(4) 실무 엑셀 함수 TOP3 자동화 - VLOOKUP 다중조건, INDEX/MATCH 파이썬으로

C.L.O.W.N 2022. 1. 22. 19:30

2022. 1. 22. 19:30

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

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

지난 편에 이어서 작성하겠습니다. 실무에서 자주 활용되는 엑셀 함수 VLOOKUP, INDEX/MATCH 기본적인 구현 방법에 대해서는 전 글을 확인해주세요^^

1
2
3
4
5
6
7
8
9
10
11

import pandas as pd     # pandas가 없으면 터미널에서 pip install pandas
 
# df =  pd.read_csv('위치/파일명.csv', encoding='utf-8-sig') # 직접 불러오기
df =  pd.read_clipboard(keep_default_na=False, sep="\t") # 복사해서 불러오기
 
for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]
    
    col_E = df[ (df['매체명'] == col_A) & (df['노출수'] == col_D) ]['가격'].values[0]
    df['비용'][i] = col_E
Colored by Color Scripter

cs

저번처럼 모듈을 불러오고 반복문을 사용합니다. 지난번에는 facebook, exposure라고 변수명을 지정했는데, 이번에는 간편하게 col_A, col_D 라고 지정하겠습니다. 지난 내용을 단지 for 문 안에 집어넣은 것이기 때문에 어려움은 없지만, 파이썬을 아예 처음 접하신 분들을 위해서 잠깐 설명을 하자면

값이 4개 밖에 되지 않아서 직접 쳐도 된다고 생각하지만, 저희가 다루는 데이터 양이 많습니다. 그래서 이를 반복문으로 돌리는 것입니다. for문의 기본 구조를 보자면,

for [변수] in [문자열, 리스트, 튜플] :

[수행부분]

이런 식으로 구성이 되어 있는데, [문자열, 리스트, 튜플] 등을 순서대로 돌면서 [변수]에 넣어주게 됩니다. range(4)를 할 경우에 [변수]에 0,1,2,3 까지를 넣어주게 됩니다. range의 경우 0부터 시작해서 range 안에 숫자-1 까지의 숫자를 사용합니다.

col_A = df['매체'][i] 는 for문을 돌면서 변수가 0 일 때 페이스북, 1 일 때 구글, 2 일 때 네이버... 이런 식으로 변경이 되는 것입니다. 몇 번 사용하시다 보면 자연스레 익히게 됩니다. 어떤 식으로 돌아가는지만 생각하시면 될 것 같네요.

만약 페이스북의 노출이 400이 되면 어떻게 될까요? 엑셀에서도 #N/A 값이라고 하면서 오류가 뜹니다. 파이썬에서도 위와 같은 코드로 진행할 경우 마찬가지로 오류가 뜨게 됩니다.

이렇게 오류가 뜨게 되는 이유는 매체명이 페이스북이면서, 노출수가 400인 케이스가 존재하지 않기 때문입니다. 그런데 .values[0]으로 값을 가져오라고 해서 index 0 is out of bounds가 뜨게 되는 것입니다. 이 오류를 해결하기 위해서는 간단하게 한 줄만 추가하면 됩니다.

1
2
3
4
5
6
7

for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]  
    col_E = df[ (df['매체명'] == col_A) & (df['노출수'] == int(col_D)) ]['가격']
    
    if len(col_E) : #존재하는지 확인
        df['비용'][i] = col_E.values[0]
Colored by Color Scripter

cs

len() 함수는 문자열의 길이를 구하는 함수인데, 아무것도 존재하지 않을 때 0 이 된다. 그리고 파이썬에서 0은 False로 인식하기 때문에, len(col_E)가 0이 되면 if 문이 실행이 되지 않는다. 그렇기 때문에 페이스북의 비용은 빈칸이 된 것이다. 그렇다면 페이스북 비용에 다른 값을 넣고 싶다면 어떻게 해야할까?

1
2
3
4
5
6
7
8
9
10

for i in range(4) : #시트를 분리하지 않았기 때문에 매체 4개 값만
    col_A = df['매체'][i]
    col_D = df['노출'][i]  
    col_E = df[ (df['매체명'] == col_A) & (df['노출수'] == col_D) ]['가격']
    
    if len(col_E) :
        df['비용'][i] = col_E.values[0]
    
    else : 
        df['비용'][i] = '비용 확인바람'
Colored by Color Scripter

cs

else 구문을 활용하여 쉽게 자신이 원하는 문구를 집어넣을 수 있다. 파이썬을 많이 이용하신 분들에게는 완전 쌩기초기 때문에 그냥 웃으면서 넘기시면 됩니다. 이제 어느정도 활용을 해봤으니, 다른 시트에서 값을 가져오는 방법을 소개하고자 합니다.

파일을 불러오는 방식은 처음과 동일합니다. 다만 차이는 데이터를 한 번에 불러오지 않고, 나눠서 불러오는 것입니다. df를 쓰려다가 이해하기 쉽게 sheet1과 sheet2로 데이터를 읽었습니다.

1
2
3
4
5
6
7
8
9
10

for i in range(len(sheet1)) : #시트를 분리했기 때문에 sheet1의 길이만큼 len()
    col_A = sheet1['매체'][i]
    col_D = sheet1['노출'][i]  
    col_E = sheet2[ (sheet2['매체명'] == col_A) & (sheet2['노출수'] == int(col_D)) ]['가격']
    
    if len(col_E) :
        sheet1['비용'][i] = col_E.values[0]
    
    else : 
        sheet1['비용'][i] = '비용 확인바람'
Colored by Color Scripter

cs

이전 코드와 달라진 부분이 뭘까요?

df에서 sheet1으로 바뀐 부분과 어떤건 sheet2로 바뀌었습니다. 차이가 무엇일까 살펴보면, 우리가 비교하려는 값은 sheet1에서 뽑아줍니다. 그리고 비교하려는 값으로 얻는 것은 sheet2에서 찾기 때문에 col_E (원하는 것 = 원하는 매체 노출수에 따른 '가격')는 sheet2에서 조건을 걸게 된 것입니다.

그리고 우리는 sheet1 '비용' 컬럼을 채워야하기 때문에 sheet1['비용'][i]에 sheet2에서 받아온 '가격'을 입력하게 됩니다. 애초에 두 시트의 컬럼명이 '가격'이었으면 덜 헷갈렸을 수도 있는데, 어떤 부분을 체크해야하는지 알려드리기 위해서 일부러 다르게 사용했습니다. 코드는 작업하기 편하게 사용하시면 됩니다.

데이터가 몇개 되지 않으면 INDEX/MATCH 함수나 VLOOKUP 함수를 엑셀에서 돌려도 시간이 얼마 안 걸립니다. 하지만 데이터가 많아지면 많아질수록 웬만한 사무용 컴퓨터에서는 엑셀이 버벅입니다. 조금이라도 도움이 되셨으면 좋겠고 다음에는 엑셀을 파이썬으로 구현할 때 사용했던 기능들에 대해서 소개하겠습니다.

'할 수 있다. 파이썬' 카테고리의 다른 글

(6) 사무 자동화 - 웹 검색 후 특정 부분 자동 캡쳐/ 스크린샷, 확대 축소 (0)	2022.01.24
(5) 효율 100% 엑셀 사무 자동화, 함수 및 기능들 총정리 (파이썬, 판다스) (0)	2022.01.23
(3) 실무 엑셀 함수 VLOOKUP, INDEX MATCH 시간 50배 단축, 파이썬으로 한 방에 잡자 (0)	2022.01.21
(2) 파이썬 엑셀 사무 자동화 : 보안 걸린 엑셀 한 번에 뚫기 openpyxl? xlwings? (2)	2022.01.20
(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신... (0)	2022.01.19

(3) 실무 엑셀 함수 VLOOKUP, INDEX MATCH 시간 50배 단축, 파이썬으로 한 방에 잡자

C.L.O.W.N 2022. 1. 21. 19:30

2022. 1. 21. 19:30

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

회사내 보안이 심하지 않으면 목차 1, 2번은 아마 안 보셨을거라 생각합니다. (~~운이 좋은 경우~~) 3편에서는 실무에서 TOP3 안에 드는 엑셀 함수 VLOOKUP 사용법, 유용한 함수들을 파이썬으로 구현하는 방법에 대해서 소개하고자 합니다. 엑셀을 제대로 사용해보지 않은 신입들도 브이룩업?이라고 많이들 들어보셨을텐데 실무에서 엄청 사용하는 것 같습니다.

솔직히 제목 어그로를 어떻게 끌까 하다가, 파이썬 엑셀 사무 자동화라는 타이틀은 빼버리고 필요한 키워드들만 넣었습니다. 단순 조회수를 위해서가 아닌, 저처럼 효율충들을 위한 글입니다. 한 번 사용해보세요 제발...

네이버에서 이런 캐릭터와 함께 오늘은 ~하는 방법 알아볼까요? 하고 특별한 정보도 없이, '그냥 그렇다고 하네요~' 로 끝나는 글들을 혐오해서 유용하지 않으면 욕하셔도 됩니다. 저도 사수 분이 엑셀을 VLOOKUP 사용법과 데이터 전처리하는 방법들을 알려주셨는데, 수작업이 너무 심하고... 데이터가 많다 보니 VLOOKUP이나 다중 조건 함수를 처리하는데 시간이 너무 오래 걸렸습니다. 결국 답답해서 파이썬으로 해결했습니다.

서론이 길었는데 엑셀에서 함수를 처리하는데 멀뚱멀뚱할 동안, 파이썬은 작업을 완료할만큼 속도도 빠르고 시간도 단축된다는 것.

데이터가 적으면 VLOOKUP 함수를 사용하면 얼마 안 걸린다. 하지만 조건이 하나 둘씩 붙어서 다중 조건이 되고, 데이터가 몇 천개 ~ 몇 만개 정도 되면 시간이 기하급수적으로 늘어난다. 다중 조건 INDEX MATCH 조합을 사용했을 때 컴퓨터가 느려서인지 시간이 정말 오래 걸렸다. 하지만 파이썬에서는 다중 조건을 여러번 사용했음에도 몇분도 안 돼서 작업이 완료 됐다.

키워드 : #다중조건, #여러시트, #여러값가져오기

VLOOKUP

이미 아시겠지만 VLOOKUP은 (비교값, 비교대상 범위, 결과를 가져올 열 번호, 옵션)을 활용하지만, 기준값의 우측 데이터만 가져올 수 있다. 하지만 좌측 데이터를 가져오려면 #N/A가 뜨면서 오류가 난다. 다중조건을 사용하기 쉽지 않아서, 많은 분들이 INDEX MATCH를 사용한다. 다른 시트에서 값을 찾으려면 범위 앞에 시트명!을 붙인다. EX) =VLOOKUP($A2, 시트명!$A:$C, 2, FALSE)

INDEX MATCH

INDEX 함수를 사용하여 특정 위치에 해당하는 값을 가져오고, MATCH를 사용해서 기준값이 어디에 위치하는지 정보를 얻을 수 있다. 실무에서 이 두 함수를 조합하는 것으로 보인다. 대부분 옵션은 0이나 FALSE가 들어가는데 정확히 일치한다는 뜻으로 쓰인다.

-INDEX (검색범위, 행번호, 열번호)

-MATCH (찾는 값, 검색범위, 옵션)

-MATCH (1, (첫번째 조건)*(두번째 조건), 옵션)

ㄴ 조건이 충족하면 1(TRUE) 아니면 0(FALSE), 곱해서 1이 되는 조건을 찾는다로 이해하시면 됩니다

*파이썬으로 진행되기 때문에 파이썬(3.7기준), 주피터노트북, pandas 모듈 필수 설치 (csv로 진행)

이걸 어떻게 설명을 할까 생각하다가 직접 보여주는게 좋을거 같아서, 임시로 데이터를 만들었습니다. 오해는 말아주세요. 그리고 파이썬에서 VLOOKUP과 INDEX MATCH 사용법이 비슷해서 INDEX MATCH 기준으로 설명드리겠습니다.

매체별 노출수에 따른 비용을 구하려고 할 때 엑셀 함수로는 =INDEX($J:$J,MATCH(1,($H:$H=$A2)*($I:$I=D2),0),1) 이런 식으로 구하면 별 문제 없이 5000, 7000, 30000, 11000이 입력이 될 것이다. 그렇다면 이것을 어떻게 파이썬으로 구현할까?

1. 파일 불러오기

1
2
3
4

import pandas as pd     # pandas가 없으면 터미널에서 pip install pandas
 
df =  pd.read_csv('위치/파일명.csv', encoding='utf-8-sig') # 직접 불러오기
df =  pd.read_clipboard(keep_default_na=False, sep="\t") # 복사해서 불러오기

cs

pandas 모듈을 불러옵니다. 직접 파일의 위치를 찾아서 불러와도 되고, 편하게 원하는 부분만 드래그 후 복사하는 두번째 방법대로 하시면 됩니다. (첫번째 행이 컬럼명이 됩니다 / df는 DataFrame의 약자, 다른 값으로 해도 됩니다)

엑셀 시트를 나눠서 '매체~비용' / '매체명~가격'으로 하셔도 되는데 우선은 시트를 나누지 않고 설명드리겠습니다.

2. 원하는 부분 가져오기

1
2
3
4

facebook = df['매체'][0]
exposure = df['노출'][0]
facebook, exposure
df[ (df['매체명'] == facebook) & (df['노출수'] == exposure) ]['가격'].values[0]

cs

=INDEX($J:$J,MATCH(1,($H:$H=$A2)*($I:$I=D2),0),1)

매칭을 시키자면, 우리가 필요로하는 A2의 값은 '매체명'컬럼에 0번째 인덱스인 '페이스북'을 facebook/ D2의 값은 '노출수'컬럼에 0번째 인덱스인 '100'을 exposure라고 칭하겠다는 소리입니다.

안쪽에 있는 df들은 MATCH의 조건들이라고 생각하시면 될 것 같고, ['가격']은 $J :$J라고 생각하시면 됩니다. df[( 조건1 ) & ( 조건2 ) ][ $J:$J ]으로 이해하면 편할 듯합니다. MATCH에서 $H:$H=$A2 이 부분은 df['매체명'] == facebook 입니다. H컬럼('매체명')에서 facebook인 애를 찾아줘. $I:$I=D2 이 부분은 df['매체명'] == exposure를 뜻합니다. I컬럼('노출수')에서 exposure를 찾아줘.

그리고 마지막 '.values[0]'은 구한 값을 가져오는 것입니다. (중복값 없이 값이 유일할 때 첫번째 값)

어느정도 이해가 되시나요? 페이스북 체류시간을 구하는 VLOOKUP 함수를 구현한다고 하면 어떻게 하면 될까요?

df[ df['이름'] == facebook ]['체류 시간'] => df [ ] 안에 조건을 하나만 넣으면 됩니다.

3. 값 대입하기

1
2

facebook_price = df[ (df['매체명'] == facebook) & (df['노출수'] == exposure) ]['가격'].values[0]
df['비용'][0] = facebook_price
Colored by Color Scripter

cs

방금 구한 값을 facebook_price라고 칭하고, '비용' 컬럼 0번째 인덱스에 집어넣겠다는 것입니다. 그런데 facebook_price 끝 부분 '.vlaues[0]'은 유의하셔서 보셔야 합니다. 만약에 매체가 페이스북이고 노출 400인 값이 들어왔을 때 비용을 구할 수 없기 때문에 #N/A 값이 생기는 것처럼 파이썬에서도 에러가 납니다.

그렇다면 이 에러를 어떻게 잡을 수 있을까요?

다음 편에 이어서 작성하겠습니다. 원래 여러 내용을 같이 작성하려고 하였으나, 이 게시물을 읽으실 분들은 아직 파이썬이 능숙하지 않으실 것 같아서 우선 위에 내용부터 이해하면 좋겠네요!

정리

VLOOKUP => df [ df['열 이름'] == A2] ['구하고자 하는 값이 있는 열 이름'].values[0]

INDEX/MATCH = > df [ ( df['열 이름1'] == A2 ) & ( df['열 이름2'] == B2 ) ] ['구하고자 하는 값이 있는 열 이름'].values[0]

조건이 추가 되면 df [ (기존 조건) & df['열 이름3'] == C3 ] 안에 &와 함께 조건을 추가하면 됩니다.

다음 글

- 반복해서 값을 적용하는 방법 (이걸 일일이 다 칠 수 없으니...)

- #N/A 값이 나올 때 예외 처리

- 다른 시트에서 값 확인

'할 수 있다. 파이썬' 카테고리의 다른 글

(5) 효율 100% 엑셀 사무 자동화, 함수 및 기능들 총정리 (파이썬, 판다스) (0)	2022.01.23
(4) 실무 엑셀 함수 TOP3 자동화 - VLOOKUP 다중조건, INDEX/MATCH 파이썬으로 (0)	2022.01.22
(2) 파이썬 엑셀 사무 자동화 : 보안 걸린 엑셀 한 번에 뚫기 openpyxl? xlwings? (2)	2022.01.20
(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신... (0)	2022.01.19
유튜브 크롤링(3) 올인원 - 채널 제목, 댓글, 조회수, 자막까지 (9)	2021.09.29

(2) 파이썬 엑셀 사무 자동화 : 보안 걸린 엑셀 한 번에 뚫기 openpyxl? xlwings?

C.L.O.W.N 2022. 1. 20. 19:30

2022. 1. 20. 19:30

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

1
2

import pandas as pd
df = pd.read_csv('111.csv')

cs

전 글에서 pip install 문제 때문에 ipykernel이 설치가 안 돼서 print(1)도 되지 않았다. 이후 문제를 해결하고 1이 출력 됐을 때의 기쁨은 엄청났다. 하지만 그 기쁨도 잠시뿐. 행복한 마음으로 csv 데이터 파일을 열었더니 오류가 이렇게나 길게 나왔다.

문제는 UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9b in position 32: invalid start byte 라고 한다. codec 문제라고 하는데, 예전에도 이런 문제와 부딪혀 봐서 금방 해결할거라 생각했다.

encodig = 'utf-8' / 'euc-kr' / 'cp949' / 'utf-8-sig' 이 중에 한 놈이라도 걸려라 했는데, 한 명도 걸리지 않았다. 그 어떤 것도 되지 않아서.. csv 파일이 문제라고 생각해서 xlsx 확장자를 열어봤는데

ValueError: Excel file format cannot be determined, you must specify an engine manually. 라는 에러 메시지가 출력됐다. 그래서 또 열심히 구글링을 하기 시작했는데 도움이 되지 않았다. 그래도 참고하실 분은 봐주세요.

PANDAS & glob - Excel file format cannot be determined, you must specify an engine manually

I am not sure why I am getting this error although sometimes my code works fine! Excel file format cannot be determined, you must specify an engine manually. Here below is my code with steps: 1- li...

stackoverflow.com

How to read SharePoint Online (Office365) Excel files in Python with Work or School Account?

I am a university student and I have registered as an Office 365 Education user via my university Email address. I usually log into https://www.office.com with my Email account: alice@abc.edu. The ...

stackoverflow.com

열심히 찾아봤는데 해결책은 의외로 간단했다.

openpyxl가 아닌, xlwings을 설치하면 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

import openpyxl
wb = openpyxl.load_workbook('파일명')
 
#sheet 열기
sheet = wb['sheet1'] 
 
#workbook 생성
wb = openpyxl.Workbook() # 기본 시트 생성됨 sheet1
sheet2 = wb.create_sheet('sheet2') #마지막에 추가
sheet3 = wb.create_sheet('sheet3', 1) #sheet1 자리에 삽입 하여 추가
 
#시트 이름 변경
sheet2.title = '업무자동화'
 
#저장
wb.save('./new_test_file.xlsx')
wb.close()
Colored by Color Scripter

cs

openpyxl 같은 경우 시트별로 불러와서 작업이 가능하지만, 애초에 회사 사내망 때문에 보안이 걸린 엑셀의 경우는 xlwings로만 불러 올 수 있었다. 하지만 xlwings를 사용하는 사람들이 많이 없어서 참고 자료를 찾기 어려워서 다른 방법을 선택했다.

1
2
3
4

import xlwings as xw
book = xw.Book('실습1.csv')
df = book.sheets(1).used_range.options(pd.DataFrame).value
df
Colored by Color Scripter

cs

~~자료는 회사와 관련이 없는 자료다~~. 이런 식으로 작업을 하면 바로 엑셀이 실행돼서 동시 작업이 된다. 엑셀 실행이 돼서 기뻤지만 작업을 하지 못 해서 다른 방법이 뭐가 있을까 찾아봤다.

사내망 엑셀파일에 걸린 자물쇠를 뚫고 코딩하기

엑셀 자동화 (1) - xlwings 라이브러리 시작하기

maeng-gun.github.io

그래도 혹시 궁금해하실 분들을 위해서 관련 링크 걸어놓습니다.

저는 xlwings 대신에 pd.read_clipboard()를 사용하였습니다. 이게 참 훌륭한 기능이라는 걸 깨달았네요. 그냥 가져오고자 하는 데이터를 드래그해서 복사하시면 dataframe이 만들어집니다. 자동으로 행, 열도 생성되고 이거 아니었으면 자동화는 힘들었을거 같네요 ㅠㅠ

그리고 이걸로 긁어와서 만든 파일의 경우 세션(?)이 끊기기 전까지는 csv로 불러서 읽을 수 있습니다! 다만 csv 파일을 열어서 수정을 하게 되면 다시 못 쓴다는 치명적인 단점이... 그래도 수작업을 할 시간에 코드로 자동화를 한다면 훨씬 더 편하니까 이렇게 삥삥 돌아서 갑니다.

다 같이 화이팅 합시다!

'할 수 있다. 파이썬' 카테고리의 다른 글

(4) 실무 엑셀 함수 TOP3 자동화 - VLOOKUP 다중조건, INDEX/MATCH 파이썬으로 (0)	2022.01.22
(3) 실무 엑셀 함수 VLOOKUP, INDEX MATCH 시간 50배 단축, 파이썬으로 한 방에 잡자 (0)	2022.01.21
(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신... (0)	2022.01.19
유튜브 크롤링(3) 올인원 - 채널 제목, 댓글, 조회수, 자막까지 (9)	2021.09.29
유튜브 크롤링(2) - ㄹㅇ 초간단 유튜브 자막 다운 & 추출 (문장분리까지) (1)	2021.09.28

(1) 파이썬 엑셀 사무 자동화 : 회사 사내망 때문에 좌절한 당신...

C.L.O.W.N 2022. 1. 19. 19:30

2022. 1. 19. 19:30

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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