【大数据】【Spark】书籍推荐统计分析

  

数据集说明

• 在这个借助Goodbook网站收集的数据会集，可以获得有关书籍的信息，如作者、页数、评分和其他信息
  

文件说明

books.csv

1. bookID,title,authors,average_rating,isbn,isbn13,language_code,  num_pages,ratings_count,text_reviews_count,publication_date,publisher
2. 1,Harry Potter and the Half-Blood Prince (Harry Potter  #6),J.K. Rowling/Mary GrandPré,4.57,0439785960,9780439785969,eng,652,2095690,27591,9/16/2006,Scholastic Inc.
3. 2,Harry Potter and the Order of the Phoenix (Harry Potter  #5),J.K. Rowling/Mary GrandPré,4.49,0439358078,9780439358071,eng,870,2153167,29221,9/1/2004,Scholastic Inc.
4. 4,Harry Potter and the Chamber of Secrets (Harry Potter  #2),J.K. Rowling,4.42,0439554896,9780439554893,eng,352,6333,244,11/1/2003,Scholastic
5. ...

复制代码

---

业务需求

（1）统计最受关注的书籍Top 10

（2）统计书籍篇幅Top 10

（3）统计差异出书社出书的书籍数量

（4）统计差异语言的书籍数量

（5）统计最不受关注的高分书籍Top 10（评分4.5以上，评分人数1w以上，批评数200以下）

（6）统计差异年份出书的书籍数量

（7）统计差异作者的书籍的均匀评分

（8）统计在最受关注的书籍Top 1000中，差异出书社出书的书籍数量

（9）统计在最受关注的书籍Top 1000中，差异语言的书籍数量

（10）统计差异作者的书籍的均匀受关注程度

---

需求实现

数据预处理处罚

1. # -*- coding: utf-8 -*-
2. # @Time     : 2024/12/14 0:49
3. # @Author   : 从心
4. # @File     : spark_book_recommendation_analysis_preprocess.py
5. # @Software : PyCharm
7. import pandas as pd
8. import numpy as np
10. df = pd.read_csv('../data/books.csv', on_bad_lines='skip')
11. df.columns = df.columns.str.strip()
12. print(df.head(3))
13. df.info()
15. df = df.dropna()
16. df.info()
18. df = df.drop_duplicates(keep='first')
19. df.info()
22. def convert_date(date_str):
23.     try:
24.         converted_date = pd.to_datetime(date_str, format='%m/%d/%Y')
26.         return converted_date.strftime('%Y-%m-%d')
27.     except ValueError as e:
28.         print(f"{date_str} 转换失败: {e}")
30.         return np.nan
33. df['publication_date'] = df['publication_date'].apply(convert_date)
34. df = df.dropna()
35. df.info()
37. print(df['language_code'].unique())
39. df.to_csv('../data/books_cleaned.csv', encoding='utf-8', index=False)

复制代码

数据统计分析

1. # -*- coding: utf-8 -*-
2. # @Time     : 2024/12/14 0:50
3. # @Author   : 从心
4. # @File     : spark_book_recommendation_analysis.py
5. # @Software : PyCharm
7. from pyspark import SparkConf
8. from pyspark.sql import SparkSession
9. from pyspark.sql.functions import date_format, split, rank
10. from pyspark.sql.window import Window
12. spark = SparkSession.builder.config(conf=SparkConf()).getOrCreate()
14. # 视图 books
15. df_books = spark.read.csv('/input_spark_book_recommendation_analysis/books_cleaned.csv', header=True, inferSchema=True)
16. df_books.show(10)
17. df_books.createOrReplaceTempView('books')
19. """
20. (1) 统计最受关注的书籍 Top 10
21. """
22. df_books_attention_top_10 = spark.sql(
23.     """
24.     select bookID, title, text_reviews_count, substring_index(authors, '/', 1) as author_first, average_rating, isbn, isbn13, language_code, num_pages, ratings_count, publication_date, publisher
25.     from books
26.     order by text_reviews_count desc
27.     """
28. )
29. df_books_attention_top_10 = df_books_attention_top_10.repartition(1)
30. df_books_attention_top_10.show(n=10, truncate=False)
31. df_books_attention_top_10.write.csv('/result/books_attention_top_10.csv',
32.                                     mode='overwrite')
34. """
35. (2) 统计书籍篇幅 Top 10
36. """
37. df_books_length_top_10 = spark.sql(
38.     """
39.     select bookID, title, num_pages, substring_index(authors, '/', 1) as author_first, average_rating, isbn, isbn13, language_code, ratings_count, text_reviews_count, publication_date, publisher
40.     from books
41.     order by num_pages desc
42.     """
43. )
44. df_books_length_top_10 = df_books_length_top_10.repartition(1)
45. df_books_length_top_10.show(n=10, truncate=False)
46. df_books_length_top_10.write.csv('/result/books_length_top_10.csv', mode='overwrite')
48. """
49. (3) 统计不同出版社出版的书籍数量
50. """
51. df_publisher_books_num = spark.sql(
52.     """
53.     select publisher, count(*) as books_num
54.     from books
55.     group by publisher
56.     order by books_num desc
57.     """
58. )
59. df_publisher_books_num = df_publisher_books_num.repartition(1)
60. df_publisher_books_num.show(n=10, truncate=False)
61. df_publisher_books_num.write.csv('/result/publisher_books_num.csv',
62.                                  mode='overwrite')
63. """
64. (4) 统计不同语言的书籍数量
65. """
66. df_language_books_num = spark.sql(
67.     """
68.     select language_code, count(*) as books_num
69.     from books
70.     group by language_code
71.     order by books_num desc
72.     """
73. )
74. df_language_books_num = df_language_books_num.repartition(1)
75. df_language_books_num.show(n=10, truncate=False)
76. df_language_books_num.write.csv('/result/language_books_num.csv', mode='overwrite')
78. """
79. (5) 统计最不受关注的高分书籍 Top 10 (评分 4.5 以上, 评分人数 1w 以上, 评论数 200 以下)
80. """
81. df_books_rating_no_attention_top_10 = spark.sql(
82.     """
83.     select bookID, title, substring_index(authors, '/', 1) as author_first, average_rating, isbn, isbn13, language_code, num_pages, ratings_count, text_reviews_count, publication_date, publisher
84.     from books
85.     where average_rating > 4.5 and ratings_count > 10000 and text_reviews_count < 200
86.     order by text_reviews_count asc
87.     """
88. )
89. df_books_rating_no_attention_top_10 = df_books_rating_no_attention_top_10.repartition(1)
90. df_books_rating_no_attention_top_10.show(n=10, truncate=False)
91. df_books_rating_no_attention_top_10.write.csv(
92.     '/result/books_rating_no_attention_top_10.csv', mode='overwrite')
94. # 视图 books_with_year
95. df_books_with_year = df_books.withColumn('year', date_format(df_books['publication_date'], 'yyyy'))
96. df_books_with_year.show(10)
97. df_books_with_year.createOrReplaceTempView('books_with_year')
99. """
100. (6) 统计不同年份出版的书籍数量
101. """
102. df_year_books_num = spark.sql(
103.     """
104.     select year, count(*) as books_num
105.     from books_with_year
106.     group by year
107.     order by year asc
108.     """
109. )
110. df_year_books_num = df_year_books_num.repartition(1)
111. df_year_books_num.show(n=10, truncate=False)
112. df_year_books_num.write.csv('/result/year_books_num.csv', mode='overwrite')
114. # 视图 books_with_author_first
115. df_books_with_author_first = df_books.withColumn('author_first', split(df_books['authors'], '/').getItem(0))
116. df_books_with_author_first.show(10)
117. df_books_with_author_first.createOrReplaceTempView('books_with_author_first')
119. """
120. (7) 统计不同作者的书籍的平均评分
121. """
122. df_author_books_avg_rating = spark.sql(
123.     """
124.     select author_first, sum(average_rating * ratings_count) / sum(ratings_count) as avg_rating, count(*) as books_num
125.     from books_with_author_first
126.     group by author_first
127.     order by avg_rating desc, books_num desc
128.     """
129. )
130. df_author_books_avg_rating = df_author_books_avg_rating.repartition(1)
131. df_author_books_avg_rating.show(n=10, truncate=False)
132. df_author_books_avg_rating.write.csv('/result/author_books_avg_rating.csv',
133.                                      mode='overwrite')
135. # 视图 books_attention_top_1000
136. window = Window.orderBy(df_books_with_author_first['text_reviews_count'].desc())
137. df_books_attention_rank = df_books_with_author_first.withColumn('rank', rank().over(window))
138. df_books_attention_top_1000 = df_books_attention_rank.filter(df_books_attention_rank['rank'] <= 1000).drop('rank')
139. df_books_attention_top_1000.show(10)
140. df_books_attention_top_1000.createOrReplaceTempView('books_attention_top_1000')
142. """
143. (8) 统计在最受关注的书籍 Top 1000 中, 不同出版社出版的书籍数量
144. """
145. df_publisher_books_top_1000_num = spark.sql(
146.     """
147.     select publisher, count(*) as books_num
148.     from books_attention_top_1000
149.     group by publisher
150.     order by books_num desc
151.     """
152. )
153. df_publisher_books_top_1000_num.show(n=10, truncate=False)
154. df_publisher_books_top_1000_num.write.csv('/result/publisher_books_top_1000_num.csv',
155.                                           mode='overwrite')
157. """
158. (9) 统计在最受关注的书籍 Top 1000 中, 不同语言的书籍数量
159. """
160. df_language_books_top_1000_num = spark.sql(
161.     """
162.     select language_code, count(*) as books_num
163.     from books_attention_top_1000
164.     group by language_code
165.     order by books_num desc
166.     """
167. )
168. df_language_books_top_1000_num.show(n=10, truncate=False)
169. df_language_books_top_1000_num.write.csv('/result/language_books_top_1000_num.csv',
170.                                          mode='overwrite')
172. """
173. (10) 统计不同作者的书籍的平均受关注程度
174. """
175. df_author_books_avg_attention = spark.sql(
176.     """
177.     select author_first, sum(text_reviews_count) / count(*) as avg_attention, count(*) as books_num
178.     from books_with_author_first
179.     group by author_first
180.     order by avg_attention desc, books_num desc
181.     """
182. )
183. df_author_books_avg_attention = df_author_books_avg_attention.repartition(1)
184. df_author_books_avg_attention.show(n=10, truncate=False)
185. df_author_books_avg_attention.write.csv('/result/author_books_avg_attention.csv',
186.                                         mode='overwrite')

复制代码

结果可视化

1. # -*- coding: utf-8 -*-
2. # @Time     : 2024/12/14 0:50
3. # @Author   : 从心
4. # @File     : spark_book_recommendation_analysis_visualization.py
5. # @Software : PyCharm
7. import pandas as pd
8. import matplotlib.pyplot as plt
10. plt.rcParams['font.sans-serif'] = ['SimHei']
11. plt.rcParams['axes.unicode_minus'] = False
13. import matplotlib.colors as mcolors
16. def chart_1():
17.     """
18.     (1) 统计最受关注的书籍 Top 10
19.     """
21.     csv_path = '../result/books_attention_top_10.csv/part-r-00000-d44ddace-d5f4-42df-980d-d3e236064461.csv'
22.     names = ['bookID', 'title', 'text_reviews_count', 'author_first', 'average_rating', 'isbn', 'isbn13',
23.              'language_code',
24.              'num_pages', 'ratings_count', 'publication_date', 'publisher']
25.     df = pd.read_csv(csv_path, header=None, names=names)
26.     df = df.head(10)
27.     df = df.sort_values(by='text_reviews_count', ascending=True)
29.     plt.figure(figsize=(16, 9))
30.     y = df['title']
31.     x = df['text_reviews_count']
32.     plt.barh(y, x, color='skyblue')
34.     for idx, value in enumerate(x):
35.         plt.text(value, idx, f'{value}', va='center', ha='left')
37.     plt.title('最受关注的书籍 Top 10', fontsize=16, fontweight='bold')
38.     plt.ylabel('标题', fontsize=16, fontweight='bold')
39.     plt.xlabel('评论数', fontsize=16, fontweight='bold')
40.     plt.tight_layout()
42.     plt.savefig('../visualization/[1]books_attention_top_10.png')
43.     plt.show()
46. def chart_2():
47.     """
48.     (2) 统计书籍篇幅 Top 10
49.     """
51.     csv_path = '../result/books_length_top_10.csv/part-r-00000-7be4f0a3-b317-408f-ba8a-41be340d193b.csv'
52.     names = ['bookID', 'title', 'num_pages', 'author_first', 'average_rating', 'isbn', 'isbn13', 'language_code',
53.              'ratings_count', 'text_reviews_count', 'publication_date', 'publisher']
54.     df = pd.read_csv(csv_path, header=None, names=names)
55.     df = df.head(10)
56.     df = df.sort_values(by='num_pages', ascending=True)
58.     plt.figure(figsize=(16, 9))
59.     y = df['title']
60.     x = df['num_pages']
61.     plt.barh(y, x, color='skyblue')
63.     for idx, value in enumerate(x):
64.         plt.text(value, idx, f'{value}', va='center', ha='left')
66.     plt.title('书籍篇幅 Top 10', fontsize=16, fontweight='bold')
67.     plt.ylabel('标题', fontsize=16, fontweight='bold')
68.     plt.xlabel('页数', fontsize=16, fontweight='bold')
69.     plt.tight_layout()
71.     plt.savefig('../visualization/[2]books_length_top_10.png')
72.     plt.show()
75. def chart_3():
76.     """
77.     (3) 统计不同出版社出版的书籍数量
78.     """
80.     csv_path = '../result/publisher_books_num.csv/part-r-00000-f627a337-9aed-44ac-a37c-6c03582640dc.csv'
81.     names = ['publisher', 'books_num']
82.     df = pd.read_csv(csv_path, header=None, names=names)
83.     df = df.head(50)
85.     plt.figure(figsize=(16, 9))
86.     x = df['publisher']
87.     y = df['books_num']
88.     bars = plt.bar(x, y, color='skyblue')
89.     for bar in bars:
90.         height = bar.get_height()
91.         plt.text(bar.get_x() + bar.get_width() / 2.0, height, f'{int(height)}', ha='center', rotation=0)
93.     plt.title('不同出版社出版的书籍数量', fontsize=16, fontweight='bold')
94.     plt.xlabel('出版社', fontsize=16, fontweight='bold')
95.     plt.ylabel('书籍数量', fontsize=16, fontweight='bold')
96.     plt.xticks(rotation=90)
97.     plt.tight_layout()
99.     plt.savefig('../visualization/[3]publisher_books_num.png')
100.     plt.show()
103. def chart_4():
104.     """
105.     (4) 统计不同语言的书籍数量
106.     """
108.     csv_path = '../result/language_books_num.csv/part-r-00000-28c77665-f13b-4bee-aef6-a83511f4213a.csv'
109.     names = ['language_code', 'books_num']
110.     df = pd.read_csv(csv_path, header=None, names=names)
111.     df = df.head(50)
113.     plt.figure(figsize=(16, 9))
114.     x = df['language_code']
115.     y = df['books_num']
116.     bars = plt.bar(x, y, color='skyblue')
117.     for bar in bars:
118.         height = bar.get_height()
119.         plt.text(bar.get_x() + bar.get_width() / 2.0, height, f'{int(height)}', ha='center', rotation=0)
121.     plt.title('不同语言的书籍数量', fontsize=16, fontweight='bold')
122.     plt.xlabel('语言', fontsize=16, fontweight='bold')
123.     plt.ylabel('书籍数量', fontsize=16, fontweight='bold')
124.     plt.tight_layout()
126.     plt.savefig('../visualization/[4]language_books_num.png')
127.     plt.show()
130. def chart_5():
131.     """
132.     (5) 统计最不受关注的高分书籍 Top 10 (评分 4.5 以上, 评分人数 1w 以上, 评论数 200 以下)
133.     """
135.     csv_path = '../result/books_rating_no_attention_top_10.csv/part-r-00000-ec31e0ee-9f5b-4d11-bbd2-5cc0633be073.csv'
136.     names = ['bookID', 'title', 'author_first', 'average_rating', 'isbn', 'isbn13', 'language_code', 'num_pages',
137.              'ratings_count', 'text_reviews_count', 'publication_date', 'publisher']
138.     df = pd.read_csv(csv_path, header=None, names=names)
139.     df = df.head(10)
140.     df = df.sort_values(by='average_rating', ascending=True)
142.     plt.figure(figsize=(16, 9))
143.     y = df['title']
144.     x = df['average_rating']
145.     plt.barh(y, x, color='skyblue')
147.     for idx, value in enumerate(x):
148.         plt.text(value, idx, f'{value}', va='center', ha='left')
150.     plt.title('最不受关注的高分书籍 Top 10', fontsize=16, fontweight='bold')
151.     plt.ylabel('标题', fontsize=16, fontweight='bold')
152.     plt.xlabel('平均评分', fontsize=16, fontweight='bold')
153.     plt.tight_layout()
155.     plt.savefig('../visualization/[5]books_rating_no_attention_top_10.png')
156.     plt.show()
159. def chart_6():
160.     """
161.     (6) 统计不同年份出版的书籍数量
162.     """
164.     csv_path = '../result/year_books_num.csv/part-r-00000-40448964-0687-400c-94da-d390ca57f2d8.csv'
165.     names = ['year', 'books_num']
166.     df = pd.read_csv(csv_path, header=None, names=names)
168.     plt.figure(figsize=(16, 9))
169.     x = df['year']
170.     y = df['books_num']
171.     plt.plot(x, y, marker='o', linestyle='-', color='skyblue')
173.     plt.title('不同年份出版的书籍数量', fontsize=16, fontweight='bold')
174.     plt.xlabel('年份', fontsize=16, fontweight='bold')
175.     plt.ylabel('书籍数量', fontsize=16, fontweight='bold')
176.     plt.grid(True, which='both', linestyle='--', linewidth=0.5)
177.     plt.tight_layout()
179.     plt.savefig('../visualization/[6]year_books_num.png')
180.     plt.show()
183. def chart_7():
184.     """
185.     (7) 统计不同作者的书籍的平均评分
186.     """
188.     csv_path = '../result/author_books_avg_rating.csv/part-r-00000-5601e037-dc5b-4cd1-ba34-b2207717cb27.csv'
189.     names = ['author_first', 'avg_rating', 'books_num']
190.     df = pd.read_csv(csv_path, header=None, names=names)
191.     df = df.head(50)
193.     plt.figure(figsize=(16, 9))
194.     x = df['author_first']
195.     y = df['avg_rating']
196.     bars = plt.bar(x, y, color='skyblue')
197.     for bar in bars:
198.         height = bar.get_height()
199.         plt.text(bar.get_x() + bar.get_width() / 2.0, height, f'{height:.2f}', ha='center', rotation=45)
201.     plt.title('不同作者的书籍的平均评分', fontsize=16, fontweight='bold')
202.     plt.xlabel('第一作者', fontsize=16, fontweight='bold')
203.     plt.ylabel('平均评分', fontsize=16, fontweight='bold')
204.     plt.xticks(rotation=90)
205.     plt.tight_layout()
207.     plt.savefig('../visualization/[7]author_books_avg_rating.png')
208.     plt.show()
211. def chart_8():
212.     """
213.     (8) 统计在最受关注的书籍 Top 1000 中, 不同出版社出版的书籍数量
214.     """
216.     csv_path = '../result/publisher_books_top_1000_num.csv/part-r-00000-c9d19260-6251-446a-b55a-2f6864e295f7.csv'
217.     names = ['publisher', 'books_num']
218.     df = pd.read_csv(csv_path, header=None, names=names)
219.     df = df.head(10)
221.     plt.figure(figsize=(9, 9))
222.     labels = df['publisher']
223.     x = df['books_num']
224.     plt.pie(x, labels=labels, autopct='%1.1f%%', startangle=0)
226.     plt.title('在最受关注的书籍 Top 1000 中, 不同出版社出版的书籍数量')
227.     plt.tight_layout()
229.     plt.savefig('../visualization/[8]publisher_books_top_1000_num.png')
230.     plt.show()
233. def chart_9():
234.     """
235.     (9) 统计在最受关注的书籍 Top 1000 中, 不同语言的书籍数量
236.     """
238.     csv_path = '../result/language_books_top_1000_num.csv/part-r-00000-ecab523d-5d46-4e4f-a5f4-8dc083e049e2.csv'
239.     names = ['language_code', 'books_num']
240.     df = pd.read_csv(csv_path, header=None, names=names)
241.     df = df.head(10)
243.     plt.figure(figsize=(9, 9))
244.     labels = df['language_code']
245.     x = df['books_num']
246.     wedges = plt.pie(x, labels=None, autopct='%1.1f%%', startangle=0)[0]
247.     handles = [plt.Rectangle((0, 0), 1, 1, color=mcolors.to_rgba(wedge.get_facecolor())) for wedge in wedges]
248.     plt.legend(handles, labels, title='语言', loc='upper right', bbox_to_anchor=(0.9, 0.9))
250.     plt.title('在最受关注的书籍 Top 1000 中, 不同语言的书籍数量')
251.     plt.tight_layout()
253.     plt.savefig('../visualization/[9]language_books_top_1000_num.png')
254.     plt.show()
257. def chart_10():
258.     """
259.     (10) 统计不同作者的书籍的平均受关注程度
260.     """
262.     csv_path = '../result/author_books_avg_attention.csv/part-r-00000-b82eadc1-8db8-494d-befa-34bf3834cfb1.csv'
263.     names = ['author_first', 'avg_attention', 'books_num']
264.     df = pd.read_csv(csv_path, header=None, names=names)
265.     df = df.head(50)
267.     plt.figure(figsize=(16, 9))
268.     x = df['author_first']
269.     y = df['avg_attention']
270.     bars = plt.bar(x, y, color='skyblue')
271.     for bar in bars:
272.         height = bar.get_height()
273.         plt.text(bar.get_x() + bar.get_width() / 2.0, height, f'{int(height)}', ha='center', rotation=45)
275.     plt.title('不同作者的书籍的平均受关注程度', fontsize=16, fontweight='bold')
276.     plt.xlabel('第一作者', fontsize=16, fontweight='bold')
277.     plt.ylabel('平均受关注程度', fontsize=16, fontweight='bold')
278.     plt.xticks(rotation=90)
279.     plt.tight_layout()
281.     plt.savefig('../visualization/[10]author_books_avg_attention.png')
282.     plt.show()
285. if __name__ == '__main__':
286.     for i in range(1, 11):
287.         eval(f'chart_{i}()')

复制代码

（1）统计最受关注的书籍Top 10

（2）统计书籍篇幅Top 10

（3）统计差异出书社出书的书籍数量

（4）统计差异语言的书籍数量

（5）统计最不受关注的高分书籍Top 10（评分4.5以上，评分人数1w以上，批评数200以下）

（6）统计差异年份出书的书籍数量

（7）统计差异作者的书籍的均匀评分

（8）统计在最受关注的书籍Top 1000中，差异出书社出书的书籍数量

（9）统计在最受关注的书籍Top 1000中，差异语言的书籍数量

（10）统计差异作者的书籍的均匀受关注程度

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。