효율적인 데이터 분석을 위해 팬더 및 SQL을 함께 활용합니다

저자의 이미지 | 캔버

Pandas와 SQL은 모두 데이터 분석에 효과적이지만 전력을 병합 할 수 있다면 어떨까요? 와 함께 PandasqlJupyter 노트북 내에 직접 SQL 쿼리를 작성할 수 있습니다. 이 통합을 통해 효과적인 데이터 분석을 위해 SQL 로직을 Python과 혼합 할 수 있습니다.

이 기사에서는 Uber의 데이터 프로젝트에서 Pandas와 SQL을 함께 사용합니다. 시작합시다!

틀 Pandasql은 무엇입니까?

Pandasql은 메모리를 통해 모든 데이터 프레임과 통합 될 수 있습니다. sqlite 엔진, 따라서 파이썬 환경 내에 순수한 SQL을 쓸 수 있습니다.

틀 팬더와 SQL을 함께 사용하는 장점

SQL은 행을 쉽게 필터링하거나 데이터를 집계하거나 다중 조건 논리를 적용하는 데 유용합니다.
반면에 Python은 통계 분석 및 사용자 정의 계산을위한 고급 도구뿐만 아니라 SQL 기능을 넘어서는 세트 기반 작업을 제공합니다.
함께 사용하면 SQL은 데이터 선택을 단순화하는 반면 Python은 분석 유연성을 추가합니다.

틀 Jupyter 노트에서 Pandasql을 실행하는 방법은 무엇입니까?

달리기 pandasql Jupyter 노트북 내부에서 다음 코드로 시작하십시오.

import pandas as pd
from pandasql import sqldf
run = lambda q: sqldf(q, globals())

다음으로 다음과 같은 SQL 코드를 실행할 수 있습니다.

run("""
SELECT *
FROM df
LIMIT 10;
""")

우리는 표시하지 않고 SQL 코드를 사용합니다 run 이 기사에서 매번 기능합니다.

SQL과 Pandas를 함께 사용하는 것이 Uber의 실제 프로젝트에서 어떻게 작동하는지 살펴 보겠습니다.

틀 실제 프로젝트 : Uber 드라이버 성능 데이터 분석

저자의 이미지

이 데이터 프로젝트에서 Uber는 운전자 성능 데이터를 분석하고 보너스 전략을 평가하도록 요청합니다.

// 데이터 탐색 및 분석

이제 데이터 세트를 살펴 보겠습니다. 먼저 데이터를로드합니다.

// 초기 데이터 세트로드

팬더 만 사용하여 데이터 세트를로드하겠습니다.

import pandas as pd
import numpy as np
df = pd.read_csv('dataset_2.csv')

// 데이터 탐색

이제 데이터 세트를 검토합시다.

출력은 다음과 같습니다.

이제 우리는 데이터를 엿볼 수 있습니다.
보시다시피, 데이터 세트에는 각 운전자 이름, 완료 한 트립 수, 수락 비율 (예 : 여행 요청 비율), 총 공급 시간 (온라인 소비 된 총 시간) 및 평균 등급이 포함됩니다.
데이터 분석을 시작하기 전에 열 이름을 확인하여 올바르게 사용할 수 있습니다.

다음은 출력입니다.

보시다시피, 데이터 세트에는 5 개의 다른 열이 있으며 결 측값이 없습니다.
이제 SQL과 Python을 모두 사용하여 질문에 답하겠습니다.

틀 질문 1 : 보너스 옵션 1에 누가 자격이 있습니까?

첫 번째 질문에서, 우리는 옵션 1에 대한 총 보너스 지불금을 결정하도록 요청받습니다.

온라인으로 8 시간 이상인 각 운전자 당 $ 50, 요청의 90%를 허용하고, 10 번의 여행을 완료하며, 기간 동안 4.7 이상의 등급 을가집니다.

// 1 단계 : SQL을 사용하여 적격 드라이버 필터링 (Pandasql)

이 단계에서는 사용을 시작합니다 pandasql.

다음 코드에서는 옵션 1 보너스 조건을 충족하는 모든 드라이버를 선택했습니다. WHERE 절과 AND 여러 조건을 연결하기위한 연산자. 사용 방법을 배우기 위해 WHERE 그리고 AND이 문서를 참조하십시오.

opt1_eligible = run("""
    SELECT Name                -- keep only a name column for clarity
    FROM   df
    WHERE  `Supply Hours`    >=  8
      AND  `Trips Completed` >= 10
      AND  `Accept Rate`     >= 90
      AND  Rating            >= 4.7;
""")
opt1_eligible

다음은 출력입니다.

// 2 단계 : 팬더 마감

SQL을 사용하여 데이터 세트를 필터링 한 후 pandasql수치 계산을 수행하고 분석을 마무리하기 위해 팬더로 전환합니다. SQL과 Python을 결합한이 하이브리드 기술은 가독성과 유연성을 모두 향상시킵니다.

다음으로 다음 Python 코드를 사용하여 자격을 갖춘 드라이버 수를 곱하여 총 지불금을 계산합니다 (사용 len()) 운전자 당 $ 50 보너스. 문서를 확인하여 사용 방법을 확인하십시오. len() 기능.

payout_opt1 = 50 * len(opt1_eligible)
print(f"Option 1 payout: ${payout_opt1:,}")

다음은 출력입니다.

틀 질문 2 : 보너스 옵션 2에 대한 총 지불금 계산 2

두 번째 질문에서는 옵션 2를 사용하여 총 보너스 지불금을 찾도록 요청받습니다.

12 번의 여행을 완료하고 4.7 이상의 등급을 가진 모든 운전자의 경우 $ 4/여행.

// 1 단계 : SQL을 사용하여 적격 드라이버 필터링 (Pandasql)

먼저 SQL을 사용하여 옵션 2 기준을 충족하는 드라이버를 필터링하여 12 개 이상의 트립을 완료하고 4.7 이상의 등급을 유지합니다.

# Grab only the rows that satisfy the Option-2 thresholds
opt2_drivers = run("""
    SELECT Name,
           `Trips Completed`
    FROM   df
    WHERE  `Trips Completed` >= 12
      AND  Rating            >= 4.7;
""")
opt2_drivers.head()

여기에 우리가 얻는 것이 있습니다.

// 2 단계 : 순수한 팬더에서 계산 마감

이제 팬더를 사용하여 계산을 수행하겠습니다. 코드는 합산하여 총 보너스를 계산합니다 Trips Completed 칼럼 sum() 그런 다음 결과에 트립당 $ 4 보너스를 곱하십시오.

total_trips   = opt2_drivers["Trips Completed"].sum()
option2_bonus = 4 * total_trips
print(f"Total trips: {total_trips},  Option-2 payout: ${option2_bonus}")

결과는 다음과 같습니다.

틀 질문 3 : 옵션 1에 자격이 있지만 옵션 2가 아닌 운전자 식별

세 번째 질문에서, 우리는 옵션 1에 대한 자격이 있지만 옵션 2에 대한 자격이없는 드라이버 수를 계산하도록 요청받습니다.

// 1 단계 : SQL을 사용하여 두 개의 자격 테이블 구축 (pandasql)

다음 SQL 코드에서, 우리는 두 개의 데이터 세트를 만듭니다. 하나는 옵션 1 기준을 충족하는 드라이버와 다른 하나는 옵션 2 기준을 충족하는 사람들을위한 것입니다.

# All Option-1 drivers
opt1_drivers = run("""
    SELECT Name
    FROM   df
    WHERE  `Supply Hours`    >=  8
      AND  `Trips Completed` >= 10
      AND  `Accept Rate`     >= 90
      AND  Rating            >= 4.7;
""")

# All Option-2 drivers
opt2_drivers = run("""
    SELECT Name
    FROM   df
    WHERE  `Trips Completed` >= 12
      AND  Rating            >= 4.7;
""")

// 2 단계 : Python Set Logic을 사용하여 차이점을 발견했습니다.

다음으로 Python을 사용하여 옵션 1에 표시되지는 않지만 옵션 2에 표시되지 않은 드라이버를 식별하고이를 위해 세트 작업을 사용할 것입니다.

코드는 다음과 같습니다.

only_opt1 = set(opt1_drivers["Name"]) - set(opt2_drivers["Name"])
count_only_opt1 = len(only_opt1)

print(f"Drivers qualifying for Option 1 but not Option 2: {count_only_opt1}")

다음은 출력입니다.

이러한 방법을 결합하여 필터링을 위해 SQL을 활용하여 결과 데이터 세트를 비교하기위한 Python의 설정 로직을 활용합니다.

틀 질문 4 : 높은 등급을 가진 저 성능 운전자 찾기

질문 4에서, 우리는 10 개 미만의 여행을 완료하고 90%미만의 수락 률을 가졌으며 여전히 4.7 이상의 등급을 유지 한 운전자의 비율을 결정하도록 요청받습니다.

// 1 단계 : SQL로 서브 세트를 당기기 (pandasql)

다음 코드에서는 10 번의 여행을 완료 한 모든 드라이버를 선택하고 90%미만의 수락 률이 있으며 4.7 이상의 등급을 보유합니다.

low_kpi_df = run("""
    SELECT *
    FROM   df
    WHERE  `Trips Completed` = 4.7;
""")
low_kpi_df

다음은 출력입니다.

// 2 단계 : 일반 팬더에서 백분율 계산

이 단계에서는 Python을 사용하여 해당 드라이버의 백분율을 계산합니다.

우리는 단순히 필터링 된 드라이버의 수를 총 운전자 수로 나눈 다음 100을 곱하여 백분율을 얻습니다.

코드는 다음과 같습니다.

num_low_kpi   = len(low_kpi_df)
total_drivers = len(df)
percentage    = round(100 * num_low_kpi / total_drivers, 2)

print(f"{num_low_kpi} out of {total_drivers} drivers ⇒ {percentage}%")

다음은 출력입니다.

틀 질문 5 : Uber와 파트너 관계없이 연간 이익 계산

다섯 번째 질문에서는 주어진 비용 및 수익 매개 변수에 따라 Uber와 파트너 관계없이 택시 운전사의 연간 소득을 계산해야합니다.

// 1 단계 : SQL (Pandasql)으로 연간 수입 및 비용을 인출합니다.

SQL을 사용함으로써 먼저 일일 요금으로부터 연간 수익을 계산하고 가스, 임대 및 보험에 대한 비용을 빼냅니다.

taxi_stats = run("""
SELECT
    200*6*(52-3)                      AS annual_revenue,
    ((200+500)*(52-3) + 400*12)       AS annual_expenses
""")
taxi_stats

다음은 출력입니다.

// 2 단계 : 팬더로 이익과 마진 도출

다음 단계에서는 Python을 사용하여 Uber와 파트너 관계를 맺지 않을 때 운전자가 얻는 이익과 마진을 계산합니다.

rev  = taxi_stats.loc[0, "annual_revenue"]
cost = taxi_stats.loc[0, "annual_expenses"]

profit  = rev - cost
margin  = round(100 * profit / rev, 2)

print(f"Revenue  : ${rev:,}")
print(f"Expenses : ${cost:,}")
print(f"Profit   : ${profit:,}    (margin: {margin}%)")

여기에 우리가 얻는 것이 있습니다.

틀 질문 6 : 수익성을 유지하기 위해 필요한 요금 인상 계산

여섯 번째 질문에서, 우리는 같은 운전자가 타운카를 사고 Uber와 파트너 관계를 결정한다고 가정합니다.

가스 비용은 5%증가하고 보험이 20%감소하고 임대 비용이 제거되지만 운전자는 4 만 달러의 자동차 비용을 충당해야합니다. 우리는이 운전자의 주간 총 요금이 자동차를 상환하고 동일한 연간 이익 마진을 유지하기 위해 첫 해에 얼마나 증가 해야하는지 계산해야합니다.

// 1 단계 : SQL로 새로운 1 년 비용 스택 구축

이 단계에서는 SQL을 사용하여 조정 된 가스 및 보험 및 임대 비용과 자동차 비용으로 새로운 1 년 비용을 계산합니다.

new_exp = run("""
SELECT
    40000             AS car,
    200*1.05*(52-3)   AS gas,        -- +5 %
    400*0.80*12       AS insurance   -- –20 %
""")
new_cost = new_exp.sum(axis=1).iloc[0]
new_cost

다음은 출력입니다.

// 2 단계 : 팬더로 주간 요금 인상을 계산합니다

다음으로, 우리는 파이썬을 사용하여 자동차를 구입 한 후 해당 마진을 보존하기 위해 운전자가 일주일에 얼마나 많은 돈을 벌어야하는지 계산합니다.

# Existing values from Question 5
old_rev    = 58800
old_profit = 19700
old_margin = old_profit / old_rev
weeks      = 49

# new_cost was calculated in the previous step (54130.0)

# We need to find the new revenue (new_rev) such that the profit margin remains the same:
# (new_rev - new_cost) / new_rev = old_margin
# Solving for new_rev gives: new_rev = new_cost / (1 - old_margin)
new_rev_required = new_cost / (1 - old_margin)

# The total increase in annual revenue needed is the difference
total_increase = new_rev_required - old_rev

# Divide by the number of working weeks to get the required weekly increase
weekly_bump = round(total_increase / weeks, 2)

print(f"Required weekly gross-fare increase = ${weekly_bump}")

여기에 우리가 얻는 것이 있습니다.

틀 결론

주로 SQL과 Python의 강점을 통합하여 pandasql우리는 6 가지 다른 문제를 해결했습니다.

SQL은 구조화 된 데이터 세트를 빠르게 필터링하고 요약하는 데 도움이되는 반면, Python은 고급 계산 및 동적 조작에 능숙합니다.

이 분석을 통해 워크 플로를 단순화하고 각 단계를보다 해석 할 수 있도록 두 도구를 모두 활용했습니다.

네이트 로시디 데이터 과학자이자 제품 전략입니다. 그는 또한 겸임 교수 교육 분석이며, 데이터 과학자들이 최고 회사의 실제 인터뷰 질문과의 인터뷰를 준비하는 플랫폼 인 Stratascratch의 창립자입니다. Nate는 커리어 시장의 최신 트렌드에 대해 글을 쓰고, 인터뷰 조언을 제공하고, 데이터 과학 프로젝트를 공유하며, 모든 SQL을 다룹니다.