import pandas as pd
import numpy as np
import matplotlib.pylab as plt 

# read data
u_cols = ['user_id', 'age', 'sex', 'occupation', 'zip_code']
users = pd.read_csv('ml-100k/u.user', sep='|', names=u_cols,encoding='latin-1')

r_cols = ['user_id', 'movie_id', 'rating', 'unix_timestamp']
ratings = pd.read_csv('ml-100k/u.data', sep='\t', names=r_cols,encoding='latin-1')

m_cols = ['movie_id', 'title', 'release_date', 'video_release_date', 'imdb_url'] 
movies = pd.read_csv('ml-100k/u.item', sep='|', names=m_cols, usecols=range(5),encoding='latin-1') 

# data merge
movie_ratings = pd.merge(movies, ratings) 
lens = pd.merge(movie_ratings, users)


movie_stats = lens.groupby('title').agg({'rating': [np.size, np.mean]})
atleast_100 = movie_stats['rating']['size'] >= 100
print( movie_stats[atleast_100].sort_values([('rating', 'mean')], ascending=False)[:10] )

'''上述语句等价于SQL中的：

SELECT title, COUNT(1) size, AVG(rating) mean
FROM lens
GROUP BY title
HAVING COUNT(1) >= 100
ORDER BY 3 DESC
LIMIT 10;'''
import pandas as pd
import numpy as np
import matplotlib.pylab as plt 

# read data
u_cols = ['user_id', 'age', 'sex', 'occupation', 'zip_code']
users = pd.read_csv('ml-100k/u.user', sep='|', names=u_cols,encoding='latin-1')

r_cols = ['user_id', 'movie_id', 'rating', 'unix_timestamp']
ratings = pd.read_csv('ml-100k/u.data', sep='\t', names=r_cols,encoding='latin-1')

m_cols = ['movie_id', 'title', 'release_date', 'video_release_date', 'imdb_url'] 
movies = pd.read_csv('ml-100k/u.item', sep='|', names=m_cols, usecols=range(5),encoding='latin-1') 

# data merge
movie_ratings = pd.merge(movies, ratings) 
lens = pd.merge(movie_ratings, users)


movie_stats = lens.groupby('title').agg({'rating': [np.size, np.mean]})
atleast_100 = movie_stats['rating']['size'] >= 100
print( movie_stats[atleast_100].sort_values([('rating', 'mean')], ascending=False)[:10] )

'''上述语句等价于SQL中的：

SELECT title, COUNT(1) size, AVG(rating) mean
FROM lens
GROUP BY title
HAVING COUNT(1) >= 100
ORDER BY 3 DESC
LIMIT 10;'''
# read data # data merge 上述语句等价于SQL中的：
 
 SELECT title, COUNT(1) size, AVG(rating) mean
 FROM lens
 GROUP BY title
 HAVING COUNT(1) >= 100
 ORDER BY 3 DESC
 LIMIT 10;