{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "!pip install getdaft --pre --extra-index-url https://pypi.anaconda.org/daft-nightly/simple" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "```{hint}\n", "✨✨✨ **Run this notebook on Google Colab** ✨✨✨\n", "\n", "You can [run this notebook yourself with Google Colab](https://colab.research.google.com/github/Eventual-Inc/Daft/blob/main/docs/source/learn/10-min.ipynb)!\n", "```" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "# 10 minutes Quickstart\n", "\n", "This is a short introduction to all the main functionality in Daft, geared towards new users.\n", "\n", "We import from daft as follows:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "from daft import DataFrame" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## DataFrame creation\n", "\n", "See also: [API Reference: DataFrame Construction](df-construction)\n", "\n", "We can create a DataFrame from a dictionary of columns - this is a dictionary where the keys are strings representing the columns' names and the values are equal-length lists representing the columns' values." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "2023-01-21 14:36:57.855 | INFO | daft.context:runner:85 - Using PyRunner\n" ] } ], "source": [ "import datetime\n", "\n", "df = DataFrame.from_pydict({\n", " \"A\": [1, 2, 3, 4],\n", " \"B\": [1.5, 2.5, 3.5, 4.5],\n", " \"C\": [True, True, False, False],\n", " \"D\": [\"a\", \"b\", \"c\", \"d\"],\n", " \"E\": [b\"a\", b\"b\", b\"c\", b\"d\"],\n", " \"F\": [datetime.date(1994, 1, 1), datetime.date(1994, 1, 2), datetime.date(1994, 1, 3), datetime.date(1994, 1, 4)],\n", " \"G\": [[1, 1, 1], [2, 2, 2], [3, 3, 3], [4, 4, 4]],\n", "})" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "You can also load DataFrames from other sources, such as:\n", "\n", "1. CSV files: `DataFrame.read_csv(\"s3://bucket/*.csv\")`\n", "2. Parquet files: `DataFrame.read_parquet(\"/path/*.parquet\")`\n", "3. JSON line-delimited files: `DataFrame.read_json(\"/path/*.parquet\")`\n", "4. Files on disk: `DataFrame.from_glob_path(\"/path/*.jpeg\")`\n", "\n", "Daft automatically supports local paths as well as paths to object storage such as AWS S3." ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "Inspect your dataframe by printing the `df` variable" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "
A
INTEGER		B
FLOAT		C
LOGICAL		D
STRING		E
BYTES		F
DATE		G
PY[list]
\n", " (No data to display: Dataframe not materialized)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+--------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+========+============+\n", "+-----------+---------+-----------+----------+---------+--------+------------+\n", "(No data to display: Dataframe not materialized)" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Executing your DataFrame and Displaying Data\n", "\n", "Notice that instead of the contents of the dataframe, the message `(no data to display: Dataframe not materialized)` is displayed when we printed our dataframe in the previous section.\n", "\n", "This is because Daft is **lazy** and only executes computations when explicitly told to do so. When you call methods on DataFrames such as `.select`, `.where` and `.read_csv`, Daft actually only enqueues these operations in a *Logical Plan*. You can examine this logical plan using `DataFrame.explain()`:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "┌─InMemoryScan\n", " output=[col(A#0: INTEGER), col(B#1: FLOAT), col(C#2: LOGICAL), col(D#3: STRING),\n", " col(E#4: BYTES), col(F#5: DATE), col(G#6: PY[list])]\n", " cache_id='6eea47a9faa44b2bb50c495246920c7f'\n", " partitioning=PartitionSpec(scheme=UNKNOWN, num_partitions=1, by=None)\n", " \n", "\n" ] } ], "source": [ "df.explain()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "Our currently plan says that there is only one operation to be executed, which is an `InMemoryScan` operation that reads from a set of in-memory data.\n", "\n", "To execute all operations on all data in the current DataFrame's plan, you can use the `DataFrame.collect()` method." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
2 2.5true b b'b' 1994-01-02 [2, 2, 2]
3 3.5false c b'c' 1994-01-03 [3, 3, 3]
4 4.5false d b'd' 1994-01-04 [4, 4, 4]
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "`DataFrame.collect()` is useful because it executes computations on **all** your data, and shows you a little preview of the materialized results. These results are then kept in memory so that subsequent operations will avoid recomputations.\n", "\n", "However, if you only wish to \"peek\" at your data instead of materializing the entire dataframe (e.g. your dataframe has a million rows, and you only want to view the first 10 without materializing the entire result set in memory), you can use `DataFrame.show(N)` instead to view the first `N` rows of your dataframe. This is especially useful when developing interactively on small samples of data." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
2 2.5true b b'b' 1994-01-02 [2, 2, 2]
\n", " (Showing first 2 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 2 rows)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.show(2)" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting Data" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "You can sort a dataframe with `DataFrame.sort`, which we do so here in descending order:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
4 4.5false d b'd' 1994-01-04 [4, 4, 4]
3 3.5false c b'c' 1994-01-03 [3, 3, 3]
2 2.5true b b'b' 1994-01-02 [2, 2, 2]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.sort(df[\"A\"], desc=True).collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data Selection\n", "\n", "You can limit the number of rows in a dataframe by calling `DataFrame.limit`." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
\n", " (Showing first 1 of 1 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 1 of 1 rows)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_limited = df.limit(1)\n", "df_limited.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To select just a few columns, you can use `DataFrame.select`:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT
1 1.5
2 2.5
3 3.5
4 4.5
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+\n", "| A | B |\n", "| INTEGER | FLOAT |\n", "+===========+=========+\n", "| 1 | 1.5 |\n", "+-----------+---------+\n", "| 2 | 2.5 |\n", "+-----------+---------+\n", "| 3 | 3.5 |\n", "+-----------+---------+\n", "| 4 | 4.5 |\n", "+-----------+---------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_selected = df.select(df[\"A\"], df[\"B\"])\n", "df_selected.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Column selection also allows you to rename columns using `Expression.alias`:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A2
INTEGER		 B
FLOAT
1 1.5
2 2.5
3 3.5
4 4.5
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+\n", "| A2 | B |\n", "| INTEGER | FLOAT |\n", "+===========+=========+\n", "| 1 | 1.5 |\n", "+-----------+---------+\n", "| 2 | 2.5 |\n", "+-----------+---------+\n", "| 3 | 3.5 |\n", "+-----------+---------+\n", "| 4 | 4.5 |\n", "+-----------+---------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_renamed = df.select(df[\"A\"].alias(\"A2\"), df[\"B\"])\n", "df_renamed.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To drop columns from the dataframe, call `DataFrame.exclude`:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1.5true a b'a' 1994-01-01 [1, 1, 1]
2.5true b b'b' 1994-01-02 [2, 2, 2]
3.5false c b'c' 1994-01-03 [3, 3, 3]
4.5false d b'd' 1994-01-04 [4, 4, 4]
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+---------+-----------+----------+---------+------------+------------+\n", "| B | C | D | E | F | G |\n", "| FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+=========+===========+==========+=========+============+============+\n", "| 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+---------+-----------+----------+---------+------------+------------+\n", "| 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+---------+-----------+----------+---------+------------+------------+\n", "| 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] |\n", "+---------+-----------+----------+---------+------------+------------+\n", "| 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] |\n", "+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_excluded = df.exclude(\"A\")\n", "df_excluded.collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Expressions\n", "\n", "See: [Expressions](user_guides/expressions.rst)\n", "\n", "Expressions are an API for defining computation that needs to happen over your columns.\n", "\n", "For example, to create a new column that is just the column A incremented by 1:" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		A_plus1
INTEGER
1 1.5true a b'a' 1994-01-01 [1, 1, 1] 2
2 2.5true b b'b' 1994-01-02 [2, 2, 2] 3
3 3.5false c b'c' 1994-01-03 [3, 3, 3] 4
4 4.5false d b'd' 1994-01-04 [4, 4, 4] 5
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+-----------+\n", "| A | B | C | D | E | F | G | A_plus1 |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | INTEGER |\n", "+===========+=========+===========+==========+=========+============+============+===========+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | 2 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+-----------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+-----------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | 4 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+-----------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | 5 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+-----------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_A_plus1 = df.with_column(\"A_plus1\", df[\"A\"] + 1) # does not run any computation\n", "df_A_plus1.collect() # materializes the new DataFrame, which includes the new column \"A_plus1\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Method Accessors\n", "\n", "Some Expression methods are only allowed on certain types and are accessible through \"method accessors\" such as the `Expression.str` accessor (see: [Expression Accessor Properties](expression-accessor-properties)).\n", "\n", "For example, the `.str.length()` expression is only valid when run on a STRING column:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		D_length
INTEGER
1 1.5true a b'a' 1994-01-01 [1, 1, 1] 1
2 2.5true b b'b' 1994-01-02 [2, 2, 2] 1
3 3.5false c b'c' 1994-01-03 [3, 3, 3] 1
4 4.5false d b'd' 1994-01-04 [4, 4, 4] 1
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+------------+\n", "| A | B | C | D | E | F | G | D_length |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | INTEGER |\n", "+===========+=========+===========+==========+=========+============+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_E_length = df.with_column(\"D_length\", df[\"D\"].str.length())\n", "df_E_length.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Another example of a useful method accessor is the `.url` accessor. You can use `.url.download()` to download data from a column of URLs like so:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
urls
STRING 		image_bytes
BYTES
http://farm9.staticflickr.com/8186/8119368305_4e622c8349_...b'\\xff\\xd8\\xff\\xe1\\x00TExif\\x00\\x00MM\\x00*\\x00\\x00\\x00\\x0...
http://farm1.staticflickr.com/1/127244861_ab0c0381e7_z.jpg b'\\xff\\xd8\\xff\\xe1\\x00(Exif\\x00\\x00MM\\x00*\\x00\\x00\\x00\\x0...
http://farm3.staticflickr.com/2169/2118578392_1193aa04a0_...b'\\xff\\xd8\\xff\\xe1\\x00\\x16Exif\\x00\\x00MM\\x00*\\x00\\x00\\x00...
\n", " (Showing first 3 of 3 rows)\n", "
\n", " " ], "text/plain": [ "+----------------------+----------------------+\n", "| urls | image_bytes |\n", "| STRING | BYTES |\n", "+======================+======================+\n", "| http://farm9.staticf | b'\\xff\\xd8\\xff\\xe1\\x |\n", "| lickr.com/8186/81193 | 00TExif\\x00\\x00MM\\x0 |\n", "| 68305_4e622c8349_... | 0*\\x00\\x00\\x00\\x0... |\n", "+----------------------+----------------------+\n", "| http://farm1.staticf | b'\\xff\\xd8\\xff\\xe1\\x |\n", "| lickr.com/1/12724486 | 00(Exif\\x00\\x00MM\\x0 |\n", "| 1_ab0c0381e7_z.jpg | 0*\\x00\\x00\\x00\\x0... |\n", "+----------------------+----------------------+\n", "| http://farm3.staticf | b'\\xff\\xd8\\xff\\xe1\\x |\n", "| lickr.com/2169/21185 | 00\\x16Exif\\x00\\x00MM |\n", "| 78392_1193aa04a0_... | \\x00*\\x00\\x00\\x00... |\n", "+----------------------+----------------------+\n", "(Showing first 3 of 3 rows)" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "image_url_df = DataFrame.from_pydict({\n", " \"urls\": [\n", " \"http://farm9.staticflickr.com/8186/8119368305_4e622c8349_z.jpg\",\n", " \"http://farm1.staticflickr.com/1/127244861_ab0c0381e7_z.jpg\",\n", " \"http://farm3.staticflickr.com/2169/2118578392_1193aa04a0_z.jpg\",\n", " ],\n", "})\n", "image_downloaded_df = image_url_df.with_column(\"image_bytes\", image_url_df[\"urls\"].url.download())\n", "image_downloaded_df.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For a full list of all Expression methods and operators, see: [Expressions API Docs](../api_docs/expressions.rst)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Operations on PY columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "PY columns contain Python objects and operations called on these columns will be mapped on each object as well.\n", "\n", "To work with such columns, Daft provides a few useful Expression methods.\n", "\n", "For example, to repeat each list in column `G` 3 times, we can use the Python `list`'s native Python `*` operator:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		G_repeat
PY[object]
1 1.5true a b'a' 1994-01-01 [1, 1, 1] [1, 1, 1, 1, 1, 1, 1, 1, 1]
2 2.5true b b'b' 1994-01-02 [2, 2, 2] [2, 2, 2, 2, 2, 2, 2, 2, 2]
3 3.5false c b'c' 1994-01-03 [3, 3, 3] [3, 3, 3, 3, 3, 3, 3, 3, 3]
4 4.5false d b'd' 1994-01-04 [4, 4, 4] [4, 4, 4, 4, 4, 4, 4, 4, 4]
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+--------------------+\n", "| A | B | C | D | E | F | G | G_repeat |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | PY[object] |\n", "+===========+=========+===========+==========+=========+============+============+====================+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | [1, 1, 1, 1, 1, 1, |\n", "| | | | | | | | 1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | [2, 2, 2, 2, 2, 2, |\n", "| | | | | | | | 2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | [3, 3, 3, 3, 3, 3, |\n", "| | | | | | | | 3, 3, 3] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | [4, 4, 4, 4, 4, 4, |\n", "| | | | | | | | 4, 4, 4] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_G_extend_0 = df.with_column(\"G_repeat\", df[\"G\"] * 3)\n", "df_G_extend_0.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To call a method on each list in column `G`, we can use the `.as_py` method. For example, here we use the Python `list`'s `.count()` method to count the number of occurences of the integer in column A:" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		G_count_A
PY[object]
1 1.5true a b'a' 1994-01-01 [1, 1, 1] 3
2 2.5true b b'b' 1994-01-02 [2, 2, 2] 3
3 3.5false c b'c' 1994-01-03 [3, 3, 3] 3
4 4.5false d b'd' 1994-01-04 [4, 4, 4] 3
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+--------------+\n", "| A | B | C | D | E | F | G | G_count_A |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | PY[object] |\n", "+===========+=========+===========+==========+=========+============+============+==============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+--------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_G_count_A = df.with_column(\"G_count_A\", df[\"G\"].as_py(list).count(df[\"A\"]))\n", "df_G_count_A.collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "For more complicated functions, you can use `.apply(f)` to call a function `f` on every object in the column. For example, here we construct a Numpy array for every list in column G.\n", "\n", "```{note}\n", "It is good practice to supply Daft with the return type of your function.\n", "\n", "You can do this using the ``return_dtype=`` keyword argument in ``.apply``, or by correctly type-annotating your function like in the ``list_to_numpy`` function below.\n", "\n", "Annotating the return type of your function correctly lets Daft effectively optimize your data and operations under the hood. For example, in this case we specify ``return_dtype=np.ndarray`` which tells Daft that each row in this column contains a Numpy array object.\n", "```" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		G_to_numpy
PY[ndarray]
1 1.5true a b'a' 1994-01-01 [1, 1, 1] <np.ndarray
shape=(3,)
dtype=int64>
2 2.5true b b'b' 1994-01-02 [2, 2, 2] <np.ndarray
shape=(3,)
dtype=int64>
3 3.5false c b'c' 1994-01-03 [3, 3, 3] <np.ndarray
shape=(3,)
dtype=int64>
4 4.5false d b'd' 1994-01-04 [4, 4, 4] <np.ndarray
shape=(3,)
dtype=int64>
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+---------------+\n", "| A | B | C | D | E | F | G | G_to_numpy |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | PY[ndarray] |\n", "+===========+=========+===========+==========+=========+============+============+===============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | [1 1 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | [2 2 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | [3 3 3] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | [4 4 4] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "\n", "def list_to_numpy(l: list) -> np.ndarray:\n", " return np.array(l)\n", "\n", "df_G_to_numpy = df.with_column(\"G_to_numpy\", df[\"G\"].apply(list_to_numpy))\n", "df_G_to_numpy.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Iterable types such as a PY[list] column can be exploded with `DataFrame.explode`, splitting each list into a row of its own and repeating the other columns:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[object]
1 1.5true a b'a' 1994-01-01 1
1 1.5true a b'a' 1994-01-01 1
1 1.5true a b'a' 1994-01-01 1
2 2.5true b b'b' 1994-01-02 2
2 2.5true b b'b' 1994-01-02 2
2 2.5true b b'b' 1994-01-02 2
3 3.5false c b'c' 1994-01-03 3
3 3.5false c b'c' 1994-01-03 3
3 3.5false c b'c' 1994-01-03 3
4 4.5false d b'd' 1994-01-04 4
\n", " (Showing first 10 of 12 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[object] |\n", "+===========+=========+===========+==========+=========+============+==============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | 1 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | 2 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | 2 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | 2 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | 3 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | 4 |\n", "+-----------+---------+-----------+----------+---------+------------+--------------+\n", "(Showing first 10 of 12 rows)" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_G_exploded = df.explode(df[\"G\"])\n", "df_G_exploded.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### User-Defined Functions\n", "\n", "`.apply` makes it really easy to map a function on a single column, but is limited in 2 main ways:\n", "\n", "1. Only runs on a single column: some algorithms require multiple columns as inputs\n", "2. Only runs on a single row: some algorithms run much more efficiently when run on a batch of rows instead\n", "\n", "To overcome these limitations, you can use User-Defined Functions (UDFs).\n", "\n", "See Also: [UDF User Guide](user_guides/udf)" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		F_add_A_days
DATE
1 1.5true a b'a' 1994-01-01 [1, 1, 1] 1994-01-02
2 2.5true b b'b' 1994-01-02 [2, 2, 2] 1994-01-04
3 3.5false c b'c' 1994-01-03 [3, 3, 3] 1994-01-06
4 4.5false d b'd' 1994-01-04 [4, 4, 4] 1994-01-08
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+----------------+\n", "| A | B | C | D | E | F | G | F_add_A_days |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | DATE |\n", "+===========+=========+===========+==========+=========+============+============+================+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | 1994-01-02 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+----------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | 1994-01-04 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+----------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | 1994-01-06 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+----------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | 1994-01-08 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+----------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import datetime\n", "from daft import udf\n", "import polars as pl\n", "\n", "@udf(return_dtype=datetime.date, input_columns={\"f_date_data\": pl.Series, \"a_days_data\": pl.Series})\n", "def add_days(f_date_data: pl.Series, a_days_data: pl.Series):\n", " return f_date_data + pl.duration(days=a_days_data)\n", "\n", "df.with_column(\"F_add_A_days\", add_days(df[\"F\"], df[\"A\"])).collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The simple UDF demonstrated above is a \"stateless UDF\", and no state is maintained between invocations of the function. In certain use-cases, it can be important to maintain some state with a \"stateful UDF\", which you can write using a Class instead of a Function. For example, running machine learning models often requires downloading some trained weights and initializing the model in memory/on a GPU, which an expensive operation and should be cached between UDF invocations." ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list] 		expensive_model_results
FLOAT
1 1.5true a b'a' 1994-01-01 [1, 1, 1] 8.07
2 2.5true b b'b' 1994-01-02 [2, 2, 2] 13.86
3 3.5false c b'c' 1994-01-03 [3, 3, 3] 19.65
4 4.5false d b'd' 1994-01-04 [4, 4, 4] 25.44
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+---------------------------+\n", "| A | B | C | D | E | F | G | expensive_model_results |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] | FLOAT |\n", "+===========+=========+===========+==========+=========+============+============+===========================+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] | 8.07 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] | 13.86 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] | 19.65 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] | 25.44 |\n", "+-----------+---------+-----------+----------+---------+------------+------------+---------------------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@udf(return_dtype=float, input_columns={\"a_data\": np.ndarray, \"b_data\": np.ndarray})\n", "class RunExpensiveModel:\n", "\n", " def __init__(self):\n", " # Initialize and cache an \"expensive\" model between invocations of the UDF\n", " self.model = np.array([1.23, 4.56])\n", " \n", " def __call__(self, a_data: np.ndarray, b_data: np.ndarray):\n", " return np.matmul(self.model, np.array([a_data, b_data]))\n", "\n", "df.with_column(\"expensive_model_results\", RunExpensiveModel(df[\"A\"], df[\"B\"])).collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Filtering Data\n", "\n", "You can filter rows in dataframe using `DataFrame.where`, which accepts a LOGICAL type Expression as an argument:" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
2 2.5true b b'b' 1994-01-02 [2, 2, 2]
\n", " (Showing first 2 of 2 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 2 of 2 rows)" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Keep only rows where values in column \"A\" are less than 3\n", "df_filtered = df.where(df[\"A\"] < 3)\n", "df_filtered.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Missing Data\n", "\n", "All columns in Daft are \"nullable\" by default. Unlike other frameworks such as Pandas, Daft differentiates between \"null\" (missing) and \"nan\" (stands for not a number - a special value indicating an invalid float)." ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
floats
FLOAT 		floats_is_null
LOGICAL 		floats_is_nan
LOGICAL
1.5 false false
None true none
nan false true
\n", " (Showing first 3 of 3 rows)\n", "
\n", " " ], "text/plain": [ "+----------+------------------+-----------------+\n", "| floats | floats_is_null | floats_is_nan |\n", "| FLOAT | LOGICAL | LOGICAL |\n", "+==========+==================+=================+\n", "| 1.5 | false | false |\n", "+----------+------------------+-----------------+\n", "| None | true | none |\n", "+----------+------------------+-----------------+\n", "| nan | false | true |\n", "+----------+------------------+-----------------+\n", "(Showing first 3 of 3 rows)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "missing_data_df = DataFrame.from_pydict({\n", " \"floats\": [1.5, None, float(\"nan\")],\n", "})\n", "missing_data_df = missing_data_df \\\n", " .with_column(\"floats_is_null\", missing_data_df[\"floats\"].is_null()) \\\n", " .with_column(\"floats_is_nan\", missing_data_df[\"floats\"].is_nan())\n", "\n", "missing_data_df.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To fill in missing values, a useful Expression is the `.if_else` expression which can be used to fill in values if the value is null:" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
floats
FLOAT 		floats_is_null
LOGICAL 		floats_is_nan
LOGICAL 		filled_in_floats
FLOAT
1.5 false false 1.5
None true none 0
nan false true nan
\n", " (Showing first 3 of 3 rows)\n", "
\n", " " ], "text/plain": [ "+----------+------------------+-----------------+--------------------+\n", "| floats | floats_is_null | floats_is_nan | filled_in_floats |\n", "| FLOAT | LOGICAL | LOGICAL | FLOAT |\n", "+==========+==================+=================+====================+\n", "| 1.5 | false | false | 1.5 |\n", "+----------+------------------+-----------------+--------------------+\n", "| None | true | none | 0 |\n", "+----------+------------------+-----------------+--------------------+\n", "| nan | false | true | nan |\n", "+----------+------------------+-----------------+--------------------+\n", "(Showing first 3 of 3 rows)" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "missing_data_df = missing_data_df.with_column(\"filled_in_floats\", (missing_data_df[\"floats\"].is_null()).if_else(0.0, missing_data_df[\"floats\"]))\n", "missing_data_df.collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Merging Dataframes\n", "\n", "DataFrames can be joined with `.join`. Here is a naive example of a self-join where we join `df` on itself with column \"A\" as the join key." ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": [ "joined_df = df.join(df, on=\"A\")" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
INTEGER		 B
FLOAT		C
LOGICAL 		D
STRING 		E
BYTES 		F
DATE 		G
PY[list]
1 1.5true a b'a' 1994-01-01 [1, 1, 1]
2 2.5true b b'b' 1994-01-02 [2, 2, 2]
3 3.5false c b'c' 1994-01-03 [3, 3, 3]
4 4.5false d b'd' 1994-01-04 [4, 4, 4]
\n", " (Showing first 4 of 4 rows)\n", "
\n", " " ], "text/plain": [ "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| A | B | C | D | E | F | G |\n", "| INTEGER | FLOAT | LOGICAL | STRING | BYTES | DATE | PY[list] |\n", "+===========+=========+===========+==========+=========+============+============+\n", "| 1 | 1.5 | true | a | b'a' | 1994-01-01 | [1, 1, 1] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 2 | 2.5 | true | b | b'b' | 1994-01-02 | [2, 2, 2] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 3 | 3.5 | false | c | b'c' | 1994-01-03 | [3, 3, 3] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "| 4 | 4.5 | false | d | b'd' | 1994-01-04 | [4, 4, 4] |\n", "+-----------+---------+-----------+----------+---------+------------+------------+\n", "(Showing first 4 of 4 rows)" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "joined_df.collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Grouping and Aggregations\n", "\n", "Groupby aggregation operations over a dataset happens in 2 phases:\n", "\n", "1. Splitting the data into groups based on some criteria using `DataFrame.groupby`\n", "2. Specifying how to aggregate the data for each group using `GroupedDataFrame.agg`\n", "\n", "Let's take a look at an example:" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
STRING 		B
STRING 		C
INTEGER		 D
INTEGER
foo a 0 0
bar a 1 1
foo b 2 2
bar c 3 3
foo b 4 4
bar b 5 5
foo a 6 6
foo c 7 7
\n", " (Showing first 8 of 8 rows)\n", "
\n", " " ], "text/plain": [ "+----------+----------+-----------+-----------+\n", "| A | B | C | D |\n", "| STRING | STRING | INTEGER | INTEGER |\n", "+==========+==========+===========+===========+\n", "| foo | a | 0 | 0 |\n", "+----------+----------+-----------+-----------+\n", "| bar | a | 1 | 1 |\n", "+----------+----------+-----------+-----------+\n", "| foo | b | 2 | 2 |\n", "+----------+----------+-----------+-----------+\n", "| bar | c | 3 | 3 |\n", "+----------+----------+-----------+-----------+\n", "| foo | b | 4 | 4 |\n", "+----------+----------+-----------+-----------+\n", "| bar | b | 5 | 5 |\n", "+----------+----------+-----------+-----------+\n", "| foo | a | 6 | 6 |\n", "+----------+----------+-----------+-----------+\n", "| foo | c | 7 | 7 |\n", "+----------+----------+-----------+-----------+\n", "(Showing first 8 of 8 rows)" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "grouping_df = DataFrame.from_pydict(\n", " {\n", " \"A\": [\"foo\", \"bar\", \"foo\", \"bar\", \"foo\", \"bar\", \"foo\", \"foo\"],\n", " \"B\": [\"a\", \"a\", \"b\", \"c\", \"b\", \"b\", \"a\", \"c\"],\n", " \"C\": [i for i in range(8)],\n", " \"D\": [i for i in range(8)],\n", " }\n", ")\n", "grouping_df.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "First we group by \"A\", so that we will evaluate rows with `A=foo` and `A=bar` separately in their respective groups." ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "GroupedDataFrame(df=+----------+----------+-----------+-----------+\n", "| A | B | C | D |\n", "| STRING | STRING | INTEGER | INTEGER |\n", "+==========+==========+===========+===========+\n", "| foo | a | 0 | 0 |\n", "+----------+----------+-----------+-----------+\n", "| bar | a | 1 | 1 |\n", "+----------+----------+-----------+-----------+\n", "| foo | b | 2 | 2 |\n", "+----------+----------+-----------+-----------+\n", "| bar | c | 3 | 3 |\n", "+----------+----------+-----------+-----------+\n", "| foo | b | 4 | 4 |\n", "+----------+----------+-----------+-----------+\n", "| bar | b | 5 | 5 |\n", "+----------+----------+-----------+-----------+\n", "| foo | a | 6 | 6 |\n", "+----------+----------+-----------+-----------+\n", "| foo | c | 7 | 7 |\n", "+----------+----------+-----------+-----------+\n", "(Showing first 8 of 8 rows), group_by=[col(A#23: STRING)])" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "grouped_df = grouping_df.groupby(grouping_df[\"A\"])\n", "grouped_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can specify the aggregations we want to compute over columns C and D. Here we compute the sum over column C, and the mean over column D for each group:" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
STRING 		C_sum
INTEGER		 D_mean
INTEGER
bar 9 3
foo 19 3.8
\n", " (Showing first 2 of 2 rows)\n", "
\n", " " ], "text/plain": [ "+----------+-----------+-----------+\n", "| A | C_sum | D_mean |\n", "| STRING | INTEGER | INTEGER |\n", "+==========+===========+===========+\n", "| bar | 9 | 3 |\n", "+----------+-----------+-----------+\n", "| foo | 19 | 3.8 |\n", "+----------+-----------+-----------+\n", "(Showing first 2 of 2 rows)" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "aggregated_df = grouped_df.agg([\n", " (grouped_df[\"C\"].alias(\"C_sum\"), \"sum\"),\n", " (grouped_df[\"D\"].alias(\"D_mean\"), \"mean\"),\n", "])\n", "aggregated_df.collect()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "These operations work as well when run over multiple groupby columns, which will produce one row for each combination of columns that occur in the DataFrame:" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
A
STRING 		B
STRING 		C_sum
INTEGER		 D_mean
INTEGER
foo a 6 3
bar b 5 5
foo c 7 7
bar a 1 1
bar c 3 3
foo b 6 3
\n", " (Showing first 6 of 6 rows)\n", "
\n", " " ], "text/plain": [ "+----------+----------+-----------+-----------+\n", "| A | B | C_sum | D_mean |\n", "| STRING | STRING | INTEGER | INTEGER |\n", "+==========+==========+===========+===========+\n", "| foo | a | 6 | 3 |\n", "+----------+----------+-----------+-----------+\n", "| bar | b | 5 | 5 |\n", "+----------+----------+-----------+-----------+\n", "| foo | c | 7 | 7 |\n", "+----------+----------+-----------+-----------+\n", "| bar | a | 1 | 1 |\n", "+----------+----------+-----------+-----------+\n", "| bar | c | 3 | 3 |\n", "+----------+----------+-----------+-----------+\n", "| foo | b | 6 | 3 |\n", "+----------+----------+-----------+-----------+\n", "(Showing first 6 of 6 rows)" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "grouping_df \\\n", " .groupby(grouping_df[\"A\"], grouping_df[\"B\"]) \\\n", " .agg([\n", " (grouping_df[\"C\"].alias(\"C_sum\"), \"sum\"),\n", " (grouping_df[\"D\"].alias(\"D_mean\"), \"mean\"),\n", " ]) \\\n", " .collect()" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## Writing Data\n", "\n", "See: [Writing Data](df-writing-data)\n", "\n", "Writing data will execute your DataFrame and write the results out to the specified backend. For example, to write data out to CSV:\n" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": [ "# NOTE: Daft does not write PY columns at the moment.\n", "# This is a feature that is on the roadmap as various options for implementation are being designed.\n", "write_df = df.exclude(\"G\")\n", "\n", "written_df = write_df.write_csv(\"my-dataframe.csv\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note that writing your dataframe is a **blocking** operation that executes your DataFrame. It will return a new `DataFrame` that contains the filepaths to the written data:" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
\n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "
file_path
STRING
my-dataframe.csv/c9da510b-9bef-4d4b-a3db-d40462100b52-0.csv
\n", " (Showing first 1 of 1 rows)\n", "
\n", " " ], "text/plain": [ "+----------------------+\n", "| file_path |\n", "| STRING |\n", "+======================+\n", "| my-dataframe.csv/c9d |\n", "| a510b-9bef-4d4b-a3db |\n", "| -d40462100b52-0.csv |\n", "+----------------------+\n", "(Showing first 1 of 1 rows)" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "written_df.collect()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "venv", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.16" }, "vscode": { "interpreter": { "hash": "e5d77f7bd5a748e4f6412a25f9708ab7af36936de941fc795d1a6b75eb2da082" } } }, "nbformat": 4, "nbformat_minor": 4 }