📝 엑셀 파일 읽고 쓰기

1) 엑셀 파일 데이터 읽기

• pd.read_excel('file.xlsx' [, sheet_name=number or '시트명', index_col=number or '열이름']) - pandas에서 csv 파일 읽어오기
  - sheet_name= : 첫 번째 시트 외 다른 시트의 데이터 읽어오기 (시트의 순서 - 1)
  - index_col= : 인덱스로 지정

import pandas as pd

df = pd.read_excel('C:/data/엑셀파일.xlsx', sheet_name = 1, index_col = '첫 번째 열')
df

2) DataFrame 데이터를 엑셀 파일로 쓰기

• pd.ExcelWriter('file.xlsx', engine='xlswriter')
• .to_excel(DF_data [, index=T/F, sheet_name=''])
  - index : 기본값 True, index 포함하기
• 같은 이름의 파일 있을 시 자동 덮어쓰기

# ➀ pandas의 ExcelWriter 객체 생성
# ExcelWriter 객체 생성, 파일 이름과 엑셀 쓰기 엔진 지정
ew = pd.ExcelWriter('excel_output.xlsx', engine='xlswriter')

# ➁ DataFrame 데이터를 지정된 엑셀 시트에 쓰기
# 엑셀 시트에 DataFrame 데이터 쓰기, index 포함 여부 결정 및 시트 이름 설정
df1.to_excel(ew [, index=T/F, sheet_name='시트명1'])
df2.to_excel(ew [, index=T/F, sheet_name='시트명2'])

# ➂ EcxelWriter 객체를 닫고, 지정된 엑셀 파일 생성
ew.save()

📑 엑셀 파일 통합하기

효율적인 데이터 처리를 위한 엑셀 데이터 구조 (엑셀에서 데이터를 생성할 때 주의할 점)

• 열의 머리글은 한 줄로만 만들고 데이터는 그 아래에 입력한다.
• 열 머리글이나 데이터 입력 부분에 셀 병합 기능은 이용하지 않는다.
• 데이터를 입력할 때 하나의 셀에 숫자와 단위를 같이 쓰지 않는다.
• 하나의 열에 입력한 값의 데이터 형식은 모두 일치해야 한다. (하나의 열에 문자열, 숫자, 날짜 등 혼합 금지)
• 가능하면 모든 데이터를 하나의 시트에 다 넣는다. (데이터를 연도, 분기, 월, 업체별, 제품별 등의 시트로 나누지 않기)
  
  
  

1) 여러 개의 엑셀 파일 데이터 DataFrame 데이터로 통합하기

• for문 이용
• glob.glob('path_file_name') - 원하는 파일명 가져오기
• 파일 이름이 복잡할 경우, re 내장 모듈로 정규표현식 함께 이용

import glob
import pandas as pd

# 1) 불러오려는 엑셀 파일명 지정하기 (직접 입력 혹은 glob 이용)
excel_data_files = ['C:/data/파일1.xlsx', 'C:/data/파일2.xlsx', 'C:/data/파일3.xlsx']
excel_data_files = glob.glob('C:/data/*.xlsx')   # data 폴더의 모든 xlsx 파일

# 2) 데이터 통합을 위한 DataFrame 생성
total_data = pd.DataFrame()

# 3) 데이터 통합하기
# 파일별 index가 붙어 있으므로, 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)

2) 통합 결과를 엑셀 파일로 저장하기

import glob
import pandas as pd

excel_f_name = 'C:/data/total.xlsx'

etf_writer = pd.ExcelWriter(excel_f_name, engine='xlsxwriter')
total_data.to_excel(etf_writer, index=False, sheet_name='엑셀 시트명')
etf_writer.save()

glob.glob(excel_f_name)

📋 엑셀 파일로 읽어온 데이터 다루기

import pandas as pd

# 엑셀 파일을 pandas의 DataFrame 형식으로 읽어오기
df = pd.read_excel('file_name.xlsx')

# 'A' 열에서 'a'가 있는 행만 선택하기
a = df[(df['A']=='a')]

# 행별로 합계 구하고, 마지막 열 다음에 추가하기
df_sum1 = pd.DataFrame(df.sum(axis=1), columns=['가로축'])
df_total1 = df.join(df_sum1)

# 열별로 합하고, 마지막 행 다음에 추가하기
df_sum2 = pd.DataFrame(df_total1.sum(), columns=['세로축'])
df_total2 = df_total1.append(df_sum2.T)

# 지정된 항목의 문자열 변경
df_total2.loc['세로축', 'A'] = 'a'

# 결과 확인
df_total2

1) 데이터 추가하기, 변경하기

엑셀 파일에서 읽어온 DataFrame 데이터에 새로운 값 추가하기, 기존의 값 변경하기

• df.loc[index_name, column_name] = value - 추가 or 변경
• df[column_name] = value - 특정 열의 데이터값 전체 변경

import pandas as pd

# 데이터 수정/변경
df = pd.read_excel('file_name.xlsx')
df.loc[5, 'E'] = 0   # index 라벨 이름이 2이고 columns 라벨 이름이 'E'인 셀의 값을 0으로 추가
df['B'] = 'hi'       # B 행의 모든 셀을 hi로 변경

# 다른 이름으로 저장하기
excel_file_name = 'C:/data/new_file.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)

2) 여러 개의 엑셀 파일에서 데이터 수정하기

• re.sub(pattern, repl, string) - string에서 pattern을 찾아서 문자열 repl로 대체

import re

file_name = 'C:/data/file.xlsx'

new_fname = re.sub('.xlsx', '2.xlsx', file_name)
# C:/data/file2.xlsx

import glob
import re
import pandas as pd

# 원하는 문자열이 포함된 파일을 검색해 리스트에 할당
excel_files = glob.glob('C:/data/*.xlsx')

# 리스트에 있는 엑셀 파일만큼 반복 수행
for f in excel_files :
	# DataFrame 형식으로 데이터 가져오기
    df = pd.read_excel(f)  
    
    # 특정 열의 값 변경
    if(df.loc[1, '담당자'] == 'A') :
    	df['담당자'] = 'Any'
    elif(df.loc[1, '담당자'] == 'B') :
    	df['담당자'] = 'Ben'

    # 엑셀 파일 이름에서 지정된 문자열 패턴을 찾아 파일명 변경
    f_new = re.sub('.xlsx', '2.xlsx', f)
    
    # 수정된 데이터를 새로운 이름의 엑셀 파일로 저장하기
    new_excel_file = pd.ExcelWriter(f_new, engine='xlsxwriter')
    df.to_excel(new_excel_file, index=False)
    new_excel_file.save()

3) 엑셀의 필터 기능

• df['열이름'] - 원하는 열의 데이터 추출 (index, 열)
• df['열이름'] == value - 해당 열에 value가 있는지 검사 (index, T/F)
• df.isin(values) - 원하는 문자열 찾기 (T/F)
• df['열이름'] 조건 - 원하는 행만 선택하기
• df.iloc(row_num, col_num) - 원하는 열만 선택하기
  - row_num : 행, col_num : 열, : 입력 시 전체 지정, [리스트] 형식으로 부분 지정
• 변수에 할당 후 그 변수를 호출하면, 엑셀 내용을 추출한다.
• 논리곱(and, %), 논리합(or, |), 논리 부정(not, ~) 이용 가능

import pandas as pd

df = pd.read_excel('C:/data/file.xlsx')

# 원하는 내용 찾기 (엑셀의 '필터' 기능)
a = df['A']
b = df[df['A'] == 'a']   # A열에서 a 문자열과 일치하는 값 추출
c = df[df['A'].isin(['a'])]   # b와 동일
d = df[df['A'].isin(['a', 'b'])]   # A열에서 셀이 a이거나 b인 행 추출
e = df[(df['A']=='a') & (df['A']=='b')]   # d와 동일

# 조건을 설정해 원하는 행만 선택하기
df(df['A'] >= 20)   # 'A'의 값이 20 이상인 행만 추출

# 조건을 설정해 원하는 열만 선택하기 (엑셀의 '숨기기' 기능)
df[['원하는', '열의', '헤더만', '리스트에', '지정']]
or
df.iloc(row_num, col_num)
df.iloc(:, [0, 3, 4])   # 모든 행 선택, [0, 3, 4] 열 선택

4) 엑셀 데이터 계산하기

• DataFrame_data.sum([axis = 0(기본) or 1])
• 0 : 열 방향 합계, 1 : 행 방향 합계

import pandas as pd

df = pd.read_excel('C:/data/file.xlsx')

df.sum(axis=1)   # 단순 값 계산
df_sum = pd.DataFrame(df.sum(axis=1), columns=['sum값'])   # 계산한 값을 담은 데이터 생성

df_total = df.join(df_sum)   # df 파일에 계산한 값 df_sum 추가

• DataFrame_data.sort_values(by [axis=0, ascending=True]) - 데이터 오름차순/내림차순 정렬
  - axis : 0 (열 방향 기준, 기준값) or 1 (행 방향 기준)
  - ascending : True (오름차순, 기본값) or False (내림차순)

df_total.sort_values(by='sum값', ascending=Ture)
# 'sum값' 열을 기준으로 오름차순 정렬

📉 엑셀 데이터의 시각화

1) 그래프를 엑셀 파일에 넣기

# 1) pandas의 ExcelWriter 객체 생성
excel_writer = pd.ExcelWriter('excel_output.xlsx', engine='xlsxwriter')

# 2) DataFrame 데이터를 지정된 엑셀 시트에 쓰기
df.to_excel(excel_writer, index=T/F, sheet_name='시트명')

# 3) ExcelWriter 객체에서 워크시트 객체 생성
# '시트명'은 2)의 sheet_name과 동일해야 한다.
worksheet = excel_write.sheets['시트명']

# 4) 워크시트에 차트가 들어갈 위치 지정해 이미지 넣기
# 이미지 좌측 상단이 위치할 셀의 위치, 경로를 포함한 이미지 파일명 지정
# 셀의 위치 : dict 변수 지정 or 0숫자로 행과 열 지정 가능
# x_scale, y_scale 지정 시 이미지으 ㅣ가로 세로 배율 조절 가능 (기준값 1)
worksheet.insert_image('셀 위치', image_file [, {'x_scale': x_scale_num, 'y_scale': y_scale_num}])
worksheet.insert_image(row_num, col_num, image_file [, {'x_scale': x_scale_num, 'y_scale': y_scale_num}])

# 5) ExcelWriter 객체 닫고, 엑셀 파일 출력
excel_writer.save()

# 1. graph 생성
import matplotlib.pyplot as plt
import pandas as pd

sales = {'time': [9, 10, 11, 12], 'product1': [10, 15, 12, 15], 'product2': [9, 1,, 14, 12]}

df = DataFrame(sales, index=sales['time'], columns=['product1', 'product2'])
df.index.name = 'time'   # index 라벨 추가

product_plot = df.plot(grid=True, style = ['-*', '-o'], tile='time and Q')
product_plot.set_ylabel('Q')

image_file = 'C:/data/fig_for_excel.png'
plt.savefig(imge_file, dpi=400)

plt.show()


# 2. 데이터와 이미지를 엑셀 파일에 넣기
excel_file = 'C:/data/to_excel.png'
excel_writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')

df.to_excel(excel_writer, index=T/F, sheet_name='Sheet1')

worksheet = excel_write.sheets['Sheet1']

worksheet.insert_image('D2', image_file, {'x_scale': 0.7, 'y_scale': 0.7}])
or
worksheet.insert_image(1, 3, image_file, {''x_scale': 0.7, 'y_scale': 0.7}])

excel_writer.save()

2) 엑셀 차트 만들기

# 1) pandas의 ExcelWriter 객체 생성
excel_writer = pd.ExcelWriter('excel_output.xlsx', engine='xlsxwriter')

# 2) DataFrame 데이터를 지정된 엑셀 시트에 쓰기
df.to_excel(excel_writer, index=T/F, sheet_name='시트명')

# 3) ExcelWriter 객체에서 워크북과 워크시트 객체 생성
# '시트명'은 2)의 sheet_name과 동일해야 한다.
wortbook = excel_write.book
worksheet = excel_write.sheets['시트명']

# 4) 차트 객체 생성 (원하는 차트의 종류 지정)
# type : area(영역형), bar(가로 막대), column(세로 막대), line(꺾은 선형), pie(원형), doughnut(도넛형), scatter(분산형), stock(주식형), radar(방사형)
chart = workbook.add_chart({'type': '차트유형'})

# 5) 차트를 생성하기 위한 데이터값의 범위 지정
chart.add_series({'values': values_range})

# +) 차트에 라벨, 제목 추가
chart.set_title({'name': '차트 제목'})
chart.set_x_axis({'name': '가로축'})
chart.set_y_axis({'name': '세로축'})

# 6) 워크시트에 차트가 들어갈 위치 지정해 차트 넣기
worksheet.insert_chart('셀 위치', chart)
or
worksheet.insert_chart(row_num, col_num, chart)

# 7) ExcelWriter 객체 닫고, 엑셀 파일 출력
excel_writer.save()

excel_chart = pd.ExcelWriter('chart_in_excel.xlsx', engine='xlsxwriter')

df.to_excel(excel_chart, index=True, sheet_name='Sheet1')

wortbook = excel_chart.book
worksheet = excel_chart.sheets['Sheet1']

chart = workbook.add_chart({'type': 'line'})

chart.add_series({'values': '=Sheet1!$B$2:$B$8', 'categories': '=Sheet1!$A$2:$A$8', 'name': '=Sheet1!$B$1'})

chart.set_title({'name': 'time and Q'})
chart.set_x_axis({'name': 'time'})
chart.set_y_axis({'name': 'Q'})

worksheet.insert_chart('D2', chart)
or
worksheet.insert_chart(1, 3, chart)

excel_chart.save()