굉장히 엄청나!

데이터 처리 시각화 (2)

https://mzero.tistory.com/89

[Data Wrangling] 데이터 처리 시각화 (1) 글에 이어서 진행

전체 파일 통합하기

• 1건 샘플링 프로세스를 이용하여 전체 파일 통합하기
• 최종 통합 데이더프레임 이름 :  df_bus_card_tot

from datetime import datetime

## 통합 시작 시간
start_date = datetime.today().strftime("%Y-%m-%d %H:%M:%S")

### 최종 통합 데이터프레임 이름 : df_bus_card_tot
df_bus_card_tot = pd.DataFrame()

### 0~79까지 폴더에 접근하기 위한 반복 수행
for i in range(0, 80, 1) :
    file_path = f"./01_data/org/trfcard({i})/trfcard.csv"
    df_bus_card_org = pd.read_csv(file_path)
    # 갯수 잘 반복했는지 확인용
    # print(i, " / ", len(df_bus_card_org))
    
    ### 메타정의서의 영문명, 한글명 컬럼 읽어들이기
    # - 데이터프레임 이름 : df_bus_cart_col_org
    file_path = f'./01_data/org/trfcard({i})/trfcard_columns.xlsx'
    df_bus_card_col_org = pd.read_excel(file_path,
                                        header=2,
                                        usecols="B:C")
    # print(i, " / ", len(df_bus_card_col_org))

    #방법 3
    df_bus_card_col_new_dict = {}
    
    for k, v in zip(df_bus_card_col_org.iloc[:, 0], df_bus_card_col_org.iloc[:, 1]):
        df_bus_card_col_new_dict[k] = v

    ### 컬럼명 변경하기
    # - inplace=True : 변경사항을 메모리에 반영하기
    df_bus_card_org.rename(columns=df_bus_card_col_new_dict, inplace = True)


    ### 데이터프레임 복제하기
    df_bus_card_kor = df_bus_card_org.copy()
    
    ### 1. 승차시각과 하차지각 데이터 타입을 문자열로 변환하기
    # - astype() : 데이터 형변환 함수
    df_bus_card_kor = df_bus_card_kor.astype({'승차시각':'str', '하차시각':'str'})

        df_bus_card = df_bus_card_kor[["승차시각","하차시각","승객연령","환승여부",
                                   "추가운임여부","승차정류장","하차정류장"]].copy()

    ### 3. 승차시각과 하차시각의 데이터타입을 날짜타입으로 변경하기(문자 타입만 datetime으로 변환가능하다.)
    df_bus_card["승차시각"] = pd.to_datetime(df_bus_card_kor.loc[:,"승차시각"])
    df_bus_card["하차시각"] = pd.to_datetime(df_bus_card_kor.loc[:,"하차시각"])

    ### 체류시간(분) 계산 및 컬럼 생성
    df_bus_card["버스내체류시간(분)"] = round((df_bus_card["하차시각"] - \
                                   df_bus_card["승차시각"]).dt.total_seconds()/60, 2)
    ### 5. 기준년도, 기준월, 기준일, 기준시간, 기준시간(분), 컬럼 생성하기
    # - 기준년도
    df_bus_card["기준년도"] = df_bus_card["승차시각"].dt.year
    # - 기준월
    df_bus_card["기준월"] = df_bus_card["승차시각"].dt.month
    # - 기준일
    df_bus_card["기준일"] = df_bus_card["승차시각"].dt.day
    # - 기준시간
    df_bus_card["기준시간"] = df_bus_card["승차시각"].dt.hour
    # - 기준시간(분)
    df_bus_card["기준시간(분)"] = df_bus_card["승차시각"].dt.minute

    #print(f"{i} / {len(df_bus_card)}")

    df_bus_card_tot = pd.concat([df_bus_card_tot, df_bus_card], axis=0, ignore_index=True)

## 통합 종료 시간
end_date = datetime.today().strftime("%Y-%m-%d %H:%M:%S")

print(f"전체 실행 시간 ==> {start_date} ~ {end_date}")
print(f" df_bus_card_tot ==> {len(df_bus_card_tot)}")
df_bus_card_tot

통합 데이터프레임 저장시키기

• 저장 파일 위치 : all 폴더
• 저장 파일 명 : 데이터프레임 변수명과 동일
• 확장자 : csv

save_path = "./01_data/all/df_bus_card_tot.csv"
df_bus_card_tot.to_csv(save_path, index = False)

데이터 처리 시각화 (1)

🍀 포항시 버스데이터 수집 가공 시각화 

• URL : 국가교통 데이터 오픈마켓
• 데이터 명 : 포항시 BIS 교통카드 사용내역

🍀 메타 정의서

샘플링

🍀 1 건 샘플링하기

• 여러개의 파일 데이터를 통합하는 경우에는 한개 파일을 기준으로 사용할 컬럼을 정의하여 가공 후 반복 처리하면 편하다.
  
  
• 0번 파일의 csv 데이터 읽어오기
  - 데이터프레임 이름 : df_bus_cart_org

file_path = "./01_data/org/trfcard(0)/trfcard.csv"
df_bus_card_org = pd.read_csv(file_path)
df_bus_card_org.head(1)

🍀 결측치가 있는지 정보확인하기

df_bus_card_org.info()

• 행의 갯수 16185
• 열의 갯수 13
• age_type 결측치 존재

컬럼명 수정하기

🍀영문 컬럼명을 한글로 수정하기

• 데이터프레임 이름 : df_bus_cart_col_org

🍀메타정의서의 영문명, 한글명 컬럼 읽어들이기

file_path = "./01_data/org/trfcard(0)/trfcard_columns.xlsx"
df_bus_card_col_org = pd.read_excel(file_path,
                                   header=2,
                                   usecols="B:C")
df_bus_card_col_org

🍀df_bus_cart_col_org의 데이터를 딕셔너리로 변환하기

• 컬럼명의 이름을 매핑하여 변경하기 위해서는 컬럼정의 값을 key : value 딕셔너리 타입으로 정의해야 함
  - 예시 : {영문명 : 한글명, 영문명 : 한글명....}
• Index로 값 조회해보기
  •  iloc[행, 열] index location : 인덱스 번호를 이용하는 방식
    - 특정 위치에 접근할 때 iloc를 많이 사용한다
  • loc[행값, 열값] : 눈에 보이는 인덱스 값을 이용하는 방식

print(df_bus_card_col_org.iloc[0, 0])
print(df_bus_card_col_org.iloc[0, 1])
print(df_bus_card_col_org.iloc[1, 0])
print(df_bus_card_col_org.iloc[1, 1])
print("-----------------------------")

print(df_bus_card_col_org.loc[0, "컬럼명 (영문)"])
print(df_bus_card_col_org.loc[0, "컬럼명 (한글)"])

• 딕셔너리 변수명 : df_bus_card_col_new_dict
• 영문명은 key로, 한글명은 value로 만들기
  - 예시  : {'on_date' : '승차시각', 'off_date' : '하차시간'}
• 방법 1

df_bus_card_col_new_dict = dict(zip(df_bus_card_col_org["컬럼명 (영문)"],
                                    df_bus_card_col_org["컬럼명 (한글)"]))
print(df_bus_card_col_new_dict)​

• 방법 2

df_bus_card_col_new_dict = {}
for k, v in zip(df_bus_card_col_org.iloc[:, 0], df_bus_card_col_org.iloc[:, 1]) :
    # print(k,v)
    df_bus_card_col_new_dict[k] = v
df_bus_card_col_new_dict​

🍀컬럼명 변경하기

• 메모리에 반영되지 않고 보이는 것만 바꿈
  

  df_bus_card_org.rename(columns=df_bus_card_col_new_dict)​

↓원본을 조회해보면 컬럼은 바뀌지 않았다.

• inplace = True로 변경사항을 메모리에 반영하기
  원본까지 컬럼이 변경 된다.

df_bus_card_org.rename(columns=df_bus_card_col_new_dict, inplace = True)

데이터 분석하기

🍀분석 주제

• 대주제 : 포항시 버스 이용량 분석
• 소주제
     <버스 이용량 분석>
     * 기준월 및 기준일자별 버스 이용량 분석 비교
     * 기준일 및 시간대별 버스 이용량 분석 비교
     * 기준시간 및 시간(분)별 버스 이용량 분석 비교
  
     <버스 내 체류시간 분석>
     * 기준월 및 기준일자별 버스 체류시간(하차시간 - 승차시간) 분석 비교
     * 기준일 및 시간대별 버스 체류시간 분석 비교
     * 기준시간 및 시간(분)별 버스 체류시간 분석 비교
     
     * 승하차정류장 구간별(정류장 체류시간의 합) 버스 내 체류시간
      - 체류시간(분) 상위 30건 분석 비교

🍀 분석을 위한 데이터 가공하기

• 데이터 프레임 복제하기

df_bus_card_kor = df_bus_card_org.copy()

• 승차시각과 하치시간 데이터 타입을 문자열로 변환하기
  - astype() : 데이터 형변환 함수. replace 지원안하기 때문에 바뀐 형상을 자기 자신에게 반영시켜야 한다.

df_bus_card_kor = df_bus_card_kor.astype({"승차시각" : "str",
                                          "하차시각" : "str"})
df_bus_card_kor.info()

승차시각과 하차시각의 Dtype이 object로 변환

• 분석에 필요한 컬럼 추출하기

df_bus_card = df_bus_card_kor[["승차시각", "하차시각", "승객연령", "환승여부",
                               "추가운임여부", "승차정류장", "하차정류장"]].copy()
df_bus_card.head()

• 승차시각과 하차시각의 데이터 타입을 날짜타입으로 변경하기
  - 문자만 날짜 타입으로 바꿀 수 있음

df_bus_card["승차시각"] = pd.to_datetime(df_bus_card_kor.loc[:, "승차시각"])
df_bus_card["하차시각"] = pd.to_datetime(df_bus_card_kor.loc[:, "하차시각"])
df_bus_card.info()

승차시각과 하차시각의 Dtype이 datetime으로 변환

🍀 가공한 데이터 새로운 컬럼으로 만들기

• 버스 내 체류시간(분단위) 컬럼만들기
  - 컬럼명 : 버스내 체류시간(분)
  
  

(df_bus_card.iloc[0, 1] - df_bus_card.iloc[0, 0])

• 위의 결과에서 total_seconds를 사용해 결과를 초단위로 변환하고 60을 나누어 분으로 나타난다.

round((df_bus_card.iloc[0, 1] - df_bus_card.iloc[0, 0]).total_seconds()/60,2)

• 체류시간(분) 계산 및 컬럼 생성
  - .dt : 행 하나하나를 date type으로 바꿔서 실행

df_bus_card["버스내체류시간(분)"] = round((df_bus_card["하차시각"] - \
                                   df_bus_card["승차시각"]).dt.total_seconds()/60,2)
df_bus_card

• 기준년도, 기준월, 기준일, 기준시간, 기준시간(분) 컬럼 생성하기

# - 기준년도
# .dt.year : 날짜 타입에서 연도만 추출
df_bus_card["기준년도"] = df_bus_card["승차시각"].dt.year

# - 기준월
df_bus_card["기준월"] = df_bus_card["승차시각"].dt.month

# - 기준일
df_bus_card["기준일"] = df_bus_card["승차시각"].dt.day

# - 기준시간
df_bus_card["기준시간"] = df_bus_card["승차시각"].dt.hour

# - 기준시간(분)
df_bus_card["기준시간(분)"] = df_bus_card["승차시각"].dt.minute
df_bus_card

DB 프로그램 실습

🍀 DB프로그램

• X : 입력 값 바뀜
• △ : 고정으로 둘 수도 있고, 안 둘 수도 있다
• 나머지 : 무조건 고정 → class로 설정
  
  1. 데이터베이스 설정 정보 준비
  2. DB 접속하기 : 커넥션(connect)
  3. 커서 받아오기 : cursor()
  4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)  --- X
  5. 구문 실행하기 :  execute() -> 결괏값은 체크 --- △
  6. 조회인 경우 커서에서 데이터 추출하기 --- △
          → 한건 : fetchone()
          → 여러 건 : fetchall()
  7. 조회결과 데이터프레임 또는 웹페이지에 출력 --- △
      →  데이터프레임 : pd.DataFrame(여러 건), pd.DataFrame([한건])
  8. 조회가 아닌 경우 : 처리 결과 조건으로 확인하기 if "처리 성공" else "처리 실패" --- △
  9. DB정보 반환하기
          - 반환 순서 : cursor > connect

🍀 class 설정

1. 클래스 생성
   - 클래스 이름 : PowerClass
2. 클래스 내에 함수들 정의
3. 외부에서 조회/입력/수정/삭제 처리하기

🍀 작성한 코드

class PowerClass:
    def __init__(self):
        self.conn = None
        self.host = "localhost"
        self.user = "gjuser"
        self.password = "dbdb"
        self.db = "gjdb"
        self.charset = "utf8"
        self.cursorclass = pymysql.cursors.DictCursor
        self.autocommit = True
        
    def connect(self):
        try:
            self.conn = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                db=self.db,
                charset=self.charset,
                autocommit=self.autocommit,
                cursorclass=self.cursorclass
            )
            print("DB접속 성공 >>> ", self.conn)
        except Exception as e:
            print("DB Server Checking...", e)

    
    def cursor(self):
        cur = self.conn.cursor()
        return cur

    def close(self):
        self.cur = self.conn.cursor()
        try:
            self.cur.close()
            self.conn.close()
            print("접속종료")
        except :
            print("이미 모든 커서와 접속 정보가 반납되었습니다.")

def read(sql, cur):
    if sql[:6] == "Select" :
        cur.execute(sql)
        rows = cur.fetchall()
        ymd_power_df = pd.DataFrame(rows)
        print(ymd_power_df)
    else:
        print('다시입력')

def insert(sql, cur):
    if sql[:6] == "Insert" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 입력되었습니다.')
        else:
            print(f'입력되지 않았습니다')
    else:
        print('다시입력')

def update(sql, cur):
    if sql[:6] == "Update" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 수정되었습니다.')
        else:
            print(f'수정되지 않았습니다')
    else:
        print('다시입력')
            

def delete(sql, cur):
    if sql[:6] == "Update" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 삭제되었습니다.')
        else:
            print(f'삭제되지 않았습니다')
    else :
        print('다시입력')

def main():
    db = PowerClass()
    db.connect()
    cur = db.cursor()
     
    
    while True:
        choice = input("""
                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                        """)
        

        if choice == "1":
            sql = input("""
                        sql 작성하기
                    """)
            read(sql, cur)
               
        elif choice == "2":
            sql = input("""
                        sql 작성하기
                    """)
            insert(sql, cur)
                                                                                              
        elif choice == "3":
            sql = input("""
                        sql 작성하기
                    """)
            update(sql, cur)
        
        elif choice == "4":
            sql = input("""
                        sql 작성하기
                    """)
            delete(sql, cur)

        elif choice == "5":
            db.close()
            break
               
        else:
            print("다시 선택해주세요")

if __name__ == "__main__":
    main()

DB접속 성공 >>>  <pymysql.connections.Connection object at 0x000001E355B99150>

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         2

                        sql 작성하기
                     Insert Into time_power_demand(         ymd, time, power     ) Values (         '2020-12-31', '24시', 1234     )
1건이 입력되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
          ymd time   power
0  2020-12-31  24시  1234.0

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         3

                        sql 작성하기
                     Update time_power_demand set power = 5679         Where ymd = '2020-12-31'             And time = '24시'
1건이 수정되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
          ymd time   power
0  2020-12-31  24시  5679.0

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         4

                        sql 작성하기
                     Delete         From time_power_demand         Where ymd = '2020-12-31'             And time = '24시'
1건이 삭제되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
Empty DataFrame
Columns: []
Index: []

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         5
접속종료

DB 프로그램 순서

1. 데이터베이스 설정 정보 준비
2. DB 접속하기 : 커넥션(connect)
3. 커서 받아오기 : cursor()
4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)
5. 구문 실행하기 :  execute() -> 결과값은 체크
6. 조회인 경우 커서에서 데이터 추출하기
        -> 한건 : fetchone()
        -> 여러건 : fetchall()
7. 조회결과 데이터프레임 또는 웹페이지에 출력
    -> 데이터프레임 : pd.DataFrame(여러건), pd.DataFrame([한건])
8. 조회가 아닌 경우 : 처리 결과 조건으로 확인하기 if "처리 성공" else "처리 실패"
9. DB정보 반환하기
        - 반환 순서 : cursor > connect
    

데이터 조회하기

🍀 데이터베이스 연결

import pymysql
### 접속 정보
# - 접속 허가를 위한 정보
# - 접속 id or 도메인
host = "localhost"
# - 사용자계정
user = "gjuser"
# - 패스워드
password = "dbdb"
# - 데이터베이스명
db = "gjdb"
# - 한글처리
charset = "utf8"
# - 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor

🍀 DB 접속하기

• connect로 서버에 연결 허가를 요청하여 conn에 허가를 받아 옴

try :
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           charset = charset,
                           # autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속 성공 >>> ", conn)
except :
    print("DB Server Checking...")

🍀 커서 받아오기

cur = conn.cursor()
cur

 🍀 Select 문 생성 후 DB에게 요청 및 응답 받기

• execute가 "Select * from time_power_demand"라는 요청문을 서버에게 가지고 가서 그에 대한 답을 가져온다.

sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

🍀 조회결과가 여러 건인 경우

• non 맵핑 방식
• df.to_sql은 orm( object relation mapping, 객체 맵핑 방식)
• cursorclass설정 시 딕셔너리 형태로 컬럼명과 같이 가져옴
• cursorclass설정 안하면 튜플로 가져와서 컬럼명 없이 값만 나옴
  - [(), ()...] 

rows = cur.fetchall()
rows

🍀 조회결과 데이터프레임으로 출력

• cursorclass설정을 안해서 컬럼명 없이 받아온 경우
  - ymd_power_df = pd.DataFrame(rows, columns=["ymd", "time", "power"]) 처럼 컬럼명 지정해서 코드 작성

ymd_power_df = pd.DataFrame(rows)
ymd_power_df

🍀 조회결과가 한 건인 경우

• 한건 조회를 위한 sql 구문 생성하기
• sql 구문 쓸 때 컴마 뒤에 띄어쓰기 꼭 하기

sql = """
        Select * From time_power_demand
        Where ymd = '2021-01-01'
            And time = '1시'
"""
sql

• execute로 sql 구문을 DB서버에 요청하고, 결과 받아오기 

cur.execute(sql)

한 건의 결과가 나왔다는 의미

• 받아오고 데이터 추출하기

row = cur.fetchone()
row

• 데이터프레임에 담아서 보여주기
  - 데이터가 한 건만 추출 될 때는 딕셔너리 타입으로 추출되기 때문에 데이터프레임에 데이터를 담을 때는 리스트 타입으로 넣어야함

ymd_power_df = pd.DataFrame([row])
ymd_power_df

데이터 입력하기

🍀 입력을 위한 구문 생성하기

sql = """
    Insert Into time_power_demand(
        ymd, time, power
    ) Values (
        '2020-12-31', '24시', 1234
    )
"""
sql

🍀 구문 처리하고 결과 값 받아오기

• 저장 요청 시에는 결과값이 숫자값으로 반환된다
  - 몇 건이 수행 됐는지 숫자값으로 나옴

rs_cnt = cur.execute(sql)
rs_cnt
print(f'{rs_cnt}건이 처리되었습니다')

🍀 처리가 잘 되었는지 확인하기

• 0보다 크면 '1건이 입력되었습니다.' 출력
• 0이하인 경우에는  "입력되지 않았습니다." 출력 

if rs_cnt > 0 :
    print(f'{rs_cnt}건이 입력되었습니다.')
else :
    print(f'입력되지 않았습니다')

데이터 수정하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 power의 값을 5678로 수정하기

sql = """
        Update time_power_demand set power = 5679
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 수정되었습니다.')
else :
    print(f'수정되지 않았습니다')

데이터 삭제하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 삭제하기

sql = """
        Delete
        From time_power_demand
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 삭제되었습니다.')
else :
    print(f'삭제되지 않았습니다')

데이터 조회 문제

🍀조회해보기

• 한번도 구매한적이 없는 회원정보를 조회하기
• 조회 컬럼 : 회원아이디, 회원이름

sql = """
    SELECT mem_id, mem_name
    FROM member
    WHERE not exists (SELECT cart_member FROM cart
WHERE cart_member = mem_id)
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 조회되었습니다.')
else :
    print(f'조회되지 않았습니다')

• 커서가 가지고 있는 결과 추출하여 데이터프레임 형식으로 나타내기

rows = cur.fetchall()
noMember = pd.DataFrame(rows)
noMember

종료하기

🍀커서 및 커넥션 정보 반납(종료)하기

try:
    cur.close()
    conn.close()
except :
    print("이미 모든 커서와 접속 정보가 반납되었습니다.")

데이터 설계하기

🫐 테이블 설계하기

• 테이블명, 컬럼명, 컬럼타입, null 여부에 대한 정의가 필요함
• 산출물
  - 테이블 정의서
  - ERD
  - 스크립트 명세서

🫐 테이블 정의서

• 자료형 길이 설정 시 한글은 *2
• double
  소숫점을 가지는 많은 양의 데이터
  정밀도를 요구하는 데이터
  소숫점 아래 값이 많은 데이터

🫐 스크립트 명세서

• SQL에 그대로 작성하여 테이블 생성

Create Table time_power_demand(
	ymd varchar(10) NOT NULL,
   time varchar(4) NOT NULL,
	power DOUBLE NOT null
);

DB 연결하기

🫐 데이터 저장시에 유용한 라이브러리

• sqlalchemy
• 설치 : pip install sqlalchemy

🫐  데이터 저장시에 유용한 라이브러리

• pymysql
• 설치 : conda install -c conda-forge pymysql
• 설치 : pip install pymysql

🫐 Database 연결하기

from sqlalchemy import create_engine

### 연결정보 작성
db_connection_info = "mysql+pymysql://gjuser:dbdb@localhost:3306/gjdb"

### Database 연결하기
db_connection = create_engine(db_connection_info)
db_connection

데이터 프레임을 Table에 저장시키기

🫐 컬럼명 수정

• 데이터프레임의 컬럼명을 테이블의 컬럼명과 같게 수정
• 특정 컬럼명만 수정하고자 할 때
  → df.columns = [{"년도" : "ymd"}]

# - 데이터프레임의 컬럼명 전체수정하기
df.columns = ["ymd", "time", "power"]
df

🫐 데이터 저장하기

df.to_sql(name="time_power_demand",
         con=db_connection,
         index=False,
         if_exists="append")

8760 rows 저장 완료

SQL 조회시 값이 들어가 있는 것을 확인할 수 있다.

🫐 데이터베이스 자원 반환(접속 끊기)

• 연결 종료하기

db_connection.dispose()

데이터 조회하기

🫐 데이터 조회

• 조회시에는 pymysql 라이브러리 사용

🫐데이터베이스 연결

import pymysql
### 접속 정보
# - 접속 허가를 위한 정보
# - 접속 id or 도메인
host = "localhost"
# - 사용자계정
user = "gjuser"
# - 패스워드
password = "dbdb"
# - 데이터베이스명
db = "gjdb"
# - 한글처리
charset = "utf8"
# - 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor

🫐 DB 접속하기

• connect로 서버에 연결 허가를 요청하여 conn에 허가를 받음

try :
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           charset = charset,
                           # autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속 성공 >>> ", conn)
except :
    print("DB Server Checking...")

🫐 커서 받아오기

cur = conn.cursor()
cur

🫐 Select 문 생성 후 DB에게 요청 및 응답 받기

• execute가 "Select * from time_power_demand"라는 요청문을 서버에게 가지고 가서 그에 대한 답을 가져 

sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

🫐 커서 및 커넥션 정보 반납(종료)하기

try:
    cur.close()
    conn.close()
except :
    print("이미 모든 커서와 접속 정보가 반납되었습니다.")

데이터 수집하기

🫐 한국전력거래소 시간별 전력수요량 데이터 수

• 수집위치 : data.go.kr
• 한국전력거래소 시간별 전력수요량 csv 파일 다운로드

데이터 불러들이기

🫐 데이터 프레임 변수명 : df

• df = pd.read_csv(file_path)
  → 이렇게 작성하면 UnicodeDecodeError 에러
  → 인코딩 필요
• pandas는 기본적으로 utf-8
• 메모장 저장될 때 기본 utf-8, 공공데이터 저장 ansi
• euc-kr 한국어 타입으로 인코딩

import pandas as pd
file_path = "./01_data/한국전력거래소_시간별 전력수요량_20211231.csv"
df = pd.read_csv(file_path, encoding= "euc-kr")

df

🫐 결측데이터 확인

df.info()

- 결측치는 없다

🫐 이상치 데이터 확인
- 측정된 데이터 이므로 min을 보는게 적절하다
- min이 0 이하로 떨어지면 이상치

df.describe()

🫐 컬럼명의 시간을 데이터화 하기 위하여 컬럼명 추출하기

df.columns

🫐 변수로 받기

col_list = df.columns
col_list

데이터 프레임 생성하기

🫐 3개의 열 (년도, 시간, 전력량)을 가지는 데이터프레임 생성하기

result_df = pd.DataFrame(columns=["년도", "시간", "전력량"])
result_df

🫐데이터프레임에 데이터 행단위 추가하기

• items() : 튜플로 만들어서 넘겨주면 그 안에 갯수만큼 왼쪽의 변수들이 각각 받아준다.
• print(ymd, "/", time, "/", value) : 2021-12-31 / 24시 / 70123 까지 추출된다.
• concat() : 데이터프레임과 데이터프레임을 행단위(axis = 0) 또는 칼럼단위(axis = 1)로 추가할 때 사용
• ignore_index=True : 행이 추가될 때 행 인덱스 번호를 자동증가 시키기 (default = false)

### 데이터프레임에 데이터 행단위 추가하기
result_df = pd.DataFrame(columns=["년도", "시간", "전력량"])

for index, row in df.iterrows() :
    # print(row)
    ### 년도 데이터
    ymd = row[col_list[0]] # 날짜
    
    ### 시간과 전령량이 합쳐진 데이터
    data = row[col_list[1:]] # 1시 부터 24시 까지. 모든 시간대
    # print(data)

    ### 시간과 전력량을 각각 추출하여 데이터프레임에 넣기
    # - 넣을 값 : 날짜, 시간, 잔력량
    for time, value in data.items() :
        # print(ymd, "/", time, "/", value)

        ### 행단위로 데이터프레임에 추가하기 위해서 추가할 행을 데이터프레임으로 생성
        df_temp = pd.DataFrame({"년도":[ymd], "시간":[time], "전력량":[value]})

        ### 데이터프레임에 행단위로 추가하기
        result_df = pd.concat([result_df, df_temp], axis=0, ignore_index=True)
        
### 최종결과 출력
result_df

🫐 정제된 데이터 파일로 저장하기

• 저장할 경로 지정
• index=False : 인덱스 번호값은 저장하지 않기(default = True)

save_path = "./01_data/new_data.csv"
result_df.to_csv(save_path, index = False)

🫐 저장한 데이터 파일 읽어들이기

file_path = "./01_data/new_data.csv"
df = pd.read_csv(file_path)
df