데이터 엔지니어링 과정/python
[12일차] 엑셀 파일 다루기
오리는짹짹
2023. 1. 5. 17:17
목차
1. 엑셀 파일을 읽고 쓰기
2. 엑셀 파일 통합하기
3. 엑셀 파일로 읽어온 데이터 다루기
4. 엑셀 데이터의 시각화
1. 엑셀 파일을 읽고 쓰기
1. 엑셀 파일의 데이터 읽기
(1) pandas로 엑셀 파일 데이터 읽기
df = pd.read_excel('excel_file.xlsx'[, sheet_name = number 혹은 '시트이름'], index_col = number 혹은 '열이름')
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/학생시험성적.xlsx')
df
👀 엑셀 파일의 첫번째 시트를 불러온다!
(2) 두번째 시트 불러오기
- 특정 시트의 내용을 pandas를 이용해 읽어 오려면 sheet_name 옵션에 번호를 지정하거나,
시트이름을 입력해도 데이터를 읽어올 수 있다.
# 번호 지정
pd.read_excel('C:/myPyCode/data/학생시험성적.xlsx', sheet_name=1)# 시트이름 직접 입력
pd.read_excel('C:/myPyCode/data/학생시험성적.xlsx', sheet_name='2차시험')
(3) 인덱스 지정하기
- index_col = 번호나 '열이름' 지정
# 번호 지정
pd.read_excel('C:/myPyCode/data/학생시험성적.xlsx', sheet_name = '2차시험', index_col = 0)# 열이름 지정
pd.read_excel('C:/myPyCode/data/학생시험성적.xlsx', sheet_name = '2차시험', index_col = '학생')
➡ 학생이 index가 되었음을 확인할 수 있다.
2. 데이터를 엑셀 파일로 쓰기
(1) 엑셀 파일로 쓸 객체 생성
import pandas as pd
excel_exam_data1 = {'학생': ['A','B','C','D','E','F'],
'국어' : [80, 90, 95, 70, 75, 85],
'영어' : [90, 95, 70, 85, 90, 95],
'수학' : [85, 95, 75, 80, 85, 100]}
df1 = pd.DataFrame(excel_exam_data1, columns = ['학생', '국어', '영어', '수학'])
df1
(2) 데이터로 쓸 엑셀 시트 작성
- engine = 엔진이름 엑셀쓰기 엔진 지정
- to_excel 지정된 엑셀 시트에 DataFrame 데이터 작성
- index = 'False' index를 엑셀 시트의 데이터에 포함 x
'True' 사용시 index를 엑셀 시트의 데이터에 포함됨 - excel_writer.save() ExcelWriter 객체를 닫고, 지정된 엑셀 파일 생성
excel_writer = pd.ExcelWriter('C:/myPyCode/data/학생시험성적2.xlsx', engine='xlsxwriter')
df1.to_excel(excel_writer, index=False)
excel_writer.save() # 객체를 닫고, 지정된 엑셀 파일 생성(3) 시트 이름을 지정하여 저장하기
excel_writer2 = pd.ExcelWriter('C:/myPyCode/data/학생시험성적3.xlsx', engine='xlsxwriter')
df1.to_excel(excel_writer2, index=False, sheet_name = '중간고사')
excel_writer2.save()(4) 하나의 엑셀에 두 개의 시트 생성하기
import pandas as pd
excel_exam_data2 = {'학생': ['A','B','C','D','E','F'],
'국어' : [85, 95, 75, 80, 85, 100],
'영어' : [80, 90, 95, 70, 75, 85],
'수학' : [90, 95, 70, 85, 90, 95]}
df2 = pd.DataFrame(excel_exam_data2, columns = ['학생', '국어', '영어', '수학'])
df2
- 파일 저장
excel_writer3 = pd.ExcelWriter('C:/myPyCode/data/학생시험성적4.xlsx', engine='xlsxwriter')
df1.to_excel(excel_writer3, index=False, sheet_name = '중간고사')
df2.to_excel(excel_writer3, index=False, sheet_name = '기말고사')
excel_writer3.save()2. 엑셀 파일 통합하기
1. 효율적인 데이터 처리를 위한 엑셀 데이터 구조
- 열의 머리글 (header)은 한 줄로만 만들고 데이터는 그 아래에 입력한다.
- 열 머리글이나 데이터 입력 부분에 셀 병합 기능은 이용하지 않는다.
- 데이터를 입력할 때 하나의 셀에 숫자와 단위를 같이 쓰지 않는다.
- 하나의 열에 입력한 값의 데이터 혀식은 모두 일치해야 한다.
즉, 하나의 열에 문자열, 숫자, 날짜 등을 혼합해서 쓰지 않는다. - 데이터를 연도, 분기, 월, 업체별, 제품별 등의 시트로 나누지 않는다.
즉, 가능하면 모든 데이터를 하나의 시트에 다 넣는다.
2. 여러 개의 엑셀 파일 데이터를 통합하기
(1) 여러 개의 엑셀 파일 지정하기
excel_data_files = ['C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx',
'C:/myPyCode/data/담당자별_판매량_Becky사원.xlsx,',
'C:/myPyCode/data/담당자별_판매량_Chris사원.xlsx'](2) 데이터를 통합하기 위해 DataFrame 형태로 변수 생성
total_data = pd.DataFrame()(3) 한 번에 출력하기
import pandas as pd
for f in excel_data_files:
df = pd.read_excel(f)
total_data = total_data.append(df)
total_data
(4) ignore_index = True 파일 index 수정
import pandas as pd
for f in excel_data_files:
df = pd.read_excel(f)
total_data = total_data.append(df, ignore_index = True)
total_data
(5) 많은 파일을 통합할 때 내장 모듈 glob 함수 사용
- * 모든
? 한글자
import glob # 내장 모듈이기 때문에 별도로 import
glob.glob('C:/myPyCode/data/담당자별_판매량_*사원.xlsx')
>>> ['C:/myPyCode/data\\담당자별_판매량_Andy사원.xlsx',
'C:/myPyCode/data\\담당자별_판매량_Becky사원.xlsx',
'C:/myPyCode/data\\담당자별_판매량_Chris사원.xlsx']import glob
import pandas as pd
excel_data_files1 = glob.glob('C:/myPyCode/data/담당자별_판매량_*사원.xlsx')
total_data1 = pd.DataFrame()
for f in excel_data_files1:
df = pd.read_excel(f)
total_data1 = total_data1.append(df, ignore_index=True)
total_data1
3. 통합 결과를 엑셀 파일로 저장하기
import glob
import pandas as pd
excel_file_name = 'C:/myPyCode/data/담당자별_판매량_통합.xlsx'
excel_total_file_writer = pd.ExcelWriter(excel_file_name, engine = 'xlsxwriter')
total_data1.to_excel(excel_total_file_writer, index = False, sheet_name = "담당자별_판매량_통합")
excel_total_file_writer.save()
glob.glob(excel_file_name)
>>> ['C:/myPyCode/data/담당자별_판매량_통합.xlsx']
3. 엑셀 파일로 읽어온 데이터 다루기
1. 데이터를 추가하고 변경하기
(0) 엑셀 데이터 읽어오기
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx')
df
(1) 데이터 변경하기
df.loc[index_name, column_name] = value
df.loc[2, '4분기'] = 0
df
(2) loc[ , ] 데이터 추가하기
df.loc[3, '제품명'] = '벨트'
df.loc[3, '담당자'] = 'A'
df.loc[3, '지역'] = '가'
df.loc[3, '1분기'] = 100
df.loc[3, '2분기'] = 150
df.loc[3, '3분기'] = 200
df.loc[3, '4분기'] = 250
df
👀 loc[index_name, column_name] = value
를 통해서 추가와 변경이 모두 가능!!
(3) 특정열의 데이터 전체 변경
df[column_name] = value
df['담당자'] = 'Andy'
df
(4) 새로운 이름 저장하기
excel_file_name = 'C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx'
new_excel_file = pd.ExcelWriter(excel_file_name, engine = 'xlsxwriter')
df.to_excel(new_excel_file, index=False)
new_excel_file.save()
glob.glob(excel_file_name)
>>> ['C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx']2. 여러 개의 엑셀 파일에서 데이터 수정하기
(1) 쓰임
immport re
re.sub(patten, repl, string)
string에서 pattern을 찾아내서 대체문자열 (repl)로 교체
import re
file_name = 'C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx'
new_file_name = re.sub(".xlsx", "2.xlsx", file_name)
new_file_name
>>> 'C:/myPyCode/data/담당자별_판매량_Andy사원2.xlsx'(2) 여러 개의 엑셀 파일에서 값 변경 후 다른 이름으로 저장하기
import glob
import re
import pandas as pd
# 원하는 문자열이 포함된 파일을 검색해 리스트를 할당한다.
excel_data_files1 = glob.glob("C:/myPyCode/data/담당자별_판매량_*사원.xlsx")
# 리스트에 있는 엑셀 파일만큼 반복 수행한다.
for f in excel_data_files1:
# 엑셀 파일에서 DataFrame 형식으로 데이터 가져온다.
df = pd.read_excel(f)
# 특정 열의 값을 변경한다.
if (df.loc[1, '담당자'] == 'A'):
df['담당자'] = 'Andy'
elif (df.loc[2, '담당자'] == 'B'):
df['담당자'] = 'Becky'
else:
df['담당자'] = 'Chris'
# 엑셀 파일 이름에서 지정된 문자열 패턴을 찾아서 파일명을 변경한다.
f_new = re.sub(".xlsx", '2.xlsx', f)
print(f_new)
# 수정된 데이터를 새로운 이름의 엑셀 파일로 저장한다.
new_exxcel_file = pd.ExcelWriter(f_new, engine = 'xlsxwriter')
df.to_excel(new_excel_file, index=False)
new_excel_file.save()
>>> C:/myPyCode/data\담당자별_판매량_Andy사원2.xlsx
>>> C:/myPyCode/data\담당자별_판매량_Becky사원2.xlsx
>>> C:/myPyCode/data\담당자별_판매량_Chris사원2.xlsx(3) 실제 엑셀 파일이 생성되었는지 확인
glob.glob('C:/myPyCode/data/담당자별_판매량_*사원?.xlsx')
>>> ['C:/myPyCode/data\\담당자별_판매량_Andy사원2.xlsx',
'C:/myPyCode/data\\담당자별_판매량_Becky사원2.xlsx',
'C:/myPyCode/data\\담당자별_판매량_Chris사원2.xlsx']3. 엑셀의 필터 기능 수행하기
(1) 엑셀의 필터 기능을 이용하여 특정 부분만 선별하여 보기
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_통합.xlsx')
df
df['제품명']
>>> 0 시계
1 구두
2 핸드백
3 벨트
4 시계
5 구두
6 핸드백
7 시계
8 구두
9 핸드백
Name: 제품명, dtype: object(2) 비교 연산자로 '핸드백'이 있는 문자열이 있는지 검사
df['제품명'] == '핸드백'
>>> 0 False
1 False
2 True
3 False
4 False
5 False
6 True
7 False
8 False
9 True
Name: 제품명, dtype: bool(3) 해당 결과를 df 안에 넣으면 True에 해당하는 열만 출력할 수 있다.
handbag = df [df['제품명'] == '핸드백']
handbag
(4) .isin()을 이용하여 출력하기
DataFrame_data.isin(values)
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_통합.xlsx')
handbag1 = df[df['제품명'].isin(['핸드백'])]
handbag1
(5) 여러 개의 원하는 행을 추출하기
- 연산자 기호 사용
df[(df['제품명'] == '구두') | (df['제품명'] == '핸드백')]- isin 사용
df[df['제품명'].isin(['구두', '핸드백'])]
4. 조건을 설정해 원하는 행만 선택하기
(1) '3분기'의 판매량이 250 이상인 행만 추출
df[df['3분기'] >= 250]
(2) '제품명'이 '핸드백'이면서 '3분기'의 판매량이 350 이상인 행만 추출
df[(df['제품명'] == '핸드백') & (df['3분기'] >= 350)]
5. 원하는 열만 선택하기
(0) 엑셀 파일을 DataFrame 형식으로 읽어오기
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_Andy사원.xlsx')
df(1) 원하는 열의 헤더(header)를 리스트 형식으로 지정
df[['제품명', '1분기', '2분기', '3분기', '4분기']]
(2) 행과 열의 위치로 원하는 데이터 선택하기
DataFrame_data.iloc[row_num,col_num]
- 원하는 열만 선택한 경우
df.iloc[:, [0, 3, 4, 5, 6]]
- 행의 위치 지정해서 원하는 행만 선택
df.iloc[[0,2],:]
6. 엑셀 데이터 계산하기
(1) 행 데이터의 합계 구하기 핸드백의 지역별 연간 판매량 구하기
- 핸드백의 분기별 판매량 DataFrame으로 만들기
import pandas as pd
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_통합.xlsx')
handbag = df[df['제품명'] == '핸드백']
handbag
- DataFrame_data.sum([axis = 0(기본) or 1]) 합계 구하기
handbag.sum(axis=0)
>>> 제품명 핸드백핸드백핸드백
담당자 AndyBC
지역 가나다
1분기 1100
2분기 1039
3분기 1040
4분기 715
dtype: objecthandbag.sum(axis=1)
>>> 2 1056
6 1459
9 1379
dtype: int64
- 헤더가 '연간판매량'인 DataFrame 데이터 생성
handbag_sum = pd.DataFrame(handbag.sum(axis=1), columns = ['연간판매량'])
handbag_sum
- join()을 이용해 데이터를 통합해서 보여주기
handbag_total = handbag.join(handbag_sum)
handbag_total
- 정렬해서 보여주기
# 오름차순
handbag_total.sort_values(by='연간판매량', ascending=True)
# 내림차순
handbag_total.sort_values(by='연간판매량', ascending=False)
(2) 열 데이터의 합계 구하기 분기별 합계와 지역별 연간 판매량의 합계 모두 구하기
- sum()이용
handbag_total.sum()
>>> 제품명 핸드백핸드백핸드백
담당자 AndyBC
지역 가나다
1분기 1100
2분기 1039
3분기 1040
4분기 715
연간판매량 3894
dtype: object- DataFrame으로 생성
handbag_sum2 = pd.DataFrame(handbag_total.sum(),columns=['합계'])
handbag_sum2
- handbag_total에 handbag_sum2 세로방향으로 추가
handbag_total2 = handbag_total.append(handbag_sum2.T)
handbag_total2
- 필요없는 문자열 변경
handbag_total2.loc['합계', '제품명'] = '핸드백'
handbag_total2.loc['합계', '담당자'] = '전체'
handbag_total2.loc['합계', '지역'] = '전체'
handbag_total2
- 전체코드
import pandas as pd
# 엑셀 파일을 pandas의 DataFrame 형식으로 읽어온다.
df = pd.read_excel('C:/myPyCode/data/담당자별_판매량_통합.xlsx')
# 제품명 열에서 핸드백이 있는 행만 선택한다.
product_name = '핸드백'
handbag = df[df['제품명']==product_name]
# 행별로 합계를 구하고 마지막 열 다음에 추가한다.
handbag_sum = pd.DataFrame(handbag_total.sum(), columns = ['합계'])
handbag_total2 = handbag_total.append(handbag_sum2.T)
# 지정된 항목의 문자열을 변경한다.
handbag_total2.loc['합계', '제품명'] = product_name
handbag_total2.loc['합계', '담당자'] = '전체'
handbag_total2.loc['합계', '지역'] = '전체'
# 결과 확인
handbag_total2
4. 엑셀 데이터의 시각화
1. 그래프를 엑셀 파일에 넣기
(1) matplotlib와 pandas를 이용해 그래프 만들기
import matplotlib.pyplot as plt
import pandas as pd
sales = {'시간' : [9, 10, 11, 12,13, 14, 15],
'제품1' : [10, 15, 12, 11, 12, 14, 13],
'제품2' : [9, 11, 14, 12, 13, 10, 12]}
df = pd.DataFrame(sales, index = sales['시간'], columns = ['제품1', '제품2'])
df.index.name = '시간' #index 라벨 추가
df
(2) 그래프 만들기
import matplotlib
import pandas as pd
matplotlib.rcParams['font.family'] = 'Malgun Gothic' # '맑은 고딕'으로 설정
matplotlib.rcParams['axes.unicode_minus'] = False
product_plot = df.plot(grid = True, style = ['-*', '-o'], title='시간대별 생산량')
product_plot.set_ylabel("생산량")
image_file = 'C:/myPyCode/figures/fig_for_excel1.png' # 이미지 파일 경로 및 이름
plt.savefig(image_file, dpi = 400) # 그래프를 이미지 파일로 저장
plt.show()
(3) 엑셀 파일에 데이터와 이미지 파일 추가
import pandas as pd
# (1) pandasdml ExcelWriter 객체 생성
excel_file = 'C:/myPyCode/data/data_image_to_excel.xlsx'
excel_writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
# (2) DataFrame 데이터를 지정된 엑셀 시트 (Sheet)에 쓰기
df.to_excel(excel_writer, index=True, sheet_name = 'Sheet1')
# (3) ExcelWriter 객체에서 워크시트(worksheet) 객체 생성
worksheet = excel_writer.sheets['Sheet1']
# (4) 워크시트에 차트가 들어갈 위치를 지정해 이미지 넣기
worksheet.insert_image('D2', image_file, {'x_scale' : 0.7, 'y_scale' : 0.7})
# worksheet.insert_image(1,3, image_file, {'x_scale' : 0.7, 'y_scale' : 0.7})
# (5) ExcelWriter 객체를 닫고 엑셀 파일 출력
excel_writer.save()2. 엑셀 차트 만들기
(1) 엑셀에서 그릴 수 있는 파트 유형
(2) 엑셀 차트 생성
# (1) pandas의 Excelwriter 객체 생성
excel_chart = pd.ExcelWriter('C:/myPyCode/data/data_chart_in_excel.xlsx', engine = 'xlsxwriter')
# (2) DataFrame 데이터를 지정된 엑셀 시트 (Sheet)에 쓰기
df.to_excel(excel_chart, index = True, sheet_name = 'Sheet1')
# (3) ExcelWriter 객체에서 워크북 (workbook) 과 워크시트 (worksheet) 객체 생성
workbook = excel_chart.book
worksheet = excel_chart.sheets['Sheet1']
# (4) 차트 객체 생성 (원하는 차트의 종류 지정)
chart = workbook.add_chart({'type': 'line'})
# (5) 차트 생성을 위한 데이터 값의 범위 지정
chart.add_series({'values' : '=Sheet1!$B$2:$B$8'})
chart.add_series({'values' : '=Sheet1!$C$2:$C$8'})
# (6) 워크시트에 차트가 들어갈 위치를 지정해 차트 넣기
worksheet.insert_chart('D2', chart)
# (7) ExcelWriter 객체를 딛고 엑셀 파일 출력
excel_chart.save()(3) x축과 y축의 값과 원하는 범례 지정
# (5) 차트 생성을 위한 데이터 값의 범위 지정
chart.add_series({'values' : '=Sheet1!$B$2:$B$8',
'categories' : 'Sheet1!$A$2:$A$8',
'name' : 'Sheet1!$B$1'})
chart.add_series({'values' : '=Sheet1!$C$2:$C$8',
'categories' : 'Sheet1!$A$2:$A$8',
'name' : 'Sheet1!$C$1'})# (5-1) 엑셀 차트에 x,,y축 라벨과 제목 추가
chart.set_title({'name':'시간대별 생산량'})
chart.set_x_axis({'name' : '시간'})
chart.set_y_axis({'name' : '생산량'})(4) 전체 코드 보기
# (1) pandas의 Excelwriter 객체 생성
excel_chart = pd.ExcelWriter('C:/myPyCode/data/data_chart_in_excel2.xlsx', engine = 'xlsxwriter')
# (2) DataFrame 데이터를 지정된 엑셀 시트 (Sheet)에 쓰기
df.to_excel(excel_chart, index = True, sheet_name = 'Sheet1')
# (3) ExcelWriter 객체에서 워크북 (workbook) 과 워크시트 (worksheet) 객체 생성
workbook = excel_chart.book
worksheet = excel_chart.sheets['Sheet1']
# (4) 차트 객체 생성 (원하는 차트의 종류 지정)
chart = workbook.add_chart({'type': 'line'})
# (5) 차트 생성을 위한 데이터 값의 범위 지정
chart.add_series({'values' : '=Sheet1!$B$2:$B$8',
'categories' : 'Sheet1!$A$2:$A$8',
'name' : 'Sheet1!$B$1'})
chart.add_series({'values' : '=Sheet1!$C$2:$C$8',
'categories' : 'Sheet1!$A$2:$A$8',
'name' : 'Sheet1!$C$1'})
# (5-1) 엑셀 차트에 x,,y축 라벨과 제목 추가
chart.set_title({'name':'시간대별 생산량'})
chart.set_x_axis({'name' : '시간'})
chart.set_y_axis({'name' : '생산량'})
# (6) 워크시트에 차트가 들어갈 위치를 지정해 차트 넣기
worksheet.insert_chart('D2', chart)
# (7) ExcelWriter 객체를 딛고 엑셀 파일 출력
excel_chart.save()