[실무 테크닉 연습] - (2) 대리점 데이터 가공

📙대리점의 매출 이력과 고객 정보 데이터를 이용해서 데이터 분석과 예측을 위해 중요한 '데이터 가공'을 연습해보자.

🤔시스템에 의해 관리되는 데이터가 아닌 경우, 종종 사람의 입력 실수나 누락 등으로 오류가 포함될 가능성이 있다. 이로인해 잘못된 결과를 도출하거나, 가공 처리부터 어려움을 겪는 경우가 있다.
실제 현장에서도 잘 정리된 데이터 보다는 소위 '지저분한' 데이터 형식을 만나게 될 가능성이 높으므로, 이런 데이터를 처리하는 경험을 쌓아보자.

✔ 전제조건

상품 A~Z까지 26개 상품을 취급하며, 매출 이력과 고객 정보 데이터는 담당직원이 직접 시스템에 입력한다. 집계기간 상품 단가 변동은 없고, 매출이력은 시스템에서 csv파일로 출력했다. 고객 정보는 주별로 집계하여 엑셀로 관리한다.

데이터	개요
uriage.csv	매출이력 (2019.01~2019.07)
kokyaku_daicho.xlsx	대리점에서 관리하는 고객 정보

---

# 경고(warning) 비표시
import warnings
warnings.filterwarnings('ignore')

1. 데이터 읽어오기

import pandas as pd
uriage_data = pd.read_csv("uriage.csv")
uriage_data.head()

kokyaku_data = pd.read_excel("kokyaku_daicho.xlsx")
kokyaku_data.head()

2. 데이터의 오류 확인

uriage_data["item_name"].head()

0      상품A
1    상 품 S
2    상 품 a
3      상품Z
4      상품a
Name: item_name, dtype: object

▶상품 명의 형식이 일관되지 않음을 볼 수 있다. 대소문자 / 공백 유무로 다른 데이터로 인식하기 때문에 정리가 필요해보임.

uriage_data["item_price"].head()

0     100.0
1       NaN
2       NaN
3    2600.0
4       NaN
Name: item_price, dtype: float64

▶결측치도 있음.
결측치를 처리하는 방법에 따라 결과도 많이 달라지기 때문에 전체 분포도 잘 살펴보자.

kokyaku_data["등록일"].head()

0    2018-01-04 00:00:00
1                  42782
2    2018-01-07 00:00:00
3                  42872
4                  43127
Name: 등록일, dtype: object

▶등록일 기준에 4xxxx은 무엇?

데이터에 입력 오류나 표기방법 차이가 부정합을 일으키면 '데이터의 정합성에 문제가 있다'라고 하는데, 위의 데이터가 좋은 사례이다.

3.데이터에 오류가 있는 상태로 집계해보기

⏺ 먼저 매출이력에서 상품별 월 매출 합계를 집계해보자.

uriage_data["purchase_date"] = pd.to_datetime(uriage_data["purchase_date"])
uriage_data["purchase_month"] = uriage_data["purchase_date"].dt.strftime("%Y%m")
res = uriage_data.pivot_table(index="purchase_month", columns="item_name", aggfunc="size", fill_value=0)
res

▶컬럼을 보면 상품S와 상품s를 다른 품목으로 인식한 것을 볼 수 있다. 총 상품은 26개인데 위의 표를 보면 99개의 컬럼, 즉 상품을 99가지로 인식하고 있다.

가로축에 item_price를 설정해서 집계해보자.

res = uriage_data.pivot_table(index="purchase_month", columns="item_name", values="item_price", aggfunc="sum", fill_value=0)
res

4. 상품명의 오류를 수정하자

⏺위의 상태로 분석을 하면 전혀 의미없는 결과가 나오기 때문에 가공단계가 전처리로 얼마나 중요한지 확인했다. 오류를 이제 수정해보자.

#상품명의 수 확인
len(uriage_data["item_name"].unique())

99

#대문자 통일
uriage_data["item_name"] = uriage_data["item_name"].str.upper()
# 공백 수정
uriage_data["item_name"] = uriage_data["item_name"].str.replace("　", "")
uriage_data["item_name"] = uriage_data["item_name"].str.replace(" ", "")
#상품별 나열
uriage_data.sort_values(by=["item_name"], ascending=True)

print(len(uriage_data["item_name"].unique()))
print(uriage_data["item_name"].unique())

26
['상품A' '상품S' '상품Z' '상품V' '상품O' '상품U' '상품L' '상품C' '상품I' '상품R' '상품X' '상품G'
 '상품P' '상품Q' '상품Y' '상품N' '상품W' '상품E' '상품K' '상품B' '상품F' '상품D' '상품M' '상품H'
 '상품T' '상품J']

▶상품명 처리!

5. 금액의 결측치를 수정해보자.

uriage_data.isnull().any()

purchase_date     False
item_name         False
item_price         True
customer_name     False
purchase_month    False
dtype: bool

▶item_price 컬럼에 결측치가 있다.

✔해당 데이터의 경우, 집계 기간에 상품 단가 변동이 없었기 때문에, 결측치는 같은 상품 단가를 이용해서 수정할 수 있다.

#어떤 행에 결측치 있는지 저장
flg_is_null = uriage_data["item_price"].isnull()
flg_is_null

0       False
1        True
2        True
3       False
4        True
        ...  
2994    False
2995    False
2996     True
2997     True
2998    False
Name: item_price, Length: 2999, dtype: bool

#결측치 있는 상품명 리스트 
list(uriage_data.loc[flg_is_null, "item_name"])[:5]

['상품S', '상품A', '상품A', '상품A', '상품A']

# unique()로 중복을 제거해서 불필요한 반복 방지 후 for문 돌리기
for trg in list(uriage_data.loc[flg_is_null, "item_name"].unique()):
    # 결측치가 있는 상품과 같은 상품을 찾고, 올바르게 입력된 행의 loc을 찾아 그 금액을 가져온다. 
    price = uriage_data.loc[(~flg_is_null) & (uriage_data["item_name"] == trg), "item_price"].max()
    # 가져온 금액으로 수정
    uriage_data["item_price"].loc[(flg_is_null) & (uriage_data["item_name"]==trg)] = price
uriage_data.head()

▶결측치 처리 후 검증

uriage_data.isnull().any()

purchase_date     False
item_name         False
item_price        False
customer_name     False
purchase_month    False
dtype: bool

▶각 상품 금액이 정상적으로 수정되었는지 확인해보자.

for trg in list(uriage_data["item_name"].sort_values().unique()):
    print(trg + "의최고가：" + str(uriage_data.loc[uriage_data["item_name"]==trg]["item_price"].max()) 
          + "의최저가：" + str(uriage_data.loc[uriage_data["item_name"]==trg]["item_price"].min(skipna=False)))
    #skipna는 NaN의 무시여부를 설정. False로 지정했기 떄문에 NaN이 존재하는 경우 최솟값이 NaN으로 표시된다. 

상품A의최고가：100.0의최저가：100.0
상품B의최고가：200.0의최저가：200.0
상품C의최고가：300.0의최저가：300.0
상품D의최고가：400.0의최저가：400.0
상품E의최고가：500.0의최저가：500.0
상품F의최고가：600.0의최저가：600.0
상품G의최고가：700.0의최저가：700.0
상품H의최고가：800.0의최저가：800.0
상품I의최고가：900.0의최저가：900.0
상품J의최고가：1000.0의최저가：1000.0
상품K의최고가：1100.0의최저가：1100.0
상품L의최고가：1200.0의최저가：1200.0
상품M의최고가：1300.0의최저가：1300.0
상품N의최고가：1400.0의최저가：1400.0
상품O의최고가：1500.0의최저가：1500.0
상품P의최고가：1600.0의최저가：1600.0
상품Q의최고가：1700.0의최저가：1700.0
상품R의최고가：1800.0의최저가：1800.0
상품S의최고가：1900.0의최저가：1900.0
상품T의최고가：2000.0의최저가：2000.0
상품U의최고가：2100.0의최저가：2100.0
상품V의최고가：2200.0의최저가：2200.0
상품W의최고가：2300.0의최저가：2300.0
상품X의최고가：2400.0의최저가：2400.0
상품Y의최고가：2500.0의최저가：2500.0
상품Z의최고가：2600.0의최저가：2600.0

6. 고객이름의 오류를 수정하자

#고객 정보의 고객 이름
kokyaku_data["고객이름"].head()

0    김 현성
1    김 도윤
2    김 지한
3    김 하윤
4    김 시온
Name: 고객이름, dtype: object

# 매출 이력의 고객 이름
uriage_data["customer_name"].head()

0    김가온
1    김우찬
2    김유찬
3    김재현
4    김강현
Name: customer_name, dtype: object

# 공백이 보이므로, 일단 제거해서 통일시킨다. 
kokyaku_data["고객이름"] = kokyaku_data["고객이름"].str.replace("　", "")
kokyaku_data["고객이름"] = kokyaku_data["고객이름"].str.replace(" ", "")
kokyaku_data["고객이름"].head()

0    김현성
1    김도윤
2    김지한
3    김하윤
4    김시온
Name: 고객이름, dtype: object

7. 날짜오류를 수정하자

⏺고객 대장의 등록일 오류를 수정해보자. 4xxxx처럼 날짜 형식이 아닌 경우가 무엇이었는지 보기.

#숫자로 읽히는 데이터 확인
#고객 정보의 등록일이 숫자인지 아닌지를 str.isdigit()로 판정하고, 숫자로 된 장소를 flg_is_serial에 저장해서 갯수 확인
flg_is_serial = kokyaku_data["등록일"].astype("str").str.isdigit()
flg_is_serial.sum()

22

kokyaku_data["등록일"]

0      2018-01-04 00:00:00
1                    42782
2      2018-01-07 00:00:00
3                    42872
4                    43127
              ...         
195    2017-06-20 00:00:00
196    2018-06-20 00:00:00
197    2017-04-29 00:00:00
198    2019-04-19 00:00:00
199    2019-04-23 00:00:00
Name: 등록일, Length: 200, dtype: object

▶pd.to_timedelta() 함수로 숫자를 날짜로 변환한다. loc()을 이용해서 데이터 추출하고, 날짜로 변경한다.

fromSerial = pd.to_timedelta(kokyaku_data.loc[flg_is_serial, "등록일"].astype("float"), unit="D") + pd.to_datetime("1900/01/01")
fromSerial

1     2017-02-18
3     2017-05-19
4     2018-01-29
21    2017-07-06
27    2017-06-17
47    2017-01-08
49    2017-07-15
53    2017-04-10
76    2018-03-31
80    2018-01-12
99    2017-06-01
114   2018-06-05
118   2018-01-31
122   2018-04-18
139   2017-05-27
143   2017-03-26
155   2017-01-21
172   2018-03-24
179   2017-01-10
183   2017-07-26
186   2018-07-15
192   2018-06-10
Name: 등록일, dtype: datetime64[ns]

# 날짜로 변환된 데이터도 서식을 통일한다.
fromString = pd.to_datetime(kokyaku_data.loc[~flg_is_serial, "등록일"])
fromString

0     2018-01-04
2     2018-01-07
5     2017-06-20
6     2018-06-11
7     2017-05-19
         ...    
195   2017-06-20
196   2018-06-20
197   2017-04-29
198   2019-04-19
199   2019-04-23
Name: 등록일, Length: 178, dtype: datetime64[ns]

kokyaku_data["등록일"] = pd.concat([fromSerial, fromString])
kokyaku_data

▶등록일로부터 등록월을 추출해서 집계해보기.

kokyaku_data["등록연월"] = kokyaku_data["등록일"].dt.strftime("%Y%m")
rslt = kokyaku_data.groupby("등록연월").count()["고객이름"]
print(rslt)
print(len(kokyaku_data))

등록연월
201701    15
201702    11
201703    14
201704    15
201705    13
201706    14
201707    17
201801    13
201802    15
201803    17
201804     5
201805    19
201806    13
201807    17
201904     2
Name: 고객이름, dtype: int64
200

# 등록일 컬럼에 숫자 데이터가 남아있는지 확인
flg_is_serial = kokyaku_data["등록일"].astype("str").str.isdigit()
flg_is_serial.sum()

0

8. 고객이름을 키로 두개의 데이터를 결합(조인)

# how='left'로, uriage_data를 기준으로 결합한다. 
join_data = pd.merge(uriage_data, kokyaku_data, left_on="customer_name", right_on="고객이름", how="left")
join_data = join_data.drop("customer_name", axis=1)
join_data

9. 정제한 데이터를 덤프

⏺컬럼을 약간 정리한 후에 가공한 데이터를 저장해두자

dump_data = join_data[["purchase_date", "purchase_month", "item_name", "item_price", "고객이름", "지역", "등록일"]]
dump_data

dump_data.to_csv("dump_data.csv", index=False)

10. 데이터를 집계하자

import_data = pd.read_csv("dump_data.csv")
import_data

#상품별 집계
byItem = import_data.pivot_table(index="purchase_month", columns="item_name", aggfunc="size", fill_value=0)
byItem

# 구입 연월, 매출 금액 집계
byPrice = import_data.pivot_table(index="purchase_month", columns="item_name", values="item_price", aggfunc="sum", fill_value=0)
byPrice

# 구입 연월, 고객별 구입 수 집계 결과
byCustomer = import_data.pivot_table(index="purchase_month", columns="고객이름", aggfunc="size", fill_value=0)
byCustomer

# 구입 연월, 지역별 판매 수 집계
byRegion = import_data.pivot_table(index="purchase_month", columns="지역", aggfunc="size", fill_value=0)
byRegion

▶import_data는 구매이력이 있는 uriage_data를 기준으로 왼쪽 조인된 결과이기 때문에,
기간 동안 구매이력이 없는 사람을 확인하려면 고객 대장을 기준으로 오른쪽 조인으로 다시 확인해야 된다.

# 집계 기간에 구매이력이 없는 사람
away_data = pd.merge(uriage_data, kokyaku_data, left_on="customer_name", right_on="고객이름", how="right") #고객 대장을 기준으로 합치기
away_data[away_data["purchase_date"].isnull()][["고객이름", "등록일"]]