[전자제품 쇼핑몰] 데이터베이스에 데이터 저장하기 & 데이터 생성하기

 

 

데이터 베이스에 넣기 위해 테이블정의서도 작성하고

테이블 정의서에 맞춰 데이터를 전처리 및 가공하였습니다.

 

이를 Mariadb에 저장했습니다

 

[ 데이터 베이스에 저장하기 ]

 

○ 사용 라이브러리

from sqlalchemy import create_engine
import pymysql
import sqlalchemy
from sqlalchemy import types
import pandas as pd

 

○ DB 접속

db_connection = create_engine('mysql+pymysql:/db사용자이름:비밀번호@35.87.206.219:3306/db이름?charset=utf8mb4', echo=True)
db_connection

 

▶ 한글으로 되어있는 데이터를 넣기 위해 charset=utf8로 맞춰줬습니다

 

 

○ 데이터 저장하기

user.to_sql(name = "user",
            con = db_connection,
            index=False,
            if_exists="append",
            dtype = {
                'user_id' : types.VARCHAR(length=50),
                'user_pwd' : types.VARCHAR(length=50),
                'user_name' : types.VARCHAR(length=50),
                'user_email' : types.VARCHAR(length=50),
                'user_tel' : types.VARCHAR(length=50),
                'user_address' : types.VARCHAR(length=50),
                'user_joined_ymd' : types.DATE(),
                'user_gender' : types.VARCHAR(length=50),
                'user_birth' : types.DATE(),
                'user_family_counts' : types.VARCHAR(length=50)
            })

▶ 데이터프레임으로 되어있는 데이터를 mariadb에 저장하는 방법입니다.

테이블정의서에 쓰여있는 컬럼의 데이터타입과 varchar의 경우 그 길이까지 지정해서 넣어줬습니다.

if_exists 부분은 이미 존재하는 데이터가 있다면 append 그 뒤에 추가할 것인지 replace 대체할것인지를 결정하는 부분입니다.

가장 중요한 부분은 데이터프레임의 데이터 형태를 미리 확인하고 테이블정의서에 맞게 수정해줘야하는 것입니다.

년도-월-일 까지는 DATE, 시 분 초 까지는 DATETIME 형태를 사용하여야합니다

 

buy.to_sql(name = "buy",
            con = db_connection,
            index=False,
            if_exists="append",
            dtype = {
                'buy_id' : types.VARCHAR(length=255),
                'buy_mbr_id' : types.VARCHAR(length=255),
                'buy_addr' : types.VARCHAR(length=255),
                'buy_stts' : types.VARCHAR(length=255),
                'buy_ymd' : types.DATETIME()
            })

buy_dtls.to_sql(name = "buy_dtls",
           con = db_connection,
           index=False,
           if_exists="append",
           dtype = {
               'buy_pd_nm' : types.VARCHAR(length=255),
               'buy_dtls_nm' : types.VARCHAR(length=255),
               'buy_dtls_cnt' : types.Integer(),
               'buy_dtls_prc' : types.Integer()
           })

▶ int 형태의 경우에는 Integer() 로 dtype을 작성합니다.

오래걸렸던 부분은 자꾸 한글 데이터가 안들어가는 부분이었는데 이는 db 자체의 문자 형태가 latin 으로 변경되어있어서 오류가 발생했었습니다.

데이터베이스 전체의 문자형태를 utf8로 바꿔주거나

ALTER TABLE prod MODIFY COLUMN prod_name 
VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

위와 같이 한글이 포함된 데이터가 있는 특정 컬럼의 문자형태를 바꿔주면 됩니다.

 

 

 

 

 

[ 데이터 생성하기 ] 

 

 

회원 정보와 구매내역의 데이터가 있어야 그에 대한 분석과 예측모델 훈련이 가능한데

아직 웹을 배포하거나 실제 데이터가 있는 상태가아니라서 로그데이터를 가상으로 설계하고 생성하는 과정을 진행했습니다.

로그데이터를 통해 조금 더 고객 세분화 전략을 세울 수 있고 회원 정보를 바탕으로 어떤 구매 양상을 보이는지를 확인 할 수있습니다.

 

 

 

○ user 데이터 생성

 

 

회원 id , 비밀번호, 이름, 이메일주소, 전화번호, 거주지, 회원등록일자 , 성별, 생년월일 , 가구원수 의 총 10개의 컬럼으로 구성되어있는 데이터를 4000개 정도 생성했습니다.

 

기존에 가지고있던 국내여행지 선호도 분석의 자료를 활용하여 성별과 연령,거주지, 조사 시작일자를 등록일자로 변경 및 id도 활용하여 구성했습니다.

 

 

전화번호의 경우에는 id를 통해 8자리를 생성하였습니다

df["user_tel"] =  ["010" + str(i).zfill(8) for i in df["user_id"]]

 

숫자로만 구성되어있는 id에서 주어진 리스트에서 정해진 개수만큼 랜덤으로 출력하는 random 라이브러리를 사용하여 영문 3자와 숫자 4자로 구성되어있는 아이디를 만들었습니다.

또한 random.randint를 통해 0부터 9까지의 숫자중에서 3개를 뽑아 하나의 아이디를 구성하였습니다.

비밀번호도 동일하게 진행하였습니다.

# 아이디 : 영문3자 + 숫자4자
# 비밀번호 : 영문4자 + 숫자 5자
# random.randint(0,10)
li = ["a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"]
a = "".join([i for i in random.sample(li,3)])
b = "".join([str(random.randint(0,10)) for i in range(3)])
a+b

 

 

 

이름의 경우에도 성과 이름을 담은 리스트를 만든 뒤 그에 대한 랜덤 추출을 random.choice를 통해 하여 생성하였습니다.

first_name = ["김","나","박","이","조","동","왕","전","도","강","송","문","황","구","손","장","한","공","임","오","소","권","신","홍","유","양","윤","최","서","안","고","주","우","노"]
last_name = ["수현","채은","지현","경수","경민","서희","태윤","진석","승관","민지","혜인","혜은","나라","원빈","현빈","혜빈","소원","철홍","홍철","수지","이지","의진","민준","지훈","우진","연우","지호","시우","성현","현서","강준","백현","성민","준우","익제","희성","종관","힘찬","상준","아현","강호","민현","석구","찬원","영웅","지섭","효진","진수","현무","나래","장우","규현","기안","우재","장훈","혜진","정은"]
name_list  = []
for i in range(len(df)):
    a = random.choice(first_name)
    b = random.choice(last_name)
    name_list.append(a+b)
len(name_list)

 

 

가구원 수는 가구원수별 가구구성 통계 지표를 통해 확인한 비율을 가중치로 주어 생성하였습니다.

# 랜덤하게 뽑을때 가중치 주기
family = ["1인가구","2인가구","3인가구","4인가구이상"]
weights = [0.345,0.288,0.192,0.176]
family_list = [random.choices(family,weights)[0] for i in range(len(df))]
df["user_family_counts"] = family_list

 

 

20대, 50대 로 되어있는 나이를 22,58 등의 나이 숫자로 변환시키는 작업을 수행하였습니다.

import random
age_list = [i[0] + str(random.randint(0,9)) for i in user["user_age"]]
len(age_list)
user["user_age"] = age_list

 

 

나이를 이용하여 생년월일을 생성하는 작업을 수행했습니다.

월과 일은 랜덤으로 선택하도록 하고 달마다 일의 숫자가 다르기 때문에 딕셔너리 형태로 저장하여 그 범위내에서 추출하도록 하였습니다.

또한 날짜형태의 데이터타입으로 변경하여 데이터베이스에 저장하기 용이하게 하였습니다.

month = ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"]
days_in_month = {"01": 31, "02": 28, "03": 31, "04": 30, "05": 31, "06": 30, "07": 31, "08": 31, "09": 30, "10": 31, "11": 30, "12": 31}
from datetime import datetime
import random
ymd = []
ymd1 = []
# user["user_birth"]가 Timestamp 객체일 경우
# user_birth_str = user["user_birth"].astype(str)
for i in user["user_birth"]:
    year = 2024 - int(i)
    selected_month = random.choice(month)
    selected_day = random.randint(1, days_in_month[selected_month])

    date_str = f"{year}{selected_month}{selected_day:02d}"
    date_obj = datetime.strptime(date_str, "%Y%m%d")
    ymd1.append(date_str)
    ymd.append(date_obj.date())
ymd

 

 

 

 

 

○  buy 데이터 생성

 

주문번호, 주문회원id, 주문 일자(시간까지), 배송주소(광역시도기준), 배송상태로 이루어진 데이터를 생성하였습니다.

 

 

주문 일자를 2021년부터 2023년까지의 기간으로 랜덤하게 생성하였습니다.

from datetime import datetime
import random
year = ["2021", "2022", "2023"]
month = ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"]
days_in_month = {"01": 31, "02": 28, "03": 31, "04": 30, "05": 31, "06": 30, "07": 31, "08": 31, "09": 30, "10": 31, "11": 30, "12": 31}
hour = ["00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"]

ymd = []
ymd1 = []
for i in range(20000):
    selected_year = random.choice(year)
    selected_month = random.choice(month)
    selected_day = random.randint(1, days_in_month[selected_month])
    selected_hour = random.choice(hour)

    date_str = f"{selected_year}{selected_month}{selected_day:02d}{selected_hour}"
    date_obj = datetime.strptime(date_str, "%Y%m%d%H")
    ymd1.append(date_str)
    ymd.append(date_obj)

print(len(ymd))

 

 

주문번호는 주문일자에서 시간까지 동일한 경우 1씩 늘어나는 001,002 순서로 구성하였습니다.

id = []

for ymd in ymd1:
    i = 1
    while ymd + str(i).zfill(3) in id:
        i += 1
    id.append(ymd + str(i).zfill(3)) 
df["buy_id"] = id

 

 

주문 회원아이디는 가중치를 주어 랜덤하게 선택하도록 하였습니다.

# buy_mbr_id
mbr_id = []
for i in range(len(df)):
    mbr_id.append(random.choice(user["user_id"]))
df["buy_mbr_id"] = mbr_id

# 가중치 주는 경우 - 필요시 진행
# 각 성별에 따른 가중치 설정
weights = {'M': 1, 'F': 2}

# user["user_gender"]에 따라 가중치를 부여하여 랜덤으로 선택
mbr_id = []
for i in range(len(df)):
    gender = user["user_gender"].iloc[random.randint(0, len(user["user_gender"])-1)]
    selected_id = random.choices(user["user_id"], weights=[weights[gender] if g == gender else 1 for g in user["user_gender"]])[0]
    mbr_id.append(selected_id)

# DataFrame에 새로운 열로 추가
df["buy_mbr_id"] = mbr_id

 

 

 

배송지역은 user 데이터에서 해당 id의 거주지 데이터를 가져와서 생성하였습니다.

# buy_addr
# 조건에 맞는 데이터 가져오기
addr = [user.loc[user["user_id"] == df["buy_mbr_id"][i], "user_address"].values[0] for i in range(len(df))]
len(addr)

 

 

 

○ buy dtls 데이터 생성

주문 상품id, 주문번호,구매수량, 구매가격으로 형성되어있습니다.

상품id와 주문번호가 동일한 행이 있으면 안되기때문에 40020개의 상품id를 생성한 뒤에 중복값을 제거하고 40000개의 행을 가진 데이터프레임으로 만들었습니다.

# 주문상품번호 buy_pd_nm
buy_pd_nm = []
for i in range(40020):
    buy_pd_nm.append(random.choice(prod["prod_id"]))
len(buy_pd_nm)
buy_dtls["buy_pd_nm"] = buy_pd_nm

buy_dtls = buy_dtls.drop_duplicates(subset=["buy_pd_nm","buy_dtls_nm"],keep="first")

buy_dtls = buy_dtls.iloc[:40000,:]
buy_dtls

 

 

 

주문번호에서 3,4,5월에 가중치를 두어 주문번호를 생성하였습니다.

# 주문번호("buy_id")에서 index 4부터 6까지의 값이 '03', '04', '05'인 경우에 가중치를 부여
# 가중치를 부여할 구간 정의
weighted_ranges = ['03', '04', '05']
weights = [2 if key in weighted_ranges else 1 for key in df_buy["buy_id"].astype(str).str[4:6]]

# 가중치를 고려하여 랜덤으로 선택
dtls_nm = random.choices(df_buy["buy_id"], weights=weights, k=20020)
# 주문번호는 있는데 dtls가 없으면 안되기 때문에 하나씩 다 넣기 위한 코드
dtls_nm.extend(df_buy["buy_id"])
len(dtls_nm)
buy_dtls["buy_dtls_nm"] = dtls_nm

 

 

가격은 구매수량을 1,2,3 중 랜덤으로 생성하여 이를 제품가격에 곱해서 생성했습니다.

# 가격
# 실제로는 데이터베이스에서 계산해야되지만 임의의 데이터를만드는 과정이기에 여기서 수행
# addr = [user.loc[user["user_id"] == df["buy_mbr_id"][i], "user_address"].values[0] for i in range(len(df))]
price = []
price = [prod.loc[prod["prod_id"] == buy_dtls["buy_pd_nm"][i] , "prod_prc"].values[0] * buy_dtls["buy_dtls_cnt"][i] for i in range(len(buy_dtls))]
price[0]