데이터 분석을 위한 새로운 기술: Pandas와 SQL을 사용하여 복잡한 데이터를 쉽게 처리

데이터 과학과 머신 러닝 분야에서는 모델의 성능이 데이터 품질에 크게 좌우됩니다.

Python의 Pandas와 SQL은 의심할 여지 없이 데이터 처리 및 분석을 위한 두 가지 훌륭한 도구입니다.

오늘은 Pandas와 SQL을 결합하여 데이터 분석 작업을 보다 효율적으로 만드는 방법에 대해 알아보겠습니다!

Pandas와 SQL: 각자 강하지만, 함께라면 더욱 강해진다!

Pandas와 SQL을 함께 사용하는 방법을 알아보기 전에 각각의 기능을 간략하게 살펴보겠습니다.

Pandas란 무엇인가?

Pandas는 파이썬 프로그래밍 언어로 작성된 강력한 소프트웨어 라이브러리로, 주로 데이터 조작과 분석에 사용됩니다. 테이블, 데이터 구조, 시계열 데이터를 조작하기 위한 강력한 기능을 제공합니다.

• Pandas의 능력  :

SQL이란 무엇인가요?

SQL(구조화 쿼리 언어)은 관계형 데이터베이스를 추출, 관리, 운영하는 데 사용되는 언어입니다. 엔터티와 변수 간의 관계를 처리하여 구조화된 데이터를 효율적으로 처리하는 데 도움이 됩니다. 이를 통해 테이블에 데이터를 삽입, 업데이트, 삭제하고 관리할 수 있습니다.

• SQL의 능력 :

왜 Pandas와 SQL을 결합해야 할까요?

Pandas와 SQL을 결합하면 코드를 더 읽기 쉽게 만들고, 어떤 경우에는 구현을 더 간단하게 만들 수 있습니다.

복잡한 워크플로의 경우 SQL 쿼리는 Pandas 코드보다 더 명확하고 읽기 쉽습니다. 게다가 대부분의 관계형 데이터는 데이터베이스에서 나오며, SQL은 관계형 데이터를 처리하는 주요 도구 중 하나입니다. 그렇기 때문에 데이터 분석가와 데이터 과학자는 모두 자신의 기능을 결합하는 것을 좋아합니다.

Pandas와 SQL을 함께 사용하는 방법은?

SQL 쿼리를 Pandas와 결합하려면 "브리지"가 필요한데, 이 브리지는  pandasql 입니다 . pandasql을 사용하면 Pandas에서 직접 SQL 쿼리를 실행할 수 있으므로 동적인 Pandas 환경에서 SQL 구문을 원활하게 사용할 수 있습니다.

PandaSQL 설치

Pandas와 SQL을 사용하려면 첫 번째 단계는 pandasql을 설치하는 것입니다. 다음 명령을 실행하세요.

pip install pandasql

Pandas에서 SQL 쿼리 실행

설치가 완료되면 pandasql을 코드로 가져와서 Pandas DataFrame에서 SQL 쿼리를 실행할 수 있습니다. 간단한 예를 살펴보겠습니다.

import pandas as pd
import pandasql as psql

#  DataFrame 생성
data = {'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [25, 30, 35]}
df = pd.DataFrame(data)

# SQL 쿼리: 전체 데이터 선택
query = "SELECT * FROM df"
result = psql.sqldf(query, locals())
print(result)

이 코드는 무슨 역할을 하나요?

• pd.DataFrame 샘플 데이터를 표 형식으로 변환합니다.
• query( SELECT * FROM df)는 DataFrame의 모든 데이터를 선택합니다.
• psql.sqldf(query, locals()) 로컬 범위를 사용하여 DataFrames에 대한 SQL 쿼리를 실행합니다.

pandasql을 이용한 데이터 분석

다음으로, pandasql을 사용하여 데이터 분석을 수행합니다. 다음은 데이터를 로드하고, 데이터를 탐색하고, 데이터를 분석하는 방법을 보여주는 전체 예입니다.

1단계: 데이터 로드

import pandas as pd
import pandasql as ps
import plotly.express as px
import ipywidgets as widgets

# 데이터셋 로드
car_data = pd.read_csv("cars_datasets.csv")
car_data.head()

이 코드는 무슨 역할을 하나요?

• 필요한 라이브러리를 가져옵니다: 데이터 처리를 위한 Pandas, DataFrame을 쿼리하기 위한 pandasql, 대화형 차트를 만들기 위한 Plotly.
•  로컬 디렉토리에서 데이터를 로드하는 데 사용합니다  .pd.read_csv
• car_data.head() 데이터의 처음 5개 행을 표시합니다.

2단계: 데이터 탐색

이 섹션에서는 먼저 열 이름, 데이터 유형, 누락된 값이 있는지 여부를 살펴보며 데이터에 익숙해집니다.

# 컬럼 이름 표시
column_names = car_data.columns
print(column_names)

# 데이터셋 정보 표시
car_data.info()

# 누락된 값이 있는지 확인
car_data.isnull().sum()

3단계: 데이터 분석

이제 데이터 세트를 로드했으므로 데이터 분석을 시작할 수 있습니다. pandasql을 사용하여 몇 가지 SQL 쿼리를 실행하고 무엇을 찾을 수 있는지 살펴보겠습니다.

질문 1: 가장 비싼 자동차 10가지 선정

def q(query):
    return ps.sqldf(query, {'car_data': car_data})

q("""
SELECT brand, model, year, price
FROM car_data
ORDER BY price DESC
LIMIT 10
""")

이 코드는 무슨 역할을 하나요?

• q(query) DataFrame에서 SQL 쿼리를 수행하는 데 사용되는 사용자 정의 함수입니다.
• 이 쿼리는 전체 데이터 세트를 살펴보고, 제조사, 모델, 연도, 가격 열을 선택한 다음, 가격을 기준으로 내림차순으로 정렬하여 가장 비싼 자동차 10대를 골라냅니다.

질의 2: 브랜드별 평균 가격 계산

q("""
SELECT brand, ROUND(AVG(price), 2) AS avg_price
FROM car_data
GROUP BY brand
ORDER BY avg_price DESC
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는  AVG(price) 각 브랜드의 평균 가격을 사용하고  ROUND 함수를 사용하여 소수점 이하 두 자리까지 유지합니다.
• GROUP BY 브랜드별로 그룹화하고 평균 가격을 내림차순으로 정렬했습니다.

질의 3: 2015년 이후 제조된 차량의 검색

q("""
SELECT *
FROM car_data
WHERE year > 2015
ORDER BY year DESC
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는 2015년 이후에 제조된 자동차를 필터링하여 연도를 기준으로 내림차순으로 정렬합니다.

질의 4: 가장 많은 차량을 생산하는 상위 5개 브랜드 나열하기

q("""
SELECT brand, COUNT(*) as total_listed
FROM car_data
GROUP BY brand
ORDER BY total_listed DESC
LIMIT 5
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는 브랜드별로 차량의 총 대수를 세고, 이를 내림차순으로 정렬한 후 상위 5개 브랜드를 선택합니다.

질의 5: 차량 상태별 평균 가격 계산

q("""
SELECT condition, ROUND(AVG(price), 2) AS avg_price, COUNT(*) as listings
FROM car_data
GROUP BY condition
ORDER BY avg_price DESC
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는 차량 상태(신차 또는 중고차)별로 그룹화하고, 평균 가격을 계산한 후, 평균 가격을 내림차순으로 정렬합니다.

질의 6: 브랜드별 평균 주행거리 및 평균 가격 계산

q("""
SELECT brand,
      ROUND(AVG(mileage), 2) AS avg_mileage,
      ROUND(AVG(price), 2) AS avg_price,
      COUNT(*) AS total_listings
FROM car_data
GROUP BY brand
ORDER BY avg_price DESC
LIMIT 10
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는 브랜드별로 그룹화하고, 평균 주행 거리와 평균 가격을 계산하며, 브랜드별 차량의 총 대수를 센 후 평균 가격을 기준으로 내림차순으로 정렬합니다.

질의 7: 브랜드별 마일당 가격 비율 계산

q("""
SELECT brand,
      ROUND(AVG(price/mileage), 4) AS price_per_mile,
      COUNT(*) AS total
FROM car_data
WHERE mileage > 0
GROUP BY brand
ORDER BY price_per_mile DESC
LIMIT 10
""")

이 코드는 무슨 역할을 하나요?

• 이 쿼리는 각 브랜드의 마일당 가격 비율을 계산하고 해당 비율에 따라 내림차순으로 정렬합니다.

질의 8: 지역별 평균 자동차 가격 계산

state_dropdown = widgets.Dropdown(
    options=car_data['state'].unique().tolist(),
    value=car_data['state'].unique()[0],
    description='Select State：',
    layout=widgets.Layout(width='50%')
)

def plot_avg_price_state(state_selected):
    query = f"""
        SELECT brand, AVG(price) AS avg_price
        FROM car_data
        WHERE state = '{state_selected}'
        GROUP BY brand
        ORDER BY avg_price DESC
    """
    result = q(query)
    fig = px.bar(result, x='brand', y='avg_price', color='brand',
                 title=f"{state_selected} Average car price")
    fig.show()

widgets.interact(plot_avg_price_state, state_selected=state_dropdown)

이 코드는 무슨 역할을 하나요?

• 사용자가 다른 주를 선택할 수 있는 드롭다운 메뉴를 만듭니다.
• plot_avg_price_state 이 함수는 선택된 주에 따라 각 브랜드의 평균 가격을 계산하고 Plotly를 사용하여 막대 차트를 표시합니다.
• widgets.interact 드롭다운 메뉴를 함수에 연결하면 사용자가 다른 상태를 선택하면 차트가 자동으로 업데이트됩니다.

PandaSQL의 한계

pandasql은 Pandas에서 SQL 쿼리를 실행하는 편의성을 제공하지만, 몇 가지 제한 사항도 있습니다.

• 대용량 데이터세트 지원 안 함 : pandasql 쿼리를 실행하면 메모리에 데이터 복사본이 생성되는데, 이로 인해 대용량 데이터세트를 작업할 때 메모리 사용량이 높아지고 실행 속도가 느려질 수 있습니다.
• 제한된 SQL 기능 : pandasql은 많은 기본 SQL 기능을 지원하지만 하위 쿼리, 복잡한 조인, 윈도우 함수와 같은 모든 고급 기능을 완벽하게 구현할 수는 없습니다.
• 복잡한 데이터와의 호환성이 낮음 : Pandas는 복잡한 데이터(중첩된 JSON이나 다중 인덱스 DataFrame 등)를 처리할 때 이상적인 결과를 제공할 수 없습니다.

요약

Pandas와 SQL을 결합하면 데이터 분석의 효율성을 크게 향상시킬 수 있습니다. pandasql을 사용하면 Pandas DataFrame에서 SQL 쿼리를 원활하게 실행할 수 있어 SQL에 익숙한 사람들에게 매우 편리합니다. 이러한 조합은 Pandas와 SQL의 유연성을 유지할 뿐만 아니라, 데이터 조작 및 분석에 대한 새로운 가능성을 열어줍니다.

 

하지만 pandasql의 한계점도 알아야 합니다. 대용량 데이터 세트와 복잡한 데이터의 경우 다른 방법을 고려해야 할 수도 있습니다.