[DB] 데이터베이스 실습 - 버스교통카드 데이터 수집 가공

데이터 파일 다운로드👻

[포항시 BIS 교통카드 사용내역 데이터 수집]
 1. URL : 국가교통 데이터 오픈마켓
 2. 로그인 후 "포항시 BIS 교통카드 사용내역" 검색
 3. 상품 다운로드 >> 200개씩 보기 >> 전체선택 >> 파일 다운로드 >> 80개 압축파일 다운

 

 

한건 샘플링 하기

 

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

 

 

 

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()

csv데이터 읽어오기

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

 

df_bus_card_org.info()

 

결측치 발견!

3.  이상치가 있는지 정보확인하기

df_bus_card_org.describe()

 

이상치 정보 확인

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

   - 데이터프레임 이름 : 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

메타정의서 가져오기

 

5. 딕셔너리 타입으로 정의

   - 컬럼명의 이름을 매핑하여 변경하기 위해서는
   - 컬럼명의 값을 key:value 딕셔너리 타입으로 정의해야함

   - 예시 : {'영문명' : '한글명', '영문명2' : '한글명2'...}

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

# iloc[행, 열] : 인덱스 번호를 이용하는 방식   (index location)
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("---------------------------")

# loc[행값, 열값] : 눈에 보이는 인덱스 값을 이용하는 방식
print(df_bus_card_col_org.loc[0, "컬럼명 (영문)"])
print(df_bus_card_col_org.loc[0, "컬럼명 (한글)"])
print(df_bus_card_col_org.loc[1, "컬럼명 (영문)"])
print(df_bus_card_col_org.loc[1, "컬럼명 (한글)"])

iloc / loc

 

6. 데이터프레임을 딕셔너리로 변환

   - df_bus_cart_col_org 데이터프레임을 딕셔너리로 변환
   - 딕셔너리 변수명 : df_bus_card_col_new_dict
   - 영문명은 key로, 한글명은 value로 만들어주세요
   - 예시 : { 'on_date' : '승차시각' , 'off_date' : '하차시각', ... }

     <  방법 1 >

df_bus_card_col_new_dict = {}
for i in range(len(df_bus_card_col_org)):
    key = df_bus_card_col_org.loc[i, "컬럼명 (영문)"]
    value = df_bus_card_col_org.loc[i, "컬럼명 (한글)"]
    df_bus_card_col_new_dict[key] = value

df_bus_card_col_new_dict

 

     <  방법 2 >

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

 

     <  방법 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

df_bus_card_col_new_dict

※ 여기서 .iloc[: , 0]  콜론은 모든 행, 0은 0번째 열을 의미함. 

딕셔너리로 변환

 

 

7. 컬럼명 변경하기

   - 컬럼명 영어에서 한글로 변경하기
   - inplace=True : 변경사항을 메모리에 반영하기

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

컬럼명 한글로 변경

 

 

 

분석 주제

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

     (버스 내 체류시간 분석)
     * 기준월 및 기준일자별 버스 체류시간 분석 비교 (하차시간 - 승차시간)
     * 기준일 및 시간대별 버스 체류시간 분석 비교
     * 기준시간 및 시간(분)별 버스 체류시간 분석 비교

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

 

 

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

   - int 타입은 우리가 분리해서 가져오거나 추출할 수 없음

   - 따라서, 년월일 시분 단위로 분리하기 위해 str타입으로 바꿔야 함

   - astype() : 데이터 형변환 함수

### 데이터프레임 복제하기
df_bus_card_kor = df_bus_card_org.copy()

### 1. 승차시각과 하차지각 데이터 타입을 문자열로 변환하기
df_bus_card_kor = df_bus_card_kor.astype({'승차시각':'str',
                        '하차시각':'str'})
df_bus_card_kor.info()

int >> str

 

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

### 필요한 컬럼들만 포함한 데이터프레임 복제하기

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

df_bus_card.head()

컬럼 추출

 

3. 승차시각과 하차시각의 데이터타입을 날짜타입으로 변경하기

   - to_datetime() : 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.info()
df_bus_card

datetime으로 변환

 

df_bus_card

 

4. 버스 내 체류시간(분단위) 계산

  - 컬럼명 : 버스내체류시간(분)

  - 체류시간 = 하차시각 - 승차시각

  - round( a, b ) : 반올림 함수, a를 소수점 b자리까지 반올림

  - total_seconds() : 초단위로 변환하는 함수

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

7.92

 

round((df_bus_card["하차시각"] - df_bus_card["승차시각"]).dt.total_seconds()/60, 2)

※ .dt는 Pandas DataFrame의 datetime 타입 열에서 날짜 및 시간과 관련된 속성 및 메서드에 접근하기 위한 속성

• .dt.year: 연도 추출
• .dt.month: 월 추출
• .dt.day: 일 추출
• .dt.hour: 시간 추출
• .dt.minute: 분 추출
• .dt.second: 초 추출
• .dt.microsecond: 마이크로초 추출
• .dt.date(): 날짜 부분 추출
• .dt.time(): 시간 부분 추출
• .dt.day_name(): 요일 추출
• .dt.month_name(): 월 이름 추출
• .dt.total_seconds(): 시간 간격을 초로 변환

체류시간 만들기

 

5. 체류시간(분) 컬럼 생성

  - \(역슬래시) 쓰고 뒤에 아무 것도 없으면 무시하고 이어서 인식됨

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

 

버스내체류시간 컬럼 생성

 

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

#  기준년도
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

컬럼들 생성

 

전체 통합하기

 

위 한건 샘플 프로세스를 이용하여 전체 파일 통합하기

 

1.  통합하기 - 내가한 방법

   - 최종 통합 데이터프레임 이름 : df_bus_card_tot

df_bus_card_tot = []

# 반복문 돌려서 리스트에 append하기(포맷사용 :  f " {변수} ")
for i in range(0,80):
    file_path = f"./01_data/org/trfcard({i})/trfcard.csv"
    df_bus_card_tot.append(pd.read_csv(file_path))

# append한 내용을 해당 컬럼에 맞게 합치게 concat해주기 (ignore_index = True 해주어 인덱스 새로설정하기 )
df_bus_card_tot = pd.concat(df_bus_card_tot, ignore_index = True)

# rename으로 한글 컬럼명으로 바꿔주기
df_bus_card_tot.rename(columns=df_bus_card_col_new_dict, inplace = True)

df_bus_card_tot

df_bus_card_tot

 

# 복사하기
df_bus_card_tot1 = df_bus_card_tot.copy()

### 1. 승차시각과 하차지각 데이터 타입을 문자열로 변환하기
df_bus_card_tot1 = df_bus_card_tot1.astype({'승차시각':'str', '하차시각':'str'})

### 2. 필요한 컬럼만 가져오기
df_bus_card_tot1 = df_bus_card_tot1[["승차시각","하차시각","승객연령","환승여부","추가운임여부","승차정류장","하차정류장"]].copy()

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

### 4. 버스 내 체류시간(분단위) 컬럼만들기
df_bus_card_tot1["버스내체류시간(분)"] = round((df_bus_card_tot1["하차시각"] - df_bus_card_tot1["승차시각"]).dt.total_seconds()/60, 2)

### 5. 기준년도, 기준월, 기준일, 기준시간, 기준시간(분), 컬럼 생성하기
#  기준년도
df_bus_card_tot1["기준년도"] = df_bus_card_tot1["승차시각"].dt.year
#  기준월
df_bus_card_tot1["기준월"] = df_bus_card_tot1["승차시각"].dt.month
#  기준일
df_bus_card_tot1["기준일"] = df_bus_card_tot1["승차시각"].dt.day
#  기준시간
df_bus_card_tot1["기준시간"] = df_bus_card_tot1["승차시각"].dt.hour
#  기준시간(분)
df_bus_card_tot1["기준시간(분)"] = df_bus_card_tot1["승차시각"].dt.minute

df_bus_card_tot1

df_bus_card_tot1

 

 

 

 

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)}")
    

    # axis=0 : 데이터프레임에 행단위로 추가하기 
    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

df_bus_card_tot

 

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

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

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

파일로 저장 성공