7 Duckdb SQL -Abfragen, die Ihnen Stunden der Pandas -Arbeit sparen

Bild von Autor | Leinwand

Die Pandas Library hat eine der am schnellsten wachsenden Gemeinschaften. Diese Popularität hat die Tür für Alternativen geöffnet, wie wie Polare. In diesem Artikel werden wir eine solche Various untersuchen, Duckdb.

Duckdb ist eine SQL -Datenbank, die Sie direkt in Ihrem Pocket book ausführen können. Es ist kein Setup erforderlich, und es sind keine Server benötigt. Es ist einfach zu installieren und kann parallel mit Pandas arbeiten.

Im Gegensatz zu anderen SQL -Datenbanken müssen Sie den Server nicht konfigurieren. Es funktioniert einfach mit Ihrem Notizbuch nach der Set up. Das bedeutet, dass keine lokalen Setup -Kopfschmerzen, Sie den Code sofort schreiben. Duckdb verarbeitet Filterung, schließt sich anUnd Aggregationen mit sauberer SQL -Syntax im Vergleich zu Pandas und führt bei großen Datensätzen signifikant besser ab.

Additionally genug mit den Bedingungen, fangen wir an!

Datenprojekt – Uber -Geschäftsmodellierung

Wir werden es mit Jupyter Pocket book verwenden und es mit Python für die Datenanalyse kombinieren. Um die Dinge aufregender zu machen, werden wir an einem realen Datenprojekt arbeiten. Fangen wir an!

Hier ist der Hyperlink Für das Datenprojekt werden wir in diesem Artikel verwenden. Es handelt sich um ein Datenprojekt von Uber namens Geschäftsmodellierung des Companions.

Uber verwendete dieses Datenprojekt im Rekrutierungsprozess für die Knowledge Science -Positionen, und Sie werden gebeten, die Daten für zwei verschiedene Szenarien zu analysieren.

• Szenario 1: Vergleichen Sie die Kosten für zwei Bonusprogramme, die für einen anstrengenden Tag mehr Treiber on-line machen sollen.
• Szenario 2: Berechnen und vergleichen Sie das jährliche Nettoeinkommen eines traditionellen Taxifahrers gegenüber einem, der mit Uber zusammenarbeitet und ein Auto kauft.

Dataset laden

Laden wir zuerst den DataFrame. Dieser Schritt wird benötigt; Daher registrieren wir diesen Datensatz in den folgenden Abschnitten mit Duckdb.

import pandas as pd
df = pd.read_csv("dataset_2.csv")

Erkundung des Datensatzes

Hier sind die ersten Zeilen:

Lassen Sie uns alle Spalten sehen.

Hier ist die Ausgabe.

Schließen Sie Duckdb an und registrieren Sie den DataFrame

Intestine, es ist ein wirklich einfacher Datensatz, aber wie können wir Duckdb mit diesem Datensatz verbinden?
Erstens, wenn Sie es noch nicht installiert haben, installieren Sie Duckdb.

Die Verbindung mit Duckdb ist einfach. Wenn Sie die Dokumentation lesen möchten, lesen Sie sie auch an Hier.

Hier ist der Code, der eine Verbindung herstellt und den Datenrahmen registriert.

import duckdb
con = duckdb.join()

con.register("my_data", df)

Intestine, lassen Sie uns sieben Abfragen erkunden, die Ihnen Stunden der Pandas -Arbeit ersparen!

1. Multi-Kriterien-Filterung für komplexe Zulassungsregeln

Einer der bedeutendsten Vorteile von SQL ist, wie es auf natürliche Weise die Filterung mit der Filterung von Multi-Konditions-Filterungen mit natürlicher Weise behandelt.

Implementierung der Multi-Kriterien-Filterung in Duckdb gegen Pandas

Mit Duckdb können Sie mehrere Filter mit SQLs anwenden, wobei Klauseln und Logik, die sich intestine skalieren, und die Anzahl der Filter wächst.

SELECT 
    *
FROM knowledge
WHERE condition_1
  AND condition_2
  AND condition_3
  AND condition_4

Lassen Sie uns nun sehen, wie wir dieselbe Logik in Pandas schreiben würden. In Pandas wird die kleine Logik mit geketteten booleschen Masken mit Klammern ausgedrückt, die unter vielen Bedingungen ausführlich werden können.

filtered_df = df(
    (df("condition_1")) &
    (df("condition_2")) &
    (df("condition_3")) &
    (df("condition_4"))
)

Beide Methoden sind gleichermaßen lesbar und für die grundlegende Verwendung anwendbar. Duckdb fühlt sich natürlicher und sauberer an, da die Logik komplexer wird.

Multi-Kriterien-Filterung für das Uber-Datenprojekt

In diesem Fall möchten wir Fahrer finden, die sich für ein bestimmtes Uber -Bonusprogramm qualifizieren.

Nach den Regeln müssen die Fahrer:

• Mindestens 8 Stunden on-line sein
• Führen Sie mindestens 10 Reisen ab
• Akzeptieren Sie mindestens 90% der Fahranfragen
• Eine Bewertung von 4,7 oder höher haben

Jetzt müssen wir nur noch eine Abfrage schreiben, die all diese Filterungen durchführt. Hier ist der Code.

SELECT 
    COUN(*) AS qualified_drivers,
    COUNT(*) * 50 AS total_payout
FROM knowledge
WHERE "Provide Hours" >= 8
  AND CAST(REPLACE("Settle for Charge", '%', '') AS DOUBLE) >= 90
  AND "Journeys Accomplished" >= 10
  AND Ranking >= 4.7

Um diesen Code mit Python auszuführen, müssen wir die Methoden von Con.execute („“ „“ „) und Fetchdf () hinzufügen, wie unten gezeigt:

con.execute("""
SELECT 
    COUNT(*) AS qualified_drivers,
    COUNT(*) * 50 AS total_payout
FROM knowledge
WHERE "Provide Hours" >= 8
  AND CAST(REPLACE("Settle for Charge", '%', '') AS DOUBLE) >= 90
  AND "Journeys Accomplished" >= 10
  AND Ranking >= 4.7
""").fetchdf()

Wir werden dies während des gesamten Artikels tun. Nachdem Sie nun wissen, wie man es in einem Jupyter -Notizbuch ausführt, werden wir von nun an nur den SQL -Code zeigen und Sie werden wissen, wie Sie ihn in die pythonische Model konvertieren.
Intestine. Denken Sie nun daran, dass das Datenprojekt möchte, dass wir die Gesamtausschüttung für Choice 1 berechnen.

Wir haben die Summe des Treibers berechnet, aber wir sollten diese mit 50 US -Greenback multiplizieren, da die Auszahlung für jeden Fahrer 50 US -Greenback beträgt, additionally werden wir es mit Depend tun
* 50.

Hier ist die Ausgabe.

Multi-Kriterien-Filterung

2. Schnelle Aggregation, um geschäftliche Anreize abzuschätzen

SQL eignet sich hervorragend zum schnellen Aggregieren, insbesondere wenn Sie Daten über Zeilen zusammenfassen müssen.

Implementierung der Aggregation in Duckdb gegen Pandas

SELECT 
    COUNT(*) AS num_rows,
    SUM(column_name) AS total_value
FROM knowledge
WHERE some_condition

Mit Duckdb können Sie Werte über Zeilen hinweg mithilfe von SQL -Funktionen wie Summe und in einem kompakten Block zählen.

filtered = df(df("some_condition"))
num_rows = filtered.form(0)
total_value = filtered("column_name").sum()

In Pandas müssen Sie zunächst den Datenrahmen filtern und dann separat mithilfe von Kettenmethoden zählen und summen.

Duckdb ist prägnanter und leichter zu lesen und erfordert keine Zwischenvariablen.

Aggregation im Uber -Datenprojekt

• Intestine, gehen wir zum zweiten Bonusprogramm, Choice 2. Nach der Projektbeschreibung erhalten die Fahrer 4 USD professional Reise, wenn:
• Sie absolvieren mindestens 12 Reisen.

Eine Bewertung von 4,7 oder besser haben.

SELECT 
    COUNT(*) AS qualified_drivers,
    SUM("Journeys Accomplished") * 4 AS total_payout
FROM knowledge
WHERE "Journeys Accomplished" >= 12
  AND Ranking >= 4.7

Anstatt nur die Treiber zu zählen, müssen wir diesmal die Anzahl der Reisen hinzufügen, die sie abgeschlossen haben, da der Bonus professional Reise nicht professional Particular person bezahlt wird.

Die Anzahl hier sagt uns, wie viele Fahrer qualifiziert sind. Um die Gesamtauszahlung zu berechnen, berechnen wir ihre Reisen und multiplizieren Sie sie mit 4 US -Greenback, wie dies in Choice 2 erforderlich ist.

Aggregation in Duckdb

Hier ist die Ausgabe.

Aggregation in Duckdb

Mit Duckdb müssen wir nicht durch die Zeilen schauen oder benutzerdefinierte Aggregationen erstellen. Die Summenfunktion kümmert sich um alles, was wir brauchen.

3.. Erkennen Sie Überlappungen und Unterschiede unter Verwendung der Booleschen Logik

In SQL können Sie die Bedingungen problemlos mit einer Booleschen Logik wie und, oder und nicht verwenden.

Implementierung der Booleschen Logik in Duckdb gegen Pandas

SELECT *
FROM knowledge
WHERE condition_a
  AND condition_b
  AND NOT (condition_c)

Duckdb unterstützt die Boolesche Logik nativ in der WHERE -Klausel mit und, oder, und nicht.

filtered = df(
    (df("condition_a")) &
    (df("condition_b")) &
    ~(df("condition_c"))
)

Pandas benötigt eine Kombination von logischen Operatoren mit Masken und Klammern, einschließlich der Verwendung von „~“ zur Negation.

Während beide funktionsfähig sind, ist Duckdb leichter zu argumentieren, wann die Logik Ausschlüsse oder verschachtelte Bedingungen beinhaltet.

Boolesche Logik für das Uber -Datenprojekt

Jetzt haben wir Choice 1 und Choice 2 berechnet. Was kommt als nächstes? Jetzt ist es Zeit, den Vergleich durchzuführen. Erinnere dich an unsere nächste Frage.

Boolesche Logik in Duckdb

SELECT COUNT(*) AS only_option1
FROM knowledge
WHERE "Provide Hours" >= 8
  AND CAST(REPLACE("Settle for Charge", '%', '') AS DOUBLE) >= 90
  AND "Journeys Accomplished" >= 10
  AND Ranking >= 4.7
  AND NOT ("Journeys Accomplished" >= 12 AND Ranking >= 4.7)

Hier können wir die Boolesche Logik verwenden. Wir werden eine Kombination von und und nicht verwenden.

Hier ist die Ausgabe.

Boolesche Logik in Duckdb

• Lassen Sie es uns aufschlüsseln:
• Die ersten vier Bedingungen finden Sie für Choice 1.

Der nicht (..) Teil wird verwendet, um Treiber auszuschließen, die sich auch für Choice 2 qualifizieren.

Es ist ziemlich einfach, oder?

4.. Schnelle Kohortengrößen mit bedingten Filtern

Manchmal möchten Sie verstehen, wie groß eine bestimmte Gruppe oder Kohorte in Ihren Daten ist.

Implementierung von bedingten Filtern in Duckdb vs Pandas?

SELECT 
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM knowledge), 2) AS share
FROM knowledge
WHERE condition_1
  AND condition_2
  AND condition_3

Duckdb verarbeitet die Kohortenfilterung und die prozentuale Berechnung mit einer SQL -Abfrage, auch einschließlich Unterabfragen.

filtered = df(
    (df("condition_1")) &
    (df("condition_2")) &
    (df("condition_3"))
)
share = spherical(100.0 * len(filtered) / len(df), 2)

Pandas erfordert Filterung, Zählung und manuelle Abteilung, um Prozentsätze zu berechnen.

Duckdb hier ist sauberer und schneller. Es minimiert die Anzahl der Schritte und vermeidet wiederholten Code.

Kohortengröße für Uber -Datenprojekt

• Jetzt sind wir in der letzten Frage von Szenario 1. In dieser Frage möchte Uber, dass wir die Treiber herausfinden, die einige Aufgaben nicht erfüllen konnten, wie Reisen und Akzeptanzrate, aber höhere Bewertungen, insbesondere die Treiber.
• Weniger als 10 Reisen abgeschlossen
• Hatte eine Akzeptanzrate von weniger als 90

Hatte eine Bewertung über 4,7

SELECT 
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM knowledge), 2) AS share
FROM knowledge
WHERE "Journeys Accomplished" < 10
  AND CAST(REPLACE("Settle for Charge", '%', '') AS DOUBLE) = 4.7

Nun, dies sind drei getrennte Filter, und wir möchten den Prozentsatz der Fahrer berechnen, die jeden von ihnen erfüllen. Lassen Sie uns die Abfrage sehen.

Hier ist die Ausgabe.

Kohortengrößen in Duckdb

Hier filterten wir die Reihen, in denen alle drei Bedingungen erfüllt waren, zählten sie und teilten sie durch die Gesamtzahl der Fahrer, um einen Prozentsatz zu erhalten.

5. grundlegende arithmetische Abfragen zur Einnahmenmodellierung

Nehmen wir nun an, Sie möchten eine grundlegende Mathematik machen. Sie können Ausdrücke direkt in Ihre Auswahlanweisung schreiben.

Implementierung von Arithmetik in Duckdb gegen Pandas

SELECT 
    daily_income * work_days * weeks_per_year AS annual_revenue,
    weekly_cost * weeks_per_year AS total_cost,
    (daily_income * work_days * weeks_per_year) - (weekly_cost * weeks_per_year) AS net_income
FROM knowledge

Mit Duckdb kann die Arithmetik wie ein Taschenrechner direkt in die Auswahlklausel geschrieben werden.

daily_income = 200
weeks_per_year = 49
work_days = 6
weekly_cost = 500

annual_revenue = daily_income * work_days * weeks_per_year
total_cost = weekly_cost * weeks_per_year
net_income = annual_revenue - total_cost

Pandas benötigt mehrere Zwischenberechnungen in separaten Variablen für dasselbe Ergebnis.

Duckdb vereinfacht die mathematische Logik in einem lesbaren SQL -Block, während Pandas mit variablen Zuordnungen ein wenig überfüllt ist.

Grundarithmetik im Uber -Datenprojekt

In Szenario 2 bat Uber uns zu berechnen, wie viel Geld (nach Ausgaben) der Fahrer professional Jahr verdient, ohne mit Uber zusammenzuarbeiten. Hier sind einige Ausgaben wie Gasoline, Miete und Versicherung.

Grundarithmetik in Duckdb

SELECT 
    200 * 6 * (52 - 3) AS annual_revenue,
    200 * (52 - 3) AS gas_expense,
    500 * (52 - 3) AS rent_expense,
    400 * 12 AS insurance_expense,
    (200 * 6 * (52 - 3)) 
      - (200 * (52 - 3) + 500 * (52 - 3) + 400 * 12) AS net_income

Berechnen wir nun den Jahresumsatz und subtrahieren die Ausgaben daraus.

Hier ist die Ausgabe.

Grundarithmetik in Duckdb

Mit Duckdb können Sie dies wie einen SQL -Matrixblock schreiben. Sie benötigen keine PANDAS -Datenrahmen oder manuelle Schleifen!

6. Bedingte Berechnungen für die dynamische Kostenplanung

Was ist, wenn sich Ihre Kostenstruktur anhand bestimmter Bedingungen ändert?

Implementierung bedingter Berechnungen in Duckdb gegen Pandas

SELECT 
    original_cost * 1.05 AS increased_cost,
    original_cost * 0.8 AS discounted_cost,
    0 AS removed_cost,
    (original_cost * 1.05 + original_cost * 0.8) AS total_new_cost

Mit Duckdb können Sie eine bedingte Logik unter Verwendung von arithmetischen Anpassungen innerhalb Ihrer Abfrage anwenden.

weeks_worked = 49
fuel = 200
insurance coverage = 400

gas_expense = fuel * 1.05 * weeks_worked
insurance_expense = insurance coverage * 0.8 * 12
rent_expense = 0
complete = gas_expense + insurance_expense

Pandas verwendet dieselbe Logik mit mehreren mathematischen Zeilen und manuellen Aktualisierungen für Variablen.

Duckdb verwandelt eine mehrstufige Logik in Pandas in einen einzelnen SQL-Expression.

Bedingte Berechnungen im Uber -Datenprojekt

• In diesem Szenario modellieren wir jetzt, was passiert, wenn der Fahrer mit Uber zusammenarbeitet und ein Auto kauft. Die Ausgaben ändern sich wie
• Die Gaskosten steigt um 5% um 5%
• Die Versicherung nimmt um 20% ab

con.execute("""
SELECT 
    200 * 1.05 * 49 AS gas_expense,
    400 * 0.8 * 12 AS insurance_expense,
    0 AS rent_expense,
    (200 * 1.05 * 49) + (400 * 0.8 * 12) AS total_expense
""").fetchdf()

Kein Mietkosten mehr

Hier ist die Ausgabe.

Bedingte Berechnungen in Duckdb

7. Zielgetriebene Mathematik für Einnahmenziele

Manchmal kann Ihre Analyse durch ein Geschäftsziel wie das Erträge auf ein Einnahmeziel oder die Abdeckung einer einmaligen Kosten angetrieben werden.

Implementierung von zielgerichteter Mathematik in Duckdb gegen Pandas Duckdb verarbeitet eine mehrstufige Logik mithilfeCtes

WITH vars AS (
  SELECT base_income, cost_1, cost_2, target_item
),
calc AS (
  SELECT 
    base_income - (cost_1 + cost_2) AS current_profit,
    cost_1 * 1.1 + cost_2 * 0.8 + target_item AS new_total_expense
  FROM vars
),
ultimate AS (
  SELECT 
    current_profit + new_total_expense AS required_revenue,
    required_revenue / 49 AS required_weekly_income
  FROM calc
)
SELECT required_weekly_income FROM ultimate

. Es macht die Abfrage modular und leicht zu lesen.

weeks = 49
original_income = 200 * 6 * weeks
original_cost = (200 + 500) * weeks + 400 * 12
net_income = original_income - original_cost

# new bills + automobile price
new_gas = 200 * 1.05 * weeks
new_insurance = 400 * 0.8 * 12
car_cost = 40000

required_revenue = net_income + new_gas + new_insurance + car_cost
required_weekly_income = required_revenue / weeks

Pandas erfordert die Verschachtung von Berechnungen und die Wiederverwendung früherer Variablen, um eine Duplizierung zu vermeiden.

Mit Duckdb können Sie Schritt für Schritt eine logische Pipeline erstellen, ohne Ihr Notizbuch mit verstreuten Code zu überfüllen.

Zielgesteuerte Mathematik im Uber-Datenprojekt

Nachdem wir die neuen Kosten modelliert haben, beantworten wir die endgültige Geschäftsfrage:

• Wie viel mehr muss der Fahrer professional Woche verdienen, um beide zu tun?
• Zahlen Sie innerhalb eines Jahres ein 40.000 US -Greenback -Auto ab

Das gleiche jährliche Nettoeinkommen beibehalten

WITH vars AS (
  SELECT 
    52 AS total_weeks_per_year,
    3 AS weeks_off,
    6 AS days_per_week,
    200 AS fare_per_day,
    400 AS monthly_insurance,
    200 AS gas_per_week,
    500 AS vehicle_rent,
    40000 AS car_cost
),
base AS (
  SELECT 
    total_weeks_per_year,
    weeks_off,
    days_per_week,
    fare_per_day,
    monthly_insurance,
    gas_per_week,
    vehicle_rent,
    car_cost,
    total_weeks_per_year - weeks_off AS weeks_worked,
    (fare_per_day * days_per_week * (total_weeks_per_year - weeks_off)) AS original_annual_revenue,
    (gas_per_week * (total_weeks_per_year - weeks_off)) AS original_gas,
    (vehicle_rent * (total_weeks_per_year - weeks_off)) AS original_rent,
    (monthly_insurance * 12) AS original_insurance
  FROM vars
),
examine AS (
  SELECT *,
    (original_gas + original_rent + original_insurance) AS original_total_expense,
    (original_annual_revenue - (original_gas + original_rent + original_insurance)) AS original_net_income
  FROM base
),
new_costs AS (
  SELECT *,
    gas_per_week * 1.05 * weeks_worked AS new_gas,
    monthly_insurance * 0.8 * 12 AS new_insurance
  FROM examine
),
ultimate AS (
  SELECT *,
    new_gas + new_insurance + car_cost AS new_total_expense,
    original_net_income + new_gas + new_insurance + car_cost AS required_revenue,
    required_revenue / weeks_worked AS required_weekly_revenue,
    original_annual_revenue / weeks_worked AS original_weekly_revenue
  FROM new_costs
)
SELECT 
  ROUND(required_weekly_revenue, 2) AS required_weekly_revenue,
  ROUND(required_weekly_revenue - original_weekly_revenue, 2) AS weekly_uplift
FROM ultimate

Schreiben wir nun den Code, der diese Logik darstellt.

Hier ist die Ausgabe.

Zielorientierte Mathematik in Duckdb

Letzte Gedanken

In diesem Artikel haben wir untersucht, wie Sie mit Duckdb eine Verbindung herstellen und Daten analysieren. Anstatt lange Pandas -Funktionen zu verwenden, haben wir SQL -Abfragen verwendet. Wir haben dies auch anhand eines realen Datenprojekts durchgeführt, das Uber im Rekrutierungsprozess von Knowledge Scientist angefordert hat.
Für Datenwissenschaftler, die an analysierten Aufgaben arbeiten, ist es eine leichte, aber leistungsstarke Various zu Pandas. Versuchen Sie es bei Ihrem nächsten Projekt, insbesondere wenn SQL Logic besser zum Downside passt.

Nate Rosidi