Pandas, SQLite3 공부하기 [2023-05-29 학습일지]

 

오늘은 데이터 다루기 연습을 해보았다.

 

목표 : csv 또는 excel 형태의 공공데이터를 pandas로 불러와 sqlite3로 db에 저장한다.

db에 저장해둔 데이터를 query를 이용해 필요한 데이터를 불러온다.

 

타겟 : https://www.data.go.kr/data/15088456/fileData.do

광주광역시_시내버스 노선별 승하차 인원 정보_20230430

나는 필요이상의 데이터를 요하지 않아 4월 27일 ~ 4월 30일 4일간의 데이터만 불러왔다. (그래도 64999 rows)

 

import pickle

import pandas as pd


# test_file = pd.read_excel('bus_info.xlsx')
#
# with open('bus_info_as_byte', 'wb') as file:
#     pickle.dump(test_file, file)

def open_pickle_return_dataframe():
    with open('bus_info_as_byte', 'rb') as file:
        load_bus_info = pickle.load(file)
    load_bus_info: pd.DataFrame
    return load_bus_info
# '일자', '노선명', '정류장명', 'ARS_ID', '시간', '승하차', '거래건수'


if __name__ == '__main__':
    df = open_pickle_return_dataframe()
    print(df.index)

 

우선 pandas로 불러들인 object를 pickle을 이용해 byte로 저장했다.

 

그렇지 않으면 반복적으로 읽을 때 너무 느리다.

 

import sqlite3
import open_xlsx


def create_table():
    conn = sqlite3.connect('bus_info.db')
    c = conn.cursor()
    # '일자', '노선명', '정류장명', 'ARS_ID', '시간', '승하차', '거래건수'
    c.execute("drop table mybus_info")
    c.execute("""
        CREATE TABLE mybus_info (
                  id INTEGER PRIMARY KEY AUTOINCREMENT, 
                  date text, 
                  bus_no text,
                  bus_stop_name text,
                  bus_stop_id int,
                  measured_time text,
                  boarding_type text,
                  transaction_count int
          )
    """)
    conn.commit()
    conn.close()


def fetch_all():
    conn = sqlite3.connect('bus_info.db')
    c = conn.cursor()
    for i in c.execute('select * from mybus_info').fetchall():
        print(i)
    conn.close()


def insert_all():
    conn = sqlite3.connect('bus_info.db')
    c = conn.cursor()
    df = open_xlsx.open_pickle_return_dataframe()
    df_filled = df.fillna(0)
    for idx in df.index:
        temp = (
            str(df_filled.iloc[idx][0]), df_filled.iloc[idx][1], df_filled.iloc[idx][2], int(df_filled.iloc[idx][3]),
            str(df_filled.iloc[idx][4]), df_filled.iloc[idx][5],
            int(df.iloc[idx][6]))
        c.execute("""
            insert into mybus_info 
            (date,bus_no,bus_stop_name,bus_stop_id,measured_time,boarding_type,transaction_count)
            values (?,?,?,?,?,?,?)
        """, temp)
    conn.commit()
    conn.close()


def select_busy_bus_stop(number):
    if not isinstance(number, int):
        raise
    conn = sqlite3.connect('bus_info.db')
    c = conn.cursor()
    for i in c.execute('select * from mybus_info where transaction_count > (?)', (number,)).fetchall():
        print(i)
    conn.close()


if __name__ == '__main__':
    select_busy_bus_stop(100)

 

이후 db를 생성해주는 함수와 dataframe에서 불러들여온 정보를 하나씩 insert 해주는 함수를 만들었다.

 

중간에 NAN으로 승강장이 들어가있지 않는 cell들이 있어 그런 경우엔 '0'으로 채웠다.

 

또한 query를 날려 승하차 건수가 1시간동안 100건이 넘는 정보를 출력하도록 함수를 하나 만들었다.(select_busy_bus_stop)

 

(8360, '20230427', '금남57', '0', 0, '17', '승차', 108)
(8680, '20230427', '금남58', '동림삼익아파트', 4148, '7', '승차', 107)
(25696, '20230427', '마을777', '전남대용봉탑', 4663, '14', '승차', 106)
(25699, '20230427', '마을777', '전남대용봉탑', 4663, '16', '승차', 137)
(36927, '20230427', '문흥18', '전남대공과대학', 4411, '16', '승차', 113)
(48867, '20230427', '봉선37', '호남대', 5265, '15', '승차', 165)
(48868, '20230427', '봉선37', '호남대', 5265, '16', '승차', 137)

 

데이터를 분석해보면, 목요일 14시, 16시 전남대 용봉탑과 공과대학쪽, 호남대는 대학생들이 많이 버스를 이용하고 있다는 것을 알았다.

 

하지만, 이는 옳지 않은 해석이다. 단일 버스 노선의 데이터이기 때문에, 정류장을 기준으로 다시 카운트해줘야하는데..

 

당분간 계속 조작하면서 이것저것 다뤄봐야겠다.

 

또한 깊은 고민 발생, 이것을 어떻게 시각화할 수 있을까..??

 

감사합니다.