'''
analysis
'''
import pandas as pd
import numpy as np
import seaborn as sns
from scipy import stats
from scipy.stats import skew
from scipy.stats import norm
import matplotlib.pyplot as plt
from sklearn.preprocessing import StandardScaler
from sklearn.manifold import TSNE
from sklearn.cluster import KMeans
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler

train_df = pd.read_csv("F:/cs/python/code/houseprice/all/train.csv")
test_df = pd.read_csv("F:/cs/python/code/houseprice/all/test.csv")

#scaning data
#train data:1460*81 
#test data: 14559*80

#transtype
all_df = pd.concat((train_df.loc[:,'MSSubClass':'SaleCondition'], test_df.loc[:,'MSSubClass':'SaleCondition']), axis=0,ignore_index=True)
all_df['MSSubClass'] = all_df['MSSubClass'].astype(str)

quantitative = [f for f in all_df.columns if all_df.dtypes[f] != 'object']
qualitative = [f for f in all_df.columns if all_df.dtypes[f] == 'object']

print("quantitative: {}, qualitative: {}" .format (len(quantitative),len(qualitative)))

#missing data processing
#large in amount depose
#little in amount avarage
#middle of the missing data treat as one-hot
missing = all_df.isnull().sum()

missing.sort_values(inplace=True,ascending=False)
missing = missing[missing > 0]

types = all_df[missing.index].dtypes

percent = (all_df[missing.index].isnull().sum()/all_df[missing.index].isnull().count()).sort_values(ascending=False)

missing_data = pd.concat([missing, percent,types], axis=1, keys=['Total', 'Percent','Types'])
missing_data.sort_values('Total',ascending=False,inplace=True)
missing_data

missing.plot.bar()

#analysis
#single var
train_df.describe()['SalePrice']

#skewness and kurtosis
print("Skewness: %f" % train_df['SalePrice'].skew())
print("Kurtosis: %f" % train_df['SalePrice'].kurt())

#conrver
corrmat = train_df.corr()

#saleprice correlation matrix
k = 10 #number of variables for heatmap
cols = corrmat.nlargest(k, 'SalePrice')['SalePrice'].index
cm = np.corrcoef(train_df[cols].values.T)
sns.set(font_scale=1.25)
hm = sns.heatmap(cm, cbar=True, annot=True, square=True, fmt='.2f', annot_kws={'size': 10}, yticklabels=cols.values, xticklabels=cols.values)
plt.show()

## both corr and missing
missing_data.index.intersection(cols)

missing_data.loc[missing_data.index.intersection(cols)]

#dealing with missing data
all_df = all_df.drop((missing_data[missing_data['Total'] > 1]).index,1)
# df_train = df_train.drop(df_train.loc[df_train['Electrical'].isnull()].index)
all_df.isnull().sum().max() #just checking that there's no missing data missing...
# missing 1 replace with average

#normal probability plot
sns.distplot(train_df['SalePrice'], fit=norm);
fig = plt.figure()
res = stats.probplot(train_df['SalePrice'], plot=plt)

#log
train_df['SalePrice'] = np.log(train_df['SalePrice'])

#histogram and normal probability plot
sns.distplot(train_df['SalePrice'], fit=norm);
fig = plt.figure()
res = stats.probplot(train_df['SalePrice'], plot=plt)

#observe every var
quantitative = [f for f in all_df.columns if all_df.dtypes[f] != 'object']
qualitative = [f for f in all_df.columns if all_df.dtypes[f] == 'object']
print("quantitative: {}, qualitative: {}" .format (len(quantitative),len(qualitative)))

f = pd.melt(all_df, value_vars=quantitative)
g = sns.FacetGrid(f, col="variable",  col_wrap=2, sharex=False, sharey=False)
g = g.map(sns.distplot, "value")

#LotArea,BsmtUnfSF,1stFlrSF,TotalBsmtSF,KitchenAbvGr can be improved by log
#skewness
all_df[quantitative].apply(lambda x: skew(x.dropna())).sort_values(ascending=False)

#quantity charctive analysis
#Analysis of variance
train = all_df.loc[train_df.index]
train['SalePrice'] = train_df.SalePrice

def anova(frame):
    anv = pd.DataFrame()
    anv['feature'] = qualitative
    pvals = []
    for c in qualitative:
        samples = []
        for cls in frame[c].unique():
            s = frame[frame[c] == cls]['SalePrice'].values
            samples.append(s)
        pval = stats.f_oneway(*samples)[1]
        pvals.append(pval)
    anv['pval'] = pvals
    return anv.sort_values('pval')

a = anova(train)
a['disparity'] = np.log(1./a['pval'].values)
sns.barplot(data=a, x='feature', y='disparity')
x=plt.xticks(rotation=90)

#quality charctive analysis
def encode(frame, feature):
    ordering = pd.DataFrame()
    ordering['val'] = frame[feature].unique()
    ordering.index = ordering.val
    ordering['spmean'] = frame[[feature, 'SalePrice']].groupby(feature).mean()['SalePrice']
    ordering = ordering.sort_values('spmean')
    ordering['ordering'] = range(1, ordering.shape[0]+1)
    ordering = ordering['ordering'].to_dict()
    
    for cat, o in ordering.items():
        frame.loc[frame[feature] == cat, feature+'_E'] = o
    
qual_encoded = []
for q in qualitative:  
    encode(train, q)
    qual_encoded.append(q+'_E')
print(qual_encoded)

# choose raws of having missing data
missing_data = all_df.isnull().sum()
missing_data = missing_data[missing_data>0]
ids = all_df[missing_data.index].isnull()
# index (0), columns (1)
all_df.loc[ids[ids.any(axis=1)].index][missing_data.index]

# nan still nan
train.loc[1379,'Electrical_E']

#corr computing
def spearman(frame, features):
    spr = pd.DataFrame()
    spr['feature'] = features
    #Signature: a.corr(other, method='pearson', min_periods=None)
    #Docstring:
    #Compute correlation with `other` Series, excluding missing values
    # 计算特征和 SalePrice的 斯皮尔曼 相关系数
    spr['spearman'] = [frame[f].corr(frame['SalePrice'], 'spearman') for f in features]
    spr = spr.sort_values('spearman')
    plt.figure(figsize=(6, 0.25*len(features))) # width, height
    sns.barplot(data=spr, y='feature', x='spearman', orient='h')
    
features = quantitative + qual_encoded
spearman(train, features)
# OverallQual Neighborhood GrLiveArea have bing influence on price

#corr between vars
plt.figure(1)
corr = train[quantitative+['SalePrice']].corr()
sns.heatmap(corr)
plt.figure(2)
corr = train[qual_encoded+['SalePrice']].corr()
sns.heatmap(corr)
plt.figure(3)
# [31,27]
corr = pd.DataFrame(np.zeros([len(quantitative)+1, len(qual_encoded)+1]), index=quantitative+['SalePrice'], columns=qual_encoded+['SalePrice'])
for q1 in quantitative+['SalePrice']:
    for q2 in qual_encoded+['SalePrice']:
        corr.loc[q1, q2] = train[q1].corr(train[q2])
sns.heatmap(corr)

#Pairplots
def pairplot(x, y, **kwargs):
    ax = plt.gca()
    ts = pd.DataFrame({'time': x, 'val': y})
    ts = ts.groupby('time').mean()
    ts.plot(ax=ax)
    plt.xticks(rotation=90)
    
f = pd.melt(train, id_vars=['SalePrice'], value_vars=quantitative+qual_encoded)
g = sns.FacetGrid(f, col="variable",  col_wrap=2, sharex=False, sharey=False, size=5)
g = g.map(pairplot, "value", "SalePrice")

#price departing
a = train['SalePrice']
a.plot.hist()

features = quantitative

standard = train[train['SalePrice'] < np.log(200000)]
pricey = train[train['SalePrice'] >= np.log(200000)]

diff = pd.DataFrame()
diff['feature'] = features
diff['difference'] = [(pricey[f].fillna(0.).mean() - standard[f].fillna(0.).mean())/(standard[f].fillna(0.).mean())
                      for f in features]

sns.barplot(data=diff, x='feature', y='difference')
x=plt.xticks(rotation=90)

#classfing
features = quantitative + qual_encoded
model = TSNE(n_components=2, random_state=0, perplexity=50)
X = train[features].fillna(0.).values
tsne = model.fit_transform(X)

std = StandardScaler()
s = std.fit_transform(X)
pca = PCA(n_components=30)
pca.fit(s)
pc = pca.transform(s)
kmeans = KMeans(n_clusters=5)
kmeans.fit(pc)

fr = pd.DataFrame({'tsne1': tsne[:,0], 'tsne2': tsne[:, 1], 'cluster': kmeans.labels_})
sns.lmplot(data=fr, x='tsne1', y='tsne2', hue='cluster', fit_reg=False)
print(np.sum(pca.explained_variance_ratio_))

'''
model
'''
import pandas as pd
import numpy as np
import seaborn as sns
from scipy import stats
from scipy.stats import skew
from scipy.stats import norm
import matplotlib
import matplotlib.pyplot as plt
from sklearn.preprocessing import StandardScaler
from sklearn.manifold import TSNE
from sklearn.cluster import KMeans
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler

train_df = pd.read_csv("F:/cs/python/code/houseprice/all/train.csv")
test_df = pd.read_csv("F:/cs/python/code/houseprice/all/test.csv")

#feature engineering
all_df = pd.concat((train_df.loc[:,'MSSubClass':'SaleCondition'], test_df.loc[:,'MSSubClass':'SaleCondition']), axis=0,ignore_index=True)
all_df['MSSubClass'] = all_df['MSSubClass'].astype(str)
quantitative = [f for f in all_df.columns if all_df.dtypes[f] != 'object']
qualitative = [f for f in all_df.columns if all_df.dtypes[f] == 'object']

#dealing missing data
missing = all_df.isnull().sum()
missing.sort_values(inplace=True,ascending=False)
missing = missing[missing > 0]

#missing 1 replaced with average
all_df = all_df.drop(missing[missing>1].index,1)

all_df.isnull().sum()[all_df.isnull().sum()>0]

#dealing log(GrLivArea、1stFlrSF、2ndFlrSF、TotalBsmtSF、LotArea、KitchenAbvGr、GarageArea )
logfeatures = ['GrLivArea','1stFlrSF','2ndFlrSF','TotalBsmtSF','LotArea','KitchenAbvGr','GarageArea']

for logfeature in logfeatures:
    all_df[logfeature] = np.log1p(all_df[logfeature].values)

#dealing boolean var
all_df['HasBasement'] = all_df['TotalBsmtSF'].apply(lambda x: 1 if x > 0 else 0)
all_df['HasGarage'] = all_df['GarageArea'].apply(lambda x: 1 if x > 0 else 0)
all_df['Has2ndFloor'] = all_df['2ndFlrSF'].apply(lambda x: 1 if x > 0 else 0)
all_df['HasWoodDeck'] = all_df['WoodDeckSF'].apply(lambda x: 1 if x > 0 else 0)
all_df['HasPorch'] = all_df['OpenPorchSF'].apply(lambda x: 1 if x > 0 else 0)
all_df['HasPool'] = all_df['PoolArea'].apply(lambda x: 1 if x > 0 else 0)
all_df['IsNew'] = all_df['YearBuilt'].apply(lambda x: 1 if x > 2000 else 0)

quantitative = [f for f in all_df.columns if all_df.dtypes[f] != 'object']
qualitative = [f for f in all_df.columns if all_df.dtypes[f] == 'object']

#encode quanlity
all_dummy_df = pd.get_dummies(all_df)

#standize var
all_dummy_df.isnull().sum().sum()

mean_cols = all_dummy_df.mean()
all_dummy_df = all_dummy_df.fillna(mean_cols)

all_dummy_df.isnull().sum().sum()

X = all_dummy_df[quantitative]
std = StandardScaler()
s = std.fit_transform(X)

all_dummy_df[quantitative] = s

dummy_train_df = all_dummy_df.loc[train_df.index]
dummy_test_df = all_dummy_df.loc[test_df.index]

y_train = np.log(train_df.SalePrice)


#model predicting
#ridge regression
from sklearn.linear_model import Ridge
from sklearn.model_selection import cross_val_score
y_train.values

def rmse_cv(model):
    rmse= np.sqrt(-cross_val_score(model, dummy_train_df, y_train.values, scoring="neg_mean_squared_error", cv = 5))
    return(rmse)
alphas = np.logspace(-3, 2, 50)
cv_ridge = []
coefs = []
for alpha in alphas:
    model = Ridge(alpha = alpha)
    model.fit(dummy_train_df,y_train)
    cv_ridge.append(rmse_cv(model).mean())
    coefs.append(model.coef_)
import matplotlib.pyplot as plt

cv_ridge = pd.Series(cv_ridge, index = alphas)
cv_ridge.plot(title = "Validation - Just Do It")
plt.xlabel("alpha")
plt.ylabel("rmse")
# plt.plot(alphas, cv_ridge)
# plt.title("Alpha vs CV Error")

#ridge trace picture
# matplotlib.rcParams['figure.figsize'] = (12.0, 12.0)
ax = plt.gca()

# ax.set_color_cycle(['b', 'r', 'g', 'c', 'k', 'y', 'm'])

ax.plot(alphas, coefs)
ax.set_xscale('log')
ax.set_xlim(ax.get_xlim()[::-1])  # reverse axis
plt.xlabel('alpha')
plt.ylabel('weights')
plt.title('Ridge coefficients as a function of the regularization')
plt.axis('tight')
plt.show()

#lesso :can choose some of feature
from sklearn.linear_model import Lasso,LassoCV

# alphas = np.logspace(-3, 2, 50)
# alphas = [1, 0.1, 0.001, 0.0005]
alphas = np.logspace(-4, -2, 100)
cv_lasso = []
coefs = []
for alpha in alphas:
    model = Lasso(alpha = alpha,max_iter=5000)
    model.fit(dummy_train_df,y_train)
    cv_lasso.append(rmse_cv(model).mean())
    coefs.append(model.coef_)

cv_lasso = pd.Series(cv_lasso, index = alphas)
cv_lasso.plot(title = "Validation - Just Do It")
plt.xlabel("alpha")
plt.ylabel("rmse")
# plt.plot(alphas, cv_ridge)
# plt.title("Alpha vs CV Error"

print(cv_lasso.min(), cv_lasso.argmin())

model = Lasso(alpha = 0.00058,max_iter=5000)
model.fit(dummy_train_df,y_train)
Lasso(alpha=0.00058, copy_X=True, fit_intercept=True, max_iter=5000,
   normalize=False, positive=False, precompute=False, random_state=None,
   selection='cyclic', tol=0.0001, warm_start=False)
coef = pd.Series(model.coef_, index = dummy_train_df.columns)
print("Lasso picked " + str(sum(coef != 0)) + " variables and eliminated the other " +  str(sum(coef == 0)) + " variables")

imp_coef = pd.concat([coef.sort_values().head(10),
                     coef.sort_values().tail(10)])
matplotlib.rcParams['figure.figsize'] = (8.0, 10.0)
imp_coef.plot(kind = "barh")
plt.title("Coefficients in the Lasso Model")

#Elastic Net :connect with lasso and ridge

from sklearn.linear_model import ElasticNet,ElasticNetCV
elastic = ElasticNetCV(l1_ratio=[.1, .5, .7, .9, .95, .99, 1], 
                                    alphas=[0.001, 0.05, 0.1, 0.3, 1, 3, 5, 10, 15, 30, 50, 75], cv=5,max_iter=5000)
elastic.fit(dummy_train_df, y_train)
ElasticNetCV(alphas=[0.001, 0.05, 0.1, 0.3, 1, 3, 5, 10, 15, 30, 50, 75],
       copy_X=True, cv=5, eps=0.001, fit_intercept=True,
       l1_ratio=[0.1, 0.5, 0.7, 0.9, 0.95, 0.99, 1], max_iter=5000,
       n_alphas=100, n_jobs=1, normalize=False, positive=False,
       precompute='auto', random_state=None, selection='cyclic',
       tol=0.0001, verbose=0)
rmse_cv(elastic).mean()

#feature engineering 2 (another methon)
import utils
train_df_munged,label_df,test_df_munged = utils.feature_engineering()

test_df = pd.read_csv('../input/test.csv')
from sklearn.metrics import mean_squared_error,make_scorer
from sklearn.model_selection import cross_val_score
# 定义自己的score函数
def my_custom_loss_func(ground_truth, predictions):
    return np.sqrt(mean_squared_error(np.exp(ground_truth), np.exp(predictions)))

my_loss_func  = make_scorer(my_custom_loss_func, greater_is_better=False)
def rmse_cv2(model):
    rmse= np.sqrt(-cross_val_score(model, train_df_munged, label_df.SalePrice, scoring='neg_mean_squared_error', cv = 5))
    return(rmse)

#ridge2
from sklearn.linear_model import RidgeCV,Ridge
alphas = np.logspace(-3, 2, 100)
model_ridge = RidgeCV(alphas=alphas).fit(train_df_munged, label_df.SalePrice)
# Run prediction on training set to get a rough idea of how well it does.
pred_Y_ridge = model_ridge.predict(train_df_munged)
print("Ridge score on training set: ", model_ridge.score(train_df_munged,label_df.SalePrice))
print("cross_validation: ",rmse_cv2(model_ridge).mean())

#lasso2
from sklearn.linear_model import Lasso,LassoCV
model_lasso = LassoCV(eps=0.0001,max_iter=20000).fit(train_df_munged, label_df.SalePrice)
# Run prediction on training set to get a rough idea of how well it does.
pred_Y_lasso = model_lasso.predict(train_df_munged)
print("Lasso score on training set: ", model_lasso.score(train_df_munged,label_df.SalePrice))

print("cross_validation: ",rmse_cv2(model_lasso).mean())

#Elastic Net
from sklearn.linear_model import ElasticNet,ElasticNetCV
model_elastic = ElasticNetCV(l1_ratio=[.1, .5, .7, .9, .95, .99, 1], 
                                    alphas=[0.001, 0.05, 0.1, 0.3, 1, 3, 5, 10, 15, 30, 50, 75], cv=5,max_iter=10000)
model_elastic.fit(train_df_munged, label_df.SalePrice)
ElasticNetCV(alphas=[0.001, 0.05, 0.1, 0.3, 1, 3, 5, 10, 15, 30, 50, 75],
       copy_X=True, cv=5, eps=0.001, fit_intercept=True,
       l1_ratio=[0.1, 0.5, 0.7, 0.9, 0.95, 0.99, 1], max_iter=10000,
       n_alphas=100, n_jobs=1, normalize=False, positive=False,
       precompute='auto', random_state=None, selection='cyclic',
       tol=0.0001, verbose=0)
# Run prediction on training set to get a rough idea of how well it does.
pred_Y_elastic = model_elastic.predict(train_df_munged)
print("Elastic score on training set: ", model_elastic.score(train_df_munged,label_df.SalePrice))

print("cross_validation: ",rmse_cv2(model_elastic).mean())

#XGBoost
# XGBoost -- I did some "manual" cross-validation here but should really find
# these hyperparameters using CV. ;-)

import xgboost as xgb

model_xgb = xgb.XGBRegressor(
                 colsample_bytree=0.2,
                 gamma=0.0,
                 learning_rate=0.05,
                 max_depth=6,
                 min_child_weight=1.5,
                 n_estimators=7200,                                                                  
                 reg_alpha=0.9,
                 reg_lambda=0.6,
                 subsample=0.2,
                 seed=42,
                 silent=1)

model_xgb.fit(train_df_munged, label_df.SalePrice)

# Run prediction on training set to get a rough idea of how well it does.
pred_Y_xgb = model_xgb.predict(train_df_munged)
print("XGBoost score on training set: ", model_xgb.score(train_df_munged,label_df.SalePrice)) # 过拟合

print("cross_validation: ",rmse_cv2(model_xgb).mean())

print("score: ",mean_squared_error(model_xgb.predict(train_df_munged),label_df.SalePrice))

#Ensemble
from sklearn.linear_model import LinearRegression
# Create linear regression object
regr = LinearRegression()
train_x = np.concatenate(
    (pred_Y_lasso[np.newaxis, :].T,pred_Y_ridge[np.newaxis, :].T,
     pred_Y_elastic[np.newaxis, :].T,pred_Y_xgb[np.newaxis, :].T), axis=1)
regr.fit(train_x,label_df.SalePrice)
LinearRegression(copy_X=True, fit_intercept=True, n_jobs=1, normalize=False)
regr.coef_

print("Ensemble score on training set: ", regr.score(train_x,label_df.SalePrice)) # overfitting

print("score: ",mean_squared_error(regr.predict(train_x),label_df.SalePrice))

#submit
model_lasso.predict(test_df_munged)[np.newaxis, :].T

test_x = np.concatenate(
(model_lasso.predict(test_df_munged)[np.newaxis, :].T,model_ridge.predict(test_df_munged)[np.newaxis, :].T,
                           model_elastic.predict(test_df_munged)[np.newaxis, :].T, model_xgb.predict(test_df_munged)[np.newaxis, :].T)
        ,axis=1)
y_final = regr.predict(test_x)
y_final

submission_df = pd.DataFrame(data= {'Id' : test_df.Id, 'SalePrice': np.exp(y_final)})
submission_df.to_csv("bag-4.csv",index=False) # conceal index

do_some_code()
f = open('/path/to/file', 'r')
r = f.read() # <== 线程停在此处等待IO操作结果
# IO操作完成后线程才能继续执行:
do_some_code(r)

loop = get_event_loop()
while True:
    event = loop.get_event()
    process_event(event)

Michael,99
Bob,85
Bart,59
Lisa,87

[
    {"name":"Michael","score":99},
    {"name":"Bob","score":85},
    {"name":"Bart","score":59},
    {"name":"Lisa","score":87}
]

SELECT * FROM classes WHERE grade_id = '1';

grade_id | class_id | name

1        | 11       | 一年级一班

1        | 12       | 一年级二班

1        | 13       | 一年级三班

发件人 -> MUA -> MTA -> MTA -> 若干个MTA -> MDA <- MUA <- 收件人

import requests
res = requests.get('http://news.sina.com.cn/china/')
res.encoding = 'utf-8'
print(type(res))
#print(res.text)

#使用示例
from bs4 import BeautifulSoup
html_sample = ' \
<html> \
 <body> \
 <h1 id="title">Hello World</h1> \
 <a href="#" class="link">This is link1</a> \
 <a href="# link2" class="link">This is link2</a> \
 </body> \
 </html>'

soup = BeautifulSoup(html_sample, 'html.parser')
print(type(soup))
print(soup.text)

#使用select 找出所有含h1的元素
soup = BeautifulSoup(html_sample, 'html.parser')
alink = soup.select('h1')
print(alink)
print(alink[0])
print(alink[0].text)

#使用select 找出所有含a的元素
soup = BeautifulSoup(html_sample, 'html.parser')
alink = soup.select('a')
print(alink)
for link in alink:
    print(link)
    print(link.text)

#使用select 找出所有id为title的元素
alink = soup.select('#title')
print(alink)

#使用select 找出所有class为link的元素
alink = soup.select('.link')
for link in alink:
    print(link)

#使用select 找出所有a tag 的href的连结
alink = soup.select('a')
for link in alink:
    print(link['href'])

import requests
res = requests.get('http://news.sina.com.cn/china/')
res.encoding = 'utf-8'
#print(type(res))
soup = BeautifulSoup(res.text, 'html.parser')

for news in soup.select('.news-item'):
    #print(news)
    if len(news.select('h2')) > 0:
        h2 = news.select('h2')[0].text
        time = news.select('.time')[0].text
        a = news.select('a')[0]['href']
        #print(time, h2, a)

import requests
res = requests.get('http://news.sina.com.cn/c/2018-07-31/doc-ihhacrcc9897484.shtml')
res.encoding = 'utf-8'
print(type(res))
#print(res.text)
soup = BeautifulSoup(res.text, 'html.parser')

#标题
title = soup.select('.main-title')[0].text
print(title)

#时间与来源
datesource = soup.select('.date-source')[0]
print(datesource)
date = datesource.span.text
print(date)
source = datesource.a.text
print(source)
#或者
#date = soup.select('.date-source')[0].contents[0].strip()
#date

#时间
from datetime import datetime
dt = datetime.strptime(date, '%Y年%m月%d日 %H:%M') #str转time
print(dt)
dt.strftime('%Y-%M-%d')#time转str

#文章内容
article = []
for p in soup.select('#article p')[:-1]:
    article.append(p.text.strip())
#print(article)
' '.join(article)
#或者' '.join[p.text.strip() for p in soup.select('#article p')[:-1]]

#编辑
editor = soup.select('.show_author')[0].text.lstrip('责任编辑：')
print(editor)

#评论
import requests
comments = requests.get('http://comment5.news.sina.com.cn/page/info?version=1&format=json&\
channel=gn&newsid=comos-hhacrcc9897484&group=undefined&compress=0&ie=utf-8&oe=utf-8&page=1&\
page_size=3')
#comments.text
import json
jd = json.loads(comments.text)
#print(jd)
commentnum = jd['result']['count']['total']

newsurl = 'http://news.sina.com.cn/c/2018-07-31/doc-ihhacrcc9897484.shtml'
newsurl.split('/')[-1].rstrip('.shtml').lstrip('doc-i')
#或正则式
re.search('doc-i(.*).shtml', newsurl).group(1)

#整理评论
commentURL = 'http://comment5.news.sina.com.cn/page/info?version=1&format=json&\
channel=gn&newsid=comos-{}&group=undefined&compress=0&ie=utf-8&oe=utf-8&page=1&\
page_size=3'
import re
import json
def getCommentCount(newsurl):
    newsid = re.search('doc-i(.*).shtml', newsurl).group(1)
    comments = requests.get(commentURL.format(newsid))
    jd = json.loads(comments.text)
    return jd['result']['count']['total']

#测试
news = 'http://news.sina.com.cn/c/2018-07-31/doc-ihhacrcc9897484.shtml'
getCommentCount(news)

#单个新闻信息
import requests
from bs4 import BeautifulSoup

def getNewsDetail(newsurl):
    result = {}
    res = requests.get(newsurl)
    res.encoding = 'utf-8'
    soup = BeautifulSoup(res.text, 'html.parser')
    result['title'] = soup.select('.main-title')[0].text
    result['newssource'] = soup.select('.date-source')[0].a.text
    date = soup.select('.date-source')[0].span.text
    result['dt'] = datetime.strptime(date, '%Y年%m月%d日 %H:%M') #str转time
    result['article'] = ' '.join([p.text.strip() for p in soup.select('#article p')[:-1]])
    result['editor'] = soup.select('.show_author')[0].text.lstrip('责任编辑：')
    result['comments'] = getCommentCount(newsurl)
    return result

#test
news = 'http://news.sina.com.cn/c/nd/2018-07-24/doc-ihftenhz7547208.shtml'
getNewsDetail(news)

#剖析分页信息
import requests
import json
res = requests.get('http://api.roll.news.sina.com.cn/zt_list?channel=news&cat_1=gnxw&\
cat_2==gdxw1||=gatxw||=zs-pl||=mtjj&level==1||=2&show_ext=1&show_all=1&show_num=22&tag=1&\
format=json&page=6&callback=newsloadercallback&_=1533026268219')
jd = json.loads(res.text.lstrip('  newsloadercallback(').rstrip(');'))
#jd
for ent in jd['result']['data']:
    print(ent['url'])

#建立剖析清单链接函式
def parseListLinks(url):
    newsdetails = []
    res = requests.get(url)
    jd = json.loads(res.text.lstrip('  newsloadercallback(').rstrip(');'))
    for ent in jd['result']['data']:
        
        try:
            print(ent['url'])
            newsdetails.append(getNewsDetail(ent['url']))
        except AttributeError:
            pass

    return newsdetails

#test 
url = 'http://api.roll.news.sina.com.cn/zt_list?channel=news&cat_1=gnxw&\
cat_2==gdxw1||=gatxw||=zs-pl||=mtjj&level==1||=2&show_ext=1&show_all=1&show_num=22&tag=1&\
format=json&page=6&callback=newsloadercallback&_=1533026268219'
parseListLinks(url)

#使用for循环产生多页连结&批次抓取每页新闻内文
def createUrl(num):
    url = 'http://api.roll.news.sina.com.cn/zt_list?channel=news&cat_1=gnxw&\
cat_2==gdxw1||=gatxw||=zs-pl||=mtjj&level==1||=2&show_ext=1&show_all=1&show_num=22&tag=1&\
format=json&page={}'#使用{}代替
    news_total = []
    for i in range(1, num):
        newsurl = url.format(i)
        print(newsurl)
        newsary = parseListLinks(newsurl)
        news_total.extend(newsary)
    return news_total
#test
news_total = createUrl(3)
#print(news_total)
len(news_total)

#pandas 整理资料
import pandas
df = pandas.DataFrame(news_total)
df.head()

#保存数据
df.to_csv('news.csv')#到csv
import sqlite3 #到sql
with sqlite3.connect('news.sqlite') as db:
    df.to_sql('news', con = db)

#操作sql
import sqlite3
with sqlite3.connect('news.sqlite') as db:
    df2 = pandas.read_sql_query('SELECT * FROM news', con = db)
df2.head()