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

목차 (작성 예정)

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

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

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

(4) 실무 엑셀 함수 응용

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

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

오랜만에 글을 썼는데.. 사무자동화 마지막 글이네요. 우선 생각했던건 여기까지고 추가로 필요한 부분 있으면 제가 또 만들지 않을까.. 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() 
 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
 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('완료')
    
 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
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

 

 

 

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
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

목차 (작성 예정)

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

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

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

(4) 실무 엑셀 함수 응용

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

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

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
 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]
 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] = '비용 확인바람'
 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] = '비용 확인바람'
 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

+ Recent posts

티스토리툴바