데이터 전처리 연습

 

### 커서 받아오기
cur = conn.cursor()
cur

<pymysql.cursors.DictCursor at 0x208e1e39010>

 

 

rows = cur.fetchall()
rows

[{'ymd': '2021-01-01', 'time': '1시', 'power': 64942.0},
 {'ymd': '2021-01-01', 'time': '2시', 'power': 62593.0},
 {'ymd': '2021-01-01', 'time': '3시', 'power': 60905.0},
 {'ymd': '2021-01-01', 'time': '4시', 'power': 59889.0},
 {'ymd': '2021-01-01', 'time': '5시', 'power': 59638.0},
 {'ymd': '2021-01-01', 'time': '6시', 'power': 59936.0},
 {'ymd': '2021-01-01', 'time': '7시', 'power': 60193.0},
 {'ymd': '2021-01-01', 'time': '8시', 'power': 59982.0},
 {'ymd': '2021-01-01', 'time': '9시', 'power': 59626.0},
 {'ymd': '2021-01-01', 'time': '10시', 'power': 57904.0},
 {'ymd': '2021-01-01', 'time': '11시', 'power': 57604.0},

 

ymd_power_df = pd.DataFrame(rows)
# - 컬럼명 없이 받아온 경우
# ymd_power_df = pd.DataFrame(rows,columns=["ymd","time","power"]
ymd_power_df

 

2021-01-01	1시	64942.0
2021-01-01	2시	62593.0
2021-01-01	3시	60905.0
2021-01-01	4시	59889.0
2021-01-01	5시	59638.0
...	...	...
2021-12-31	21시	71602.0
2021-12-31	22시	69383.0
2021-12-31	23시	68874.0
2021-12-31	24시	70123.0
2020-12-31	24시	1234.0

8761 rows × 3 columns

 

### 한건 조회를 위한 sql 구문 생성하기
sql = """
        Select * From time_power_demand
        Where ymd = '2021-01-01'
            And time = '1시'
     """
sql

 

 

### SQL 구문을 DB서버에 요청하고, 결과 받아오기
cur.execute(sql)

1

 

### 1건 데이터 추출하기
row = cur.fetchone()
row

 

{'ymd': '2021-01-01', 'time': '1시', 'power': 64942.0}

### 데이터 프레임에 담아서 보여주기
ymd_power_one = pd.DataFrame([row])
ymd_power_one

2021-01-01

1시

64942.0

 

### 입력을 위한 구문 생성하기
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("입력되지 않았습니다")

1건이 처리되었습니다.

 

수정하기 처리하기

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

 

sql = """
    update time_power_demand
    set power = '5678'
    where ymd = '2020-12-31' and
    time = '24시' 
    
"""
sql

 

 

### SQL 구문을 DB서버에 요청하고, 결과 받아오기
cur.execute(Sql2)

2

cur.fetchall()

 

[{'ymd': '2021-01-01', 'time': '1시', 'power': 64942.0}]

 

rs_cnt = cur.execute(sql)
if rs_cnt >0:
    print(f"{rs_cnt}건이 처리되었습니다.")
else:
    print("수정 실패")

 

2건이 처리되었습니다.

 

삭제하기

sql = """ delete from time_power_demand
            where ymd ='2020-12-31'
            and time='24시'
            """
sql

 

rs_cnt=cur.execute(sql)
if rs_cnt > 0:
    print(f"{rs_cnt}건이 삭제되었습니다.")
else:
    print("삭제실패...")

2건이 삭제되었습니다.

 

### pymysql 하면 nonmapping 방식으로 여러번해야하기 때문에 to_sql로 orm 방식으로 매핑방식으로 밀어넣을 수 있다. 그럴경우 테이블 컬럼명을 일치시켜야한다
### 둘 다 할 줄 알아야한다. jpa

 

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

 

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

 

# mvc
# model 데이터 가공 처리
# view
# controller
# mvt  v=controller
# t - view

 

 

"""<포항시 bis 교통카드 사용내역 데이터 수집>"""

한 건 샘플링하기

"""
- 여러개의 파일 데잍터를 통합하는 경우에는
- 한개 파일을 기준으로 사용할 컬럼을 정의하여 가공 후
- 반복 처리하면 편합니다.
"""

 

### 0번 파일의 csv 데이터를 읽어들이기
# - 데이터 프레임 이름 : df_bus_card_org
file_path = "./01_data/org/trfcard(0)/trfcard.csv"
df_bus_card_org = pd.read_csv(file_path )
df_bus_card_org.info()

 

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 16185 entries, 0 to 16184
Data columns (total 13 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   on_date      16185 non-null  int64  
 1   off_date     16185 non-null  int64  
 2   route_name   16185 non-null  object 
 3   descr        16185 non-null  object 
 4   age_type     16164 non-null  object 
 5   trans_yn     16185 non-null  object 
 6   addfee_yn    16185 non-null  object 
 7   start_bstop  16185 non-null  object 
 8   start_gps_x  16185 non-null  float64
 9   start_gps_y  16185 non-null  float64
 10  end_bstop    16185 non-null  object 
 11  end_gps_x    16185 non-null  float64
 12  end_gps_y    16185 non-null  float64
dtypes: float64(4), int64(2), object(7)
memory usage: 1.6+ MB

 

df_bus_card_org.describe()

 

	on_date	off_date	start_gps_x	start_gps_y	end_gps_x	end_gps_y
count	1.618500e+04	1.618500e+04	16185.000000	16185.000000	16185.000000	16185.000000
mean	2.020010e+13	2.020010e+13	129.369118	36.034985	129.368939	36.031927
std	4.127644e+04	4.175148e+04	0.048054	0.043280	0.040777	0.033987
min	2.020010e+13	2.020010e+13	129.052958	35.804119	129.052958	35.804119
25%	2.020010e+13	2.020010e+13	129.350290	36.012537	129.353856	36.013250
50%	2.020010e+13	2.020010e+13	129.365461	36.031720	129.365281	36.034110
75%	2.020010e+13	2.020010e+13	129.383462	36.064088	129.370823	36.046050
max	2.020010e+13	2.020010e+13	129.578137	36.254158	129.578137	36.244762

 

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

### 메타정의서의 영문명, 한글명 컬럼 읽어들이기
#  - 데이터프레임 이름: df_bus_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.head(1)
df_bus_card_col_org

 

	컬럼명 (영문)	컬럼명 (한글)
0	on_date	승차시각
1	off_date	하차시각
2	route_name	노선명
3	descr	노선설명
4	age_type	승객연령
5	trans_yn	환승여부
6	addfee_yn	추가운임여부
7	start_bstop	승차정류장
8	start_gps_x	승차정류장 GPS X
9	start_gps_y	승차정류장 GPS Y
10	end_bstop	하차정류장
11	end_gps_x	하차정류장 GPS X
12	end_gps_y	하차정류장 GPS Y

 

### 컬럼명의이름을 매핑하여 변경하기 위해서는
# 컬럼명의 값을 key: value 딕셔너리 타입으로 정의해야함
# 예시 : {영문명: 한글명, 영문명: 한글명...}
# df_bus_cart_col_org의 데이터를 딕셔너리로 변경하기
# iloc [행번호,열번호]:인덱스 번호를 
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("-------------")
# df_bus_card_col_org 데이터프레임을 딕셔너리로 변환
# 딕셔너리 변수명: df_bus_card_col_new_dict
# 영문명은 key로 한글명은 value로 만들어주세요
# 예시 : {'on_date' : '승차시각', 'off_date': '하차시각'}
 

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[0, df_bus_card_col_org.columns.get_loc("컬럼명 (영문)")])
print(df_bus_card_col_org.loc[0,"컬럼명 (한글)"])
df_bus_card_col_org.columns

 

on_date
승차시각
off_date
하차시각
-------------
on_date
승차시각
승차시각

Index(['컬럼명 (영문)', '컬럼명 (한글)'], dtype='object')

[

 

# df_bus_card_org

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
df_bus_card_col_new_dict

 

{'on_date': '승차시각',
 'off_date': '하차시각',
 'route_name': '노선명',
 'descr': '노선설명',
 'age_type': '승객연령',
 'trans_yn': '환승여부',
 'addfee_yn': '추가운임여부',
 'start_bstop': '승차정류장',
 'start_gps_x': '승차정류장 GPS X',
 'start_gps_y': '승차정류장 GPS Y',
 'end_bstop': '하차정류장',
 'end_gps_x': '하차정류장 GPS X',
 'end_gps_y': '하차정류장 GPS Y'}

 

### 칼럼명 변경하기
df_bus_cart_org.rename(columns=df_bus_card_col_new_dict) # 실제 값은 안바뀜
df_bus_cart_org

 

 

	승차시각	하차시각	노선명	노선설명	승객연령	환승여부	추가운임여부	승차정류장	승차정류장 GPS X	승차정류장 GPS Y	하차정류장	하차정류장 GPS X	하차정류장 GPS Y
0	20200102051049	20200102051844	131	양덕-송도-시청-양학-양덕	일반	N	N	양덕차고지	129.401693	36.077258	동부초등학교	129.380866	36.062209
1	20200102051338	20200102054549	175	문덕-고속터미널-선린병원-달전	일반	N	N	문덕사거리	129.403178	35.962298	새마을금고해도지점	129.370626	36.015468
2	20200102051549	20200102051930	160	문덕-대송-송도-죽도-용흥	일반	N	N	문덕사거리	129.403442	35.962846	성우오토모티브	129.384492	35.972675
3	20200102051617	20200102055046	175	문덕-고속터미널-선린병원-달전	일반	N	N	부영사랑3차	129.402520	35.973038	오거리	129.366312	36.031816
4	20200102051841	20200102052310	102	문덕-남구청-고속터미널-북구보건소-양덕	일반	N	N	문덕온천	129.406532	35.961964	용덕사거리	129.415174	35.972826
...	...	...	...	...	...	...	...	...	...	...	...	...	...
16180	20200102232423	20200102233802	102	문덕-남구청-고속터미널-북구보건소-양덕	일반	Y	N	죽도시장	129.365486	36.034110	문화예술회관	129.365551	36.010599
16181	20200102232449	20200102232657	200	양덕-남구청(야구장)-구룡포	일반	N	N	형산로터리	129.368470	36.011630	뱃머리 평생학습원	129.354538	36.009020
16182	20200102232849	20200102233230	500	문덕-시외터미널-흥해-청하-월포	일반	N	N	남부경찰서	129.344637	36.005408	연일전통시장	129.349210	35.997120
16183	20200102233518	20200102235624	107	문덕-시외(고속)터미널-흥해	일반	N	N	쌍용사거리	129.354637	36.015728	화이트빌라	129.406610	35.957090
16184	20200102235936	20200103000017	108	양덕-죽도-양학-시청-유강-지곡	일반	N	N	장량휴먼시아	129.384057	36.090840	법원검찰청	129.387075	36.090211
16185 rows × 13 columns

 

 

df_bus_card_org.rename(columns=df_bus_card_col_new_dict, inplace=True) # 실제 값은 안바뀜
df_bus_card_org

	승차시각	하차시각	노선명	노선설명	승객연령	환승여부	추가운임여부	승차정류장	승차정류장 GPS X	승차정류장 GPS Y	하차정류장	하차정류장 GPS X	하차정류장 GPS Y
0	20200102051049	20200102051844	131	양덕-송도-시청-양학-양덕	일반	N	N	양덕차고지	129.401693	36.077258	동부초등학교	129.380866	36.062209
1	20200102051338	20200102054549	175	문덕-고속터미널-선린병원-달전	일반	N	N	문덕사거리	129.403178	35.962298	새마을금고해도지점	129.370626	36.015468
2	20200102051549	20200102051930	160	문덕-대송-송도-죽도-용흥	일반	N	N	문덕사거리	129.403442	35.962846	성우오토모티브	129.384492	35.972675
3	20200102051617	20200102055046	175	문덕-고속터미널-선린병원-달전	일반	N	N	부영사랑3차	129.402520	35.973038	오거리	129.366312	36.031816
4	20200102051841	20200102052310	102	문덕-남구청-고속터미널-북구보건소-양덕	일반	N	N	문덕온천	129.406532	35.961964	용덕사거리	129.415174	35.972826
...	...	...	...	...	...	...	...	...	...	...	...	...	...
16180	20200102232423	20200102233802	102	문덕-남구청-고속터미널-북구보건소-양덕	일반	Y	N	죽도시장	129.365486	36.034110	문화예술회관	129.365551	36.010599
16181	20200102232449	20200102232657	200	양덕-남구청(야구장)-구룡포	일반	N	N	형산로터리	129.368470	36.011630	뱃머리 평생학습원	129.354538	36.009020
16182	20200102232849	20200102233230	500	문덕-시외터미널-흥해-청하-월포	일반	N	N	남부경찰서	129.344637	36.005408	연일전통시장	129.349210	35.997120
16183	20200102233518	20200102235624	107	문덕-시외(고속)터미널-흥해	일반	N	N	쌍용사거리	129.354637	36.015728	화이트빌라	129.406610	35.957090
16184	20200102235936	20200103000017	108	양덕-죽도-양학-시청-유강-지곡	일반	N	N	장량휴먼시아	129.384057	36.090840	법원검찰청	129.387075	36.090211
16185 rows × 13 columns

 

"""
<분석주제>

-대주제 : 포항시 버스 이용량 분석
-소주제: 
(버스 이용량 분석)
* 기준월 및 기준일자별 버스 이용량 분석 비교
* 기준일 및 시간대 별 버스 이용량 분석 비교
* 기준시간 및 시간(분)별 버스 이용량 분석 비교


(버스 내 체류시간 분석)
*버스 내 체류시간
*승하차정류장 구간별 버스 내 체류시간
*기준시간 및 시간(분)별 버스 체류시간 분석 비교

*승하차정류장 구간별 버스 내 체류시간
-체류시간 (분) 상위 30건 분석비교
"""

 

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

 

### 승차시각과 하차시각 데이터 타입을 문자열로 변환하가
df_bus_card_kor = df_bus_card_org.copy()
### 승차시각과 하차시각 데이터 타입을 문자열로 변환하기
# astype() : 데이터 형변환 함수
df_bus_card_kor= df_bus_card_kor.astype({"승차시각":"str", "하차시각":"str"})
df_bus_card_kor.info()

 

### 분석에 필요한 칼럼 추출하기 
df_bus_card = df_bus_card_kor[["승차시각", "하차시각", "승객연령", "환승여부", "추가운임여부", "승차정류장","하차정류장"]].copy()# 두개이상 할때는 한번더 []
df_bus_card

### 승차시각과 하차시강의 데이터타입을 날짜타입으로 변경하기
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()

### 버스 내 체류시간 (분단위) 컬럼만들기
# 칼럼명 : 버스 내 체류시간 (분)
round((df_bus_card.iloc[0,1]- df_bus_card.iloc[0,0]).total_seconds()/60,2)

 

### 체류 시간 (분) 계산 및 칼럼 
df_bus_card["버스내체류시간(분)"]=round((df_bus_card["하차시각"]- df_bus_card["승차시각"]).dt.total_seconds()/60,2)  # 시리즈 타입, 컬럼명이 없는 데이터 타입,튜플형태
df_bus_card

테이블에 버스체류시간 칼럼을 추가했다.

### 기준년도, 기준월, 기준일 ,기준시간, 기준시간분 칼럼 생성하기
# 기준년도
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

그외 필요한 칼럼을 추가해주었다.

 

 

내일 할 것

###  전체파일 통합하기
# 위한건 샘플 프로세스를 이용하여 전체파일 통합하기
### 최종 통합 데이터 프레임 이름 :df_bus_card_tot