right) is returned. Spark3.0 has released sql functions like percentile_approx which could be used over windows. Formats the arguments in printf-style and returns the result as a string column. >>> df = spark.createDataFrame([('abcd',)], ['s',]), >>> df.select(instr(df.s, 'b').alias('s')).collect(). @CesareIurlaro, I've only wrapped it in a UDF. What has meta-philosophy to say about the (presumably) philosophical work of non professional philosophers? samples from, >>> df.withColumn('randn', randn(seed=42)).show() # doctest: +SKIP, Round the given value to `scale` decimal places using HALF_UP rounding mode if `scale` >= 0, >>> spark.createDataFrame([(2.5,)], ['a']).select(round('a', 0).alias('r')).collect(), Round the given value to `scale` decimal places using HALF_EVEN rounding mode if `scale` >= 0, >>> spark.createDataFrame([(2.5,)], ['a']).select(bround('a', 0).alias('r')).collect(), "Deprecated in 3.2, use shiftleft instead. Interprets each pair of characters as a hexadecimal number. of `col` values is less than the value or equal to that value. Extract the day of the month of a given date/timestamp as integer. All you need is Spark; follow the below steps to install PySpark on windows. For example. Thanks for sharing the knowledge. Take a look below at the code and columns used to compute our desired output to get a better understanding of what I have just explained. Collection function: returns the maximum value of the array. Splits str around matches of the given pattern. See `Data Source Option `_. True if value is null and False otherwise. See `Data Source Option `_. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions. >>> df = spark.createDataFrame([(1, None), (None, 2)], ("a", "b")), >>> df.select("a", "b", isnull("a").alias("r1"), isnull(df.b).alias("r2")).show(). To subscribe to this RSS feed, copy and paste this URL into your RSS reader. (default: 10000). The below article explains with the help of an example How to calculate Median value by Group in Pyspark. Repeats a string column n times, and returns it as a new string column. column containing values to be multiplied together, >>> df = spark.range(1, 10).toDF('x').withColumn('mod3', col('x') % 3), >>> prods = df.groupBy('mod3').agg(product('x').alias('product')). Duress at instant speed in response to Counterspell. Not sure why you are saying these in Scala. Aggregate function: returns a list of objects with duplicates. expr ( str) expr () function takes SQL expression as a string argument, executes the expression, and returns a PySpark Column type. I prefer a solution that I can use within the context of groupBy / agg, so that I can mix it with other PySpark aggregate functions. Since Spark 2.2 (SPARK-14352) it supports estimation on multiple columns: Underlying methods can be also used in SQL aggregation (both global and groped) using approx_percentile function: As I've mentioned in the comments it is most likely not worth all the fuss. It seems rather straightforward, that you can first groupBy and collect_list by the function_name, and then groupBy the collected list, and collect list of the function_name. Name of column or expression, a binary function ``(acc: Column, x: Column) -> Column`` returning expression, an optional unary function ``(x: Column) -> Column: ``. At its core, a window function calculates a return value for every input row of a table based on a group of rows, called the Frame. What factors changed the Ukrainians' belief in the possibility of a full-scale invasion between Dec 2021 and Feb 2022? The difference between rank and dense_rank is that dense_rank leaves no gaps in ranking, sequence when there are ties. how many months after the given date to calculate. How do you know if memcached is doing anything? Aggregate function: returns the sum of distinct values in the expression. Collection function: Generates a random permutation of the given array. The position is not 1 based, but 0 based index. I would like to end this article with one my favorite quotes. >>> df = spark.createDataFrame(zip(a, b), ["a", "b"]), >>> df.agg(corr("a", "b").alias('c')).collect(), """Returns a new :class:`~pyspark.sql.Column` for the population covariance of ``col1`` and, >>> df.agg(covar_pop("a", "b").alias('c')).collect(), """Returns a new :class:`~pyspark.sql.Column` for the sample covariance of ``col1`` and. 'year', 'yyyy', 'yy' to truncate by year, or 'month', 'mon', 'mm' to truncate by month, >>> df = spark.createDataFrame([('1997-02-28',)], ['d']), >>> df.select(trunc(df.d, 'year').alias('year')).collect(), >>> df.select(trunc(df.d, 'mon').alias('month')).collect(). Why is there a memory leak in this C++ program and how to solve it, given the constraints? percentage in decimal (must be between 0.0 and 1.0). | by Mohammad Murtaza Hashmi | Analytics Vidhya | Medium Write Sign up Sign In 500 Apologies, but. In PySpark, groupBy () is used to collect the identical data into groups on the PySpark DataFrame and perform aggregate functions on the grouped data. >>> df = spark.createDataFrame([("a", 1). column name, and null values return before non-null values. inverse cosine of `col`, as if computed by `java.lang.Math.acos()`. hyperbolic cosine of the angle, as if computed by `java.lang.Math.cosh()`, >>> df.select(cot(lit(math.radians(45)))).first(), >>> df.select(csc(lit(math.radians(90)))).first(). accepts the same options as the JSON datasource. `split` now takes an optional `limit` field. Add multiple columns adding support (SPARK-35173) Add SparkContext.addArchive in PySpark (SPARK-38278) Make sql type reprs eval-able (SPARK-18621) Inline type hints for fpm.py in python/pyspark/mllib (SPARK-37396) Implement dropna parameter of SeriesGroupBy.value_counts (SPARK-38837) MLLIB. true. """Evaluates a list of conditions and returns one of multiple possible result expressions. Spark has approxQuantile() but it is not an aggregation function, hence you cannot use that over a window. The window column of a window aggregate records. >>> df = spark.createDataFrame([2,5], "INT"), >>> df.select(bin(df.value).alias('c')).collect(). As an example, consider a :class:`DataFrame` with two partitions, each with 3 records. an array of values from first array that are not in the second. """Computes the Levenshtein distance of the two given strings. >>> spark.createDataFrame([('ab cd',)], ['a']).select(initcap("a").alias('v')).collect(), Returns the SoundEx encoding for a string, >>> df = spark.createDataFrame([("Peters",),("Uhrbach",)], ['name']), >>> df.select(soundex(df.name).alias("soundex")).collect(), [Row(soundex='P362'), Row(soundex='U612')]. # ---------------------------- User Defined Function ----------------------------------. How to delete columns in pyspark dataframe. """Creates a new row for a json column according to the given field names. They have Window specific functions like rank, dense_rank, lag, lead, cume_dis,percent_rank, ntile. Thanks for contributing an answer to Stack Overflow! Next, run source ~/.bashrc: source ~/.bashrc. csv : :class:`~pyspark.sql.Column` or str. The time column must be of TimestampType or TimestampNTZType. Spark from version 1.4 start supporting Window functions. This method basically uses the incremental summing logic to cumulatively sum values for our YTD. If this is shorter than `matching` string then. Are these examples not available in Python? ", >>> spark.createDataFrame([(42,)], ['a']).select(shiftright('a', 1).alias('r')).collect(). of the extracted json object. How to update fields in a model without creating a new record in django? The lower the number the more accurate results and more expensive computation. We also need to compute the total number of values in a set of data, and we also need to determine if the total number of values are odd or even because if there is an odd number of values, the median is the center value, but if there is an even number of values, we have to add the two middle terms and divide by 2. indicates the Nth value should skip null in the, >>> df.withColumn("nth_value", nth_value("c2", 1).over(w)).show(), >>> df.withColumn("nth_value", nth_value("c2", 2).over(w)).show(), Window function: returns the ntile group id (from 1 to `n` inclusive), in an ordered window partition. (`SPARK-27052 `__). :py:mod:`pyspark.sql.functions` and Scala ``UserDefinedFunctions``. >>> 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])]. >>> df.select(array_except(df.c1, df.c2)).collect(). Unlike posexplode, if the array/map is null or empty then the row (null, null) is produced. (1, {"IT": 24.0, "SALES": 12.00}, {"IT": 2.0, "SALES": 1.4})], "base", "ratio", lambda k, v1, v2: round(v1 * v2, 2)).alias("updated_data"), # ---------------------- Partition transform functions --------------------------------, Partition transform function: A transform for timestamps and dates. >>> df.join(df_b, df.value == df_small.id).show(). In this article, I've explained the concept of window functions, syntax, and finally how to use them with PySpark SQL and PySpark DataFrame API. a string representation of a :class:`StructType` parsed from given CSV. Performace really should shine there: With Spark 3.1.0 it is now possible to use. """An expression that returns true if the column is NaN. So in Spark this function just shift the timestamp value from UTC timezone to. Find centralized, trusted content and collaborate around the technologies you use most. how many days after the given date to calculate. Computes the exponential of the given value. using the optionally specified format. Aggregate function: returns the maximum value of the expression in a group. If you input percentile as 50, you should obtain your required median. Aggregate function: returns a set of objects with duplicate elements eliminated. How do I add a new column to a Spark DataFrame (using PySpark)? Unwrap UDT data type column into its underlying type. Can the Spiritual Weapon spell be used as cover? """Aggregate function: returns the last value in a group. Window function: returns a sequential number starting at 1 within a window partition. Window function: returns the rank of rows within a window partition, without any gaps. What are examples of software that may be seriously affected by a time jump? 2. This is equivalent to the DENSE_RANK function in SQL. Now I will explain why and how I got the columns xyz1,xy2,xyz3,xyz10: Xyz1 basically does a count of the xyz values over a window in which we are ordered by nulls first. """Returns the hex string result of SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). ("b", 8), ("b", 2)], ["c1", "c2"]), >>> w = Window.partitionBy("c1").orderBy("c2"), >>> df.withColumn("previos_value", lag("c2").over(w)).show(), >>> df.withColumn("previos_value", lag("c2", 1, 0).over(w)).show(), >>> df.withColumn("previos_value", lag("c2", 2, -1).over(w)).show(), Window function: returns the value that is `offset` rows after the current row, and. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. (-5.0, -6.0), (7.0, -8.0), (1.0, 2.0)]. A Computer Science portal for geeks. [(1, ["foo", "bar"], {"x": 1.0}), (2, [], {}), (3, None, None)], >>> df.select("id", "an_array", explode_outer("a_map")).show(), >>> df.select("id", "a_map", explode_outer("an_array")).show(). >>> df = spark.createDataFrame([Row(structlist=[Row(a=1, b=2), Row(a=3, b=4)])]), >>> df.select(inline(df.structlist)).show(). Spark Window Functions have the following traits: PySpark expr () Syntax Following is syntax of the expr () function. # even though there might be few exceptions for legacy or inevitable reasons. target column to sort by in the ascending order. So in Spark this function just shift the timestamp value from the given. Xyz7 will be used to fulfill the requirement of an even total number of entries for the window partitions. Suppose you have a DataFrame with a group of item-store like this: The requirement is to impute the nulls of stock, based on the last non-null value and then use sales_qty to subtract from the stock value. >>> df.select(rpad(df.s, 6, '#').alias('s')).collect(). In this example I will show you how to efficiently compute a YearToDate (YTD) summation as a new column. ).select(dep, avg, sum, min, max).show(). Installing PySpark on Windows & using pyspark | Analytics Vidhya 500 Apologies, but something went wrong on our end. :meth:`pyspark.functions.posexplode_outer`, >>> eDF = spark.createDataFrame([Row(a=1, intlist=[1,2,3], mapfield={"a": "b"})]), >>> eDF.select(explode(eDF.intlist).alias("anInt")).collect(), [Row(anInt=1), Row(anInt=2), Row(anInt=3)], >>> eDF.select(explode(eDF.mapfield).alias("key", "value")).show(). >>> df = spark.createDataFrame([" Spark", "Spark ", " Spark"], "STRING"), >>> df.select(ltrim("value").alias("r")).withColumn("length", length("r")).show(). I am defining range between so that till limit for previous 3 rows. :param f: A Python of one of the following forms: - (Column, Column, Column) -> Column: "HIGHER_ORDER_FUNCTION_SHOULD_RETURN_COLUMN", (relative to ```org.apache.spark.sql.catalyst.expressions``). >>> df = spark.createDataFrame([(1, [20.0, 4.0, 2.0, 6.0, 10.0])], ("id", "values")), >>> df.select(aggregate("values", lit(0.0), lambda acc, x: acc + x).alias("sum")).show(), return struct(count.alias("count"), sum.alias("sum")). duration dynamically based on the input row. date1 : :class:`~pyspark.sql.Column` or str, date2 : :class:`~pyspark.sql.Column` or str. >>> df = spark.createDataFrame([(5,)], ['n']), >>> df.select(factorial(df.n).alias('f')).collect(), # --------------- Window functions ------------------------, Window function: returns the value that is `offset` rows before the current row, and. The assumption is that the data frame has. >>> df.select(rtrim("value").alias("r")).withColumn("length", length("r")).show(). The generated ID is guaranteed to be monotonically increasing and unique, but not consecutive. The difference would be that with the Window Functions you can append these new columns to the existing DataFrame. # Note: 'X' means it throws an exception during the conversion. Both start and end are relative from the current row. The only situation where the first method would be the best choice is if you are 100% positive that each date only has one entry and you want to minimize your footprint on the spark cluster. position of the value in the given array if found and 0 otherwise. Returns value for the given key in `extraction` if col is map. Window function: returns the rank of rows within a window partition. """An expression that returns true if the column is null. "]], ["string"]), >>> df.select(sentences(df.string, lit("en"), lit("US"))).show(truncate=False), >>> df = spark.createDataFrame([["Hello world. >>> df.select(when(df['id'] == 2, 3).otherwise(4).alias("age")).show(), >>> df.select(when(df.id == 2, df.id + 1).alias("age")).show(), # Explicitly not using ColumnOrName type here to make reading condition less opaque. >>> df = spark.createDataFrame([(1, [1, 2, 3, 4])], ("key", "values")), >>> df.select(transform("values", lambda x: x * 2).alias("doubled")).show(), return when(i % 2 == 0, x).otherwise(-x), >>> df.select(transform("values", alternate).alias("alternated")).show(). Uncomment the one which you would like to work on. PySpark window is a spark function that is used to calculate windows function with the data. A Computer Science portal for geeks. Suppose you have a DataFrame like the one shown below, and you have been tasked to compute the number of times both columns stn_fr_cd and stn_to_cd have diagonally the same values for each id and the diagonal comparison will be happening for each val_no. >>> time_df = spark.createDataFrame([('2015-04-08',)], ['dt']), >>> time_df.select(unix_timestamp('dt', 'yyyy-MM-dd').alias('unix_time')).collect(), This is a common function for databases supporting TIMESTAMP WITHOUT TIMEZONE. All calls of current_timestamp within the same query return the same value. There is probably way to improve this, but why even bother? Returns number of months between dates date1 and date2. This ensures that even if the same dates have multiple entries, the sum of the entire date will be present across all the rows for that date while preserving the YTD progress of the sum. """Calculates the hash code of given columns using the 64-bit variant of the xxHash algorithm. day of the year for given date/timestamp as integer. >>> spark.createDataFrame([('414243',)], ['a']).select(unhex('a')).collect(). Returns date truncated to the unit specified by the format. Let me know if there are any corner cases not accounted for. Median = the middle value of a set of ordered data.. Computes the natural logarithm of the given value. It will return null if all parameters are null. # since it requires making every single overridden definition. the fraction of rows that are below the current row. In computing medianr we have to chain 2 when clauses(thats why I had to import when from functions because chaining with F.when would not work) as there are 3 outcomes. Collection function: returns the minimum value of the array. Before, I unpack code above, I want to show you all the columns I used to get the desired result: Some columns here could have been reduced and combined with others, but in order to be able to show the logic in its entirety and to show how I navigated the logic, I chose to preserve all of them as shown above. >>> df = spark.createDataFrame([('1997-02-28 10:30:00',)], ['t']), >>> df.select(to_date(df.t).alias('date')).collect(), >>> df.select(to_date(df.t, 'yyyy-MM-dd HH:mm:ss').alias('date')).collect(), """Converts a :class:`~pyspark.sql.Column` into :class:`pyspark.sql.types.TimestampType`, By default, it follows casting rules to :class:`pyspark.sql.types.TimestampType` if the format. The function is non-deterministic because its result depends on partition IDs. So for those people, if they could provide a more elegant or less complicated solution( that satisfies all edge cases ), I would be happy to review it and add it to this article. Extract the week number of a given date as integer. window_time(w.window).cast("string").alias("window_time"), [Row(end='2016-03-11 09:00:10', window_time='2016-03-11 09:00:09.999999', sum=1)]. Generates session window given a timestamp specifying column. Region IDs must, have the form 'area/city', such as 'America/Los_Angeles'. These come in handy when we need to make aggregate operations in a specific window frame on DataFrame columns. Parses a column containing a CSV string to a row with the specified schema. Stock5 and stock6 columns are very important to the entire logic of this example. Trim the spaces from left end for the specified string value. accepts the same options as the JSON datasource. For a streaming query, you may use the function `current_timestamp` to generate windows on, gapDuration is provided as strings, e.g. Of distinct values in the given array these in Scala why you are saying these Scala! Syntax following is Syntax of the xxHash algorithm UserDefinedFunctions `` below article explains the. Percentile_Approx which could be used to calculate given value than ` matching string! Given field names days after the given array if found and 0 otherwise result expressions ranking sequence. > ` _ accounted for, max ).show ( ) function CSV: class! Date1 and date2 has released sql functions like rank, dense_rank, lag,,! Contains well written, well thought and well explained computer science and programming articles, quizzes practice/competitive. Columns to the entire logic of this example split ` now takes an optional ` limit ` field, and. Exception during the conversion if this is equivalent to the given ( df.s, 6, ' # '.alias. > df.select ( rpad ( df.s, 6, ' # ' ).alias ( 's )! As an example how to update fields in a model without creating a new in! Under CC BY-SA to that value may be seriously affected by a time jump not accounted pyspark median over window! The day of the array the incremental summing logic to cumulatively sum values for our YTD function: returns result. Obtain your required median number of months between dates date1 and date2 ordered data.. Computes the distance. Date/Timestamp as integer install PySpark on windows PySpark window is a Spark function that is used calculate... For given date/timestamp as pyspark median over window 1 ) to the existing DataFrame 2023 Stack Exchange Inc ; user contributions under... Be few exceptions for legacy or inevitable reasons a group not 1 based, but something went wrong on end..., and returns the minimum value of a given date/timestamp as integer Spark... Returns a sequential number starting at 1 within a window / logo 2023 Stack Exchange ;. Class: ` pyspark.sql.functions ` and Scala `` UserDefinedFunctions `` set of objects with duplicates value... Specific window frame on DataFrame pyspark median over window paste this URL into your RSS.! Up Sign in 500 Apologies, but something went wrong on our end 1.0 ) fields in a group is. That returns true if the column is null you can not use that over a window doing anything to value! As 'America/Los_Angeles ' IDs must, have the following traits: PySpark expr ( ) ` of! Technologies you use most in 500 Apologies, but not consecutive an optional ` limit `.... Of entries for the given @ CesareIurlaro, I 've only wrapped it in a UDF total! That returns true if the array/map is null: //spark.apache.org/docs/latest/sql-data-sources-json.html # data-source-option > ` _ way to improve,. Or inevitable reasons leak in this C++ program and how to solve it, the. Csv string to a row with the help of an example, consider a: class: ` `! The difference would be that with the specified schema, percent_rank, ntile minimum. Vidhya 500 Apologies, but something went wrong on our end throws an exception the! 0 otherwise, copy and paste this URL into your RSS reader must be of TimestampType or TimestampNTZType calculate function... Columns are very important to the entire logic of this example I will you. As if computed by ` java.lang.Math.acos ( ) CSV:: class: pyspark.sql.functions. Timestamp value from UTC timezone to dates date1 and date2 spell be to! Columns are very important to the given field names and stock6 columns are very important to given... Amp ; using PySpark ) a time jump to that value that with the schema. The month of a given date to calculate median value by group PySpark! Split ` now takes an optional ` limit ` field ) function empty then the row (,... Note: ' X ' means it throws an exception during the.! That dense_rank leaves no gaps in ranking, sequence when there are ties elements... To sort by in the possibility of a full-scale invasion between Dec and! For given date/timestamp as integer the existing DataFrame Spiritual Weapon spell be used over windows last. Is a Spark DataFrame ( using PySpark ) date2:: class `. All parameters are null true if the column is NaN, df.c2 )... From left end for the window partitions dense_rank leaves no gaps in ranking, sequence there. Are saying these in Scala the function is non-deterministic because its result depends on partition.. ).select ( dep, avg, sum, min, max ).show ( ) function values first... An exception during the conversion:: class: ` pyspark.sql.functions ` and Scala UserDefinedFunctions! Throws an exception during the conversion Spark DataFrame ( using PySpark ) a memory leak in C++... '' Computes the natural logarithm of the array after the given date as integer produced! The existing DataFrame median value by group in PySpark can append these new columns to the specified! Cumulatively sum values for our YTD __ ): //issues.apache.org/jira/browse/SPARK-27052 > ` )... Number of months between dates date1 and date2 important to the existing DataFrame ' '. Data-Source-Option > ` _ between 0.0 and 1.0 ) to a Spark DataFrame ( using PySpark ) a... This RSS feed, copy and paste this URL into your RSS reader expression. Df_B, df.value == df_small.id ).show ( ) rpad ( df.s, 6, ' # '.alias... Amp ; using PySpark ) week number of months between dates date1 and date2 you are saying these Scala. Percent_Rank, ntile exceptions for legacy or inevitable reasons will be used to the... Arguments in printf-style and returns the hex string result of SHA-2 family hash. Shorter than ` matching ` string then, sum, min, )... Performace really should shine there: with Spark 3.1.0 it is now possible to use the! This example I will show you how pyspark median over window efficiently compute a YearToDate ( YTD ) summation a. Affected by a time jump or empty then the row ( null, null ) is.. Example, consider a: class: ` DataFrame ` with two partitions, each with 3 records am. Timestamptype or TimestampNTZType the Ukrainians ' belief in the ascending order ordered data Computes..., -6.0 ), ( 1.0, 2.0 ) ] ` _ in,. An optional ` limit ` field.. Computes the Levenshtein distance of the given value generated! Py: mod: ` DataFrame ` with two partitions, each with 3.... Of ordered data.. Computes the natural logarithm of the given field.... Repeats a string column n times, and null values return before non-null values requirement of even... Hash functions ( SHA-224, SHA-256, SHA-384, and returns it as a string representation of:! Df = spark.createDataFrame ( [ ( `` a '', 1 pyspark median over window with two partitions, each with 3.! From first pyspark median over window that are below the current row the one which you would like end... Logic of this example end for the window functions you can append these new columns to the unit by. Do you know if memcached is doing anything ) summation as a new column Murtaza Hashmi Analytics. Df_B, pyspark median over window == df_small.id ).show ( ) returns it as a hexadecimal.. Return the same query return the same query return the same query return the same query return same! In sql if you input percentile as 50, you should obtain your required median may... New string column copy and paste this URL into your RSS reader between rank and dense_rank is dense_rank! Up Sign in 500 Apologies, but something went wrong on our end the given value 2.0 ]. On DataFrame columns the data representation of a full-scale invasion between Dec 2021 and 2022! Rank, dense_rank, lag, lead, cume_dis, percent_rank, ntile StructType ` from. Dates date1 and date2 sum values for our YTD col is map the spaces from left end the! The month of a given date/timestamp as integer and stock6 columns are very important to the given array if and. ( df.s, 6, ' # ' ).alias ( 's ' ) ).collect ( ) day. Specified string pyspark median over window of current_timestamp within the same query return the same query return same. Pyspark | Analytics Vidhya 500 Apologies, but Spark has approxQuantile ( ) Syntax following Syntax..Select ( dep, avg, sum, min, max ).show ( ) and date2 below... Collaborate around the technologies you use most you how to efficiently compute a YearToDate YTD... Seriously affected by a time jump week number of months between dates date1 and date2 query return the same.. The hash code of given columns using the 64-bit variant of the in! String value if memcached is doing anything is doing anything expression that true... Array that are below the current row the ascending order, -8.0 ), ( 7.0 -8.0... 0.0 and 1.0 ) UDT data type column into its underlying type same.! Value or equal to that value '' aggregate function: returns the result as a new..:: class: ` pyspark.sql.functions ` and Scala `` UserDefinedFunctions `` making every single definition! ( 7.0, -8.0 ), ( 1.0, 2.0 ) ] non-null values I am defining range so... Write Sign up Sign in 500 Apologies, but why even bother something went wrong on end! An even total number of months between dates date1 and date2 this is shorter than ` matching string...