다시 뛰는 공정도시 서울!

문서번호 배급수연구과-10182 결재일자 2023. 12. 14. 공개여부 부분공개(5) 방침번호 시 민 주무관 배급수연구과장 수도연구부장 서울물연구원장 김철 김완섭 안재찬 12/14 손정수 관망해석을 위한 원격검침 데이터 변환 툴 개발 2023. 12. 13. (수) 서울물연구원 수도연구부 배급수연구과 ☞ 해당사항이 있는 부분에 ‘ ■ ’ 표시하시기 바랍니다. (※ 비고 : 필요시 검토내용 기재) 구 분 사전 검토항목 점검 사항 검토완료 해당없음 비 고 일반사항 정책일반 ◆ 제반 법규와 실태, 실제 현장의 의견 등을 검토하였습니까? (법령, 규칙, 통계·빅데이터, 시민의견 등) □ ■ ◆ 정책(사업) 집행의 직·간접적 영향 및 효과성을 분석하였습니까?(갈등, 약자, 일자리, 안전, 탄소 감축 등) ※ 약자 : 하단 세부 검토항목 점검 □ ■ ◆ 정책·계획·전시물·홍보물 등이 역사적 사실에 부합하는지 검토하였습니까? □ ■ ◆ 불필요한 외국어·외래어 표현 대신 바른 우리말을 사용하였습니까? ■ □ ◆ 정책·계획 등의 지속가능성을 검토하였습니까?(경제·사회·환경 등) □ ■ 약자와의 동행 목적 ◆ 경제적으로 취약한 약자를 지원하기 위한 사업인가? □ ■ ◆ 주거, 생계, 의료, 교육, 기타 분야 중 어느 것에 해당하는가? □ ■ 주거/생계/의료/교육/기타 ◆ 단순지원 사업과 사다리지원 사업 중 어느 것에 해당하는가? (단순 : 어려움 즉시 개선 / 사다리 : 계층 상향 또는 안정 유도) □ ■ 단순지원/ 사다리지원 ◆ 경제적으로 취약한 약자가 아니라 하더라도, 다양한 분야의 사회적 약자를 배려할 수 있는 수단을 포함하였는가? □ ■ 계획수립 ◆ 사업 대상자가 쉽고 편리하게 서비스를 활용할 수 있도록 사업을 구성하였는가? □ ■ 집행·홍보 ◆ 집행과정에서 발생할 수 있는 역효과를 최소화하기 위한 대안을 고려하였는가?(‘약자’ 낙인효과, 역차별 등) □ ■ ◆ 사업 대상자에게 적절한 홍보 수단을 채택하였는가? □ ■ 평가·환류 ◆ 사업효과를 측정할 수 있는 지표(산출, 과정, 성과 등)는 마련하였는가? 아닐 경우, 성과를 평가할 수 있는 대체·보완 수단이 존재하는가? ■ □ 관망해석을 위한 원격검침 데이터 변환 툴 개발 수도연구부 부장:안재찬☎1810 배급수연구과장:안재찬☎1830 담당:김철☎1825 원격검침 자료를 관망해석 모델용 데이터 포맷으로 변환하여 관망모델 구축 절차 간소화 및 준실시간(시간, 일간) 관망 시뮬레이션에 활용하고자 함 □ 현황 및 필요성 ㅇ 관망해석 모델 구축 방법 - GIS 자료(관경, 관길이, 고도 등) 변환 및 물 사용량 입력 - 유량, 수압, 수질 등을 관망에서 측정하여 모델 보정 <관망해석 모델 구축 방법> ㅇ 검침 데이터 변환 필요성 - 기존 관망해석에서 물사용량은 2개 인자(Base Demand, Demand Pattern) 입력 ·(Base Demand) 수용가의 월 검침량을 일별 사용량으로 계산 입력 ·(Demand Pattern) 주간, 야간 등 시간에 따라 물사용량 패턴 반영(주로 정수장 유출, 배수지 유출 등 공급량을 평균으로 나누어 계수 산출) - 원격검침자료는 시간별 1개씩 24개 데이터로 사용량과 패턴을 함께 나타내고 있어 기존 관망해석 프로그램에는 입력할 수 없어 변형 필요 ·(Base Demand) 원격검침량 증감용 계수를 입력하여 필요시 조정 ·(Demand Pattern) 시간별 원격검침량 데이터 입력(24개) ⇒ 즉 기존 관망해석의 Base Demand와 Demand Pattern의 기능을 원격검침용 관망해석에서는 반대로 사용량을 Demand Pattern에 입력 <기존 관망해석 및 원격검침데이터 활용 관망해석 차이> 구 분 기존 관망해석 원격검침데이터 활용 관망해석 Base Demand 수용가별 월검침량(1개)* 물사용량 변동 조정계수(1개) Demand Pattern 시간별 물사용량 변화율(24개) 시간별 원격검침량(24개) *관망모델 절점(Junction)에 다수의 수용가 검침량이 있는 경우 각각 구분 입력 가능 - 기존 관망해석에서는 수용가의 사용량을 관망모델의 Base demand에 일괄 입력하는 메뉴 기능이 있으나, Demand Pattern에 일괄 입력 기능이 없어 수용가별로 시간별 검침량을 수기 입력해야 함 ⇒ 따라서 원격검침 데이터 포맷을 관망모델의 Demand Pattern에 사용량을 일괄 입력할 수 있는 데이터 포맷으로 변환하는 툴 개발 필요 □ 데이터 현황 및 추출방향 ㅇ 데이터 설명(여의도 원격검침데이터 활용) - 여의도 원격 단말기 현황_20220112_1번 파일.xlsx ⇒ 고객번호, 업종, 수용가번호, 이름, 주소, 계량기번호 등 고객번호별 원격검침 요금 관련 대장 ⇒ 관망해석 도면에서 ‘고객번호’에 해당하는 ‘Junction’을 여의도 관망모델에 활용하기 위해 이전에 수기 입력한 상태 <여의도 원격 단말기 현황 엑셀 파일 일부> - 2022-01-1-_데이터_2번 파일.xlsx ⇒ 수용가번호(고객번호와 동일), 구경, 업종, 주소, 계량기번호, 검침일시, 검침량, 구간검침량 등 원격검침 데이터 기록 대상 ⇒ ’22년 1월 10일 여의도 원격검침 데이터 자료 : 정상적인 경우 ‘구간사용량’에 24개 데이터 입력되어 있음 <2022-01-1-원격검침 데이터 엑셀 파일 일부> ㅇ 관망해석 모델 입력용 데이터 추출 방향 - 원격단말기 현황 대장과 원격검침 기록 대장의 고객번호(수용가번호)를 이용하여 절점(Junction)별 검침량(24개)를 매칭 추출 ⇒ 엑셀함수, 파이썬 프로그램 언어를 데이터 변환 및 추출 ⇒ 절점(Junction)은 행, 시간별 검침량은 열의 2차원 배열 데이터 작성 <관망해석 모델용 검침데이터 추출 예> □ 추진결과 : 데이터 변환 방법 방법 1. 엑셀함수 및 파이썬 코딩 적용 ㅇ 데이터 변환 절차 - 엑셀 데이터에서 동일한 속성인‘고객번호’를 이용하여‘Junction(절점)’별 ‘구간사용량(시간별 검침량)’할당 - 관망해석 모델에 일괄 입력하기 위한 데이터 포맷으로 변환 ① 데이터 분류 ② 데이터 결합 ③ 구간사용량 정리 고객번호, Junction, 구간사용량 속성의 별도 엑셀파일 생성 ⇒ 고객번호 속성을 이용, Junction 속성에 해당 구간사용량 할당(엑셀 match함수) ⇒ Junction별 시간별 검침량 할당(파이썬 코딩 ) ㅇ 엑셀 변환 작업 : 2번 파일의 구간사용량을 Junction에 할당 - match함수 적용.xlsx을 만들어 sheet1에 데이터 정리 ⇒ ‘1번 파일(여의도 원격 단말기 현황_20220112_1번 파일.xlsx)’에서 ‘고객번호’와 ‘Junction’ 열을 복사하여 ‘sheet1’의 E열과 F열에 붙이기 한다. ⇒ ‘2번 파일(2022-01-1-_데이터_2번 파일.xlsx)’에서 ‘수용가번호’를 ‘고객번호’로 수정하고, ‘고객번호’와 ‘구간사용량’을 복사하여 ‘sheet1’의 각각 B열과 D열에 붙이기 한다. ⇒ C열에는 열이름 ‘Junction’로 입력한다. Junction열의 셀은 공란이다. ⇒ C열의 셀에 함수를 작성하기 전에 먼저 셀의 입력된 숫자들이 텍스트로 설정되어 있어 함수 연산이 안되므로 텍스트를 숫자로 변환(아래 점선박스 참조)해야 한다. <셀의 텍스트를 숫자로 변환> → 계산시 인식이 안되므로 변환 필요 *1을 어느 지점에 임의로 입력하고, 복사(ctrl+c)한 다음, 텍스트를 숫자로 바꾸고자 하는 셀 선택하고 ctrl+alt+v를 하면 선택하여 붙여넣기 메뉴 뜸 => '붙여넣기'에 '값' 및 '연산'에서 곱하기(M) 클릭 *범위 지정시 넓게 지정하면 해당하지 않는 부분은 0으로 됨 ⇒ 텍스트를 숫자로 변환 후, C2셀에 아래와 같이 함수를 입력한다. 그다음 아래로 복사해서 붙여넣기 하면 B열, E열, F열을 참조하여 C열에 Junction이 입력된다. → ‘Junction’ 번호에 따라 해당 ‘구간사용량’ 정리 <엑셀 match 함수> =INDEX($E$1:$F$438, MATCH(B2,$E$1:$E$438,), MATCH($C$1, $E$1:$F$1,)) ㅇ 파이썬 코딩으로 데이터 포맷 변환 : Junction별 시간별 사용량 가로 정렬 - match함수 적용.xlsx에 result 시트를 만들어 sheet1의 ‘고객번호’, ‘Junction’, ‘구간사용량’ 3개의 열을 복사하여 붙여넣기 한다. ⇒ result 시트를 sheet1 시트 앞에 둔다 : 파이썬 코드 실행시 앞쪽 시트를 실행함 - 파이썬 파일 datamanage1.py에서 코드 작성 ⇒ 결과를 ‘results.xlsx’ 엑셀 파일로 저장하도록 코드 작성 # Excel 파일로 저장 excel_file_name = "results.xlsx" df.to_excel(excel_file_name) <파이썬 코드 및 설명> import pandas as pd file1 = pd.read_excel("match함수 적용.xlsx") junction_type = file1['Junction'].unique() junction_list = [] for i in junction_type: if i == 0: # Junction 컬럼에 0은 제외 continue junction_list.append(i) # print(i) #동일한 junction에 할당된 구간사용량 추출 df = pd.DataFrame(file1) result_dict = {} # 빈 딕셔너리 생성 for i in junction_list: junction_reading = df[df['Junction'] == i]['구간사용량'].tolist() result_dict[i] = junction_reading # i를 key로, junction_reading을 value로 저장 # 결과 데이터를 DataFrame으로 변환 df = pd.DataFrame.from_dict(result_dict, orient='index') # Excel 파일로 저장 excel_file_name = "results.xlsx" df.to_excel(excel_file_name) print(f"결과가 {excel_file_name} 파일로 저장되었습니다.") 방법 2. 파이썬 코딩 적용 ㅇ 데이터 변환 절차 - 동일한 속성인‘고객번호’를 이용하여‘Junction(절점)’별‘구간사용량(시간별 검침량)’할당 - 관망해석 모델에 일괄 입력하기 위한 데이터 포맷으로 변환 ① 데이터 분류 ② 데이터 결합 ③ 구간사용량 정리 고객번호와 Junction 및 고객번호와 구간사용량을 각각 딕셔너리로 생성 ⇒ 공통 속성인 고객번호를 공통키로 하여 딕셔녀리 결합 ⇒ Junction별 시간별 검침량 할당 ㅇ 파이썬 코딩 데이터 추출 - 엑셀 파일명 변경 : 파일명이 길어 줄임 ⇒ 1번 파일_고객번호_Junction.xlsx로 변경 ⇒ 2번 파일_고객번호_구간사용량.xlsx로 변경 : ‘수용가번호’를 ‘고객번호’로 수정 - 파이썬 코드 설명 ⇒ 1번 파일_고객번호_Junction.xlsx을 file1으로 불러들여, ‘고객번호’를 키로, ‘Junction’을 값으로 하는 딕셔너리로 생성 ⇒ 2번 파일_고객번호_구간사용량.xlsx을 file2로 불러들여, ‘고객번호’를 키로, ‘구간사용량’을 값으로 하는 딕셔너리로 생성 ⇒ file1과 file2에 키는 ‘고객번호’이므로 공통 키를 찾아서 데이터프레임 생성 → ‘고객번호’, ‘Junction’, ‘구간사용량’으로 정리 ⇒ 데이터프레임을 엑셀 파일로 저장 : common_keys_data.xlsx excel_file_name = "common_keys_data.xlsx" df.to_excel(excel_file_name, index=False) - 엑셀 결과 파일에서 조정 ⇒ 엑셀 파일에서 고객번호는 Key열, Junction은 file1_dict Value열, ‘구간사용량’은 file2_dict Value열에 있으며, file2_dict Value열에는 1개 셀에 여러 데이터가 쉼표로 구분되어 있어 분리 필요(아래 점선박스 참조) <셀의 텍스트 나누기> → 셀의 여러개 값을 각각의 셀로 저장 *file2_dict Value열(C열)을 클릭하고 엑셀 ‘데이터’ 메뉴의 ‘텍스트 나누기’ 클릭 *텍스트 마법사 창 → ‘구분기호로 분리됨(D)’ 클릭 → ‘쉼표(C)’, ‘공백(S)’, ‘연속된 구분 기호’ 클릭 → ‘일반(G)’ 클릭 → ‘마침(E)’ 버튼 클릭 <파이썬 코드 및 설명> import pandas as pd file1 = pd.read_excel("1번파일_고객번호_Junction.xlsx") file2 = pd.read_excel("2번파일_고객번호_구간사용량.xlsx") # '고객번호'를 키로, 'Junction'을 값으로 하는 딕셔너리 생성 file1_dict = file1[['고객번호', 'Junction']].set_index('고객번호')['Junction'].to_dict() # file2 파일에서 '고객번호'와 '구간사용량' 열을 딕셔너리로, 고객번호가 동일한 경우 구간사용량 모음 customer_type = file2['고객번호'].unique() customer_list = [] for i in customer_type: if i == 0: # customer 컬럼에 0은 제외 continue customer_list.append(i) #동일한 고객번호에 할당된 구간사용량 추출 file2_df = pd.DataFrame(file2) file2_dict = {} # 빈 딕셔너리 생성 for i in customer_list: customer_reading = file2_df[file2_df['고객번호'] == i]['구간사용량'].tolist() file2_dict[i] = customer_reading # i를 key로, customer_reading을 value로 저장 # file1_dict과 file2_dict의 공통 키를 찾아서 데이터프레임 생성 common_keys = list(file1_dict.keys() & file2_dict.keys()) # "[ ]"를 없애는 함수 def remove_brackets(s): return s.replace('[', '').replace(']', '') data = {"Key": common_keys, "file1_dict Value": [str(file1_dict[key]) for key in common_keys], "file2_dict Value": [remove_brackets(str(file2_dict[key])) for key in common_keys]} df = pd.DataFrame(data) # 데이터프레임을 엑셀 파일로 저장 excel_file_name = "common_keys_data.xlsx" df.to_excel(excel_file_name, index=False) □ 향후 활용계획 ㅇ 원격검침 자료를 활용하여 실시간 급수량 입력치 모델을 자동 생성하고, 관망해석용 프로그램(EPANET 등)상에서 일괄처리(Batch Processing) 방식의 실시간 관망해석 시스템 구현에 활용 ㅇ 관망해석 결과의 정확도 제고를 위해서, 수용가별 원격검침 물사용량 자료에서 결측이나 오측된 데이터에 대한 보정에 활용 붙임 : 1. 여의도 원격 단말기 현황_20220112_1번 파일.xlsx 1부 2. 2022-01-1-_데이터_2번 파일.xlsx 1부 3. match함수 적용.xlsx 1부 4. results.xlsx 1부 5. 1번 파일_고객번호_Junction.xlsx 1부 6. 2번 파일_고객번호_구간사용량.xlsx 1부 7. common_keys_data.xlsx 1부 8. 원격검침 데이터를 활용한 상수관망 모델링(미국수도협회 논문 번역) 1부. 끝

29933159

20231216044008

본청

배급수연구과-10182

D0000049652244