(5)데이터 확인 및 전처리_관로 노후화 의심구역 선정을 위한 수질정보 분석

 

1.     데이터 확인(EDA) 및 전처리

❍     정수장 데이터

-   노후 관로에서 측정하는 항목(암모니아성 질소, 구리, 아연, 염소이온, 철, 망간)을 분석하기 위해 일반 수도꼭지를 제외하고 노후 수도꼭지 데이터만 추출

-   48개 이상의 수질 데이터를 보유한 수도꼭지 지역(150군데)에 수돗물을 공급한 정수장을 ‘시설물 코드’를 활용하여 추출

-   추출된 2015~2019년 월간 정수장 수질 데이터정수장(약 50여 군데)의 결측값(2018년도 대부분 등)을 분석항목(암모니아성 질소, 구리, 아연, 염소이온, 철, 망간)별로 각 항목의 전체 평균으로 대체

 

광역시도	시설물 번호	정수장명	정수장 주소	채수 년월일	수질검사기관	NH3-N	Cu	Zn	Cl	Fe	Mn
전라남도 화순군	○○○	화순읍	●●●	20190108	이산 친환경연구원	0	0	0.016	7.4	0	0
전라남도 화순군	○○○	화순읍	●●●	20190212	이산 친환경연구원	0	0	0.002	9.8	0	0
전라남도 화순군	○○○	화순읍	●●●	20190312	이산 친환경연구원	0	0	0	6.7	0	0
전라남도 화순군	○○○	화순읍	●●●	20180109	바른 환경연구소	0	·	·	·	·	·
전라남도 화순군	○○○	화순읍	●●●	20180206	바른 환경연구소	0	·	·	·	·	·

표 4. 추출하여 정수장 ‘시설물 코드’별로 정리한 수도꼭지 데이터

 

광역시도	시설물 번호	정수장명	정수장 주소	채수 년월일	수질 검사기관	NH3-N	Cu	Zn	Cl	Fe	Mn
전라남도 화순군	○○○	화순읍	●●●	2019 0108	이산 친환경연구원	0	0	0.016	7.4	0	0
전라남도 화순군	○○○	화순읍	●●●	2019 0212	이산 친환경연구원	0	0	0.002	9.8	0	0
전라남도 화순군	○○○	화순읍	●●●	2019 0312	이산 친환경연구원	0	0	0	6.7	0	0
전라남도 화순군	○○○	화순읍	●●●	2018 0109	바른 환경 연구소	0	0.00 6769	0.01 3154	7.83 8462	0.02 4872	0.00 0462
전라남도 화순군	○○○	화순읍	●●●	2018 0206	바른 환경 연구소	0	0.00 6769	0.01 3154	7.83 8462	0.02 4872	0.00 0462

표 5. <표 4>의 결측값을 평균으로 대체

 

❍     수도꼭지 데이터

-   노후 관로에서 측정하는 항목(암모니아성 질소, 구리, 아연, 염소이온, 철, 망간)을 분석하기 위해 일반 수도꼭지를 제외하고 노후 수도꼭지 데이터만 추출

-  2015~2019년 월간 수도꼭지 수질 데이터에 60개(5개년)가 관측되어야 하는데, 결측값이 많이 존재하여 경향성 분석의 오차를 최소화하기 위해 최소 4개년(48개 이상)의 데이터가 있는 지역 150군데를 추출

import pandas as pd 
pd.set_option('display.max_rows', None) 
data=pd.read_csv("./WaterTab.csv", encoding='cp949') 
is_old = data['일반노후구분'] == '수도꼭지 노후'
old = data[is_old] 
old
df=old.groupby('수도꼭지상세').size()    #수도꼭지 상세 
num = df[df >= 48]
print(num)

 

수도꼭지 상세	데이터 수
○○○	54
■■■	57
▲▲▲	52
☆☆☆	54
●●●	54

표 6. 수도꼭지 지역 선정(코드 결과)

-   “수도꼭지 상세”별로 데이터를 구분하여 정리

-   결측값이 있는 경우 해당 월 제거

 

광역 시도	시설물 번호	정수장명	수도꼭지 법정동	수도꼭지 상세	일반노후 구분	채수 년월일	NH3-N	Cu	Zn	Cl	Fe	Mn
강원도 양양군	△△ △	양양	강원도 양양군	▲▲▲	수도꼭지 노후	2019 0107	0	0	0.028	10.5	0	0
강원도 양양군	△△ △	양양	강원도 양양군 손양면	▲▲▲	수도꼭지 노후	2019 0211	0	0	0.005	10	0	0
강원도 양양군	△△ △	양양	강원도 양양군 손양면	▲▲▲	수도꼭지 노후	2019 0306	0.07	0.02	0.157	9.8	0	0
강원도 양양군	△△ △	양양	강원도 양양군 손양면	▲▲▲	수도꼭지 노후	2019 0404	0	0.01	0.032	10.5	0	0
강원도 양양군	△△ △	양양	강원도 양양군 손양면	▲▲▲	수도꼭지 노후	2019 0503	0	0.009	0.03	9.7	0	0

표 7. 추출하여 ‘수도꼭지 상세’별로 정리한 수도꼭지 데이터

 

❍     정수장 대비 수도꼭지 수질 데이터 차이

-   정수장 ‘시설물 코드’를 바탕으로 수도꼭지 데이터에서 정수장 데이터의 값을 뺀 수질 데이터값을 항목별로 정리

 

import pandas as pd 
import openpyxl

waterplant = #정수장 파일 이름 
waterplant_num = #정수장 파일 시트 이름 
watertab = #수도꼭지 파일 이름 
watertab_name = #수도꼭지 파일 시트 이름
plant = openpyxl.load_workbook(waterplant, data_only = True) #비교할 엑셀파일 부르기
pl = plant.get_sheet_by_name(waterplant_num) 
tab = openpyxl.load_workbook(watertab)
ta = tab.get_sheet_by_name(watertab_name)
result = openpyxl.load_workbook('result.xlsx') #결과(수도꼭지 - 정수장) 
re = result.get_sheet_by_name("Sheet1")

pl.delete_rows(1) #첫 행 제거 
ta.delete_rows(1)

plant.save("new_"+ waterplant) 
tab.save("new_"+ watertab)
plant = openpyxl.load_workbook("new_"+ waterplant) 
pl = plant.get_sheet_by_name(waterplant_num)
tab = openpyxl.load_workbook("new_"+ watertab) 
ta = tab.get_sheet_by_name(watertab_name)

- 정수장 데이터 파일과 수도꼭지 데이터 파일을 불러와서 ‘채수년월’을 기준으로 수도꼭지 데이터에서 정수장 데이터의 차이를 각 항목(암모니아성 질소, 구리, 아연, 염소이온, 철, 망간)별로 저장

 

for r in re.rows:
for i in range(2, 62):
if re.cell(row=i, column = 4).value == None: #수도꼭지에 해당 정보가 없어서 NULL 값인 경우
	re.cell(row=i, column = 3).value = '/a#N' #모든 값을 0으로 할당 
    re.cell(row=i, column = 4).value = '/a#N'
	re.cell(row=i, column = 5).value = '/a#N' 
    re.cell(row=i, column = 6).value = '/a#N' 
    re.cell(row=i, column = 7).value = '/a#N' 
    re.cell(row=i, column = 8).value = '/a#N'

re.cell(row=1, column = 2).value = "채수년월일" 
re.cell(row=1, column = 3).value = "암모니아성질소" 
re.cell(row=1, column = 4).value = "구리" 
re.cell(row=1, column = 5).value = "아연" 
re.cell(row=1, column = 6).value = "염소이온" 
re.cell(row=1, column = 7).value = "철" 
re.cell(row=1, column = 8).value = "망간"

result.save(waterplant_num+watertab_name+".xlsx") 
result.close()

data = pd.read_excel(waterplant_num+watertab_name+".xlsx") 
data = data.sort_values(by='채수년월일')
with pd.ExcelWriter(waterplant_num+watertab_name+".xlsx") as writer: 
data.to_excel(writer, sheet_name="Sheet1", index=False)

-  수도꼭지 데이터에‘채수년월’에 정보가 없는 경우, 값을 “/a#N”으로 할당하고 이후에 해당 열을 삭제하여 사용

 

채수년월일	NH3-N	Cu	Zn	CL	Fe	Mn
1501	0	0	0.002	0.2	0	0
1502	0	0.69	0.02	-0.3	0.06	0
1503	0	0.028	0.009	-0.3	0.06	-0.005
1504	0	0.012	0.002	0.1	0	0
1506	0	0.011	0.004	0.8	0	0

표 8. 수도꼭지 데이터에서 정수장 데이터의 값을 뺀 수질 데이터