[파이썬] 엑셀과 연결하여 위키피디아 크롤링하기 [야매코딩]

[파이썬] 엑셀과 연결하여 위키피디아 크롤링하기 [야매코딩]

안녕하세요

모든것을 공유하자

모공입니다.

오늘은 제가 일하면서 필요해서 만든 코딩을 공유해보려 합니다.

핵심은

엑셀과 연결해서,

엑셀에 있는 데이터를 받아 위키피디아에 접속,

위키피디아에서 원하는 정보 체크하기,

다시 엑셀에 정리하는

야매코딩이라는 것입니다.

야매코딩이라고 부르는 이유는 제가 코딩을 제대로 공부해본 적 없기 때문입니다.

구글링해서 제가 필요한 부분만 긁어와서 조립한 코딩입니다.

어쨋든 저는 작동하는데 성공했고, 참고하실 분은 참고해주세요.

참고로 저는 윈도우 10 기반입니다.

그럼 좀 더 구체적으로 제가 한 코딩에 대해 설명하자면,

저는 한국의 모든 고등학교를 '사립'과 '공립'으로 나누는 코딩을 원했습니다.

이렇게 엑셀파일을 열어 미리 조사해둔 전국의 고등학교 이름을 가져와

해당 학교의 위키피디아에 접속해서

해당 학교가 공립인지 사립인지 알아내어

엑셀파일의 원하는 위치에 넣어 정리, 저장하는 코딩을 원했습니다.

그리고 아래가 결과물입니다.

--------------------------------------------------------------------------------------------------------------------------------

1. 코딩 작업을 위한 환경 준비

우선 파이썬은 다들 깔려있을거라 생각합니다.

만약 아직 설치를 하시지 않으셨다면

https://tutorial.djangogirls.org/ko/python_installation/

여기를 참고해주세요

설치가 완료되면

크롤링에 필요한 BeautifulSoup4 를 설치합니다.

http://shaeod.tistory.com/900

여기를 참고해주시면 됩니다.

2. 이제 코딩을 해봅시다.

우선 전체 코딩을 보여드리겠습니다.

import openpyxl
import requests
from bs4 import BeautifulSoup

filename = "학교 리스트.xlsx"
book = openpyxl.load_workbook(filename)
sheet = book.worksheets[0]
for a in range(1, 17):
    data = []
    for row in sheet.rows:
        data.append(
            row[2*a-1].value
            )
    del data[0]
    del data[0]
    del data[0]

    k=4
    blank = sheet.cell(row=1,column=1).value

    for i in data:
            if i != blank: #엑셀의 셀 값이 빈값일 아닐 경우에만 작동
                newstr = str(i)
                response = requests.get('https://ko.wikipedia.org/wiki/' + newstr) #리스트 학교 위키피디아에 검색
                html = response.text

                soup = BeautifulSoup(html, 'html.parser')

                table = soup.find('table', attrs = {'class':'infobox'})

                if table is not None:
                    if "사립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "사립"
                        k = k+1
                        print(newstr + "는 사립학교입니다.")
                    elif "공립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "공립"
                        k = k+1
                        print(newstr + "는 공립학교입니다.")
                    else:
                        sheet.cell(row=k,column=2*a+1).value = "학교에 대한 정보가 없습니다."
                        k = k+1
                        print(newstr + "데이터가 없습니다")
                else:
                    sheet.cell(row=k,column=2*a+1).value = "위키피디아 페이지 문제"
                    k = k+1
                    print(newstr + "의 위키피디아 페이지에 문제가 있습니다.")
book.save("학교리스트매크로.xlsx")
book.close()

하나 하나 설명하겠습니다.

우선 첫번째로 엑셀에 연결하여 데이터를 가져오는 작업입니다.

import openpyxl  #파이썬과 엑셀을 연결하기 위해 필요한 함수를 불러오는 코딩입니다.
filename = "학교 리스트.xlsx" #제가 만든 엑셀 파일을 열기 위해 파일명을 선언합니다. 여러분이 미리 만들어둔 엑셀 파일 명을 적으면 됩니다.
book = openpyxl.load_workbook(filename)  #실질적으로 엑셀을 여는 부분입니다.
sheet = book.worksheets[0] #엑셀을 열고, 맨 첫 번째 시트를 가져옵니다. 두 번째 시트에서 작업하실꺼면 1, 세 번째는 2 라고 입력하시면 됩니다.
for a in range(1, 17): #작업을 할 배열 범위입니다.
    data = [] #학교리스트를 담을 저장소를 선언합니다.
    for row in sheet.rows: #엑셀에서 열을 가져오는 부분입니다.
        data.append(
            row[2*a-1].value
            )
    del data[0] #쓸모없는 부분을 지우는 부분입니다.
    del data[0] #쓸모없는 부분을 지우는 부분입니다.
    del data[0] #쓸모없는 부분을 지우는 부분입니다.

우선 a의 범위를 1부터 17까지 정한 부분입니다.

a가 1일때는 무엇을 의미할까요?

data.append( row[2*a-1].value )

 가

data.append( row[1].value )

가 된다는 의미이지요.

for row in sheet.rows: #엑셀에서 열을 가져오는 부분입니다. data.append( row[2*a-1].value )

이 코딩은

엑셀에서 B열에 있는 값들을 data에 넣으란 의미입니다.

(참고로 row[0] 이 A열에 관한 값들입니다.)

즉 B1,B2,B3,B4... 의 값들인

"" <아무것도 없으니 빈값

표시된 곳이 어쩌고

서울특별시

강서고등학교

.

.

.

이것들이 data = ["","표시된 곳이 어쩌고","서울특별시","강서고등학교"....] 이렇게 저장되는 것이지요.

여기 있는 값들을 통해서 위키피디아에 주소를 입력해 해당 학교의 위키피디아에 접속할 것입니다.

그러면 앞에 빈값과 "표시된 어쩌고", "서울특별시"는 필요없는 값이지요?

그걸 지우기 위해

del data[0]

를 3번 썼습니다.

그리고 row[2*a-1] 에서 2*a-1 의 의미는 홀수의 의미입니다.

a를 1부터 16까지 대입시켜 보면

1, 3, 5, 7, 9, 11, 13, .... 31 입니다.

왜 16까지 대입시키냐면

제가 정리한 학교 리스트의 열이 31까지 있기 때문입니다.

즉 엑셀에서 A열부터 AF열까지 한칸씩 건너뛰면서 데이터를 가져오기 위한 코드는

for a in range(1, 17): #작업을 할 배열 범위입니다. data = [] #학교리스트를 담을 저장소를 선언합니다. for row in sheet.rows: #엑셀에서 열을 가져오는 부분입니다. data.append( row[2*a-1].value )

이렇게 됩니다.

for 범위에 17까지 적는게 맞습니다. 17미만까지라는 의미라서 16이라고 입력하면 29열인 AD까지만 데이터가 뽑힙니다.

이제 데이터는 가져왔습니다.

하지만 여기까지 해서 코딩을 돌려보면 이상하게 나옵니다.

data에 불필요한 빈값들이 대거 담깁니다.

이유는 각 열마다 행의 길이가 다르기 때문입니다.

for row in sheet.rows:

 이란 코딩은 제가 원문을 찾아보진 않았지만 한번 써보니

값이 입력되어 있는데 까지 for을 돌리는게 아니라

활성화된 셀까지 for을 돌리는것같습니다.

즉 B열이 74열까지 활성화되어 있으니까 대전광역시가 있는 F열도 74열까지 활성화 되어 있는 걸로 인식하는듯 싶습니다.

여기서 저는 야매코딩을 썼습니다.

(다른 아주 좋은 아이디어가 있으시분은 다른 방법을 쓰는 것도 좋은 방법입니다.

코딩이란게 정해진 답은 없고 효율적으로 돌아가기만 하면 되니까요 ㅋㅋ)

저는 data에 None 즉 빈값이 들어가게 놔두었습니다.

대신 나중에 이 데이터에서 자료를 꺼낼때 None 앞의 자료까지 꺼내는 겁니다.

즉, 여기서는 '대전여자상업고등학교'까지만 작업을 하고 None은 data에서 꺼내지 않고 바로 다음 작업으로 넘어가게 코딩하는 겁니다.

그러기 위해 None 값을 선언합니다. 저는 sheet.cell(row=1,column=1).value 을 이용했습니다.

blank = sheet.cell(row=0,column=0).value

제 엑셀에서 1행 A열은 빈값입니다.

그리고 if 구문을 돌려서 data가  blank 랑 값이 같지 않을 때만 작업을 돌리면 됩니다.

자, 다음으로 위키피디아에 연결하는 방법입니다.

for i in data: #data에 있는 값들을 차례대로 꺼냅니다. 그 값을 i로 선언합니다.
            if i != blank: #엑셀의 셀 값이 빈값(None)이 아닐 경우에만 작동
                newstr = str(i) #데이터의 형태가 배열이라서 str로 바꿔줍니다.
                response = requests.get('https://ko.wikipedia.org/wiki/' + newstr) #리스트 학교 위키피디아에 검색
                html = response.text #크롤링하기 위한 작업입니다.

                soup = BeautifulSoup(html, 'html.parser') #크롤링하기 위한 작업입니다.

                table = soup.find('table', attrs = {'class':'infobox'}) #공립, 사립의 데이터가 있는 table을 찾아줍니다.
                #밑에 코딩들은 밑에서 설명하겠습니다.
                if table is not None:
                    if "사립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "사립"
                        k = k+1 
                        print(newstr + "는 사립학교입니다.")
                    elif "공립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "공립"
                        k = k+1
                        print(newstr + "는 공립학교입니다.")
                    else:
                        sheet.cell(row=k,column=2*a+1).value = "학교에 대한 정보가 없습니다."
                        k = k+1
                        print(newstr + "데이터가 없습니다")
                else:
                    sheet.cell(row=k,column=2*a+1).value = "위키피디아 페이지 문제"
                    k = k+1
                    print(newstr + "의 위키피디아 페이지에 문제가 있습니다.")

이제 data엔 우리가 원하는 학교이름들이 저장되어 있습니다.

하나씩 꺼내서 해당 학교의 위키피디아에 접속해봅시다.

for i in data: #data에 있는 값들을 차례대로 꺼냅니다. 그 값을 i로 선언합니다. if i != blank: #엑셀의 셀 값이 빈값(None)이 아닐 경우에만 작동 newstr = str(i) # i의 데이터의 형태가 배열이라서 str로 바꿔줍니다. response = requests.get('https://ko.wikipedia.org/wiki/' + newstr) #리스트 학교 위키피디아에 검색

딱히 설명할 부분은 없습니다. #에 있는 설명이 전부입니다.

위키피디아에 접속하는 방법은 간단합니다.

https://ko.wikipedia.org/wiki/

여기 뒤에다 학교 이름만 적으면 됩니다.

이렇게 말이죠

그런데 간혹 이런 경우가 있습니다.

전국에 같은 이름으로 된 학교가 2개 이상 있을 경우 이렇게 나옵니다.

제가 직접 해본 결과 이러한 케이스가 10개 정도 있었습니다.

저는 그냥 예외처리만 하고 별다른 작업은 안했습니다.

이정도면 나중에 수작업으로 하는게 훨씬 시간 절약이 되니까요.

else: sheet.cell(row=k,column=2*a+1).value = "위키피디아 페이지 문제" k = k+1 print(newstr + "의 위키피디아 페이지에 문제가 있습니다.")

제가 한 예외 처리입니다.

(코딩 하나하나가 이해가 안 가시더라도 일단 넘깁시다.)

자, 이제 사이트가 비정상적인 곳에 접속됐을 때, 예외처리는 했고,

정상적으로 들어가졌을 때, 사립, 공립을 가져오는 방법입니다.

위키피디아에서 원하는 정보 빼내기

학교 위키피디아에 사립, 공립 표기는 저 테이블 안에 다 있습니다.

저 테이블의 이름은 table 의 infobox 입니다.

이 안에 "사립" 이라는 데이터가 있는가 "공립"이라는 데이터가 있는가 조사하면 됩니다.

table = soup.find('table', attrs = {'class':'infobox'}) #공립, 사립의 데이터가 있는 table을 찾아줍니다.

if "사립" in table.text:

이렇게 간단한 형태로 체크할 수 있습니다.

참 간단하죠?

제가 한 작업은 원하는 정보를 체크만 하는 거고 실제 정보를 가져오는 것도 간단합니다.(체크하는 것보단 코딩이 길어지지만)

이건 다른 포스팅을 참고해주세요

다시 엑셀 정리하기

아까 적은 if 문안에 원하는 동작을 코딩하면 됩니다.

if "사립" in table.text: sheet.cell(row=k,column=2*a+1).value = "사립" k = k+1 print(newstr + "는 사립학교입니다.") elif "공립" in table.text: sheet.cell(row=k,column=2*a+1).value = "공립" k = k+1 print(newstr + "는 공립학교입니다.") else: sheet.cell(row=k,column=2*a+1).value = "학교에 대한 정보가 없습니다." k = k+1 print(newstr + "데이터가 없습니다")

sheet.cell(row=k,column=2*a+1).value = "사립"

위의 코딩은 원하는 셀에 등호(=) 다음에 있는 값을 넣어줍니다.

(아까 배운 blank = sheet.cell(row=k,column=2*a+1).value 과는 반대로 씁니다.)

여기서 row 는

1행

2행

3행

.

.

을 의미합니다.

column 은 열을 의미합니다.

A열

B열

C열

이죠.

데이터를 다시 집어 넣을 곳은 맨 처음에 데이터를 뽑아온 열, 바로 옆에 있는 열입니다.

C열

E열

G열...

이렇게 집어 넣을 예정이죠.

근데 아까랑 다른게 하나 있습니다.

맨 처음 row[2*a-1] 기억나시나요?

a 가 1 일 때

row[1]

이고

sheet.cell(row=k, column=3).value

이 됩니다. (저 안에 있는 row는 다른 row입니다.)

바로 옆이라면 column이 2가 되어야 되는데 이상하죠?

하지만 저게 정상입니다.

row는 배열이기 때문에 0 부터 시작하고

column는 셀 위치이기 때문에 1 부터 시작합니다.

row[0] 이 A열이고

column=1 이 A열입니다.

자 이제

sheet.cell(row=k, column=3).value

안에 있는 k 값입니다.

이 값은 4부터 시작해야 합니다.

x 친 부분은 필요없는 부분이죠

우리가 작업한 data는 강서고등학교 부터 시작합니다.

그러니까 처음을 k=4 라고 잡아야 합니다.

그리고 다음 학교로 넘어갔을 때

k = 5 (경기기계공업고등학교)

k = 6 (광운전자공업고등학교)

k는 하나씩 증가해야합니다.

if "사립" in table.text: sheet.cell(row=k,column=2*a+1).value = "사립" k = k+1 print(newstr + "는 사립학교입니다.")

print 는 그냥 제가 코딩이 잘 돌아가나 확인하고 싶어서 넣었습니다.

불필요하시면 빼시면 됩니다.

이렇게 계속 작업과정을 확인할 수 있죠.

자 이제 k = 4 라는 코딩을 집어넣을 위치입니다.

원하는 코드들을 수집해도 제대로 조립하지 않으면, 코딩이 잘 작동하질 않죠.

다시 처음부터 순서에 따라 설명하겠습니다.

제가 만든 코딩은

1. B열에 있는 값들을 data라는 배열에 집어넣었습니다.

2. 불필요한 값들을 없앴습니다. (빈값, 표시된 어쩌고, 서울특별시)

3. data의 값들을 하나씩 꺼냅니다.

4. data에서 꺼낸 값이 빈값(파란색 값 / blank = sheet.cell(row=1, column=1).value)이 아니라면 위키피디아에 접속, 원하는 정보(table infobox)를 체크합니다.

5. 이제 원하는 정보를 해당 학교 옆에 입력합니다. (sheet.cell(row=k,column=2*a+1).value = "사립")

6. 이 과정을 data에 있는 모든 값들이 수행하게 합니다.(for i in data:)

7. 여기까지 했으면 B열에 있는 학교 리스트 작업이 끝난겁니다.

8. 이 과정을 다음 열들도 반복합니다.(for a in range(1, 17):)

(이 때 data는 새로 정의되고, 값들도 새로 채워집니다.)

따라서 k 는 8번에 관한 코딩 바로 밑에 있어야 합니다.

(k도 한 열의 작업이 끝날 때 마다 새로 정의되어야 합니다.)

import openpyxl
import requests
from bs4 import BeautifulSoup

filename = "학교 리스트.xlsx"
book = openpyxl.load_workbook(filename)
sheet = book.worksheets[0]
for a in range(1, 17):
    data = []
    for row in sheet.rows:
        data.append(
            row[2*a-1].value
            )
    del data[0]
    del data[0]
    del data[0]

    k=4
    blank = sheet.cell(row=1,column=1).value

    for i in data:
            if i != blank: #엑셀의 셀 값이 빈값일 아닐 경우에만 작동
                newstr = str(i)
                response = requests.get('https://ko.wikipedia.org/wiki/' + newstr) #리스트 학교 위키피디아에 검색
                html = response.text

                soup = BeautifulSoup(html, 'html.parser')

                table = soup.find('table', attrs = {'class':'infobox'})

                if table is not None:
                    if "사립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "사립"
                        k = k+1
                        print(newstr + "는 사립학교입니다.")
                    elif "공립" in table.text:
                        sheet.cell(row=k,column=2*a+1).value = "공립"
                        k = k+1
                        print(newstr + "는 공립학교입니다.")
                    else:
                        sheet.cell(row=k,column=2*a+1).value = "학교에 대한 정보가 없습니다."
                        k = k+1
                        print(newstr + "데이터가 없습니다")
                else:
                    sheet.cell(row=k,column=2*a+1).value = "위키피디아 페이지 문제"
                    k = k+1
                    print(newstr + "의 위키피디아 페이지에 문제가 있습니다.")
book.save("학교리스트매크로.xlsx")
book.close()

자 이제 마지막입니다.

book.save("학교리스트매크로.xlsx") #새로운 정보를 입력한 엑셀을 새 이름으로 저장합니다. book.close() #열었던 엑셀을 종료합니다.

끝입니다.

이상

모든것을 공유하자

모공이였습니다

이 공유가 유익했다면 좋아요! 부족했다면 댓글로 피드백을 남겨주세요!

블로그 운영에 큰 힘이 된답니다 ^^