<jupyter_start><jupyter_text><jupyter_code>!pip install pyspark

#1

import pandas as pd
import pyspark
import seaborn as sns
from pyspark.sql import SparkSession
from pyspark.sql.functions import mean, stddev, round as _round
from matplotlib import pyplot as plt
import seaborn as sns

spark_session = SparkSession.builder.getOrCreate()

df = spark_session.read.csv("https://drive.google.com/file/d/1eTEbn59Q6ZwVbSIeqtZwYgOeJkta1ipi/view?usp=sharing")
df.show()

df2 = df.filter("_c2 > 4.7")
df2.select(mean("_c4").alias("average price")).show()
df=spark_session.read.option("header","true").csv("bestsellers with categories.csv")
df.show()
df3 = df.select("Reviews")
df3.toPandas()[["Reviews"]].astype('float64').hist(column = "Reviews")
plt.show()
df=spark_session.read.option("header","true").csv("bestsellers with categories.csv")
df.show()
df_matrix = df.select("User Rating", "Reviews", "Price")
df_matrix.show()
df=spark_session.read.option("header","true").csv("bestsellers with categories.csv")
df.show()
df_matrix = df.select(df["User Rating"].astype('float'), df.Reviews.astype('float'), df.Price.astype('float'))
df_matrix.show()
df_matrix.toPandas().corr()
df4 = df_matrix.toPandas()
sns.pairplot(df4)
plt.show()
#2
df = spark_session.read.csv("bestsellers with categories.csv")
df.show()
#Найдите автора с самым высоким рейтингом.
df.createOrReplaceTempView("My_df")
spark_session.sql("Select _c1, max(_c2) From My_df Group By _c1 Order by max(_c2) desc ").show()

#Найдите трех авторов с самым низким рейтингом.
df.createOrReplaceTempView("My_df")
spark_session.sql("Select _c1, min(_c2) From My_df Group By _c1 Order by min(_c2) asc Limit 3").show()

#Определите какая книга жанра "Fiction" имеет самый маленький рейтинг.
df.createOrReplaceTempView("My_df")
spark_session.sql("Select _c0, _c2, _c6 From My_df Where _c6 = 'Fiction' Order by _c2 asc").show()

#Определите какая книга жанра "Non Fiction" имеет самый высокий рейтинг.
df.createOrReplaceTempView("My_df")
spark_session.sql("Select _c0, _c2, _c6 From My_df Where _c6 = 'Non Fiction' Order by _c2 desc").show()

df=spark_session.read.option("header","true").csv("bestsellers with categories.csv")
df.show()

#Определите какой автор получил наибольшее количество рецензий.
df.createOrReplaceTempView("df3")
spark_session.sql("Select Author, Reviews From df3 Order by Reviews desc").show()

#Определите какой автор написал наибольшее количество книг за весь доступный в данных период.
df.createOrReplaceTempView("My_df")
spark_session.sql("Select Author, count(Author) From My_df Group By Author").show()

#3

import matplotlib.pyplot as plt
import pandas as pd
import pyspark
from pyspark.sql import SparkSession
import seaborn as sns

spark_session = SparkSession.builder.getOrCreate()

df=spark_session.read.option("header","true").csv("bestsellers with categories.csv")
df.show()

df_avg = df.groupBy("Year").agg({"Reviews": "avg"})
df_avg.show()

df_avg.toPandas().plot(x="Year", y="avg(Reviews)", kind = "bar")
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14182058047493404, 'fraction_numerical': 0.013522427440633245, 'mean_word_length': 6.81701030927835, 'pattern_counts': {'":': 1, '<': 5, '<?xml version=': 0, '>': 6, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '511292', 'license': 'MIT', 'n_tokens_mistral': 1164, 'n_tokens_neox': 1167, 'n_words': 253, 'path': 'Hw6.ipynb', 'repo_name': 'Iryna-Lytvynchuk/Data_Science', 'size': '3032'}