다양한 데이터 저장/읽기 - 엑셀(xlsx) 파일

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.
잔재미코딩 소식 공유
좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 사이트와, 다음 두 채널도 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다
●  잔재미코딩 뉴스레터 오픈 [구독해보기]
●  잔재미코딩 유투브 오픈 [구독해보기]

3. 다양한 데이터 저장/읽기 - 엑셀(xlsx) 파일

3.1. 다양한 데이터 읽기1 - xlsx 파일

  • pandas 라이브러리 활용
    • read_excel() 메서드 활용
In [43]:
import pandas as pd
doc = pd.read_excel("03_data/sample.xlsx")
doc
Out[43]:
Unnamed: 0 타이틀 댓글수
0 NaN 미국→한국 데이터 트랙픽 지연시간, 10분의 1로 단축 493
1 NaN 완전자급제 하면 단말기 가격 떨어질까 526
2 NaN 삼성전자 ‘밀크’, 3년 7개월 만에 역사 속으로 982
3 NaN “어! 화면에 그라데이션이…” LG V30 ‘한지액정’논란 1135
4 NaN 미군, 최신예 원자력 잠수함에 MS '엑스박스 컨트롤러' 채택 992
5 NaN 군사·보안 시설 훤히 보여주는 국토부 3D 지도 서비스 518
6 NaN 아스텔앤컨, ‘AK70 MK II’ 국내 출시 831
7 NaN 구글, HTC 픽셀팀만 인수한 이유는 607
8 NaN 차별·비하 웹 1위 ‘일베’…“유해매체 지정해야” 588
9 NaN 중국 러에코, 한국시장 조용히 진출…내년 초 스마트폰 들여와 295
10 NaN 2분기 플렉시블 OLED 매출 2조3천억원…전년비 132% 급증 177
11 NaN 애플 '아이폰X' 공개 후 시총 50조 증발 447
12 NaN “암실서 12시간씩 테스트”···DSLR 뺨치는 LG ‘V30 카메라’ 개발 뒷얘기 1104
13 NaN V30·노트8의 요란한 격돌, 잠잠한 시장 1571
14 NaN 아이폰8 출시…예전같은 열기는 없었다 1686
15 NaN LG전자 'QLED' 상표 출원 거절은 적법..법원 "특정인 독점 안돼" 1180
16 NaN LG V30, 성공 열쇠는 번인 극복 1308
17 NaN LG "V30 카메라 오작동률 0%…불량 없다" 1313
18 NaN 구글 어시스턴트, 한국말도 한다…V30에 첫 적용 528
19 NaN LG 야심작 'V30', 잇따른 호평 속 예판 실적 '관심 집중' 1173
20 NaN 구글-HTC, 11억불 규모 협력계약…인력·지재권 ↔ 현금 423
21 NaN 가까이 두기만 해도…무선 충전기 ‘파이’ 1531
22 NaN 이통3사, 유심칩 독점 매출액 8700억 1439
23 NaN 방통위, 단말기 지원금 상한제 10월부터 폐지 683
24 NaN 소니 무선헤드셋 3종 21일 시판 1046
In [44]:
# 특정한 쉬트만 읽기

import pandas as pd
doc = pd.read_excel("03_data/sample.xlsx", sheetname="Sheet2")
doc
Out[44]:
Unnamed: 0 타이틀 댓글수
0 NaN 미국→한국 데이터 트랙픽 지연시간, 10분의 1로 단축 493

3.2. 다양한 데이터 읽기/저장2 - xlsx 파일

  • openpyxl 라이브러리 활용
    • xlsx 파일 읽고, 저장 모두 가능
    • 설치
      • pip install openpyxl
In [ ]:
import openpyxl
 
# 엑셀파일 열기
excel_file = openpyxl.load_workbook('03_data/sample.xlsx')
 
excel_sheet = excel_file.get_sheet_by_name("Sheet1")

for row in excel_sheet.rows:
    print(row[0].row)
    print(row[1].value)
    print(row[2].value)
    
    excel_sheet.cell(row=row[0].row, column=3).value = 10

# 엑셀 파일 저장
excel_file.save("03_data/sample2.xlsx")
excel_file.close()
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다
In [ ]:
import openpyxl
 
# 엑셀파일 열기
excel_file = openpyxl.load_workbook('03_data/sample.xlsx')
 
excel_sheet = excel_file.get_sheet_by_name("Sheet1")
total = 0
count = 0
for row in excel_sheet.rows:
    if count > 1:
        print(row[0].row)
        total = total + int(row[2].value)
    count += 1
print(count)
excel_sheet.cell(row=count+1, column=3).value = total

excel_file.save("03_data/sample3.xlsx")
excel_file.close()
In [ ]:
import openpyxl
 
# 엑셀파일 열기
excel_file = openpyxl.load_workbook('03_data/sample.xlsx')
 
excel_sheet = excel_file.get_sheet_by_name("Sheet1")
total = 0
count = 0
In [ ]:
for row in excel_sheet.rows:
    if count > 1:
        print(row[0].row)
        total = total + int(row[2].value)
    count += 1
In [ ]:
print(total)
excel_sheet.cell(row=count+1, column=3).value = total
In [ ]:
excel_file.save("03_data/sample3.xlsx")
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다
연습문제: openpyxl 라이브러리 다루기
03_data/sample.xlsx 파일의 Sheet1 에 댓글수 총 합계를 댓글수 마지막 셀에 넣어서 sample03.xlsx 파일로 저장하기

3.3. 다양한 데이터 저장3 - xlsx 파일 (엑셀을 셀단위로 조작하여 엑셀 파일 제대로 만들기)

  • xlsxwriter 라이브러리를 활용
    • xlsx 엑셀 파일 (최신 엑셀 포멧) 작업 가능
    • 셀단위로 세부적인 조작이 가능함
    • 설치
      • pip install xlsxwriter
    • 사용법
      • xlsx 엑셀 파일 생성
      • 엑셀 파일 내 쉬트 생성
      • 셀 단위 길이 설정
      • 셀 단위 폰트 굵기, 정렬, 셀배경색, 폰트색, 셀외곽선종류 설정
      • 셀 단위 데이터 입력
In [54]:
''' 
다음 기능을 모두 구현한 예제 
 - xlsx 엑셀 파일 생성
 - 엑셀 파일 내 쉬트 생성
 - 셀 단위 길이 설정
 - 셀 단위 폰트 굵기, 정렬, 셀배경색, 폰트색, 셀외곽선종류 설정
 - 셀 단위 데이터 입력
'''

import xlsxwriter

title_list = ["웹기술", "빅데이터기술", "IoT", "DataScience", "adfdf", "dfadfadf", "ddd"]
hit_count_list = [2000, 10000, 2000, 2000, 4000, 5000, 6000, 4040]

report_excel = xlsxwriter.Workbook('report.xlsx')
report_sheet1 = report_excel.add_worksheet('report1')
report_sheet2 = report_excel.add_worksheet('report2')

report_sheet1.set_column(0, 0, 5)
report_sheet1.set_column(1, 1, 80)

report_sheet2.set_column(0, 0, 5)
report_sheet2.set_column(1, 1, 80)

cell_format = report_excel.add_format({'bold': True, 'align': 'center', 'fg_color': '#FFC107', 'color': 'blue', 'border': 10})
report_sheet1.write(1, 1, '타이틀', cell_format)
report_sheet1.write(1, 2, '클릭수', cell_format)

report_sheet2.write(1, 1, '타이틀', cell_format)
report_sheet2.write(1, 2, '클릭수', cell_format)

cell_format_gray = report_excel.add_format({'fg_color': '#ECEFF1', 'border': 1})
cell_format_white = report_excel.add_format({'fg_color': 'white', 'border': 1})

for num in range(len(title_list)):
    report_sheet1.write(num + 2, 1, title_list[num], cell_format_gray)
    report_sheet1.write(num + 2, 2, hit_count_list[num], cell_format_white)

for num in range(len(title_list)):
    report_sheet2.write(num + 2, 1, title_list[num], cell_format_gray)
    report_sheet2.write(num + 2, 2, hit_count_list[num], cell_format_white)
    
report_excel.close()