python으로 엑셀파일 열고 쓰기(with pandas)

이 코드는 직접 작성이 아닌 코드 분석 입니다.

 

이번 시간에는 pandas 모듈을 사용하여 엑셀 파일을 열고 쓰기로 했다.

pandas는 전에 썼던 모듈인 openpyxl 보다 간단하다.

1. pandas 모듈 설치

먼저 pandas 모듈을 설치하자

pandas 모듈을 설치하기위해 환경설정에 들어가야 한다.

ctrl+alt+s(환경설정) -> project -> project interpreter에 들어가면

나는 이미 pandas를 설치했기 때문에 현재 설치된 모듈에 pandas가 있지만 아마 pandas를 설치하지 않은 사람은 pandas가 없을 것이다.

이제 여기서 + 버튼을 누르고 검색창에 pandas라 검색하면

여기서 install package를 누르면 pandas 모듈이 설치된다.

이때 pandas 외에도 numpy 등 여러 가지 모듈이 더 깔리기 때문에 시간이 조금 소요된다.

 

설치가 완료되면 pandas를 사용할 수 있다.

 

2. 엑셀 파일 open 하기

이제 pandas를 이용해서 엑셀 파일을 열어보자

pd.read_excel 함수는 다음과 같은 input 값이 필요하다.

pd.read_excel('파일명',sheet_name = '시트명')

이를 이용하여 코드를 작성해보면

import pandas as pd

df = pd.read_excel('C:/Users/박상근/Desktop/port.xlsx',sheet_name='handsome_geuncheol')

a=list(df.columns)
print(a)

코드를 해석하면 port.xlsx 파일을 열어(pd.read_excel) 'handsome_geuncheol'이라는 시트의 값을 읽어 온 다음에 

a에다가 읽어온 엑셀(df)의 열(가로)의 값을 a에 저장한다. 

 

다음과 같이 코드를 작성하고 코드를 실행해보면

다음과 같은 에러가 발생하는데 이 에러들은 xlrd 모듈이 설치되어 있지 않아 생긴 에러다.

xlrd 모듈 설치 방식은 pandas 모듈 설치 방식과 똑같다.

검색창에 xlrd를 검색한 후 install을 해준다.

설치된 모듈 창을 확인해보면 다음과 같이 xlrd가 설치되어 있는 것을 확인할 수 있다.

 

xlrd 모듈을 설치한 후 다시 코드를 실행해보면

열의 값들을 출력해주는 것을 확인하였다.

 

3.  칼럼 번호 검색창 만들기

print ("""

################################

#데이터를 검색할 컬럼번호를 입력해주세요.

#

# 1. 내부IP

# 2. 내부IP 뒷 세자리

# 3. 외부포트

# 4. 내부포트

# 5. 룰 이름

################################

""")

col_num = int(input ("검색할 컬럼 번호[1~5] : "))

다음으로는 검색창을 만들었다.

검색창은 다음 코드와 같이 작성하였고 이제 값을 받기 위한 코드를 작성하였다.

사용자에게 검색을 희망하는 번호를 받을 변수를 만들고(input) 그 값을 col_num이라는 변수에 저장하였다.

※ print 문에서 "가 3개 쓰인 이유 : 여러 줄을 한 번에 print 하기 위해

col_num = int(input ("검색할 컬럼 번호[1~5] : "))

col_num -= 1

search_col = a[col_num]

print ("선택한 컬럼 : " + search_col)

4. 검색 희망 값 저장하기

다음으로 search_col이라는 변수를 만들어 해당 열에 있는 값만 검색하기로 한다.

col_num에서 -1을 하는 이유는 엑셀에서 값을 받아올 때 0번째부터 시작하기 때문이다.

그 후 -1을 한 값을 search_col이라는 변수를 만들어 col_num의 열에 있는 값만 저장해둔다.

내가 말을 잘 못해서 결과로 보여주면

다음과 같이 나온다.

 

5. 검색 희망 값 저장하기

이제 검색할 내용을 저장하는 변수를 만들어야 한다.

search_str = input ("검색할 문자를 입력해주세요 : ")

다음과 같이 검색할 문자를 저장할 'search_str'라는 변수를 만들어 검색한 값을 저장받는다.

 

6. 사용자의 검색 희망 값 추출하기

이제 사용자가 검색을 희망하는 열의 검색 값과 같은 값을 찾는 작업을 한다.

match_col = []

for index, data in enumerate(df[search_col]):
    if (search_str == str(data)):
        match_col.append(index)
del df[a[1]]

우선 match_col이라는 리스트를 만들어 만약 일치하는 경우 해당 행의 번호를 저장을 하도록 한다.

그 후 사용자가 검색을 희망하는 열을 상대로 for문을 돌면서(for index, data in enumerate(df [search_col]) :) 

만약 찾고 싶어 하는 값과 해당 열에 있는 값이 동일하게 되면(if search_str == str(data))

해당 행 번호를 match_col 리스트에 저장을 한다.(match_col.append(index))

여기서 enumerate라는 함수는 리스트가 있는 경우 순서와 리스트의 값을 전달해주는 함수인데 주로 for문하고 같이 쓰인다.

그리고 엑셀에서 값을 읽어올 때 첫 번째 로우는 칼럼 네임이라 읽어오지 않고 2번째 로우부터 읽어 오기 때문에 2번째 로우부터 for 문이 돈다.

그렇기 때문에 만약 3번째(외부 포트)에서 1521의 값을 찾으면 index 값은 0이 된다.

 

7. 두 번째 열(내부 IP 뒷 세 자리) 삭제하기

del df[a[1]]

우리는 '내부 뒷자리 IP 뒷 세 자리' column은 출력을 하지 않기로 하였기 때문에 del 함수를 사용하여 내부 리스트에 1번째 값은 삭제하였다.

 

8.  검색 희망 값과 일치하는 행의 값 추출하기

new_datas = df.iloc[match_col]

iloc 함수를 이용하여 new_datas 리스트에 match_col 행에 값을 저장한다.

만약 match_col이 0이라면 

밑줄 친 값을 new_datas에 저장하게 된다.

※ iloc 함수와 loc 함수

속성	설명
iloc	행 번호를 기준으로 행 데이터 읽기
loc	인덱스 기준으로 행 데이터 읽기

 

9. 새로운 엑셀 파일을 열어 읽어온 값 저장 후 저장하기

filename = input ("저장할 파일이름을 입력해주세요 : ")

new_datas.to_excel('./'+filename+'.xlsx', sheet_name='handsome_geuncheol')

저장할 파일명을 filename 변수로 받아 온다. 

그 후 to_excel 함수를 이용하여 new_datas 리스트에 저장되어 있는 리스트 값을 새로운 엑셀을 열어 값을 집어넣은 후 현재 위치에 'filename.xlsx'라는 엑셀 파일로 저장을 한다.

 

10. 프로그램 실행

이제 프로그램을 실행을 해보면

다음과 같이 성공적으로 실행이 되었다고 뜬다.

만약 다음과 같은 에러코드가 뜨는 경우 openpyxl 모듈을 설치해주면 해결이 된다.

 

11. 프로그램 실행 결과

제대로 엑셀에 값이 들어갔는지 확인해보기 위해 practice.xlsx 파일을 열어 보았다.

다음과 같이 제대로 저장이 되어 있다.