엑셀데이터를 처리해보자!

이번시간에는 엑셀파일에 import하여 접근후, datascience, pandas, numpy를 활용하여 부가적인 데이터를 뽑아내고, 다시 새롭게 만들어진 output파일을 export해보려고 합니다!

 

해당 포스팅에서는 주피터 노트북을 활용하여 진행하였습니다.

 

아래와 같이 가락시장 경매 데이터(4월30일자)가 있습니다. 해당 데이터에는 

 

사용할데이터: 가락시장_경매_데이터_2024.0430.xlsx

가락시장_경매_데이터_2024.0430_.xlsx

0.04MB

 

가락시장에서 진행된 경매기록에 대한 정보가 담겨있습니다. 우선 이를 코드상에서 쉽게 가공할 수 있도록 하기 위해 csv파일로 변환하는 작업부터 해보도록 하겠습니다.

 

0. xlsx파일을 csv파일로 변환하기

import pandas as pd

# Excel 파일 읽기
df = pd.read_excel('./../data/가락시장_경매_데이터_2024.0430_.xlsx')

# CSV 파일로 저장
df.to_csv('./../data/가락시장_경매_데이터_2024.0430_.csv', index=False, encoding='utf-8-sig')

print("변환 완료!!")

 

우선 위와 같이 판다스를 활용하여 xlsx파일을 읽고 이를 df라는 변수에 저장합니다. 그러면 데이터프레임의 형태로 전환이 되는 것을 알수가 있습니다. 이때 데이터프레임 형식의 객체, 'df'는 to_csv라는 메소드를 활용할수가 있는데, 이를 활용하여 csv파일로 변환해주는 작업을 거쳐줍니다.

1.라이브러리 포함시키기

from datascience import Table
import matplotlib
matplotlib.use('Agg')
from datascience import Table
from datascience import make_array
%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import re
plt.style.use('fivethirtyeight')

 

 

2.csv파일 읽어들이기

table = Table().read_table('./../data/가락시장_경매_데이터_2024.0430_.csv')
table

result>

 

3.필터링 함수 생성하기

조건:
상위20% : 총거래량(L)에서 거래량 상위 20%에 속하는지 여부
하위20% : 총거래량(L)에서 거래량 하위 20%에 속하는지 여부
중간20% : 총거래량(L)에서 거래량 중간 20%에 속하는지 여부
상위10% : 총거래량(L)에서 거래량 상위 10%에 속하는지 여부
하위10% : 총거래량(L)에서 거래량 하위 10%에 속하는지 여부
중간10% : 총거래량(L)에서 거래량 중간 10%에 속하는지 여부

# 상위20% 
def is_in_top_20_percent(data, value):
    threshold = np.percentile(data, 80)
    if value > threshold:
        return 1
    else:
        return 0

# 하위20%
def is_in_bottom_20_percent(data, value):
    threshold = np.percentile(data, 20)
    if value < threshold:
        return 1
    else:
        return 0

# 중간20%
def is_in_middle_20_percent(data, value):
    lower_band = np.percentile(data, 40)
    upper_band = np.percentile(data, 60)
    if lower_band < value < upper_band:
        return 1
    else:
        return 0

# 상위10%
def is_in_top_10_percent(data, value):
    threshold = np.percentile(data, 90)
    if value > threshold:
        return 1
    else:
        return 0

# 하위10%
def is_in_bottom_10_percent(data, value):
    threshold = np.percentile(data, 10)
    if value < threshold:
        return 1
    else:
        return 0

# 중간10%
def is_in_middle_10_percent(data, value):
    lower_band = np.percentile(data, 45)
    upper_band = np.percentile(data, 55)
    if lower_band < value < upper_band:
        return 1
    else:
        return 0

 

 

4.'규격숫자', '총거래량' 칼럼 추가하기

조건:
규격숫자 :  규격(H)에 나타나는 '숫자kg'에서 숫자만 추출한 값
총거래량 : 규격숫자(K) X 거래량(I)

table2=table.with_columns(
    '규격숫자', [int(re.findall(r'\d+', i)[0]) for i in table.column('규격')],
    '총거래량', table2.column('규격숫자')*table2.column('거래량'),
)
table2

result>

 

 

5. '상위비율 여부와 하위비율 여부', '단위경락가' 칼럼 추가하기

 

table3=table2.with_columns(
    '상위20%', [is_in_top_20_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    '하위20%', [is_in_bottom_20_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    '중간20%', [is_in_middle_20_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    
    '상위10%', [is_in_top_10_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    '하위10%', [is_in_bottom_10_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    '중간10%', [is_in_middle_20_percent(table2.column('총거래량'),i) for i in table2.column('총거래량')],
    '단위경락가', table2.column('경락가')/table2.column('규격숫자')
)
table3

result>

 

6.판다스의 데이터프레임으로 데이터형식 변환하기
(datascience.Table → pandas.dataframe)

import pandas as pd

dataframe = {
    '경락일시': table3.column('경락일시'),
    '도매시장': table3.column('도매시장'),
    '법인': table3.column('법인'),
    '부류': table3.column('부류'),
    '품목': table3.column('품목'),
    '품종': table3.column('품종'),
    '출하지': table3.column('출하지'),
    '규격': table3.column('규격'),
    '거래량': table3.column('거래량'),
    '경락가': table3.column('경락가'),
    '규격숫자': table3.column('규격숫자'),
    '총거래량': table3.column('총거래량'),
    '상위20%': table3.column('상위20%'),
    '하위20%': table3.column('하위20%'),
    '중간20%': table3.column('중간20%'),
    '상위10%': table3.column('상위10%'),
    '하위10%': table3.column('하위10%'),
    '중간10%': table3.column('중간10%'),
    '단위경락가': table3.column('단위경락가'),
}
df = pd.DataFrame(dataframe)
df['단위경락가'] = df['단위경락가'].astype(int)
df

result>

 

7.데이터프레임을 엑셀파일로 변환하기

df.to_excel('output.xlsx', index=False)

result>

 

 

 

완성된 데이터:

output.xlsx

0.06MB

 

 

지금까지 읽어주셔서 감사합니다!