<jupyter_start><jupyter_text>操作與觀察 Dataframe  Part1. 基本操作與觀察  Read filedata source hdfs:///tmp/ratings.csv<jupyter_code>fileDF =<jupyter_output><empty_output><jupyter_text>觀察檔案<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>觀察欄位<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>How many columns?  欄位統計值<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>如果只想看 userid?<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>打印 schema<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>選擇欄位  please show top 20 rows of userid and rating<jupyter_code>fileDF
### please show top 20 rows of userid and rating + 1
fileDF
### please show top 20 rows of userid, rating + 1 and turn data type of rating to double
fileDF
### please check if the data type od new rating is double<jupyter_output><empty_output><jupyter_text>篩選欄位<jupyter_code>### please select userid which is equal to 3
fileDF
### please select userid and rating and userid is equal to 3<jupyter_output><empty_output><jupyter_text>計算不重複值<jupyter_code>### how many rows in the data?
fileDF
### how many unique users?
fileDF<jupyter_output><empty_output><jupyter_text>小練習: 有幾部電影被評為5分?<jupyter_code>fileDF<jupyter_output><empty_output><jupyter_text>Part2 資料清理  觀察數值分配<jupyter_code>fileDF.crosstab('userid', 'rating').show()<jupyter_output><empty_output><jupyter_text>處理遺漏值<jupyter_code># please fill missing value with 0 if the type of column is numeric, otherwise, please fill it with ''    
fileDF.fillna(0).show()<jupyter_output><empty_output><jupyter_text>轉換欄位屬性<jupyter_code>from pyspark.sql.types import DoubleType
fileDF = fileDF.withColumn("rating_double", fileDF["rating"].cast(DoubleType()))
fileDF.printSchema()
fileDF.show()
fileDF.fillna(0).show()
fileDF_clean = fileDF.fillna(0)
fileDF_clean.crosstab('userid', 'rating_double').show()
fileDF.dropna().show()<jupyter_output><empty_output><jupyter_text>處理重復值<jupyter_code>### How many unique rows in the dataset?
### Please delete duplicated data and verify it
fileDF.show()
fileDF.crosstab("userid", "movieid").show()
(fileDF.select("userid", "movieid", "rating").count() - 
    fileDF.select("userid", "movieid", "rating").distinct().count())
fileDF.dropDuplicates().orderBy(['userid', 'movieid', 'rating'], ascending=[1,0]).show()
fileDF.dropDuplicates(['userid', 'movieid', 'rating']).orderBy(['userid', 'movieid', 'rating'], ascending=[1,0]).show()
fileDF_nodup = fileDF.dropDuplicates(['userid', 'movieid', 'rating']).orderBy(['userid', 'movieid', 'rating'], ascending=[1,0])
(fileDF_nodup.select("userid", "movieid", "rating").count() - 
    fileDF_nodup.select("userid", "movieid", "rating").distinct().count())
spark.stop()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14864864864864866, 'fraction_numerical': 0.008400292184075968, 'mean_word_length': 9.575289575289576, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 57, 'https://': 0, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '87062', 'license': 'MIT', 'n_tokens_mistral': 1118, 'n_tokens_neox': 1098, 'n_words': 179, 'path': 'spark_sql/5_play_with_data_frame.ipynb', 'repo_name': 'bryanyang0528/spark_practice', 'size': '2738'}