<jupyter_start><jupyter_text>Group by and Aggregate * GroupBy allows you to group rows together based off some column value* An aggregate function aggregates multiple rows of data into a single output, such as taking the sum of inputs, or counting the number of inputs.1. Perform the group by operation customer wise/ day wise 2. Use Aggregate function on the results<jupyter_code>from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('aggs').getOrCreate()
df = spark.read.csv('./data/sales_info.csv', inferSchema=True, header= True)
df.show()
df.printSchema()<jupyter_output>root
 |-- Company: string (nullable = true)
 |-- Person: string (nullable = true)
 |-- Sales: double (nullable = true)<jupyter_text>Group by Operation group by company<jupyter_code>df.groupBy("Company")<jupyter_output><empty_output><jupyter_text>.groupBy() method returns a GroupedData object.Multiple functions can be applied to this object  GroupBy + Aggregte Operation 1. Mean<jupyter_code>df.groupBy("Company").mean().show()<jupyter_output>+-------+-----------------+
|Company|       avg(Sales)|
+-------+-----------------+
|   APPL|            370.0|
|   GOOG|            220.0|
|     FB|            610.0|
|   MSFT|322.3333333333333|
+-------+-----------------+<jupyter_text>2. Count<jupyter_code>df.groupBy("Company").count().show()<jupyter_output>+-------+-----+
|Company|count|
+-------+-----+
|   APPL|    4|
|   GOOG|    3|
|     FB|    2|
|   MSFT|    3|
+-------+-----+<jupyter_text>3. Max<jupyter_code>df.groupBy("Company").max().show()<jupyter_output>+-------+----------+
|Company|max(Sales)|
+-------+----------+
|   APPL|     750.0|
|   GOOG|     340.0|
|     FB|     870.0|
|   MSFT|     600.0|
+-------+----------+<jupyter_text>4. Min<jupyter_code>df.groupBy("Company").min().show()<jupyter_output>+-------+----------+
|Company|min(Sales)|
+-------+----------+
|   APPL|     130.0|
|   GOOG|     120.0|
|     FB|     350.0|
|   MSFT|     124.0|
+-------+----------+<jupyter_text>5. Sum<jupyter_code>df.groupBy("Company").sum().show()<jupyter_output>+-------+----------+
|Company|sum(Sales)|
+-------+----------+
|   APPL|    1480.0|
|   GOOG|     660.0|
|     FB|    1220.0|
|   MSFT|     967.0|
+-------+----------+<jupyter_text>Check out this link for more info on other methods: http://spark.apache.org/docs/latest/api/python/pyspark.sql.htmlpyspark-sql-module  Aggregate Operation Not all aggregation function require group by 1. Sum<jupyter_code>df.agg({'Sales':'sum'}).show()<jupyter_output>+----------+
|sum(Sales)|
+----------+
|    4327.0|
+----------+<jupyter_text>2. Max<jupyter_code>df.agg({'Sales':'max'}).show()<jupyter_output>+----------+
|max(Sales)|
+----------+
|     870.0|
+----------+<jupyter_text>Creating new object from dataframe using group by and then performing aggregation function<jupyter_code>grouped_data = df.groupBy("Company")
grouped_data.agg({'Sales':'max'}).show()<jupyter_output>+-------+----------+
|Company|max(Sales)|
+-------+----------+
|   APPL|     750.0|
|   GOOG|     340.0|
|     FB|     870.0|
|   MSFT|     600.0|
+-------+----------+<jupyter_text>FunctionsThere are a variety of functions you can import from pyspark.sql.functions. Check out the documentation for the full list available:http://spark.apache.org/docs/latest/api/python/pyspark.sql.htmlmodule-pyspark.sql.functions<jupyter_code>from pyspark.sql.functions import countDistinct, avg, stddev
df.select(countDistinct('Sales')).show()
df.select(avg('Sales')).show()<jupyter_output>+-----------------+
|       avg(Sales)|
+-----------------+
|360.5833333333333|
+-----------------+<jupyter_text>Often you will want to change the name, use the .alias() method for this:<jupyter_code>df.select(avg('Sales').alias('Average Sales')).show()
df.select(stddev('Sales')).show()<jupyter_output>+------------------+
|stddev_samp(Sales)|
+------------------+
|250.08742410799007|
+------------------+<jupyter_text>Precision for digits - Using format_number<jupyter_code>from pyspark.sql.functions import format_number
sales_std = df.select(stddev('Sales').alias('std'))
sales_std.show()
sales_std.select(format_number('std',2).alias('std')).show()<jupyter_output>+------+
|   std|
+------+
|250.09|
+------+<jupyter_text>Order BySorting with the orderBy method<jupyter_code>df.show()
df.orderBy("Sales")
df.orderBy("Sales").show()
# Descending call off the column itself.

df.orderBy(df["Sales"].desc()).show()<jupyter_output>+-------+-------+-----+
|Company| Person|Sales|
+-------+-------+-----+
|     FB|   Carl|870.0|
|   APPL|   Mike|750.0|
|   MSFT|   Tina|600.0|
|     FB|  Sarah|350.0|
|   APPL|  Chris|350.0|
|   GOOG|  Frank|340.0|
|   APPL|   John|250.0|
|   MSFT|Vanessa|243.0|
|   GOOG|    Sam|200.0|
|   APPL|  Linda|130.0|
|   MSFT|    Amy|124.0|
|   GOOG|Charlie|120.0|
+-------+-------+-----+
{'fraction_non_alphanumeric': 0.2664869348817918, 'fraction_numerical': 0.042098714226462046, 'mean_word_length': 2.6621108580106303, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 44, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '475389', 'license': 'MIT', 'n_tokens_mistral': 1872, 'n_tokens_neox': 1733, 'n_words': 308, 'path': 'Dataframes/3-GroupBy_Aggregate.ipynb', 'repo_name': 'akshat-rastogi/spark-basics', 'size': '4822'}