SHARE
TWEET

content

s0ares Aug 18th, 2018 120 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import urllib.request, json
  2. import MySQLdb
  3. import pandas as pd
  4.  
  5. def process_json(json):
  6.    
  7.     obj = {'id': '','title':'','description':'',
  8.            'channelTitle':'',
  9.            'channelId':'',
  10.            'tags': '', 'country': ''}    
  11.    
  12.     try :
  13.         snippet = (((json["items"])[0])["snippet"])
  14.     except:
  15.         print('Cant get JSON')
  16.         return obj
  17.  
  18.     tags = []
  19.    
  20.     try:
  21.         tags =  snippet["tags"]
  22.     except:
  23.         tags = []
  24.    
  25.     obj = {'id': snippet["title"],
  26.            'title': snippet["title"],
  27.            'description': snippet["description"],
  28.            'channelTitle': snippet["channelTitle"],
  29.            'channelId':snippet["channelId"],
  30.            'tags': tags, 'country': ''}
  31.            
  32.     return obj
  33.  
  34.  
  35. def return_json(video_id):
  36.     print('VIDEO ID = ' + video_id)
  37.     json_url = 'https://www.googleapis.com/youtube/v3/videos?id='+ video_id
  38.     json_url += '&key=AIzaSyBumLjL7RVMIu62kPlu9JU4fVppNRKzbxM&part=snippet,contentDetails,statistics,status'
  39.     print(json_url)
  40.     with urllib.request.urlopen(json_url) as url:
  41.         data = json.loads(url.read().decode())
  42.         return json.dumps(data)
  43.  
  44.  
  45. query = 'SELECT * FROM ad.playlist'
  46.  
  47. # Load database data
  48.  
  49. # Informações do DB
  50.  
  51. dbname = 'ad'
  52. dbuser = 'locus@video-ads'
  53. dbpass = 'JS~K=8V^y9S=3aG4'
  54. dbhost = 'video-ads.mysql.database.azure.com'
  55.  
  56. dbconn = MySQLdb.connect(
  57.       database=dbname, user=dbuser, password=dbpass, host=dbhost)
  58.  
  59. df = pd.read_sql(query, con=dbconn)
  60.  
  61. lista = []
  62.  
  63. for index, row in df.iterrows():
  64.     if 'kids' in row['theme']:
  65.         obj = process_json(json.loads(return_json(row['content_id'])))
  66.         obj['country'] = row['country']
  67.         lista.append(obj)
  68.  
  69.  
  70. data = pd.DataFrame(lista)
  71.  
  72. videos_por_pais = data.groupby(['country']).size().reset_index(name='counts').sort_values(by=['counts'], ascending=False)
  73. print("{}:{}".format('Vídeos por País:',videos_por_pais))
  74.  
  75.  
  76. # BRASIL CHANNELS
  77.  
  78. brasil = data.loc[data['country'] == 'brazil' ]
  79. brasil = brasil.groupby(['channelTitle']).size().reset_index(name='counts').sort_values(by=['counts'], ascending=False)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top