<jupyter_start><jupyter_text>Grab all records with closing price less than 500<jupyter_code>df.filter('Close < 500').select('Open').show()
df.filter(df['Close'] < 500).select("Open").show()<jupyter_output>+------------------+
|              Open|
+------------------+
|        213.429998|
|        214.599998|
|        214.379993|
|            211.75|
|        210.299994|
|212.79999700000002|
|209.18999499999998|
|        207.870005|
|210.11000299999998|
|210.92999500000002|
|        208.330002|
|        214.910006|
|        212.079994|
|206.78000600000001|
|202.51000200000001|
|205.95000100000001|
|        206.849995|
|        204.930004|
|        201.079996|
|192.36999699999998|
+------------------+
only showing top 20 rows<jupyter_text>Multiple filter conditions Have parantheses for each condition separately<jupyter_code>df.filter((df['Close'] < 500) & (df['Open'] > 200)).select('Open').show()
df.filter(df['Low'] == 197.16).show()
result = df.filter(df['Low'] == 197.16).collect()<jupyter_output><empty_output><jupyter_text>Group by and other advanced operations<jupyter_code>sales_data = spark.read.csv("sales_info.csv", header=True, inferSchema=True)
sales_data.groupBy('Company').count().show()
sales_data.agg({'Sales':'avg'}).show()
sales_data.groupBy('Company').agg({'Sales':'avg'}).show()<jupyter_output>+-------+-----------------+
|Company|       avg(Sales)|
+-------+-----------------+
|   APPL|            370.0|
|   GOOG|            220.0|
|     FB|            610.0|
|   MSFT|322.3333333333333|
+-------+-----------------+<jupyter_text>Functions<jupyter_code>from pyspark.sql.functions import countDistinct, stddev, avg, max, format_number
sales_data.select(max('Sales').alias("Maximum sales")).show()
stddev_sales = sales_data.select(stddev('Sales').alias("stddev_sales"))
stddev_sales.select(format_number('stddev_sales', 2).alias("Formatted")).show()
sales_data.orderBy(sales_data['Sales'].desc()).show()
sales_data.orderBy('Sales').show()<jupyter_output>+-------+-------+-----+
|Company| Person|Sales|
+-------+-------+-----+
|   GOOG|Charlie|120.0|
|   MSFT|    Amy|124.0|
|   APPL|  Linda|130.0|
|   GOOG|    Sam|200.0|
|   MSFT|Vanessa|243.0|
|   APPL|   John|250.0|
|   GOOG|  Frank|340.0|
|     FB|  Sarah|350.0|
|   APPL|  Chris|350.0|
|   MSFT|   Tina|600.0|
|   APPL|   Mike|750.0|
|     FB|   Carl|870.0|
+-------+-------+-----+
{'fraction_non_alphanumeric': 0.2505280946345585, 'fraction_numerical': 0.14533164343050275, 'mean_word_length': 2.8566775244299674, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 9, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '572380', 'license': 'MIT', 'n_tokens_mistral': 1128, 'n_tokens_neox': 880, 'n_words': 114, 'path': 'spark/dataframes/operations.ipynb', 'repo_name': 'avinashingit/hadoop-ecosystem', 'size': '2367'}