Wikipedia pageview数据获取(bigquery)

SELECT wiki,datehour,SUM(views) as totalViews FROM `bigquery-public-data.wikipedia.pageviews_2015` WHERE datehour BETWEEN "2015-01-01" AND "2016-01-01" 
GROUP BY datehour,wiki;

SELECT * FROM `bigquery-public-data.wikipedia.pageviews_2020`
 WHERE datehour BETWEEN "2020-01-01" AND "2021-01-01" AND ( (wiki='en' AND (title='Main_Page' OR title='Special:Search')) 
      OR (wiki='en.m' AND (title='Main_Page' OR title='Special:Search'))
      OR (wiki='zh' AND (title='Wikipedia:首页' OR title='Special:搜索'))
      OR (wiki='de' AND (title='Wikipedia:Hauptseite' OR title='Spezial:Suche'))
      OR (wiki='fr' AND (title='Wikipédia:Accueil_principal' OR title='Spécial:Recherche'))
      OR (wiki='ja' AND (title='メインページ' OR title='特別:検索'))
      OR (wiki='ru' AND (title='Заглавная_страница' OR title='Служебная:Поиск'))
      OR (wiki='ar' AND (title='الصفحة_الرئيسية' OR title='خاص:بحث'))
      OR (wiki='ca' AND (title='Portada' OR title='Especial:Cerca'))
      OR (wiki='it' AND (title='Pagina_principale' OR title='Speciale:Ricerca'))
      );

SELECT title FROM (
SELECT title,AVG(views) AS perviews FROM `bigquery-public-data.wikipedia.pageviews_2015` WHERE datehour BETWEEN "2015-07-12" AND "2015-07-13" AND wiki='en' 
GROUP BY title)
WHERE perviews>100;

SELECT wiki.datehour,wiki.title,wiki.views FROM (
  SELECT innerViewer.title,innerViewer.perviews FROM(
    SELECT title,AVG(views) AS perviews,COUNT(*) as viewCount FROM `bigquery-public-data.wikipedia.pageviews_2015` WHERE datehour BETWEEN "2015-01-01" AND "2016-01-01" AND wiki='en' 
    GROUP BY title ORDER BY perviews) innerViewer
   WHERE innerViewer.perviews>500 AND viewCount > 3600 LIMIT 100) doc, `bigquery-public-data.wikipedia.pageviews_2019` as wiki
 WHERE wiki.datehour BETWEEN "2019-01-01" AND "2020-01-01" AND wiki.title = doc.title AND wiki.wiki='en';

from datetime import datetime
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import os
os.chdir(r"E:\wikidata")
# 2015的版本作为基底
dirname = 'top100en'
filename = '2015.csv'
baseData = pd.read_csv(dirname+'\\'+filename,encoding='utf-8')

grouped_result = baseData.groupby('title')
# convert result to dictionary
baseDict = {}
for name,group in grouped_result:
    baseDict[name] = group
# 开始遍历后面的所有年份
for year in range(2016,2021):
    keys = list(baseDict.keys())
    filename = str(year)+'.csv'
    yearData = pd.read_csv(dirname+'\\'+filename,encoding='utf-8')
    grouped_result = yearData.groupby('title')

    # 遍历所有的keys，尝试将pandas DataFrame数据进行拼接
    errorList = []
    for key in keys:
        try:
            newDataFrame = grouped_result.get_group(key)
            #将获取到的新值与旧有数据进行拼接
            baseDict[key] = pd.concat([baseDict[key],newDataFrame])
        except KeyError:
            #如果该值没有找到，则会报这个错误。此时记录下来，循环结束后将其从baseData中删除
            errorList.append(key)
    
    print("error_list of year {} is {}".format(year,errorList))
    print('Delete them')
    for errorItem in errorList:
        del baseDict[errorItem]


# 获取数据
data = baseDict["Donald_Trump"] # ! 此处修改需要获取的页面名称
data.sort_values("datehour",inplace=True)
outputData = data["views"].to_numpy()
print("数据长度",len(data))
# 进行改写
newDF = data[["datehour","views"]]
newDF.columns = ["datetime","view"]
newDF["datetime"] = newDF["datetime"].apply(lambda x: datetime.strptime(x,"%Y-%m-%d %H:%M:%S %Z").strftime("%Y%m%d%H"))
newDF.to_csv("result.csv",index=False) # 导出