pandas 종합 아래한글 with pd.ExcelWriter('결과.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer: 일부시트 수정

https://www.youtube.com/watch?v=e7KSXuwuvSU&t=52s 

 

pip install faker

from faker import Faker
import random
import pandas as pd

https://wikidocs.net/105448

120 테스트용 데이터를 생성하려면? ― faker

### 단축키 마크다운창 m 코드창 y
### 단축키 셀병합 셀아래로 병합 shift + m
### 단축키 셀분리 ctrl + shift + '-'

fake = Faker('ko-KR')
성명_list = [fake.name() for i in range(100)]

수험번호_list = ["2023-"+str(i+1).zfill(3) for i in range(100)]

학위과정 = ['학사','석사','박사']
학위과정_list = [random.choice(학위과정) for i in range(100)]
학과 = ['경영학과','경제학과','무역학과','회계학과','부동산학과']
학과_list = [random.choice(학과) for i in range(100)]

df = pd.DataFrame({'수험번호' : 수험번호_list,
                   '전형' : 학위과정_list,
                   '학과' : 학과_list,
                   '성명' : 성명_list
                  })
df

df["학과"] # 0     무역학과
df["학과"] == "회계학과" # 0     False
df[df["학과"] == "회계학과"] # 	1	2023-002	박사	회계학과	서순옥
df[(df["학과"] == "회계학과") & (df["전형"] == "석사")] # 6	2023-007	석사	회계학과	차정자

# 표-표/셀 속성-필드이름 ← 변수명 Ctrl + n + k 수험번호1
# 첫번째 셀에 hello world 입력하면

hwp.GetFieldText("수험번호1")
hwp.PutFieldText("수험번호1","2023-999")

# 입력-개체-필드입력 Ctrl + k + e

df["학과"].unique() # array(['무역학과', '회계학과', '경영학과', '경제학과', '부동산학과'], dtype=object)
df["학과"].nunique() # 5
df["전형"].unique() # array(['석사', '박사', '학사'], dtype=object)
df["전형"].nunique() # 3
df["전형"] + df["학과"] # 0     석사무역학과
(df["전형"] + df["학과"]).unique()
# array(['석사무역학과', '박사회계학과', '박사무역학과', '학사무역학과', '석사회계학과', '박사경영학과',
       '학사회계학과', '학사경제학과', '석사부동산학과', '박사경제학과', '석사경제학과', '학사경영학과',
       '박사부동산학과', '학사부동산학과', '석사경영학과'], dtype=object)
(df["전형"] + df["학과"]).nunique() # 15

hwp.HAction.Run("CopyPage")
hwp.HAction.Run("PastePage")
# 19장 복사
for _ in range(19):
    hwp.HAction.Run("PastePage")

df_ = df[["전형","학과"]] # 0	석사	무역학과
df_

df_ = df.groupby(["전형","학과"]).size()
df_
# 	전형	학과	0
0	박사	경영학과	9

df_ = df.groupby(["전형","학과"]).size().reset_index().drop(0, axis=1) # 행 0 열 1
df_
# 전형	학과
0	박사	경영학과

df_.columns # Index(['전형', '학과'], dtype='object')

for i in range(len(df_)):
    전형, 학과 = df_.iloc[i]
    print(전형,학과)
   
df_.iloc[0]
# 전형      박사
학과    경영학과
Name: 0, dtype: object

df_.iloc[0]["전형"] # '박사'
df_.iloc[0]["학과"] # '경영학과'

hwp.PutFieldText(필드명, 데이터)
hwp.PutFieldText("지원과정", "") # 지원과정은 공백
hwp.PutFieldText(f"지원과정{{1}}", "박사") # 두 번째 페이지에만 출력
hwp.PutFieldText(f"지원과정{{{{{1}}}}}", "박사") # 두 번째 페이지에만 출력

i = 1
print(f"지원과정{{{{{i}}}}}") # 지원과정{{1}}

for i in range(len(df_)):
    전형, 학과 = df_.iloc[i]
#     hwp.PutFieldText(f"지원과정{{{{{i}}}}}", 전형)
#     hwp.PutFieldText(f"학과{{{{{i}}}}}", 학과)
#     hwp.PutFieldText(f"지원과정{{{{{i}}}}}\x02학과{{{{{i}}}}}\x02", "\x02".join[전형,학과)

# 전형과 학과 필드에 해당하는 학생들을 추려서
# 표 안에 입력해주기
# hwp.GetFieldText("지원과정{{0}}")
# hwp.GetFieldText("학과{{0}}")

df_ # 0	박사	경영학과

df_[["전형", "학과"]] # 0	박사	경영학과

for i in range(len(df_)):
    전형 = hwp.GetFieldText(f"지원과정{{{{{i}}}}}")
    학과 = hwp.GetFieldText(f"학과{{{{{i}}}}}")
    data = df[(df["전형"] == 전형) & (df["학과"] == 학과)]
    for j in range(1, len(data)+1):
        hwp.PutFieldText(f"수험번호{j}{{{{{i}}}}}", data["수험번호"].iloc[j-1])
        hwp.PutFieldText(f"성명{j}{{{{{i}}}}}", data["성명"].iloc[j-1])
        
for i in range(len(df_)):
#     전형 = hwp.GetFieldText(f"지원과정{{{{{i}}}}}")
#     학과 = hwp.GetFieldText(f"학과{{{{{i}}}}}")
    전형, 학과 = df_.iloc[i]
    print(f"====={전형}/{학과}=====")
    data = df[(df["전형"] == 전형) & (df["학과"] == 학과)]
    for j in range(1, len(data)+1):
        print("수험번호 : " + data["수험번호"].iloc[j-1], "성명 : " + data["성명"].iloc[j-1])
#         hwp.PutFieldText(f"수험번호{j}{{{{{i}}}}}", data["수험번호"].iloc[j-1])
#         hwp.PutFieldText(f"성명{j}{{{{{i}}}}}", data["성명"].iloc[j-1])

https://www.youtube.com/watch?v=w7Q_eKN5r-I&t=32s 

엑셀에서 짝수 번째 행을 전부 삭제하는 기능

import pandas as pd

df = pd.read_excel('data.xlsx')
df[::2].to_excel('even.xlsx') # 짝수행

df[1::2].to_excel('odd.xlsx') # 홀수행

	엑셀 설치 ×	엑셀 설치 ○
xlsx 지원	OpenPyXL XlsxWriter	xlwings pywin32
xls 지원	xlrd,xlwt

import openpyxl

wb = openpyxl.load_workbook('2017년 광고비 - 삼성전자.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet

sheet['A2'].value
sheet['C1'].value = 43700

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

muti_cells = sheet['E2':'F14']
muti_cells

# 모든 row 살펴보기
for row in sheet.rows:
    print([col.value for col in row)

# 데이터 병합

import pandas as pd

df_삼성전자 = pd.read_excel('2017년 광고비 - 삼성전자.xlsx')
df_삼성전자.set_index('date', inplace=True)

df_LG전자 = pd.read_excel('2017년 광고비 - LG전자.xlsx')
df_LG전자.set_index('date', inplace=True)

df_merge = pd.DataFrame()

df_merge['삼성전자'] = df_삼성전자['total']
df_merge['LG전자'] = df_LG전자['total']

date	삼성전자	LG전자
2017-01	합계액	합계액
2017-02	합계액	합계액

# 합계 계산
sum_삼성전자 = sum([row[0].value for row in sheet['B2':'B13']])
sheet['B14'].value = sum_삼성전자

sum_LG전자 = sum([row[0].value for row in sheet['C2':'C13']])
sheet['C14'].value = sum_LG전자

date	삼성전자	LG전자
2017-01	합계액	합계액
...	...	...
2017-12	합계액	합계액
합계	누계계산액	누계계산액

# 합계 계산
sheet['B14'].value = '=SUM(B2:B13)'
sheet['C14'].value = '=SUM(C2:C13)'

# 스타일 지정
from openpyxl.styles import Font, Alignment
from openpyxl.styles import Border, Side, Color, PattenFill

# Font
font_15 = Font(name='맑은 고딕', size=15, bold=True)

# Alignment: 가로 세로, 가운데 정렬
align_center = Alignment(horizontal='center', vertical='center')
align_vcenter = Alignment(vertical='center')

# Border: 테두리 지정
border_thin = Border(
    left = Side(style='thin'), right = Side(style='thin'),
    top = Side(style='thin'), bottom = Side(style='thin'))
    
# PatternFill: 셀 색상 지정
fill_orage = PatternFill(patternType='solid', fgColor=Color('FFC000'))
fill_lightgrey = PatternFill(patternType='solid', fgColor=Color('D3D3D3'))
cell_sum = sheet['A14'] # 합계 제목 셀

cell_sum.font = font_15
cell_sum.alignment = align_center
cell_sum.border = border_thin
cell_sum.fill = fill_orange

for row in sheet['B2:C14']:
    for cell in row:
        cell.border = border_thin
        cell.number_format = '0.00'
        
for row in sheet['B14:C14']:
    for cell in row:
        cell.alignment = align_vcenter
        cell.fill = fill_orange

# 차트 추가 하기
from openpyxl.chat import Reference, Series, BarChart

chart = BarChart()
chart.title = '2017년 월별 광고비 (억원)'

vals = Reference(sheet, range_string='Sheet1!B1:B13')
series = Series(vals, title="삼성전자")
chart.append(series)

vals = Reference(sheet, range_string='Sheet1!C1:C13')
series = Series(vals, title="LG전자")
chart.append(series)

sheet.add_chart(chart, 'E1')

데이터 준비

종목코드	종목명	PER	PBR	주당배당금	배당수익률

# 데이터 준비
import pandas as pd

df_팩터 = pd.read_excel('종목별 팩터 데이터.xlsx', dtype={'종목코드':str})
df_팩터 = df_팩터.set_index('종목코드')
df_팩터

# 필터링
df_팩터[df_팩터['PER']<=10]

# 정렬
# 배당수익률을 내림차순으로 정렬
df = df_팩터[df_팩터['PER']<=10].sort_values('배당수익률', ascending=False)
df

# VLOOKUP
import pandas as pd
# import openpyxl
# 원본 테이블
df_data = pd.read_excel("excel_vlookup.xlsx", sheet_name = 'sh데이터')
# vlookup 조회하기
df_search = pd.read_excel("excel_vlookup.xlsx", sheet_name = 'sh조회')
# VLOOKUP을 수행할 기준 열을 선택합니다.
key_column = '품명'
# VLOOKUP을 수행할 열을 선택합니다.
lookup_column = '품번'
lookup_column2 = '단가'
# VLOOKUP을 수행하고 결과를 새로운 열에 저장합니다.
merged_df = pd.merge(df_search, df_data[[key_column, lookup_column]], on=key_column, how='left')
result_df = pd.merge(merged_df, df_data[[key_column, lookup_column2]], on=key_column, how='left')
# merged_df.rename(columns={lookup_column: 'VLOOKUP 결과'}, inplace=True)
# 결과를 엑셀 파일로 저장합니다.
with pd.ExcelWriter('결과.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    result_df.to_excel(writer, sheet_name = 'sh조회', index=False)

 

파일명: excel_vlookup

sh데이터

sh조회

import pandas as pd

df = pd.read_excel("excel_vlookup.xlsx", sheet_name = 'sh조회')
df_data = pd.read_excel("excel_vlookup.xlsx", sheet_name = 'sh데이터')

df_count = df.groupby(["품명"]).size().reset_index(name="수량")

# VLOOKUP을 수행할 기준 열을 선택합니다.
key_column = '품명'
# VLOOKUP을 수행할 열을 선택합니다.
lookup_column = "수량"
df_result = pd.merge(df_data, df_count[[key_column, lookup_column]], on=key_column, how='left')
df_result

 

# 엑셀파일로 저장
import pandas as pd

writer = pd.ExcelWriter("관심팩터병합.xlsx", engine='openpyxl')
df_관심종목.to_excel(writer, sheet_name='Sheet1')

wb = writer.book
sheet = wb.sheets['Sheet1']

# 컬럼 너비 지정
for col in list('BCDE'):
    sheet.column_dimensions[col].width = 14
    
# 테두리, 숫자 포맷 지정
for row in sheet[['B2:B5']:
    for cell in row:
        cell.border = border_then
        cell.namber_format = '0.00'
        
# 헤더 색상 지정
for row in sheet['A1:E1']:
    for cell in row:
    	cell.fill = fill_lightgrey

write.save()

 

# 피벗 테이블
import pandas as pd

df = pd.read_excel('광고비 데이터 3사.xlsx, dtype = {'종목코드':str})
table = pd.pivot_table(df, values='total', index='date', columns='name')
table

# 엑셀에서는 약간 까다로운 작업
# 특정 주기로 데이터 추출 pd.date_range()

# 매주 월요일
inx = pd.date_range('2017-01-01', '2017-12-31', freq='W-MON')

# 매월 말일
inx = pd.date_range('2017-01-01', '2017-12-31, freq='M')

# 매분기 첫 영업일
inx = pd.date_range('2017-01-01', '2017-12-31, freq='BQS')

# 매월 마지막 영업일
inx = pd.date_range('2017-01-01', '2017-12-31', freq='BM')

df_x = pd.DataFrame(index=inx)
df_x['종가'] = df_01['종가']
df_x.head(10)

import pandas as pd

df = pd.DataFrame({
'name': ['밀','헴','스','헴','밀','밀','헴','밀','밀','스','헴','타','파','헴'],
'sale': [1,3,4,2,1,5,3,2,10,2,4,6,3,1]
})

# 그룹 순번
df['temp'] = 1
df['seq'] = df.groupby(['name']).temp.apply(lambda x: x.cumsum())
df.drop(columns=['temp'],inplace=True)

# 그룹 합계
df['groupsum'] = df.groupby('name')['sale'].apply(lambda x: x.cumsum())

df[['name','sale','groupsum']]

# df['seq'] = df['seq'].astype(str)
# df['conb'] = df['name']+df['seq']

# pandas
import pandas as pd

file_path = "07_24_05_P.xlsx"
df = pd.read_excel(file_path,sheet_name=None) # 시트 2개 sheet_name=None 모든시트
# sheet_name=None 리턴값 dictionary
df.keys() # key

#두개의 시트를 합쳐 하나의 데이터프레임으로 합치기
df = pd.concat(df,ignore_index=True) # index 새로 부여
df.head()

df.shape
df.shape[0] # 행수
df.shape[1] # 열수

df.columns # 리턴 object string

#column 이름 확인
for col in df.columns: 
    print(col)
    
#사업장명 데이터만 출력
print(df['사업장명'])    

df['사업장명'].str.contains('스타벅스') # 리턴 True False

#스타벅스, 이디야, 메가커피 이름이 포함된 매장만 다시 데이터프레임 생성 후 몇개인지 확인
스타벅스_df = df[df['사업장명'].str.contains('스타벅스')]
print("스타벅스 매장수:",len(스타벅스_df))

스타벅스_df['소재지전체주소'].iloc[0]

# pip show pandas_datareader

import pandas as pd

download_link = 'http://kind.krx.co.kr/corpgeneral/corpList.do' 
download_link = download_link + '?method=download'  
download_link = download_link + '&marketType=' + "stockMkt"  
df = pd.read_html(download_link, header=0)[0] # 열이름  쓸 행을 지정, 리스트 리턴
df.종목코드 = df.종목코드.map('{:06d}.KS'.format) 
# df.종목코드 = df.종목코드.map('{0:,}'.format) # 3자리마다 콤마
df

import time

start_time = datetime.now()
print(start_time)
time.sleep(5)
end_time = datetime.now()
print(end_time)
elapsed_time = end_time - start_time # 경과시간
print(f'경과시간 : {elapsed_time}')

# 10개월 후
now = datetime.now()

before = now + relativedelta(months=10)

now_day = now.strftime("%Y-%m-%d")
befor_day = before.strftime("%Y-%m-%d")
print(f"end  : {now_day}")
print(f"start: {befor_day}")

https://www.youtube.com/watch?v=4FCcp4D9kMs&t=104s

https://financedata.github.io/posts/finance-data-reader-users-guide.html

FinanceDataReader 사용자 안내서

# pip install -U finance-datareader
# pip show finance-datareader

import FinanceDataReader as fdr
# fdr.__version__

import pandas as pd
from datetime import date, timedelta

# 한국거래소 상장종목 전체
df_krx = fdr.StockListing('KRX')

start_day = "2023-01-01"
# now = datetime.now()
today = date.today()
yesterday = date.today() - timedelta(1)
# last_day = now.strftime("%Y-%m-%d")

회사이름_list = df_krx["Name"].to_list()
종목코드_list = df_krx["Code"].to_list()

df = fdr.DataReader('005930', start_day, yesterday)

df_stock = pd.DataFrame()
for i, 회사이름 in enumerate(회사이름_list):
    tmp_df = fdr.DataReader(종목코드_list[i], start=start_day, end=yesterday)
    df_stock[회사이름] = tmp_df["Close"]
    if i >= 10:
        break;
        
# 빈 값을 채우기
df_stock = df_stock.fillna(method='backfill')

# 수익률 계산
df_수익률 = pd.DataFrame()
df_수익률 = (df_stock.iloc[-1] - df_stock.iloc[0]) / df_stock.iloc[0] * 100

# 설치된 폰트 확인
import matplotlib.font_manager as fonm

font_list = [font.name for font in fonm.fontManager.ttflist]
for f in font_list:
    print(f)

import matplotlib as mat

mat.rcParams['font.family'] = 'D2Coding'

# 그래프 그리기
df_수익률.plot.bar(rot=270)