o ?DiD @sddlZddlmZmZmZmZmZmZmZm Z ddl m Z m Z m Z mZmZmZmZmZe r4ddlmZddlmZddlmZdd lmZdd lmZmZdd lmZ d e!d ddefddZ"de!fddZ#dddefddZ$dddefddZ%dddedefddZ&dd Z'd edefd!d"Z(d edeeded#ffdefd$d%Z)dedefd&d'Z*d(e!fd)d*Z+dedefd+d,Z,ddd-e!d.e!defd/d0Z-d1dd2ede.fd3ede.fdefd4d5Z/dddefd6d7Z0dddefd8d9Z1dddefd:d;Z2dddefdd?Z4dddefd@dAZ5dddBddefdCdDZ6dddBddefdEdFZ7 ddDddFddGee.defdHdIZ8dddBe.dJe!defdKdLZ9dddBe.dJe!defdMdNZ:dddefdOdPZ;dddefdQdRZ< dddd-ddSeddefdTdUZ= dddd-ddSeddefdVdWZ>dddefdXdYZ?dddefdZd[Z@dddedefd\d]ZAd^dd_ede.fdedefd`daZBdddedefdbdcZCdddefdddeZDdfddgddefdhdiZEdfddgddefdjdkZFdddefdldmZGdddefdndoZHdddefdpdqZIdddefdrdsZJdddefdtduZKdddefdvdwZLdddefdxdyZMdddefdzd{ZNdddefd|d}ZOdddefd~dZPdddefddZQddddeeRdefddZSddddeeRdefddZTedddeegefdefddZUedddeeegefdefddZUdddeeegefeeegeffdefddZUde!d dddfddZVdddefddZWdddefddZXdddefddZYdddefddZZdddefddZ[dddefddZ\dddefddZ]dddefddZ^dddefddZ_dddefddZ`dddefddZadddedefddZbdddefddZcdddefddZddddefddZedddefddZfdfddgddefddZgdddefddZhdefddZidefddZjdddefddZkdfedeRfdgedeRfdefddZlddd ddefddZmdddefddZnddee.defddĄZodddddefddńZpdddddefddȄZqddd-e!de.defdd˄Zr ddddddeee.efdefdd̈́ZsdddddefddτZtdddddefddфZuddde.defddԄZv dd2ddddeddefdd؄ZwdddefddڄZxdddefdd܄ZydddefddބZzdddefddZ{ddde!defddZ|dddddefddZ}dddde~defddZdddde~defddZd ddefddZd ddefddZdddefddZdddefddZdddefddZddddeddefddZdddddefddZdddddefddZdddefdd3Zd ddefddZdfddgddefddZdfddgddddefddZdfddgddefddZdfddgddefd d Zdddefd d Zddd e.defddZdefddZdefddZdefddZdddefdd=Z3dddefdd7Z0de!dddefddZdddddefddZdddddefdd Zdddddefd!d"Zdddefd#d$Zdddefd%d&Zdddefd'd(Zdddefd)d*Zdddefd+d,Zdddefd-d.Zdddefd/d0Zdddefd1d2Zdddefd3d4Zdddefd5d6Zdddefd7d8Zdddefd9d:Zdddefd;d<Zd=e!d ddefd>d?Zdfddgddefd@dAZdfddgddefdBdCZdddefdDdEZdddefdFdGZdddefdHdIZdddefdJdKZdddefdLdMZdddefdNdOZdddefdPdQZdfedeRfdgedeRfdefdRdSZdddefdTdUZddddVe.defdWdXZddddVe.defdYdZZddd[ede.fdefd\d]Zdddefd^d_Zdddefd`daZdddefdbdcZdddefdddeZdddefdfdgZdddefdhdiZd2ddjede.fdefdkdlZ ddddme!dnee!defdodpZdddedefdqdrZdddedefdsdtZdddede.fdefdudvZdddefdwdxZ ddddyeeeegefdefdzd{Zdddd7e~defd}d~Zdddd-e!de.defddZdddmddddefddZdddefddZdddefddZdddefddZdddefddZdddefddZdddefddZdddefddZdddefddZdddefddZ̐dddde jdee!defddZΐddddee!defddZϐddddee!defddZ dʐdddeddefddZ dʐdddeddefddZ dddd_ddBeddefddZddde!defddZdddefddZdddefddZdddefddZdddefddZؐdddddefddZِde defddZd ddefddZddd_e.dBe.defddZdDddFddefddZdddefddZd ddefddZddde!defddÄZde!defdĐdńZdϐdȐdɄZdS(N)AnyCallableUnionoverloadOptionalListTuple TYPE_CHECKING)CaseExpressionCoalesceOperatorColumnExpressionConstantExpression ExpressionFunctionExpressionLambdaExpression SQLExpression) DataFrame)PySparkTypeError)ContributionsAcceptedError) ColumnOrName)Column _get_expr)typesnamecolsrreturncGsdd|D}t|g|RS)zs Invokes n-ary JVM function identified by name and wraps the result with :class:`~pyspark.sql.Column`. cSg|]}t|qS_to_column_expr.0exprrr`/var/www/Datamplify/venv/lib/python3.10/site-packages/duckdb/experimental/spark/sql/functions.py z1_invoke_function_over_columns.._invoke_function)rrrrr%_invoke_function_over_columnssr*columncC tt|SN)rr )r+rrr%col!s r.cC td|S)ao Converts a string expression to upper case. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` upper case values. Examples -------- >>> df = spark.createDataFrame(["Spark", "PySpark", "Pandas API"], "STRING") >>> df.select(upper("value")).show() +------------+ |upper(value)| +------------+ | SPARK| | PYSPARK| | PANDAS API| +------------+ upperr*r.rrr%r0% r0strcCt|S)a Returns `str` with all characters changed to uppercase. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str Input column or strings. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select(sf.ucase(sf.lit("Spark"))).show() +------------+ |ucase(Spark)| +------------+ | SPARK| +------------+ )r0r4rrr%ucaseGr7 conditionrvaluecCs.t|ts tdt|}t|j|}t|S)Nzcondition should be a Column) isinstancer TypeErrorrr r$)r9r:vr$rrr%when_s  r>cCst|tr|jS|Sr-)r;rr$)valrrr%_inner_expr_or_valgr@cGsttdgdd|DRS)N struct_packcSrr)r@)r#xrrr%r&mr'zstruct..rrrrrr%structksrF)r.cGs6t|dkrt|dttfr|d}tdg|RS)a^Creates a new array column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- cols : :class:`~pyspark.sql.Column` or str column names or :class:`~pyspark.sql.Column`\s that have the same data type. Returns ------- :class:`~pyspark.sql.Column` a column of array type. Examples -------- >>> df = spark.createDataFrame([("Alice", 2), ("Bob", 5)], ("name", "age")) >>> df.select(array('age', 'age').alias("arr")).collect() [Row(arr=[2, 2]), Row(arr=[5, 5])] >>> df.select(array([df.age, df.age]).alias("arr")).collect() [Row(arr=[2, 2]), Row(arr=[5, 5])] >>> df.select(array('age', 'age').alias("col")).printSchema() root |-- col: array (nullable = false) | |-- element: long (containsNull = true) rr list_value)lenr;listsetr*rErrr%arrayqs!rKcCst|tr|Stt|Sr-)r;rr r2rrr%litsrLfunctioncGstt|g|RSr-rD)rM argumentsrrr%r)rAr)cCs:t|tr|jSt|trt|Stddt|jdd)NNOT_COLUMN_OR_STRr.)arg_namearg_type) error_classmessage_parameters)r;rr$r4r rtype__name__r2rrr%r!s  r!pattern replacementcCs tdt|t|t|tdS)a,Replace all substrings of the specified string value that match regexp with rep. .. versionadded:: 1.5.0 Examples -------- >>> df = spark.createDataFrame([('100-200',)], ['str']) >>> df.select(regexp_replace('str', r'(\d+)', '--').alias('d')).collect() [Row(d='-----')] regexp_replacegr)r!r )r4rVrWrrr%rXs rXrCstartlengthcCsNt|tr t|nt|}t|trt|nt|}||}tdt|||S)a Collection function: returns an array containing all the elements in `x` from index `start` (array indices start at 1, or from the end if `start` is negative) with the specified `length`. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- x : :class:`~pyspark.sql.Column` or str column name or column containing the array to be sliced start : :class:`~pyspark.sql.Column` or str or int column name, column, or int containing the starting index length : :class:`~pyspark.sql.Column` or str or int column name, column, or int containing the length of the slice Returns ------- :class:`~pyspark.sql.Column` a column of array type. Subset of array. Examples -------- >>> df = spark.createDataFrame([([1, 2, 3],), ([4, 5],)], ['x']) >>> df.select(slice(df.x, 2, 2).alias("sliced")).collect() [Row(sliced=[2, 3]), Row(sliced=[5])] list_slicer;intr r!r))rCr[r\endrrr%slices racCtt|SaN Returns a sort expression based on the ascending order of the given column name. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the ascending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- Sort by the column 'id' in the descending order. >>> df = spark.range(5) >>> df = df.sort(desc("id")) >>> df.show() +---+ | id| +---+ | 4| | 3| | 2| | 1| | 0| +---+ Sort by the column 'id' in the ascending order. >>> df.orderBy(asc("id")).show() +---+ | id| +---+ | 0| | 1| | 2| | 3| | 4| +---+ rr!ascr2rrr%re1recC t|S)a7 Returns a sort expression based on the ascending order of the given column name, and null values return before non-null values. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the ascending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- >>> df1 = spark.createDataFrame([(1, "Bob"), ... (0, None), ... (2, "Alice")], ["age", "name"]) >>> df1.sort(asc_nulls_first(df1.name)).show() +---+-----+ |age| name| +---+-----+ | 0| NULL| | 2|Alice| | 1| Bob| +---+-----+ )re nulls_firstr2rrr%asc_nulls_first #ricCrg)a5 Returns a sort expression based on the ascending order of the given column name, and null values appear after non-null values. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the ascending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- >>> df1 = spark.createDataFrame([(0, None), ... (1, "Bob"), ... (2, "Alice")], ["age", "name"]) >>> df1.sort(asc_nulls_last(df1.name)).show() +---+-----+ |age| name| +---+-----+ | 2|Alice| | 1| Bob| | 0| NULL| +---+-----+ )re nulls_lastr2rrr%asc_nulls_last@rjrlcCrba~ Returns a sort expression based on the descending order of the given column name. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the descending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- Sort by the column 'id' in the descending order. >>> spark.range(5).orderBy(desc("id")).show() +---+ | id| +---+ | 4| | 3| | 2| | 1| | 0| +---+ rr!descr2rrr%rof"rocCrg)a: Returns a sort expression based on the descending order of the given column name, and null values appear before non-null values. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the descending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- >>> df1 = spark.createDataFrame([(0, None), ... (1, "Bob"), ... (2, "Alice")], ["age", "name"]) >>> df1.sort(desc_nulls_first(df1.name)).show() +---+-----+ |age| name| +---+-----+ | 0| NULL| | 1| Bob| | 2|Alice| +---+-----+ )rorhr2rrr%desc_nulls_firstrjrqcCrg)a8 Returns a sort expression based on the descending order of the given column name, and null values appear after non-null values. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to sort by in the descending order. Returns ------- :class:`~pyspark.sql.Column` the column specifying the order. Examples -------- >>> df1 = spark.createDataFrame([(0, None), ... (1, "Bob"), ... (2, "Alice")], ["age", "name"]) >>> df1.sort(desc_nulls_last(df1.name)).show() +---+-----+ |age| name| +---+-----+ | 1| Bob| | 2|Alice| | 0| NULL| +---+-----+ )rorkr2rrr%desc_nulls_lastrjrrrHcCs:t|}tt|tdktdtdt|td|S)a= Returns the leftmost `len`(`len` can be string type) characters from the string `str`, if `len` is less or equal than 0 the result is an empty string. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str Input column or strings. len : :class:`~pyspark.sql.Column` or str Input column or strings, the leftmost `len`. Examples -------- >>> df = spark.createDataFrame([("Spark SQL", 3,)], ['a', 'b']) >>> df.select(left(df.a, df.b).alias('r')).collect() [Row(r='Spa')] r array_slicer!rr r otherwiserr4rHrrr%leftsrxc Cs<t|}tt|tdktdtdt|| tdS)a@ Returns the rightmost `len`(`len` can be string type) characters from the string `str`, if `len` is less or equal than 0 the result is an empty string. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str Input column or strings. len : :class:`~pyspark.sql.Column` or str Input column or strings, the rightmost `len`. Examples -------- >>> df = spark.createDataFrame([("Spark SQL", 3,)], ['a', 'b']) >>> df.select(right(df.a, df.b).alias('r')).collect() [Row(r='SQL')] rrsrtrurwrrr%rightsrz thresholdcCs@td||}|dur |St|}tt|t|k|tdS)aComputes the Levenshtein distance of the two given strings. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- left : :class:`~pyspark.sql.Column` or str first column value. right : :class:`~pyspark.sql.Column` or str second column value. threshold : int, optional if set when the levenshtein distance of the two given strings less than or equal to a given threshold then return result distance, or -1 .. versionchanged: 3.5.0 Added ``threshold`` argument. Returns ------- :class:`~pyspark.sql.Column` Levenshtein distance as integer value. Examples -------- >>> df0 = spark.createDataFrame([('kitten', 'sitting',)], ['l', 'r']) >>> df0.select(levenshtein('l', 'r').alias('d')).collect() [Row(d=3)] >>> df0.select(levenshtein('l', 'r', 2).alias('d')).collect() [Row(d=-1)] levenshteinNry)r*r!rr r rv)rxrzr{distancerrr%r|s $ r|padcCtdt|t|t|S)a^ Left-pad the string column to width `len` with `pad`. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. len : int length of the final string. pad : str chars to prepend. Returns ------- :class:`~pyspark.sql.Column` left padded result. Examples -------- >>> df = spark.createDataFrame([('abcd',)], ['s',]) >>> df.select(lpad(df.s, 6, '#').alias('s')).collect() [Row(s='##abcd')] lpadrZr.rHr~rrr%r?rcCr)a_ Right-pad the string column to width `len` with `pad`. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. len : int length of the final string. pad : str chars to append. Returns ------- :class:`~pyspark.sql.Column` right padded result. Examples -------- >>> df = spark.createDataFrame([('abcd',)], ['s',]) >>> df.select(rpad(df.s, 6, '#').alias('s')).collect() [Row(s='abcd##')] rpadrZrrrr%r_rrcCr/)a Computes the numeric value of the first character of the string column. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` numeric value. Examples -------- >>> df = spark.createDataFrame(["Spark", "PySpark", "Pandas API"], "STRING") >>> df.select(ascii("value")).show() +------------+ |ascii(value)| +------------+ | 83| | 80| | 80| +------------+ asciir1r2rrr%rr3rcCs6t|}tt|dk|dkBttdtd|S)ae Computes inverse sine of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` inverse sine of `col`, as if computed by `java.lang.Math.asin()` Examples -------- >>> df = spark.createDataFrame([(0,), (2,)]) >>> df.select(asin(df.schema.fieldNames()[0])).show() +--------+ |ASIN(_1)| +--------+ | 0.0| | NaN| +--------+ gg?nanasin)r!rr r floatrvrr2rrr%rs.r escapeCharcC0|dur td}nt|}tdt|t||S)ae Returns true if str matches `pattern` with `escape`, null if any arguments are null, false otherwise. The default escape character is the ''. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str A string. pattern : :class:`~pyspark.sql.Column` or str A string. See the DuckDB documentation on like_escape for more information. escape : :class:`~pyspark.sql.Column` An character added since Spark 3.0. The default escape character is the ''. If an escape character precedes a special symbol or another escape character, the following character is matched literally. It is invalid to escape any other character. Examples -------- >>> df = spark.createDataFrame([("Spark", "_park")], ['a', 'b']) >>> df.select(like(df.a, df.b).alias('r')).collect() [Row(r=True)] >>> df = spark.createDataFrame( ... [("%SystemDrive%/Users/John", "/%SystemDrive/%//Users%")], ... ['a', 'b'] ... ) >>> df.select(like(df.a, df.b, lit('/')).alias('r')).collect() [Row(r=True)] N\ like_escaper r!r)r4rVrrrr%like" rcCr)a{ Returns true if str matches `pattern` with `escape` case-insensitively, null if any arguments are null, false otherwise. The default escape character is the ''. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str A string. pattern : :class:`~pyspark.sql.Column` or str A string. See the DuckDB documentation on ilike_escape for more information. escape : :class:`~pyspark.sql.Column` An character added since Spark 3.0. The default escape character is the ''. If an escape character precedes a special symbol or another escape character, the following character is matched literally. It is invalid to escape any other character. Examples -------- >>> df = spark.createDataFrame([("Spark", "_park")], ['a', 'b']) >>> df.select(ilike(df.a, df.b).alias('r')).collect() [Row(r=True)] >>> df = spark.createDataFrame( ... [("%SystemDrive%/Users/John", "/%SystemDrive/%//Users%")], ... ['a', 'b'] ... ) >>> df.select(ilike(df.a, df.b, lit('/')).alias('r')).collect() [Row(r=True)] Nr ilike_escaperrrrr%ilikerrcCr/)a Aggregate function: returns a list of objects with duplicates. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` list of objects with duplicates. Examples -------- >>> df = spark.createDataFrame([[1],[1],[2]], ["c"]) >>> df.agg(array_agg('c').alias('r')).collect() [Row(r=[1, 1, 2])] rIr1r2rrr% array_agg rcCr5)a Aggregate function: returns a list of objects with duplicates. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The function is non-deterministic because the order of collected results depends on the order of the rows which may be non-deterministic after a shuffle. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` list of objects with duplicates. Examples -------- >>> df2 = spark.createDataFrame([(2,), (5,), (5,)], ('age',)) >>> df2.agg(collect_list('age')).collect() [Row(collect_list(age)=[2, 5, 5])] )rr2rrr% collect_list/srcCtdt|t|S)az Collection function: returns an array of the elements in col1 along with the added element in col2 at the last of the array. .. versionadded:: 3.4.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column containing array value : a literal value, or a :class:`~pyspark.sql.Column` expression. Returns ------- :class:`~pyspark.sql.Column` an array of values from first array along with the element. Notes ----- Supports Spark Connect. Examples -------- >>> from pyspark.sql import Row >>> df = spark.createDataFrame([Row(c1=["b", "a", "c"], c2="c")]) >>> df.select(array_append(df.c1, df.c2)).collect() [Row(array_append(c1, c2)=['b', 'a', 'c', 'c'])] >>> df.select(array_append(df.c1, 'x')).collect() [Row(array_append(c1, x)=['b', 'a', 'c', 'x'])] list_append)r)r!rr.r:rrr% array_appendPs rarrposcCst|}t|}|dk}tdtd|d}t||ktd|td|dt|dktd||k@tdtdtd d tdtd||||}tdtdtd |dt|td|d|td t|td |t||td|dd S) aw Collection function: adds an item into a given array at a specified array index. Array indices start at 1, or start from the end if index is negative. Index above array size appends the array, or prepends the array if index is negative, with 'null' elements. .. versionadded:: 3.4.0 Parameters ---------- arr : :class:`~pyspark.sql.Column` or str name of column containing an array pos : :class:`~pyspark.sql.Column` or str or int name of Numeric type column indicating position of insertion (starting at index 1, negative position is a start from the back of the array) value : a literal value, or a :class:`~pyspark.sql.Column` expression. Returns ------- :class:`~pyspark.sql.Column` an array of values, including the new specified value Notes ----- Supports Spark Connect. Examples -------- >>> df = spark.createDataFrame( ... [(['a', 'b', 'c'], 2, 'd'), (['c', 'b', 'a'], -2, 'd')], ... ['data', 'pos', 'val'] ... ) >>> df.select(array_insert(df.data, df.pos.cast('integer'), df.val).alias('data')).collect() [Row(data=['a', 'd', 'b', 'c']), Row(data=['c', 'b', 'd', 'a'])] >>> df.select(array_insert(df.data, 5, 'hello').alias('data')).collect() [Row(data=['a', 'b', 'c', None, 'hello']), Row(data=['c', 'b', 'a', None, 'hello'])] raddrHr list_resizesubtractabs list_concatrGNr]ry)rr!rr rvr))rrr:pos_is_positivelist_length_plus_1list_rrr% array_insertssd)      rcCst|}tdt||S)a Collection function: returns null if the array is null, true if the array contains the given value, and false otherwise. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column containing array value : value or column to check for in array Returns ------- :class:`~pyspark.sql.Column` a column of Boolean type. Examples -------- >>> df = spark.createDataFrame([(["a", "b", "c"],), ([],)], ['data']) >>> df.select(array_contains(df.data, "a")).collect() [Row(array_contains(data, a)=True), Row(array_contains(data, a)=False)] >>> df.select(array_contains(df.data, lit("a"))).collect() [Row(array_contains(data, a)=True), Row(array_contains(data, a)=False)] array_contains)rr)r!rrrr%rsrcCr/)a_ Collection function: removes duplicate values from the array. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` an array of unique values. Examples -------- >>> df = spark.createDataFrame([([1, 2, 3, 2],), ([4, 5, 5, 4],)], ['data']) >>> df.select(array_distinct(df.data)).collect() [Row(array_distinct(data)=[1, 2, 3]), Row(array_distinct(data)=[4, 5])] array_distinctr1r2rrr%r rcol1col2cC td||S)a Collection function: returns an array of the elements in the intersection of col1 and col2, without duplicates. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str name of column containing array col2 : :class:`~pyspark.sql.Column` or str name of column containing array Returns ------- :class:`~pyspark.sql.Column` an array of values in the intersection of two arrays. Examples -------- >>> from pyspark.sql import Row >>> df = spark.createDataFrame([Row(c1=["b", "a", "c"], c2=["c", "d", "a", "f"])]) >>> df.select(array_intersect(df.c1, df.c2)).collect() [Row(array_intersect(c1, c2)=['a', 'c'])] array_intersectr1rrrrr%r rcCtdtd||S)a Collection function: returns an array of the elements in the union of col1 and col2, without duplicates. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str name of column containing array col2 : :class:`~pyspark.sql.Column` or str name of column containing array Returns ------- :class:`~pyspark.sql.Column` an array of values in union of two arrays. Examples -------- >>> from pyspark.sql import Row >>> df = spark.createDataFrame([Row(c1=["b", "a", "c"], c2=["c", "d", "a", "f"])]) >>> df.select(array_union(df.c1, df.c2)).collect() [Row(array_union(c1, c2)=['b', 'a', 'c', 'd', 'f'])] r array_concatr1rrrr% array_union3srcCtdttd|tdS)a7 Collection function: returns the maximum value of the array. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` maximum value of an array. Examples -------- >>> df = spark.createDataFrame([([2, 1, 3],), ([None, 10, -1],)], ['data']) >>> df.select(array_max(df.data).alias('max')).collect() [Row(max=3), Row(max=10)] array_extract array_sortryr)r!r*rr2rrr% array_maxSrcCr)a4 Collection function: returns the minimum value of the array. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` minimum value of array. Examples -------- >>> df = spark.createDataFrame([([2, 1, 3],), ([None, 10, -1],)], ['data']) >>> df.select(array_min(df.data).alias('min')).collect() [Row(min=1), Row(min=-1)] rrrrr2rrr% array_minorrcCr/)a# Aggregate function: returns the average of the values in a group. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(10) >>> df.select(avg(col("id"))).show() +-------+ |avg(id)| +-------+ | 4.5| +-------+ avgr1r2rrr%r rcCr/)a% Aggregate function: returns the sum of all values in the expression. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(10) >>> df.select(sum(df["id"])).show() +-------+ |sum(id)| +-------+ | 45| +-------+ sumr1r2rrr%rrrcCr/)a) Aggregate function: returns the maximum value of the expression in a group. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` column for computed results. Examples -------- >>> df = spark.range(10) >>> df.select(max(col("id"))).show() +-------+ |max(id)| +-------+ | 9| +-------+ maxr1r2rrr%rrrcCr/)a= Aggregate function: returns the average of the values in a group. An alias of :func:`avg`. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(10) >>> df.select(mean(df.id)).show() +-------+ |avg(id)| +-------+ | 4.5| +-------+ meanr1r2rrr%r rcCr/)at Returns the median of the values in a group. .. versionadded:: 3.4.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the median of the values in a group. Notes ----- Supports Spark Connect. Examples -------- >>> df = spark.createDataFrame([ ... ("Java", 2012, 20000), ("dotNET", 2012, 5000), ... ("Java", 2012, 22000), ("dotNET", 2012, 10000), ... ("dotNET", 2013, 48000), ("Java", 2013, 30000)], ... schema=("course", "year", "earnings")) >>> df.groupby("course").agg(median("earnings")).show() +------+----------------+ |course|median(earnings)| +------+----------------+ | Java| 22000.0| |dotNET| 10000.0| +------+----------------+ medianr1r2rrr%r  #rcCr/)aG Returns the most frequent value in a group. .. versionadded:: 3.4.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the most frequent value in a group. Notes ----- Supports Spark Connect. Examples -------- >>> df = spark.createDataFrame([ ... ("Java", 2012, 20000), ("dotNET", 2012, 5000), ... ("Java", 2012, 20000), ("dotNET", 2012, 5000), ... ("dotNET", 2013, 48000), ("Java", 2013, 30000)], ... schema=("course", "year", "earnings")) >>> df.groupby("course").agg(mode("year")).show() +------+----------+ |course|mode(year)| +------+----------+ | Java| 2012| |dotNET| 2012| +------+----------+ moder1r2rrr%r2rrcCr/)a% Aggregate function: returns the minimum value of the expression in a group. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` column for computed results. Examples -------- >>> df = spark.range(10) >>> df.select(min(df.id)).show() +-------+ |min(id)| +-------+ | 0| +-------+ minr1r2rrr%rXrrcCr/)aReturns some value of `col` for a group of rows. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. ignorenulls : :class:`~pyspark.sql.Column` or bool if first value is null then look for first non-null value. Returns ------- :class:`~pyspark.sql.Column` some value of `col` for a group of rows. Examples -------- >>> df = spark.createDataFrame([(None, 1), ... ("a", 2), ... ("a", 3), ... ("b", 8), ... ("b", 2)], ["c1", "c2"]) >>> df.select(any_value('c1'), any_value('c2')).collect() [Row(any_value(c1)=None, any_value(c2)=1)] >>> df.select(any_value('c1', True), any_value('c2', True)).collect() [Row(any_value(c1)='a', any_value(c2)=1)] any_valuer1r2rrr%rxrrcCr/)a Aggregate function: returns the number of items in a group. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` column for computed results. Examples -------- Count by all columns (start), and by a column that does not count ``None``. >>> df = spark.createDataFrame([(None,), ("a",), ("b",), ("c",)], schema=["alphabets"]) >>> df.select(count(expr("*")), count(df.alphabets)).show() +--------+----------------+ |count(1)|count(alphabets)| +--------+----------------+ | 4| 3| +--------+----------------+ countr1r2rrr%rr3rrsdcCs|durtdtd|S)a:Aggregate function: returns a new :class:`~pyspark.sql.Column` for approximate distinct count of column `col`. .. versionadded:: 2.1.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str rsd : float, optional maximum relative standard deviation allowed (default = 0.05). For rsd < 0.01, it is more efficient to use :func:`count_distinct` Returns ------- :class:`~pyspark.sql.Column` the column of computed results. Examples -------- >>> df = spark.createDataFrame([1,2,2,3], "INT") >>> df.agg(approx_count_distinct("value").alias('distinct_values')).show() +---------------+ |distinct_values| +---------------+ | 3| +---------------+ Nzrsd is not supported by DuckDBapprox_count_distinct) ValueErrorr*r.rrrr%rs rcCstdtt||S)z .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. .. deprecated:: 2.1.0 Use :func:`approx_count_distinct` instead. z5Deprecated in 2.1, use approx_count_distinct instead.)warningswarn FutureWarningrrrrr%approxCountDistincts rfcCdSr-rr.rrrr% transformrcCrr-rrrrr%rrcCst)a% Returns an array of elements after applying a transformation to each element in the input array. .. versionadded:: 3.1.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression f : function a function that is applied to each element of the input array. Can take one of the following forms: - Unary ``(x: Column) -> Column: ...`` - Binary ``(x: Column, i: Column) -> Column...``, where the second argument is a 0-based index of the element. and can use methods of :class:`~pyspark.sql.Column`, functions defined in :py:mod:`pyspark.sql.functions` and Scala ``UserDefinedFunctions``. Python ``UserDefinedFunctions`` are not supported (`SPARK-27052 `__). Returns ------- :class:`~pyspark.sql.Column` a new array of transformed elements. Examples -------- >>> df = spark.createDataFrame([(1, [1, 2, 3, 4])], ("key", "values")) >>> df.select(transform("values", lambda x: x * 2).alias("doubled")).show() +------------+ | doubled| +------------+ |[2, 4, 6, 8]| +------------+ >>> def alternate(x, i): ... return when(i % 2 == 0, x).otherwise(-x) ... >>> df.select(transform("values", alternate).alias("alternated")).show() +--------------+ | alternated| +--------------+ |[1, -2, 3, -4]| +--------------+ )NotImplementedErrorrrrr%rs6sepcGs$dd|D}tdt|g|RS)a Concatenates multiple input string columns together into a single string column, using the given separator. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- sep : str words separator. cols : :class:`~pyspark.sql.Column` or str list of columns to work on. Returns ------- :class:`~pyspark.sql.Column` string of concatenated words. Examples -------- >>> df = spark.createDataFrame([('abcd','123')], ['s', 'd']) >>> df.select(concat_ws('-', df.s, df.d).alias('s')).collect() [Row(s='abcd-123')] cSrrr r"rrr%r&Ir'zconcat_ws.. concat_ws)r)r )rrrrr%r-srcCr/)ao Converts a string expression to lower case. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` lower case values. Examples -------- >>> df = spark.createDataFrame(["Spark", "PySpark", "Pandas API"], "STRING") >>> df.select(lower("value")).show() +------------+ |lower(value)| +------------+ | spark| | pyspark| | pandas api| +------------+ lowerr1r2rrr%rMr3rcCr5)a Returns `str` with all characters changed to lowercase. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str Input column or strings. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select(sf.lcase(sf.lit("Spark"))).show() +------------+ |lcase(Spark)| +------------+ | spark| +------------+ )rr6rrr%lcaseor8rcCr/)a Computes the ceiling of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(1) >>> df.select(ceil(lit(-0.1))).show() +----------+ |CEIL(-0.1)| +----------+ | 0| +----------+ ceilr1r2rrr%rrrcCr5r-)rr2rrr%ceilingsrcCr/)a. Computes the floor of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column to find floor for. Returns ------- :class:`~pyspark.sql.Column` nearest integer that is less than or equal to given value. Examples -------- >>> df = spark.range(1) >>> df.select(floor(lit(2.5))).show() +----------+ |FLOOR(2.5)| +----------+ | 2| +----------+ floorr1r2rrr%rrrcCr/)a Computes the absolute value. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` column for computed results. Examples -------- >>> df = spark.range(1) >>> df.select(abs(lit(-1))).show() +-------+ |abs(-1)| +-------+ | 1| +-------+ rr1r2rrr%rrrcCr/)aAn expression that returns true if the column is NaN. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` True if value is NaN and False otherwise. Examples -------- >>> df = spark.createDataFrame([(1.0, float('nan')), (float('nan'), 2.0)], ("a", "b")) >>> df.select("a", "b", isnan("a").alias("r1"), isnan(df.b).alias("r2")).show() +---+---+-----+-----+ | a| b| r1| r2| +---+---+-----+-----+ |1.0|NaN|false| true| |NaN|2.0| true|false| +---+---+-----+-----+ isnanr1r2rrr%rrrcCtt|S)aAn expression that returns true if the column is null. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` True if value is null and False otherwise. Examples -------- >>> df = spark.createDataFrame([(1, None), (None, 2)], ("a", "b")) >>> df.select("a", "b", isnull("a").alias("r1"), isnull(df.b).alias("r2")).show() +----+----+-----+-----+ | a| b| r1| r2| +----+----+-----+-----+ | 1|NULL|false| true| |NULL| 2| true|false| +----+----+-----+-----+ )rr!isnullr2rrr%r rcCr)aX Returns true if `col` is not null, or false otherwise. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([(None,), (1,)], ["e"]) >>> df.select(isnotnull(df.e).alias('r')).collect() [Row(r=False), Row(r=True)] )rr! isnotnullr2rrr%r+srcCs*t|}t|}tt|dtd|S)a Collection function: creates a single array from an array of arrays. If a structure of nested arrays is deeper than two levels, only one level of nesting is removed. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` flattened array. Examples -------- >>> df = spark.createDataFrame([([[1, 2, 3], [4, 5], [6]],), ([None, [4, 5]],)], ['data']) >>> df.show(truncate=False) +------------------------+ |data | +------------------------+ |[[1, 2, 3], [4, 5], [6]]| |[NULL, [4, 5]] | +------------------------+ >>> df.select(flatten(df.data).alias('r')).show() +------------------+ | r| +------------------+ |[1, 2, 3, 4, 5, 6]| | NULL| +------------------+ Nflatten)r!_list_contains_nullrr rvr)r. contains_nullrrr%r>s' rcCstdt|tdtdS)a^ Collection function: removes null values from the array. .. versionadded:: 3.4.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` an array by excluding the null values. Notes ----- Supports Spark Connect. Examples -------- >>> df = spark.createDataFrame([([1, None, 2, 3],), ([4, 5, None, 4],)], ['data']) >>> df.select(array_compact(df.data)).collect() [Row(array_compact(data)=[1, 2, 3]), Row(array_compact(data)=[4, 5, 4])] list_filterrC)r)r!rr rr2rrr% array_compactnsrelementcCs"tdt|tdtdt|kS)a Collection function: Remove all elements that equal to element from the given array. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column containing array element : element to be removed from the array Returns ------- :class:`~pyspark.sql.Column` an array excluding given value. Examples -------- >>> df = spark.createDataFrame([([1, 2, 3, 1, 1],), ([],)], ['data']) >>> df.select(array_remove(df.data, 1)).collect() [Row(array_remove(data, 1)=[2, 3]), Row(array_remove(data, 1)=[])] rrC)r)r!rr r )r.rrrr% array_removes"rdatecCtdt|S)a1 Returns the last day of the month which the given date belongs to. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- date : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` last day of the month. Examples -------- >>> df = spark.createDataFrame([('1997-02-10',)], ['d']) >>> df.select(last_day(df.d).alias('date')).collect() [Row(date=datetime.date(1997, 2, 28))] last_dayr)r!)rrrr%rsrcCr/)a Computes the square root of the specified float value. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` column for computed results. Examples -------- >>> df = spark.range(1) >>> df.select(sqrt(lit(4))).show() +-------+ |SQRT(4)| +-------+ | 2.0| +-------+ sqrtr1r2rrr%rrrcCr/)a Computes the cube-root of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(1) >>> df.select(cbrt(lit(27))).show() +--------+ |CBRT(27)| +--------+ | 3.0| +--------+ cbrtr1r2rrr%rrrcCs*t|}ttdt|dk|d|S)a Returns the ASCII character having the binary equivalent to `col`. If col is larger than 256 the result is equivalent to char(col % 256) .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str Input column or strings. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select(sf.char(sf.lit(65))).show() +--------+ |char(65)| +--------+ | A| +--------+ chr)r!rrr rvr2rrr%chars"rcCr)aReturns a new :class:`~pyspark.sql.Column` for the Pearson Correlation Coefficient for ``col1`` and ``col2``. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str first column to calculate correlation. col1 : :class:`~pyspark.sql.Column` or str second column to calculate correlation. Returns ------- :class:`~pyspark.sql.Column` Pearson Correlation Coefficient of these two column values. Examples -------- >>> a = range(20) >>> b = [2 * x for x in range(20)] >>> df = spark.createDataFrame(zip(a, b), ["a", "b"]) >>> df.agg(corr("a", "b").alias('c')).collect() [Row(c=1.0)] corrr1rrrr%r"rrcCr/)a Computes cotangent of the input column. .. versionadded:: 3.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str angle in radians. Returns ------- :class:`~pyspark.sql.Column` cotangent of the angle. Examples -------- >>> import math >>> df = spark.range(1) >>> df.select(cot(lit(math.radians(45)))).first() Row(COT(0.78539...)=1.00000...) cotr1r2rrr%rB rcCtdS)zReturns Euler's number. .. versionadded:: 3.5.0 Examples -------- >>> spark.range(1).select(e()).show() +-----------------+ | E()| +-----------------+ |2.718281828459045| +-----------------+ giW @)rLrrrr%e_rcCr)zReturns Pi. .. versionadded:: 3.5.0 Examples -------- >>> spark.range(1).select(pi()).show() +-----------------+ | PI()| +-----------------+ |3.141592653589793| +-----------------+ pir(rrrr%rprrcCr,)a Returns the value. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str input value column. Returns ------- :class:`~pyspark.sql.Column` value. Examples -------- >>> df = spark.createDataFrame([(-1,), (0,), (1,)], ['v']) >>> df.select(positive("v").alias("p")).show() +---+ | p| +---+ | -1| | 0| | 1| +---+ )rr!r2rrr%positives rcCr)ae Returns the value of the first argument raised to the power of the second argument. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : str, :class:`~pyspark.sql.Column` or float the base number. col2 : str, :class:`~pyspark.sql.Column` or float the exponent number. Returns ------- :class:`~pyspark.sql.Column` the base rased to the power the argument. Examples -------- >>> df = spark.range(1) >>> df.select(pow(lit(3), lit(2))).first() Row(POWER(3, 2)=9.0) powr1rrrr%rs rformatcGstd|g|RS)a Formats the arguments in printf-style and returns the result as a string column. .. versionadded:: 3.5.0 Parameters ---------- format : :class:`~pyspark.sql.Column` or str string that can contain embedded format tags and used as result column's value cols : :class:`~pyspark.sql.Column` or str column names or :class:`~pyspark.sql.Column`\s to be used in formatting Examples -------- >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("aa%d%s", 123, "cc",)], ["a", "b", "c"] ... ).select(sf.printf("a", "b", "c")).show() +---------------+ |printf(a, b, c)| +---------------+ | aa123cc| +---------------+ printfr1)rrrrr%rsrcCr/)a Aggregate function: returns the product of the values in a group. .. versionadded:: 3.2.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : str, :class:`Column` column containing values to be multiplied together Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.range(1, 10).toDF('x').withColumn('mod3', col('x') % 3) >>> prods = df.groupBy('mod3').agg(product('x').alias('product')) >>> prods.orderBy('mod3').show() +----+-------+ |mod3|product| +----+-------+ | 0| 162.0| | 1| 28.0| | 2| 80.0| +----+-------+ productr1r2rrr%r rseedcCs|durtdtdS)a.Generates a random column with independent and identically distributed (i.i.d.) samples uniformly distributed in [0.0, 1.0). .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The function is non-deterministic in general case. Parameters ---------- seed : int (default: None) seed value for random generator. Returns ------- :class:`~pyspark.sql.Column` random values. Examples -------- >>> from pyspark.sql import functions as sf >>> spark.range(0, 2, 1, 1).withColumn('rand', sf.rand(seed=42) * 3).show() +---+------------------+ | id| rand| +---+------------------+ | 0|1.8575681106759028| | 1|1.5288056527339444| +---+------------------+ NzSeed is not yet implementedrandom)rr))rrrr%rands"rregexpcCr)aReturns true if `str` matches the Java regex `regexp`, or false otherwise. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. regexp : :class:`~pyspark.sql.Column` or str regex pattern to apply. Returns ------- :class:`~pyspark.sql.Column` true if `str` matches a Java regex, or false otherwise. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp('str', sf.lit(r'(\d+)'))).show() +------------------+ |REGEXP(str, (\d+))| +------------------+ | true| +------------------+ >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp('str', sf.lit(r'\d{2}b'))).show() +-------------------+ |REGEXP(str, \d{2}b)| +-------------------+ | false| +-------------------+ >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp('str', sf.col("regexp"))).show() +-------------------+ |REGEXP(str, regexp)| +-------------------+ | true| +-------------------+ regexp_matchesr1r4rrrr%r%  1cCr)aOReturns a count of the number of times that the Java regex pattern `regexp` is matched in the string `str`. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. regexp : :class:`~pyspark.sql.Column` or str regex pattern to apply. Returns ------- :class:`~pyspark.sql.Column` the number of times that a Java regex pattern is matched in the string. Examples -------- >>> df = spark.createDataFrame([("1a 2b 14m", r"\d+")], ["str", "regexp"]) >>> df.select(regexp_count('str', lit(r'\d+')).alias('d')).collect() [Row(d=3)] >>> df.select(regexp_count('str', lit(r'mmm')).alias('d')).collect() [Row(d=0)] >>> df.select(regexp_count("str", col("regexp")).alias('d')).collect() [Row(d=3)] rHregexp_extract_allr1rrrr% regexp_countY sridxcCr)a@Extract a specific group matched by the Java regex `regexp`, from the specified string column. If the regex did not match, or the specified group did not match, an empty string is returned. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. pattern : str regex pattern to apply. idx : int matched group id. Returns ------- :class:`~pyspark.sql.Column` matched value specified by `idx` group id. Examples -------- >>> df = spark.createDataFrame([('100-200',)], ['str']) >>> df.select(regexp_extract('str', r'(\d+)-(\d+)', 1).alias('d')).collect() [Row(d='100')] >>> df = spark.createDataFrame([('foo',)], ['str']) >>> df.select(regexp_extract('str', r'(\d+)', 1).alias('d')).collect() [Row(d='')] >>> df = spark.createDataFrame([('aaaac',)], ['str']) >>> df.select(regexp_extract('str', '(a+)(b)?(c)', 2).alias('d')).collect() [Row(d='')] regexp_extractrZ)r4rVrrrr%rx s#rcCs&|durd}tdt|t|t|S)aaExtract all strings in the `str` that match the Java regex `regexp` and corresponding to the regex group index. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. regexp : :class:`~pyspark.sql.Column` or str regex pattern to apply. idx : int matched group id. Returns ------- :class:`~pyspark.sql.Column` all strings in the `str` that match a Java regex and corresponding to the regex group index. Examples -------- >>> df = spark.createDataFrame([("100-200, 300-400", r"(\d+)-(\d+)")], ["str", "regexp"]) >>> df.select(regexp_extract_all('str', lit(r'(\d+)-(\d+)')).alias('d')).collect() [Row(d=['100', '300'])] >>> df.select(regexp_extract_all('str', lit(r'(\d+)-(\d+)'), 1).alias('d')).collect() [Row(d=['100', '300'])] >>> df.select(regexp_extract_all('str', lit(r'(\d+)-(\d+)'), 2).alias('d')).collect() [Row(d=['200', '400'])] >>> df.select(regexp_extract_all('str', col("regexp")).alias('d')).collect() [Row(d=['100', '300'])] NrrrZ)r4rrrrr%r s"rcCr)aReturns true if `str` matches the Java regex `regexp`, or false otherwise. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. regexp : :class:`~pyspark.sql.Column` or str regex pattern to apply. Returns ------- :class:`~pyspark.sql.Column` true if `str` matches a Java regex, or false otherwise. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp_like('str', sf.lit(r'(\d+)'))).show() +-----------------------+ |REGEXP_LIKE(str, (\d+))| +-----------------------+ | true| +-----------------------+ >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp_like('str', sf.lit(r'\d{2}b'))).show() +------------------------+ |REGEXP_LIKE(str, \d{2}b)| +------------------------+ | false| +------------------------+ >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("1a 2b 14m", r"(\d+)")], ["str", "regexp"] ... ).select(sf.regexp_like('str', sf.col("regexp"))).show() +------------------------+ |REGEXP_LIKE(str, regexp)| +------------------------+ | true| +------------------------+ rr1rrrr% regexp_like rrcCs$ttdtdt|t|tdS)avReturns the substring that matches the Java regex `regexp` within the string `str`. If the regular expression is not found, the result is null. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. regexp : :class:`~pyspark.sql.Column` or str regex pattern to apply. Returns ------- :class:`~pyspark.sql.Column` the substring that matches a Java regex within the string `str`. Examples -------- >>> df = spark.createDataFrame([("1a 2b 14m", r"\d+")], ["str", "regexp"]) >>> df.select(regexp_substr('str', lit(r'\d+')).alias('d')).collect() [Row(d='1')] >>> df.select(regexp_substr('str', lit(r'mmm')).alias('d')).collect() [Row(d=None)] >>> df.select(regexp_substr("str", col("regexp")).alias('d')).collect() [Row(d='1')] nullifrrs)rrr!r rrrr% regexp_substr s$rncCr)aO Repeats a string column n times, and returns it as a new string column. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. n : int number of times to repeat value. Returns ------- :class:`~pyspark.sql.Column` string with repeated values. Examples -------- >>> df = spark.createDataFrame([('ab',)], ['s',]) >>> df.select(repeat(df.s, 3).alias('s')).collect() [Row(s='ababab')] repeatrZ)r.rrrr%r  sr stopstepcCs"|dur td||Std|||S)a Generate a sequence of integers from `start` to `stop`, incrementing by `step`. If `step` is not set, incrementing by 1 if `start` is less than or equal to `stop`, otherwise -1. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- start : :class:`~pyspark.sql.Column` or str starting value (inclusive) stop : :class:`~pyspark.sql.Column` or str last values (inclusive) step : :class:`~pyspark.sql.Column` or str, optional value to add to current to get next element (default is 1) Returns ------- :class:`~pyspark.sql.Column` an array of sequence values Examples -------- >>> df1 = spark.createDataFrame([(-2, 2)], ('C1', 'C2')) >>> df1.select(sequence('C1', 'C2').alias('r')).collect() [Row(r=[-2, -1, 0, 1, 2])] >>> df2 = spark.createDataFrame([(4, -4, -2)], ('C1', 'C2', 'C3')) >>> df2.select(sequence('C1', 'C2', 'C3').alias('r')).collect() [Row(r=[4, 2, 0, -2, -4])] Ngenerate_seriesr1)r[r r rrr%sequence6 s$ r cCr/)a Computes the signum of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select( ... sf.sign(sf.lit(-5)), ... sf.sign(sf.lit(6)) ... ).show() +--------+-------+ |sign(-5)|sign(6)| +--------+-------+ | -1.0| 1.0| +--------+-------+ signr1r2rrr%r` rrcCr5)a Computes the signum of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select( ... sf.signum(sf.lit(-5)), ... sf.signum(sf.lit(6)) ... ).show() +----------+---------+ |SIGNUM(-5)|SIGNUM(6)| +----------+---------+ | -1.0| 1.0| +----------+---------+ )rr2rrr%signum s rcCr/)a Computes sine of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` sine of the angle, as if computed by `java.lang.Math.sin()` Examples -------- >>> import math >>> df = spark.range(1) >>> df.select(sin(lit(math.radians(90)))).first() Row(SIN(1.57079...)=1.0) sinr1r2rrr%r rrcCr/)a Aggregate function: returns the skewness of the values in a group. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` skewness of given column. Examples -------- >>> df = spark.createDataFrame([[1],[1],[2]], ["c"]) >>> df.select(skewness(df.c)).first() Row(skewness(c)=0.70710...) skewnessr1r2rrr%r rrcharsetcCs|dkrtdtdt|S)a  Computes the first argument into a binary from a string using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. charset : str charset to use to encode. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.createDataFrame([('abcd',)], ['c']) >>> df.select(encode("c", "UTF-8")).show() +----------------+ |encode(c, UTF-8)| +----------------+ | [61 62 63 64]| +----------------+ zUTF-8z)Only UTF-8 charset is supported right nowencode)rr)r!)r.rrrr%r s r str_arrayc CsNt|}t|}tttd|tddttdtd|td|tdS)af Returns the index (1-based) of the given string (`str`) in the comma-delimited list (`strArray`). Returns 0, if the string was not found or if the given string (`str`) contains a comma. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str The given string to be found. str_array : :class:`~pyspark.sql.Column` or str The comma-delimited list. Examples -------- >>> df = spark.createDataFrame([("ab", "abc,b,ab,c,def")], ['a', 'b']) >>> df.select(find_in_set(df.a, df.b).alias('r')).collect() [Row(r=3)] contains,r list_position string_split)r!rr rr rvr )r4rrrr% find_in_set s"rF ignorenullscC|rttd|S)aAggregate function: returns the first value in a group. The function by default returns the first values it sees. It will return the first non-null value it sees when ignoreNulls is set to true. If all values are null, then null is returned. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column to fetch first value for. ignorenulls : :class:`~pyspark.sql.Column` or str if first value is null then look for first non-null value. Returns ------- :class:`~pyspark.sql.Column` first value of the group. Examples -------- >>> df = spark.createDataFrame([("Alice", 2), ("Bob", 5), ("Alice", None)], ("name", "age")) >>> df = df.orderBy(df.age) >>> df.groupby("name").agg(first("age")).orderBy("name").show() +-----+----------+ | name|first(age)| +-----+----------+ |Alice| NULL| | Bob| 5| +-----+----------+ Now, to ignore any nulls we needs to set ``ignorenulls`` to `True` >>> df.groupby("name").agg(first("age", ignorenulls=True)).orderBy("name").show() +-----+----------+ | name|first(age)| +-----+----------+ |Alice| 2| | Bob| 5| +-----+----------+ firstrr*r.rrrr%r, 2 rcCr)aAggregate function: returns the last value in a group. The function by default returns the last values it sees. It will return the last non-null value it sees when ignoreNulls is set to true. If all values are null, then null is returned. .. versionadded:: 1.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column to fetch last value for. ignorenulls : :class:`~pyspark.sql.Column` or str if last value is null then look for non-null value. Returns ------- :class:`~pyspark.sql.Column` last value of the group. Examples -------- >>> df = spark.createDataFrame([("Alice", 2), ("Bob", 5), ("Alice", None)], ("name", "age")) >>> df = df.orderBy(df.age.desc()) >>> df.groupby("name").agg(last("age")).orderBy("name").show() +-----+---------+ | name|last(age)| +-----+---------+ |Alice| NULL| | Bob| 5| +-----+---------+ Now, to ignore any nulls we needs to set ``ignorenulls`` to `True` >>> df.groupby("name").agg(last("age", ignorenulls=True)).orderBy("name").show() +-----+---------+ | name|last(age)| +-----+---------+ |Alice| 2| | Bob| 5| +-----+---------+ lastrrrrr%r c rr cG2t|dkr tddd|D}tdg|RS)a Returns the greatest value of the list of column names, skipping null values. This function takes at least 2 parameters. It will return null if all parameters are null. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str columns to check for gratest value. Returns ------- :class:`~pyspark.sql.Column` gratest value. Examples -------- >>> df = spark.createDataFrame([(1, 4, 3)], ['a', 'b', 'c']) >>> df.select(greatest(df.a, df.b, df.c).alias("greatest")).collect() [Row(greatest=4)] rz'greatest should take at least 2 columnscSrrr r"rrr%r& r'zgreatest..greatestrHrr)rErrr%r" s r"cGr!)a Returns the least value of the list of column names, skipping null values. This function takes at least 2 parameters. It will return null if all parameters are null. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- cols : :class:`~pyspark.sql.Column` or str column names or columns to be compared Returns ------- :class:`~pyspark.sql.Column` least value. Examples -------- >>> df = spark.createDataFrame([(1, 4, 3)], ['a', 'b', 'c']) >>> df.select(least(df.a, df.b, df.c).alias("least")).collect() [Row(least=1)] rz$least should take at least 2 columnscSrrr r"rrr%r& r'zleast..leastr#rErrr%r$ s r$cCr/) Trim the spaces from left end for the specified string value. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` left trimmed values. Examples -------- >>> df = spark.createDataFrame([" Spark", "Spark ", " Spark"], "STRING") >>> df.select(ltrim("value").alias("r")).withColumn("length", length("r")).show() +-------+------+ | r|length| +-------+------+ | Spark| 5| |Spark | 7| | Spark| 5| +-------+------+ trimr1r2rrr%r& r3r&cCr/)a Trim the spaces from right end for the specified string value. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` right trimmed values. Examples -------- >>> df = spark.createDataFrame([" Spark", "Spark ", " Spark"], "STRING") >>> df.select(rtrim("value").alias("r")).withColumn("length", length("r")).show() +--------+------+ | r|length| +--------+------+ | Spark| 8| | Spark| 5| | Spark| 6| +--------+------+ rtrimr1r2rrr%r' r3r'cCr/)r%ltrimr1r2rrr%r(" r3r(cCs|dur td||Std|S)a Remove the leading and trailing `trim` characters from `str`. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str Input column or strings. trim : :class:`~pyspark.sql.Column` or str The trim string characters to trim, the default value is a single space Examples -------- >>> df = spark.createDataFrame([("SSparkSQLS", "SL", )], ['a', 'b']) >>> df.select(btrim(df.a, df.b).alias('r')).collect() [Row(r='parkSQ')] >>> df = spark.createDataFrame([(" SparkSQL ",)], ['a']) >>> df.select(btrim(df.a).alias('r')).collect() [Row(r='SparkSQL')] Nr&r1)r4r&rrr%btrimD s  r)suffixcCr)aX Returns a boolean. The value is True if str ends with suffix. Returns NULL if either input expression is NULL. Otherwise, returns False. Both str or suffix must be of STRING or BINARY type. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str A column of string. suffix : :class:`~pyspark.sql.Column` or str A column of string, the suffix. Examples -------- >>> df = spark.createDataFrame([("Spark SQL", "Spark",)], ["a", "b"]) >>> df.select(endswith(df.a, df.b).alias('r')).collect() [Row(r=False)] >>> df = spark.createDataFrame([("414243", "4243",)], ["e", "f"]) >>> df = df.select(to_binary("e").alias("e"), to_binary("f").alias("f")) >>> df.printSchema() root |-- e: binary (nullable = true) |-- f: binary (nullable = true) >>> df.select(endswith("e", "f"), endswith("f", "e")).show() +--------------+--------------+ |endswith(e, f)|endswith(f, e)| +--------------+--------------+ | true| false| +--------------+--------------+ r*r1)r4r*rrr%endswitha  "r+prefixcCr)as Returns a boolean. The value is True if str starts with prefix. Returns NULL if either input expression is NULL. Otherwise, returns False. Both str or prefix must be of STRING or BINARY type. .. versionadded:: 3.5.0 Parameters ---------- str : :class:`~pyspark.sql.Column` or str A column of string. prefix : :class:`~pyspark.sql.Column` or str A column of string, the prefix. Examples -------- >>> df = spark.createDataFrame([("Spark SQL", "Spark",)], ["a", "b"]) >>> df.select(startswith(df.a, df.b).alias('r')).collect() [Row(r=True)] >>> df = spark.createDataFrame([("414243", "4142",)], ["e", "f"]) >>> df = df.select(to_binary("e").alias("e"), to_binary("f").alias("f")) >>> df.printSchema() root |-- e: binary (nullable = true) |-- f: binary (nullable = true) >>> df.select(startswith("e", "f"), startswith("f", "e")).show() +----------------+----------------+ |startswith(e, f)|startswith(f, e)| +----------------+----------------+ | true| false| +----------------+----------------+ starts_withr1)r4r-rrr% startswith r,r/cCr/)ayComputes the character length of string data or number of bytes of binary data. The length of character data includes the trailing spaces. The length of binary data includes binary zeros. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` length of the value. Examples -------- >>> spark.createDataFrame([('ABC ',)], ['a']).select(length('a').alias('length')).collect() [Row(length=4)] r\r1r2rrr%r\ rcGsdd|D}tt|S)aIReturns the first column that is not null. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- cols : :class:`~pyspark.sql.Column` or str list of columns to work on. Returns ------- :class:`~pyspark.sql.Column` value of the first column that is not null. Examples -------- >>> cDf = spark.createDataFrame([(None, None), (1, None), (None, 2)], ("a", "b")) >>> cDf.show() +----+----+ | a| b| +----+----+ |NULL|NULL| | 1|NULL| |NULL| 2| +----+----+ >>> cDf.select(coalesce(cDf["a"], cDf["b"])).show() +--------------+ |coalesce(a, b)| +--------------+ | NULL| | 1| | 2| +--------------+ >>> cDf.select('*', coalesce(cDf["a"], lit(0.0))).show() +----+----+----------------+ | a| b|coalesce(a, 0.0)| +----+----+----------------+ |NULL|NULL| 0.0| | 1|NULL| 1.0| |NULL| 2| 0.0| +----+----+----------------+ cSrrr r"rrr%r& r'zcoalesce..)rr rErrr%coalesce s* r0cC t||S)a Returns `col2` if `col1` is null, or `col1` otherwise. .. versionadded:: 3.5.0 Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str col2 : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([(None, 8,), (1, 9,)], ["a", "b"]) >>> df.select(nvl(df.a, df.b).alias('r')).collect() [Row(r=8), Row(r=1)] r0rrrr%nvl s r3col3cCs0t|}t|}t|}tt|||S)a Returns `col2` if `col1` is not null, or `col3` otherwise. .. versionadded:: 3.5.0 Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str col2 : :class:`~pyspark.sql.Column` or str col3 : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([(None, 8, 6,), (1, 9, 9,)], ["a", "b", "c"]) >>> df.select(nvl2(df.a, df.b, df.c).alias('r')).collect() [Row(r=6), Row(r=9)] )r!rr rrv)rrr4rrr%nvl2 sr5cCr1)a Returns `col2` if `col1` is null, or `col1` otherwise. .. versionadded:: 3.5.0 Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str col2 : :class:`~pyspark.sql.Column` or str Examples -------- >>> import pyspark.sql.functions as sf >>> df = spark.createDataFrame([(None,), (1,)], ["e"]) >>> df.select(sf.ifnull(df.e, sf.lit(8))).show() +------------+ |ifnull(e, 8)| +------------+ | 8| | 1| +------------+ r2rrrr%ifnull s r6cCr)a Returns null if `col1` equals to `col2`, or `col1` otherwise. .. versionadded:: 3.5.0 Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str col2 : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([(None, None,), (1, 9,)], ["a", "b"]) >>> df.select(nullif(df.a, df.b).alias('r')).collect() [Row(r=None), Row(r=1)] rr1rrrr%r6 s rcCr/)a+Calculates the MD5 digest and returns the value as a 32 character hex string. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> spark.createDataFrame([('ABC',)], ['a']).select(md5('a').alias('hash')).collect() [Row(hash='902fbdd2b1df0c4f70b4a5d23525e932')] md5r1r2rrr%r7J s r7numBitscCs*|dvrtd|dkrtd|Std)aBReturns the hex string result of SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The numBits indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. numBits : int the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). Returns ------- :class:`~pyspark.sql.Column` the column for computed results. Examples -------- >>> df = spark.createDataFrame([["Alice"], ["Bob"]], ["name"]) >>> df.withColumn("sha2", sha2(df.name, 256)).show(truncate=False) +-----+----------------------------------------------------------------+ |name |sha2 | +-----+----------------------------------------------------------------+ |Alice|3bc51062973c458d5a6f2d8d64a023246354ad7e064b1e4e009ec8a0699a3043| |Bob |cd9fb1e148ccd8442e5aa74904cc73bf6fb54d1d54d333bd596aa9bb4bb4e961| +-----+----------------------------------------------------------------+ >rrz0numBits should be one of {224, 256, 384, 512, 0}rsha256z4SHA-224, SHA-384, and SHA-512 are not supported yet.)rr*r)r.r8rrr%sha2d s# r=cCr)a  Returns the current date at the start of query evaluation as a :class:`DateType` column. All calls of current_date within the same query return the same value. .. versionadded:: 3.5.0 Returns ------- :class:`~pyspark.sql.Column` current date. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(1).select(sf.curdate()).show() # doctest: +SKIP +--------------+ |current_date()| +--------------+ | 2022-08-26| +--------------+ todayr(rrrr%curdate sr?cCstS)a3 Returns the current date at the start of query evaluation as a :class:`DateType` column. All calls of current_date within the same query return the same value. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Returns ------- :class:`~pyspark.sql.Column` current date. Examples -------- >>> df = spark.range(1) >>> df.select(current_date()).show() # doctest: +SKIP +--------------+ |current_date()| +--------------+ | 2022-08-26| +--------------+ )r?rrrr% current_date sr@cCr)a Returns the current timestamp at the start of query evaluation. .. versionadded:: 3.5.0 Returns ------- :class:`~pyspark.sql.Column` current timestamp at the start of query evaluation. Examples -------- >>> df = spark.range(1) >>> df.select(now()).show(truncate=False) # doctest: +SKIP +-----------------------+ |now() | +-----------------------+ |2022-08-26 21:23:22.716| +-----------------------+ nowr(rrrr%rA r8rAcCrrmrnr2rrr%ro rpcCrrcrdr2rrr%rerf timestampcCsN|}|dvr d}n|dvrd}n |dkrd}n|dkrd}td t||S) a Returns timestamp truncated to the unit specified by the format. .. versionadded:: 2.3.0 Parameters ---------- format : str 'year', 'yyyy', 'yy', 'month', 'mon', 'mm', 'day', 'dd', 'hour', 'minute', 'second', 'week', 'quarter' timestamp : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([('1997-02-28 05:02:11',)], ['t']) >>> df.select(date_trunc('year', df.t).alias('year')).collect() [Row(year=datetime.datetime(1997, 1, 1, 0, 0))] >>> df.select(date_trunc('mon', df.t).alias('month')).collect() [Row(month=datetime.datetime(1997, 2, 1, 0, 0))] )yyyyyyyear)monmmmonthdddayhhhour date_trunc)rr*rL)rrBrrr%rM5srMfieldsourcecCr)a Extracts a part of the date/timestamp or interval source. .. versionadded:: 3.5.0 Parameters ---------- field : :class:`~pyspark.sql.Column` or str selects which part of the source should be extracted, and supported string values are as same as the fields of the equivalent function `extract`. source : :class:`~pyspark.sql.Column` or str a date/timestamp or interval column from where `field` should be extracted. Returns ------- :class:`~pyspark.sql.Column` a part of the date/timestamp or interval source. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select( ... date_part(lit('YEAR'), 'ts').alias('year'), ... date_part(lit('month'), 'ts').alias('month'), ... date_part(lit('WEEK'), 'ts').alias('week'), ... date_part(lit('D'), 'ts').alias('day'), ... date_part(lit('M'), 'ts').alias('minute'), ... date_part(lit('S'), 'ts').alias('second') ... ).collect() [Row(year=2015, month=4, week=15, day=8, minute=8, second=Decimal('15.000000'))] date_partr1rNrOrrr%rPVs !rPcCr1)a- Extracts a part of the date/timestamp or interval source. .. versionadded:: 3.5.0 Parameters ---------- field : :class:`~pyspark.sql.Column` or str selects which part of the source should be extracted. source : :class:`~pyspark.sql.Column` or str a date/timestamp or interval column from where `field` should be extracted. Returns ------- :class:`~pyspark.sql.Column` a part of the date/timestamp or interval source. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select( ... extract(lit('YEAR'), 'ts').alias('year'), ... extract(lit('month'), 'ts').alias('month'), ... extract(lit('WEEK'), 'ts').alias('week'), ... extract(lit('D'), 'ts').alias('day'), ... extract(lit('M'), 'ts').alias('minute'), ... extract(lit('S'), 'ts').alias('second') ... ).collect() [Row(year=2015, month=4, week=15, day=8, minute=8, second=Decimal('15.000000'))] rPrQrrr%extractzrrScCr1)a Extracts a part of the date/timestamp or interval source. .. versionadded:: 3.5.0 Parameters ---------- field : :class:`~pyspark.sql.Column` or str selects which part of the source should be extracted, and supported string values are as same as the fields of the equivalent function `extract`. source : :class:`~pyspark.sql.Column` or str a date/timestamp or interval column from where `field` should be extracted. Returns ------- :class:`~pyspark.sql.Column` a part of the date/timestamp or interval source. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select( ... datepart(lit('YEAR'), 'ts').alias('year'), ... datepart(lit('month'), 'ts').alias('month'), ... datepart(lit('WEEK'), 'ts').alias('week'), ... datepart(lit('D'), 'ts').alias('day'), ... datepart(lit('M'), 'ts').alias('minute'), ... datepart(lit('S'), 'ts').alias('second') ... ).collect() [Row(year=2015, month=4, week=15, day=8, minute=8, second=Decimal('15.000000'))] rRrQrrr%datepart !rTcCr/)a, Extract the year of a given date/timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` year part of the date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(year('dt').alias('year')).collect() [Row(year=2015)] rEr1r2rrr%rErrEcCr/)a3 Extract the quarter of a given date/timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` quarter of the date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(quarter('dt').alias('quarter')).collect() [Row(quarter=2)] quarterr1r2rrr%rVrrVcCr/)a. Extract the month of a given date/timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` month part of the date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(month('dt').alias('month')).collect() [Row(month=4)] rHr1r2rrr%rHrrHcCstd|tdS)a{ Extract the day of the week of a given date/timestamp as integer. Ranges from 1 for a Sunday through to 7 for a Saturday .. versionadded:: 2.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` day of the week for given date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(dayofweek('dt').alias('day')).collect() [Row(day=4)] dayofweekrr*rLr2rrr%rWsrWcCr/)a Extract the day of the month of a given date/timestamp as integer. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` day of the month for given date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(day('dt').alias('day')).collect() [Row(day=8)] rJr1r2rrr%rJ2rrJcCr5)aC Extract the day of the month of a given date/timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` day of the month for given date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(dayofmonth('dt').alias('day')).collect() [Row(day=8)] )rJr2rrr% dayofmonthKsrYcCr/)aA Extract the day of the year of a given date/timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` day of the year for given date/timestamp as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(dayofyear('dt').alias('day')).collect() [Row(day=98)] dayofyearr1r2rrr%rZgrrZcCr/)aU Extract the hours of a given timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` hour part of the timestamp as integer. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select(hour('ts').alias('hour')).collect() [Row(hour=13)] rLr1r2rrr%rLrrLcCr/)a_ Extract the minutes of a given timestamp as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` minutes part of the timestamp as integer. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select(minute('ts').alias('minute')).collect() [Row(minute=8)] minuter1r2rrr%r[rr[cCr/)a] Extract the seconds of a given date as integer. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` `seconds` part of the timestamp as integer. Examples -------- >>> import datetime >>> df = spark.createDataFrame([(datetime.datetime(2015, 4, 8, 13, 8, 15),)], ['ts']) >>> df.select(second('ts').alias('second')).collect() [Row(second=15)] secondr1r2rrr%r\rr\cCr/)a Extract the week number of a given date as integer. A week is considered to start on a Monday and week 1 is the first week with more than 3 days, as defined by ISO 8601 .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` `week` of the year for given date as integer. Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(weekofyear(df.dt).alias('week')).collect() [Row(week=15)] weekofyearr1r2rrr%r] r]cCr/)a Computes cosine of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str angle in radians Returns ------- :class:`~pyspark.sql.Column` cosine of the angle, as if computed by `java.lang.Math.cos()`. Examples -------- >>> import math >>> df = spark.range(1) >>> df.select(cos(lit(math.pi))).first() Row(COS(3.14159...)=-1.0) cosr1r2rrr%r_rr_cCr/)aC Computes inverse cosine of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` inverse cosine of `col`, as if computed by `java.lang.Math.acos()` Examples -------- >>> df = spark.range(1, 3) >>> df.select(acos(df.id)).show() +--------+ |ACOS(id)| +--------+ | 0.0| | NaN| +--------+ acosr1r2rrr%r`rr`funcNamecGst|g|RS)a Call a SQL function. .. versionadded:: 3.5.0 Parameters ---------- funcName : str function name that follows the SQL identifier syntax (can be quoted, can be qualified) cols : :class:`~pyspark.sql.Column` or str column names or :class:`~pyspark.sql.Column`\s to be used in the function Returns ------- :class:`~pyspark.sql.Column` result of executed function. Examples -------- >>> from pyspark.sql.functions import call_udf, col >>> from pyspark.sql.types import IntegerType, StringType >>> df = spark.createDataFrame([(1, "a"),(2, "b"), (3, "c")],["id", "name"]) >>> _ = spark.udf.register("intX2", lambda i: i * 2, IntegerType()) >>> df.select(call_function("intX2", "id")).show() +---------+ |intX2(id)| +---------+ | 2| | 4| | 6| +---------+ >>> _ = spark.udf.register("strX2", lambda s: s * 2, StringType()) >>> df.select(call_function("strX2", col("name"))).show() +-----------+ |strX2(name)| +-----------+ | aa| | bb| | cc| +-----------+ >>> df.select(call_function("avg", col("id"))).show() +-------+ |avg(id)| +-------+ | 2.0| +-------+ >>> _ = spark.sql("CREATE FUNCTION custom_avg AS 'test.org.apache.spark.sql.MyDoubleAvg'") ... # doctest: +SKIP >>> df.select(call_function("custom_avg", col("id"))).show() ... # doctest: +SKIP +------------------------------------+ |spark_catalog.default.custom_avg(id)| +------------------------------------+ | 102.0| +------------------------------------+ >>> df.select(call_function("spark_catalog.default.custom_avg", col("id"))).show() ... # doctest: +SKIP +------------------------------------+ |spark_catalog.default.custom_avg(id)| +------------------------------------+ | 102.0| +------------------------------------+ r1)rarrrr% call_function6s@rbcCr)aReturns a new :class:`~pyspark.sql.Column` for the population covariance of ``col1`` and ``col2``. .. versionadded:: 2.0.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str first column to calculate covariance. col1 : :class:`~pyspark.sql.Column` or str second column to calculate covariance. Returns ------- :class:`~pyspark.sql.Column` covariance of these two column values. Examples -------- >>> a = [1] * 10 >>> b = [1] * 10 >>> df = spark.createDataFrame(zip(a, b), ["a", "b"]) >>> df.agg(covar_pop("a", "b").alias('c')).collect() [Row(c=0.0)] covar_popr1rrrr%rcyrrccCr)aReturns a new :class:`~pyspark.sql.Column` for the sample covariance of ``col1`` and ``col2``. .. versionadded:: 2.0.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : :class:`~pyspark.sql.Column` or str first column to calculate covariance. col1 : :class:`~pyspark.sql.Column` or str second column to calculate covariance. Returns ------- :class:`~pyspark.sql.Column` sample covariance of these two column values. Examples -------- >>> a = [1] * 10 >>> b = [1] * 10 >>> df = spark.createDataFrame(zip(a, b), ["a", "b"]) >>> df.agg(covar_samp("a", "b").alias('c')).collect() [Row(c=0.0)] covar_sampr1rrrr%rdrrdcCr/)a  Computes the exponential of the given value. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column to calculate exponential for. Returns ------- :class:`~pyspark.sql.Column` exponential of the given value. Examples -------- >>> df = spark.range(1) >>> df.select(exp(lit(0))).show() +------+ |EXP(0)| +------+ | 1.0| +------+ expr1r2rrr%rerrecCr/)a Computes the factorial of the given value. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str a column to calculate factorial for. Returns ------- :class:`~pyspark.sql.Column` factorial of given value. Examples -------- >>> df = spark.createDataFrame([(5,)], ['n']) >>> df.select(factorial(df.n).alias('f')).collect() [Row(f=120)] factorialr1r2rrr%rfrrfcCr/)aReturns the base-2 logarithm of the argument. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str a column to calculate logariphm for. Returns ------- :class:`~pyspark.sql.Column` logariphm of given value. Examples -------- >>> df = spark.createDataFrame([(4,)], ['a']) >>> df.select(log2('a').alias('log2')).show() +----+ |log2| +----+ | 2.0| +----+ log2r1r2rrr%rgs rgcCr/)aReturns the natural logarithm of the argument. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str a column to calculate logariphm for. Returns ------- :class:`~pyspark.sql.Column` natural logarithm of given value. Examples -------- >>> df = spark.createDataFrame([(4,)], ['a']) >>> df.select(ln('a')).show() +------------------+ | ln(a)| +------------------+ |1.3862943611198906| +------------------+ lnr1r2rrr%rhrrhcCr/)aQ Converts an angle measured in radians to an approximately equivalent angle measured in degrees. .. versionadded:: 2.1.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str angle in radians Returns ------- :class:`~pyspark.sql.Column` angle in degrees, as if computed by `java.lang.Math.toDegrees()` Examples -------- >>> import math >>> df = spark.range(1) >>> df.select(degrees(lit(math.pi))).first() Row(DEGREES(3.14159...)=180.0) degreesr1r2rrr%ri0r^ricCr/)a7 Converts an angle measured in degrees to an approximately equivalent angle measured in radians. .. versionadded:: 2.1.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str angle in degrees Returns ------- :class:`~pyspark.sql.Column` angle in radians, as if computed by `java.lang.Math.toRadians()` Examples -------- >>> df = spark.range(1) >>> df.select(radians(lit(180))).first() Row(RADIANS(180)=3.14159...) radiansr1r2rrr%rjOrrjcCr/)a2 Compute inverse tangent of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` inverse tangent of `col`, as if computed by `java.lang.Math.atan()` Examples -------- >>> df = spark.range(1) >>> df.select(atan(df.id)).show() +--------+ |ATAN(id)| +--------+ | 0.0| +--------+ atanr1r2rrr%rklrrkcCs.dtdtfdtfdd}td||||S)a .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col1 : str, :class:`~pyspark.sql.Column` or float coordinate on y-axis col2 : str, :class:`~pyspark.sql.Column` or float coordinate on x-axis Returns ------- :class:`~pyspark.sql.Column` the `theta` component of the point (`r`, `theta`) in polar coordinates that corresponds to the point (`x`, `y`) in Cartesian coordinates, as if computed by `java.lang.Math.atan2()` Examples -------- >>> df = spark.range(1) >>> df.select(atan2(lit(1), lit(2))).first() Row(ATAN2(1, 2)=0.46364...) rCrrcSst|ttfr t|S|Sr-)r;r_rrL)rCrrr% lit_or_columnszatan2..lit_or_columnatan2)rrrr*)rrrlrrr%rmsrmcCr/)a Computes tangent of the input column. .. versionadded:: 1.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str angle in radians Returns ------- :class:`~pyspark.sql.Column` tangent of the given value, as if computed by `java.lang.Math.tan()` Examples -------- >>> import math >>> df = spark.range(1) >>> df.select(tan(lit(math.radians(45)))).first() Row(TAN(0.78539...)=0.99999...) tanr1r2rrr%rnrrnscalecCtd|t|S)ag Round the given value to `scale` decimal places using HALF_UP rounding mode if `scale` >= 0 or at integral part when `scale` < 0. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str input column to round. scale : int optional default 0 scale value. Returns ------- :class:`~pyspark.sql.Column` rounded values. Examples -------- >>> spark.createDataFrame([(2.5,)], ['a']).select(round('a', 0).alias('r')).collect() [Row(r=3.0)] roundrXr.rorrr%rqrqcCrp)aj Round the given value to `scale` decimal places using HALF_EVEN rounding mode if `scale` >= 0 or at integral part when `scale` < 0. .. versionadded:: 2.0.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str input column to round. scale : int optional default 0 scale value. Returns ------- :class:`~pyspark.sql.Column` rounded values. Examples -------- >>> spark.createDataFrame([(2.5,)], ['a']).select(bround('a', 0).alias('r')).collect() [Row(r=2.0)] round_evenrXrrrrr%broundrsruindexcCs2t|tr t|nt|}|d}tdt||S)a  Collection function: Returns element of array at given (0-based) index. If the index points outside of the array boundaries, then this function returns NULL. .. versionadded:: 3.4.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column containing array index : :class:`~pyspark.sql.Column` or str or int index to check for in array Returns ------- :class:`~pyspark.sql.Column` value at given position. Notes ----- The position is not 1 based, but 0 based index. Supports Spark Connect. See Also -------- :meth:`element_at` Examples -------- >>> df = spark.createDataFrame([(["a", "b", "c"], 1)], ['data', 'index']) >>> df.select(get(df.data, 1)).show() +------------+ |get(data, 1)| +------------+ | b| +------------+ >>> df.select(get(df.data, -1)).show() +-------------+ |get(data, -1)| +-------------+ | NULL| +-------------+ >>> df.select(get(df.data, 3)).show() +------------+ |get(data, 3)| +------------+ | NULL| +------------+ >>> df.select(get(df.data, "index")).show() +----------------+ |get(data, index)| +----------------+ | b| +----------------+ >>> df.select(get(df.data, col("index") - 1)).show() +----------------------+ |get(data, (index - 1))| +----------------------+ | a| +----------------------+ r list_extractr^)r.rvrrr%get sCrxcCsZttdtdtdt|tdtdtdtdtdtdd td tdd d tdS) aTranslate the first letter of each word to upper case in the sentence. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` string with all first letters are uppercase in each word. Examples -------- >>> spark.createDataFrame([('ab cd',)], ['a']).select(initcap("a").alias('v')).collect() [Row(v='Ab Cd')] array_to_stringlist_transformr rCconcatr0rrrtrry)rrr!r rr r2rrr%initcapSs2 r}cCstdt|dS)ul Calculates the byte length for the specified string column. .. versionadded:: 3.3.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str Source column or strings Returns ------- :class:`~pyspark.sql.Column` Byte length of the col Examples -------- >>> from pyspark.sql.functions import octet_length >>> spark.createDataFrame([('cat',), ( '🐈',)], ['cat']) \ ... .select(octet_length('cat')).collect() [Row(octet_length(cat)=3), Row(octet_length(cat)=4)] octet_lengthblob)r)r!castr2rrr%r~sr~cCr/)a Computes hex value of the given column, which could be :class:`~pyspark.sql.types.StringType`, :class:`~pyspark.sql.types.BinaryType`, :class:`~pyspark.sql.types.IntegerType` or :class:`~pyspark.sql.types.LongType`. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` hexadecimal representation of given value as string. Examples -------- >>> spark.createDataFrame([('ABC', 3)], ['a', 'b']).select(hex('a'), hex('b')).collect() [Row(hex(a)='414243', hex(b)='3')] hexr1r2rrr%r rcCr/)a Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of number. column and returns it as a binary column. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` string representation of given hexadecimal value. Examples -------- >>> spark.createDataFrame([('414243',)], ['a']).select(unhex('a')).collect() [Row(unhex(a)=bytearray(b'ABC'))] unhexr1r2rrr%rrrcCs&t|tr tt|}td|dS)a Computes the BASE64 encoding of a binary column and returns it as a string column. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` BASE64 encoding of string value. Examples -------- >>> df = spark.createDataFrame(["Spark", "PySpark", "Pandas API"], "STRING") >>> df.select(base64("value")).show() +----------------+ | base64(value)| +----------------+ | U3Bhcms=| | UHlTcGFyaw==| |UGFuZGFzIEFQSQ==| +----------------+ base64BLOB)r;r4rr r*rr2rrr%rs  rcCr/)a Decodes a BASE64 encoded string column and returns it as a binary column. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. Returns ------- :class:`~pyspark.sql.Column` encoded string value. Examples -------- >>> df = spark.createDataFrame(["U3Bhcms=", ... "UHlTcGFyaw==", ... "UGFuZGFzIEFQSQ=="], "STRING") >>> df.select(unbase64("value")).show() +--------------------+ | unbase64(value)| +--------------------+ | [53 70 61 72 6B]| |[50 79 53 70 61 7...| |[50 61 6E 64 61 7...| +--------------------+ from_base64r1r2rrr%unbase64rUrmonthscCs6t|tr t|nt|}tdt|td|dS)ah Returns the date that is `months` months after `start`. If `months` is a negative value then these amount of months will be deducted from the `start`. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- start : :class:`~pyspark.sql.Column` or str date column to work on. months : :class:`~pyspark.sql.Column` or str or int how many months after the given date to calculate. Accepts negative value as well to calculate backwards. Returns ------- :class:`~pyspark.sql.Column` a date after/before given number of months. Examples -------- >>> df = spark.createDataFrame([('2015-04-08', 2)], ['dt', 'add']) >>> df.select(add_months(df.dt, 1).alias('next_month')).collect() [Row(next_month=datetime.date(2015, 5, 8))] >>> df.select(add_months(df.dt, df.add.cast('integer')).alias('next_month')).collect() [Row(next_month=datetime.date(2015, 6, 8))] >>> df.select(add_months('dt', -2).alias('prev_month')).collect() [Row(prev_month=datetime.date(2015, 2, 8))] date_add to_monthsr)r;r_r r!r)rr)r[rrrr% add_months s!r delimiternull_replacementc CsNt|}|durtd|tdttdt|td}td|t|S)a Concatenates the elements of `column` using the `delimiter`. Null values are replaced with `null_replacement` if set, otherwise they are ignored. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. delimiter : str delimiter used to concatenate elements null_replacement : str, optional if set then null values will be replaced by this value Returns ------- :class:`~pyspark.sql.Column` a column of string type. Concatenated values. Examples -------- >>> df = spark.createDataFrame([(["a", "b", "c"],), (["a", None],)], ['data']) >>> df.select(array_join(df.data, ",").alias("joined")).collect() [Row(joined='a,b,c'), Row(joined='a')] >>> df.select(array_join(df.data, ",", "NULL").alias("joined")).collect() [Row(joined='a,b,c'), Row(joined='a,NULL')] NrzrCry) r!rrr r rr rvr))r.rrrrr% array_joinEs "(rcCs"ttttd|t|tdS)a Collection function: Locates the position of the first occurrence of the given value in the given array. Returns null if either of the arguments are null. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The position is not zero based, but 1 based index. Returns 0 if the given value could not be found in the array. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to work on. value : Any value to look for. Returns ------- :class:`~pyspark.sql.Column` position of the value in the given array if found and 0 otherwise. Examples -------- >>> df = spark.createDataFrame([(["c", "b", "a"],), ([],)], ['data']) >>> df.select(array_position(df.data, "a")).collect() [Row(array_position(data, a)=3), Row(array_position(data, a)=0)] rr)rr r!r*rLr rrrr%array_positionos"!rcCstdt||S)a Collection function: Returns an array containing element as well as all elements from array. The new element is positioned at the beginning of the array. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column containing array value : a literal value, or a :class:`~pyspark.sql.Column` expression. Returns ------- :class:`~pyspark.sql.Column` an array excluding given value. Examples -------- >>> df = spark.createDataFrame([([2, 3, 4],), ([],)], ['data']) >>> df.select(array_prepend(df.data, 1)).collect() [Row(array_prepend(data, 1)=[1, 2, 3, 4]), Row(array_prepend(data, 1)=[1])] list_prependrXrrrr% array_prependsrcCs*t|tr t|n|}tdtd|||S)a Collection function: creates an array containing a column repeated count times. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column name or column that contains the element to be repeated count : :class:`~pyspark.sql.Column` or str or int column name, column, or int containing the number of times to repeat the first argument Returns ------- :class:`~pyspark.sql.Column` an array of repeated elements. Examples -------- >>> df = spark.createDataFrame([('ab',)], ['data']) >>> df.select(array_repeat(df.data, 3).alias('r')).collect() [Row(r=['ab', 'ab', 'ab'])] rrG)r;r_rLr*)r.rrrr% array_repeatsrcCr/)a Returns the total number of elements in the array. The function returns null for null input. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` total number of elements in the array. Examples -------- >>> df = spark.createDataFrame([([2, 1, 3],), (None,)], ['data']) >>> df.select(array_size(df.data).alias('r')).collect() [Row(r=3), Row(r=None)] rHr1r2rrr% array_sizerr comparatorcCs&|durtdtd|tdtdS)a Collection function: sorts the input array in ascending order. The elements of the input array must be orderable. Null elements will be placed at the end of the returned array. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Can take a `comparator` function. .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression comparator : callable, optional A binary ``(Column, Column) -> Column: ...``. The comparator will take two arguments representing two elements of the array. It returns a negative integer, 0, or a positive integer as the first element is less than, equal to, or greater than the second element. If the comparator function returns null, the function will fail and raise an error. Returns ------- :class:`~pyspark.sql.Column` sorted array. Examples -------- >>> df = spark.createDataFrame([([2, 1, None, 3],),([1],),([],)], ['data']) >>> df.select(array_sort(df.data).alias('r')).collect() [Row(r=[1, 2, 3, None]), Row(r=[1]), Row(r=[])] >>> df = spark.createDataFrame([(["foo", "foobar", None, "bar"],),(["foo"],),([],)], ['data']) >>> df.select(array_sort( ... "data", ... lambda x, y: when(x.isNull() | y.isNull(), lit(0)).otherwise(length(y) - length(x)) ... ).alias("r")).collect() [Row(r=['foobar', 'foo', None, 'bar']), Row(r=['foo']), Row(r=[])] Nzcomparator is not yet supported list_sortASC NULLS LASTrr*rL)r.rrrr%rs+rTcCs,|rd}d}nd}d}td|t|t|S)aA Collection function: sorts the input array in ascending or descending order according to the natural ordering of the array elements. Null elements will be placed at the beginning of the returned array in ascending order or at the end of the returned array in descending order. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression asc : bool, optional whether to sort in ascending or descending order. If `asc` is True (default) then ascending and if False then descending. Returns ------- :class:`~pyspark.sql.Column` sorted array. Examples -------- >>> df = spark.createDataFrame([([2, 1, None, 3],),([1],),([],)], ['data']) >>> df.select(sort_array(df.data).alias('r')).collect() [Row(r=[None, 1, 2, 3]), Row(r=[1]), Row(r=[])] >>> df.select(sort_array(df.data, asc=False).alias('r')).collect() [Row(r=[3, 2, 1, None]), Row(r=[1]), Row(r=[])] rz NULLS FIRSTDESCrrrX)r.reorder null_orderrrr% sort_arrays !rrylimitcCs |dkrtdtd|t|S)ax Splits str around matches of the given pattern. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- str : :class:`~pyspark.sql.Column` or str a string expression to split pattern : str a string representing a regular expression. The regex string should be a Java regular expression. limit : int, optional an integer which controls the number of times `pattern` is applied. * ``limit > 0``: The resulting array's length will not be more than `limit`, and the resulting array's last entry will contain all input beyond the last matched pattern. * ``limit <= 0``: `pattern` will be applied as many times as possible, and the resulting array can be of any size. .. versionchanged:: 3.0 `split` now takes an optional `limit` field. If not provided, default limit value is -1. Returns ------- :class:`~pyspark.sql.Column` array of separated strings. Examples -------- >>> df = spark.createDataFrame([('oneAtwoBthreeC',)], ['s',]) >>> df.select(split(df.s, '[ABC]', 2).alias('s')).collect() [Row(s=['one', 'twoBthreeC'])] >>> df.select(split(df.s, '[ABC]', -1).alias('s')).collect() [Row(s=['one', 'two', 'three', ''])] rzlimit is not yet supportedregexp_split_to_arrayr)r4rVrrrr%splitCs)rsrcpartNumcCsjt|}t|}t|}td|||}tt||B|Btdt|tdktd|S)a Splits `str` by delimiter and return requested part of the split (1-based). If any input is null, returns null. if `partNum` is out of range of split parts, returns empty string. If `partNum` is 0, throws an error. If `partNum` is negative, the parts are counted backward from the end of the string. If the `delimiter` is an empty string, the `str` is not split. .. versionadded:: 3.5.0 Parameters ---------- src : :class:`~pyspark.sql.Column` or str A column of string to be splited. delimiter : :class:`~pyspark.sql.Column` or str A column of string, the delimiter used for split. partNum : :class:`~pyspark.sql.Column` or str A column of string, requested part of the split (1-based). Examples -------- >>> df = spark.createDataFrame([("11.12.13", ".", 3,)], ["a", "b", "c"]) >>> df.select(split_part(df.a, df.b, df.c).alias('r')).collect() [Row(r='13')] split_partNrs)r!rrr rr rv)rrrpartrrr%rss DrcCr/)a Aggregate function: returns the unbiased sample standard deviation of the expression in a group. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` standard deviation of given column. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(6).select(sf.stddev_samp("id")).show() +------------------+ | stddev_samp(id)| +------------------+ |1.8708286933869...| +------------------+ stddev_sampr1r2rrr%rrrcCr5)aa Aggregate function: alias for stddev_samp. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` standard deviation of given column. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(6).select(sf.stddev("id")).show() +------------------+ | stddev(id)| +------------------+ |1.8708286933869...| +------------------+ rr2rrr%stddevrcCr5)a Aggregate function: alias for stddev_samp. .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` standard deviation of given column. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(6).select(sf.std("id")).show() +------------------+ | std(id)| +------------------+ |1.8708286933869...| +------------------+ rr2rrr%stdsrcCr/)a Aggregate function: returns population standard deviation of the expression in a group. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` standard deviation of given column. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.range(6).select(sf.stddev_pop("id")).show() +-----------------+ | stddev_pop(id)| +-----------------+ |1.707825127659...| +-----------------+ stddev_popr1r2rrr%rrrcCr/)a Aggregate function: returns the population variance of the values in a group. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` variance of given column. Examples -------- >>> df = spark.range(6) >>> df.select(var_pop(df.id)).first() Row(var_pop(id)=2.91666...) var_popr1r2rrr%rrrcCr/)aJ Aggregate function: returns the unbiased sample variance of the values in a group. .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` variance of given column. Examples -------- >>> df = spark.range(6) >>> df.select(var_samp(df.id)).show() +------------+ |var_samp(id)| +------------+ | 3.5| +------------+ var_sampr1r2rrr%r.rrcCr5)a Aggregate function: alias for var_samp .. versionadded:: 1.6.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str target column to compute on. Returns ------- :class:`~pyspark.sql.Column` variance of given column. Examples -------- >>> df = spark.range(6) >>> df.select(variance(df.id)).show() +------------+ |var_samp(id)| +------------+ | 3.5| +------------+ )rr2rrr%varianceOrrcCstd|dS)aT Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday). .. versionadded:: 3.5.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str target date/timestamp column to work on. Returns ------- :class:`~pyspark.sql.Column` the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday). Examples -------- >>> df = spark.createDataFrame([('2015-04-08',)], ['dt']) >>> df.select(weekday('dt').alias('day')).show() +---+ |day| +---+ | 2| +---+ isodowrr1r2rrr%weekdayosrcCst|tdS)a Returns zero if `col` is null, or `col` otherwise. .. versionadded:: 4.0.0 Parameters ---------- col : :class:`~pyspark.sql.Column` or str Examples -------- >>> df = spark.createDataFrame([(None,), (1,)], ["a"]) >>> df.select(zeroifnull(df.a).alias("result")).show() +------+ |result| +------+ | 0| | 1| +------+ r)r0rLr2rrr% zeroifnullsrspark_datatypecCs"|durtdtt||S)Nzformat is not yet supported as DuckDB and PySpark use a different way of specifying them. As a workaround, you can use F.call_function('strptime', col, format))rrr!r)r.rrrrr%_to_date_or_timestamps rcCt|t|S)aConverts a :class:`~pyspark.sql.Column` into :class:`pyspark.sql.types.DateType` using the optionally specified format. Specify formats according to `datetime pattern`_. By default, it follows casting rules to :class:`pyspark.sql.types.DateType` if the format is omitted. Equivalent to ``col.cast("date")``. .. _datetime pattern: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html .. versionadded:: 2.2.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str input column of values to convert. format: str, optional format to use to convert date values. Returns ------- :class:`~pyspark.sql.Column` date value as :class:`pyspark.sql.types.DateType` type. Examples -------- >>> df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['t']) >>> df.select(to_date(df.t).alias('date')).collect() [Row(date=datetime.date(1997, 2, 28))] >>> df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['t']) >>> df.select(to_date(df.t, 'yyyy-MM-dd HH:mm:ss').alias('date')).collect() [Row(date=datetime.date(1997, 2, 28))] )r_typesDateTyper.rrrr%to_date#rcCr)aConverts a :class:`~pyspark.sql.Column` into :class:`pyspark.sql.types.TimestampType` using the optionally specified format. Specify formats according to `datetime pattern`_. By default, it follows casting rules to :class:`pyspark.sql.types.TimestampType` if the format is omitted. Equivalent to ``col.cast("timestamp")``. .. _datetime pattern: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html .. versionadded:: 2.2.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str column values to convert. format: str, optional format to use to convert timestamp values. Returns ------- :class:`~pyspark.sql.Column` timestamp value as :class:`pyspark.sql.types.TimestampType` type. Examples -------- >>> df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['t']) >>> df.select(to_timestamp(df.t).alias('dt')).collect() [Row(dt=datetime.datetime(1997, 2, 28, 10, 30))] >>> df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['t']) >>> df.select(to_timestamp(df.t, 'yyyy-MM-dd HH:mm:ss').alias('dt')).collect() [Row(dt=datetime.datetime(1997, 2, 28, 10, 30))] rrTimestampNTZTyperrrr% to_timestamprrcCr)aL Parses the `timestamp` with the `format` to a timestamp without time zone. Returns null with invalid input. .. versionadded:: 3.5.0 Parameters ---------- timestamp : :class:`~pyspark.sql.Column` or str Input column or strings. format : :class:`~pyspark.sql.Column` or str, optional format to use to convert type `TimestampType` timestamp values. Examples -------- >>> df = spark.createDataFrame([("2016-12-31",)], ["e"]) >>> df.select(to_timestamp_ltz(df.e, lit("yyyy-MM-dd")).alias('r')).collect() ... # doctest: +SKIP [Row(r=datetime.datetime(2016, 12, 31, 0, 0))] >>> df = spark.createDataFrame([("2016-12-31",)], ["e"]) >>> df.select(to_timestamp_ltz(df.e).alias('r')).collect() ... # doctest: +SKIP [Row(r=datetime.datetime(2016, 12, 31, 0, 0))] rrBrrrr%to_timestamp_ltzrrcCr)aK Parses the `timestamp` with the `format` to a timestamp without time zone. Returns null with invalid input. .. versionadded:: 3.5.0 Parameters ---------- timestamp : :class:`~pyspark.sql.Column` or str Input column or strings. format : :class:`~pyspark.sql.Column` or str, optional format to use to convert type `TimestampNTZType` timestamp values. Examples -------- >>> df = spark.createDataFrame([("2016-04-08",)], ["e"]) >>> df.select(to_timestamp_ntz(df.e, lit("yyyy-MM-dd")).alias('r')).collect() ... # doctest: +SKIP [Row(r=datetime.datetime(2016, 4, 8, 0, 0))] >>> df = spark.createDataFrame([("2016-04-08",)], ["e"]) >>> df.select(to_timestamp_ntz(df.e).alias('r')).collect() ... # doctest: +SKIP [Row(r=datetime.datetime(2016, 4, 8, 0, 0))] rrrrr%to_timestamp_ntzrrcCs"|dur td|||Std||S)a Returns the substring of `str` that starts at `pos` and is of length `len`, or the slice of byte array that starts at `pos` and is of length `len`. .. versionadded:: 3.5.0 Parameters ---------- src : :class:`~pyspark.sql.Column` or str A column of string. pos : :class:`~pyspark.sql.Column` or str A column of string, the substring of `str` that starts at `pos`. len : :class:`~pyspark.sql.Column` or str, optional A column of string, the substring of `str` is of length `len`. Examples -------- >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("Spark SQL", 5, 1,)], ["a", "b", "c"] ... ).select(sf.substr("a", "b", "c")).show() +---------------+ |substr(a, b, c)| +---------------+ | k| +---------------+ >>> import pyspark.sql.functions as sf >>> spark.createDataFrame( ... [("Spark SQL", 5, 1,)], ["a", "b", "c"] ... ).select(sf.substr("a", "b")).show() +------------------------+ |substr(a, b, 2147483647)| +------------------------+ | k SQL| +------------------------+ N substringr1r4rrHrrr%substr8s( rrcCstdt|tdd|S)N date_diffz1970-01-01 00:00:00+00:00rB)r*rLr)r.rrrr% _unix_difffsrcC t|dS)ayReturns the number of days since 1970-01-01. .. versionadded:: 3.5.0 Examples -------- >>> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles") >>> df = spark.createDataFrame([('1970-01-02',)], ['t']) >>> df.select(unix_date(to_date(df.t)).alias('n')).collect() [Row(n=1)] >>> spark.conf.unset("spark.sql.session.timeZone") daysrr2rrr% unix_datei rcCr)aReturns the number of microseconds since 1970-01-01 00:00:00 UTC. .. versionadded:: 3.5.0 Examples -------- >>> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles") >>> df = spark.createDataFrame([('2015-07-22 10:00:00',)], ['t']) >>> df.select(unix_micros(to_timestamp(df.t)).alias('n')).collect() [Row(n=1437584400000000)] >>> spark.conf.unset("spark.sql.session.timeZone") microsecondsrr2rrr% unix_microsyrrcCr)aReturns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision. .. versionadded:: 3.5.0 Examples -------- >>> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles") >>> df = spark.createDataFrame([('2015-07-22 10:00:00',)], ['t']) >>> df.select(unix_millis(to_timestamp(df.t)).alias('n')).collect() [Row(n=1437584400000)] >>> spark.conf.unset("spark.sql.session.timeZone") millisecondsrr2rrr% unix_millis rcCr)aReturns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision. .. versionadded:: 3.5.0 Examples -------- >>> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles") >>> df = spark.createDataFrame([('2015-07-22 10:00:00',)], ['t']) >>> df.select(unix_seconds(to_timestamp(df.t)).alias('n')).collect() [Row(n=1437584400)] >>> spark.conf.unset("spark.sql.session.timeZone") secondsrr2rrr% unix_secondsrra1a2cCstt|}t|}t|}t|}tttd||tdttd|dktd|dk@||B@tdtdS)a Collection function: returns true if the arrays contain any common non-null element; if not, returns null if both the arrays are non-empty and any of them contains a null element; returns false otherwise. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Returns ------- :class:`~pyspark.sql.Column` a column of Boolean type. Examples -------- >>> df = spark.createDataFrame([(["a", "b"], ["b", "c"]), (["a"], ["b", "c"])], ['x', 'y']) >>> df.select(arrays_overlap(df.x, df.y).alias("overlap")).collect() [Row(overlap=True), Row(overlap=False)] list_has_anyTrHrNF)r!rrr rr rv)rr a1_has_null a2_has_nullrrr%arrays_overlaps( rcc Cs"tdtd|tdtddS)N list_containsrzrCT)rrr r)rrrr%rsrcGtdg|RS)a9 Collection function: Returns a merged array of structs in which the N-th struct contains all N-th values of input arrays. If one of the arrays is shorter than others then resulting struct type value will be a `null` for missing elements. .. versionadded:: 2.4.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- cols : :class:`~pyspark.sql.Column` or str columns of arrays to be merged. Returns ------- :class:`~pyspark.sql.Column` merged array of entries. Examples -------- >>> from pyspark.sql.functions import arrays_zip >>> df = spark.createDataFrame([([1, 2, 3], [2, 4, 6], [3, 6])], ['vals1', 'vals2', 'vals3']) >>> df = df.select(arrays_zip(df.vals1, df.vals2, df.vals3).alias('zipped')) >>> df.show(truncate=False) +------------------------------------+ |zipped | +------------------------------------+ |[{1, 2, 3}, {2, 4, 6}, {3, 6, NULL}]| +------------------------------------+ >>> df.printSchema() root |-- zipped: array (nullable = true) | |-- element: struct (containsNull = false) | | |-- vals1: long (nullable = true) | | |-- vals2: long (nullable = true) | | |-- vals3: long (nullable = true) list_zipr1rErrr% arrays_zips(rcCr)a1 Substring starts at `pos` and is of length `len` when str is String type or returns the slice of byte array that starts at `pos` in byte and is of length `len` when str is Binary type. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The position is not zero based, but 1 based index. Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. pos : int starting position in str. len : int length of chars. Returns ------- :class:`~pyspark.sql.Column` substring of given value. Examples -------- >>> df = spark.createDataFrame([('abcd',)], ['s',]) >>> df.select(substring(df.s, 1, 2).alias('s')).collect() [Row(s='ab')] rrZrrrr%rs rcCr)a Returns a boolean. The value is True if right is found inside left. Returns NULL if either input expression is NULL. Otherwise, returns False. Both left or right must be of STRING or BINARY type. .. versionadded:: 3.5.0 Parameters ---------- left : :class:`~pyspark.sql.Column` or str The input column or strings to check, may be NULL. right : :class:`~pyspark.sql.Column` or str The input column or strings to find, may be NULL. Examples -------- >>> df = spark.createDataFrame([("Spark SQL", "Spark")], ['a', 'b']) >>> df.select(contains(df.a, df.b).alias('r')).collect() [Row(r=True)] >>> df = spark.createDataFrame([("414243", "4243",)], ["c", "d"]) >>> df = df.select(to_binary("c").alias("c"), to_binary("d").alias("d")) >>> df.printSchema() root |-- c: binary (nullable = true) |-- d: binary (nullable = true) >>> df.select(contains("c", "d"), contains("d", "c")).show() +--------------+--------------+ |contains(c, d)|contains(d, c)| +--------------+--------------+ | true| false| +--------------+--------------+ rr1)rxrzrrr%r+s rcCr)a Collection function: returns a reversed string or an array with reverse order of elements. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- col : :class:`~pyspark.sql.Column` or str name of column or expression Returns ------- :class:`~pyspark.sql.Column` array of elements in reverse order. Examples -------- >>> df = spark.createDataFrame([('Spark SQL',)], ['data']) >>> df.select(reverse(df.data).alias('s')).collect() [Row(s='LQS krapS')] >>> df = spark.createDataFrame([([2, 1, 3],) ,([1],) ,([],)], ['data']) >>> df.select(reverse(df.data).alias('r')).collect() [Row(r=[3, 1, 2]), Row(r=[1]), Row(r=[])] reverserr2rrr%rLsrcGr)ae Concatenates multiple input columns together into a single column. The function works with strings, numeric, binary and compatible array columns. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- cols : :class:`~pyspark.sql.Column` or str target column or columns to work on. Returns ------- :class:`~pyspark.sql.Column` concatenated values. Type of the `Column` depends on input columns' type. See Also -------- :meth:`pyspark.sql.functions.array_join` : to concatenate string columns with delimiter Examples -------- >>> df = spark.createDataFrame([('abcd','123')], ['s', 'd']) >>> df = df.select(concat(df.s, df.d).alias('s')) >>> df.collect() [Row(s='abcd123')] >>> df DataFrame[s: string] >>> df = spark.createDataFrame([([1, 2], [3, 4], [5]), ([1, 2], None, [3])], ['a', 'b', 'c']) >>> df = df.select(concat(df.a, df.b, df.c).alias("arr")) >>> df.collect() [Row(arr=[1, 2, 3, 4, 5]), Row(arr=None)] >>> df DataFrame[arr: array] r|r1rErrr%r|es!r|cCr)a& Locate the position of the first occurrence of substr column in the given string. Returns null if either of the arguments are null. .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Notes ----- The position is not zero based, but 1 based index. Returns 0 if substr could not be found in str. Parameters ---------- str : :class:`~pyspark.sql.Column` or str target column to work on. substr : str substring to look for. Returns ------- :class:`~pyspark.sql.Column` location of the first occurrence of the substring as integer. Examples -------- >>> df = spark.createDataFrame([('abcd',)], ['s',]) >>> df.select(instr(df.s, 'b').alias('s')).collect() [Row(s=2)] instrrZ)r4rrrr%rs!rcCr,)aParses the expression string into the column that it represents .. versionadded:: 1.5.0 .. versionchanged:: 3.4.0 Supports Spark Connect. Parameters ---------- str : str expression defined in string. Returns ------- :class:`~pyspark.sql.Column` column representing the expression. Examples -------- >>> df = spark.createDataFrame([["Alice"], ["Bob"]], ["name"]) >>> df.select("name", expr("length(name)")).show() +-----+------------+ | name|length(name)| +-----+------------+ |Alice| 5| | Bob| 3| +-----+------------+ )rrr6rrr%r$rr$dfrcCs|S)aq The broadcast function in Spark is used to optimize joins by broadcasting a smaller dataset to all the worker nodes. However, DuckDB operates on a single-node architecture . As a result, the function simply returns the input DataFrame without applying any modifications or optimizations, since broadcasting is not applicable in the DuckDB context. r)rrrr% broadcastsrr-)F)r)T)ry)rrrr)rtypingrrrrrrrr duckdbr r r r rrrr dataframererrorsr exceptionr_typingrr+rrrsrrr4r*r.r0r7r>r@rFrKrLr)r!rXr_rarerirlrorqrrrxrzr|rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr r rrrrrrboolrr r"r$r&r'r(r)r+r/r\r0r3r5r6rr7r=r?r@rArMrPrSrTrErVrHrWrJrYrZrLr[r\r]r_r`rbrcrdrerfrgrhrirjrkrmrnrqrurxr}r~rrrrrrrrrrrrrrrrrrrrrrrDataTyperrrrrrrrrrrrrrrrrr|rr$rrrrr%sv((      " &    (4&&%&& ,  "$ * )!#  g     !&&  "$"$  9 "     0   &#(4' '4 *##%(78"!"""%%. .$3!$#$!C   * $  $J2$$$&*$" 1$*$0!!!! *" "&("!.& +%!$#