Windeln Backlinks Data¶

from urlparse import urlparse
from lxml import etree
import json
import re
import os

import requests
from bs4 import BeautifulSoup
from pandas import *
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline

# data downloaded from http://webmeup.com/
fpath = 'C:/workspace/analysis_reporting/joe/milk_china/windeln.de-1430237039144.csv'

df = read_csv(fpath, encoding='utf-16', sep='\t')
df.columns = [
    'source_url','source_title','anchor_url','anchor_text',
    'is_text','nofollow','linking_domain_country','linking_domain_ip','check_date','first_seen'
]
df['source_domain'] = df.source_url.apply(lambda x: urlparse(x).netloc)
df['source_path'] = df.source_url.apply(lambda x: urlparse(x).path)
df['anchor_path'] = df.anchor_url.apply(lambda x: urlparse(x).path)
df['anchor_domain'] = df.anchor_url.apply(lambda x: urlparse(x).netloc)

df_cn = df[(df.linking_domain_country=='cn') & (df['anchor_domain']=='www.windeln.de')]

def get_alexa_rank(domain):
    def get_attr(path, attr, default):
        return tree.find(path).attrib[attr] if tree.find(path) is not None else default
    
    res_default = {
        'alexa_domain': domain,
        'alexa_gl_rank': -1,
        'alexa_cc_rank': -1,
        'alexa_cc': -1
    }
    
    try:
        url = 'http://data.alexa.com/data?cli=10&url={0}'.format(domain)
#         url = 'http://www.alexa.com/siteinfo/{0}'.format(domain)
    except Exception, err:
        return res_default, None
    
    sc = 0
    err_count = 0
    while err_count < 2:
        try:
            r = requests.get(url)
            sc = r.status_code
            
            tree = etree.XML(r.content)
            cc_rank = int(get_attr('SD/COUNTRY', 'RANK', -1))
            gl_rank = int(get_attr('SD/REACH', 'RANK', -1))
            cc = get_attr('SD/COUNTRY', 'CODE', '')
            title = get_attr('DMOZ/SITE', 'TITLE', '')
            desc = get_attr('DMOZ/SITE', 'DESC', '')
            
            if ((title == '') or (desc == '')) and (0 < cc_rank < 10000):
                title, desc = fetch_meta(domain)
    
            return {
                'alexa_domain': domain,
                'alexa_cc_rank': cc_rank,
                'alexa_gl_rank': gl_rank,
                'alexa_cc': cc,
                'alexa_title': title,
                'alexa_title_en': translate(title) if title != '' else '',
                'alexa_desc': desc,
                'alexa_desc_en': translate(desc) if desc != '' else '',
            }, r
        
        except Exception, err:
            print err
            err_count += 1
            
    return res_default, r

def fetch_meta(url):
    if re.search('http', url) is None:
        url = 'http://' + url
    try:
        r = requests.get(url)
    except Exception, err:
        print err
        return ''
    if r.status_code==200:
        soup = BeautifulSoup(r.content)
        try:
            return soup.select('meta[name="description"]')[0]['content'], soup.select('title')[0].text
        except Exception, err:
            return '', ''
    else:
        return '', ''

    
def translate(t):
    api_key = 'trnsl.1.1.20150429T194039Z.49a0abddd64a0760.b4a75adeabde25bb28d0fe58ad10f3ee994fcf6b'
    url = u'https://translate.yandex.net/api/v1.5/tr.json/translate?key={0}&lang=zh-en&text={1}'\
        .format(api_key, t)
    try:
        r = requests.get(url)
        if r.status_code == 200:
            data = json.loads(r.content)
            return data.get('text')[0] if data.get('text') is not None else ''
        else:
            return ''
    except Exception, err:
        print err
        return ''

    
def is_thread(url):
    return re.search('thread|forum|community', url) is not None
            
    
def get_thread_stats(url):
    def get_num(pat):
        m = re.search(pat, raw)
        if m is not None:
            n = m.groups()[0]
            try:
                return int(n)
            except Exception, err:
                return n
        return None
    
    pat_views = u'(?:查看|浏览)(?:: ?)(\d+)'
    pat_replies = u'(?:回复)(?:: ?)(\d+)'
    global r
    if is_thread(url):
        try:
            r = requests.get(url)
        except Exception, err:
            return None
        
        if r.status_code == 200:
            soup = BeautifulSoup(r.content)
            raw = soup.text
            
            views = get_num(pat_views)
            replies = get_num(pat_replies)
            
            return {
                'views': views,
                'replies': replies,
                'soup': soup,
            }
            
    return None

def get_connection_links(soup, domains):
    links = []
    for a in soup.select('a'):
        href = a.get('href')
        try:
            p = urlparse(href)
        except:
            return links
        if p is not None and p.netloc in domains:
            links.append(href)
    return links
            
def has_promotion(soup):
    m = re.search('优惠码', soup.text)
    return m is not None

def output(df):
    for c in df.columns:
        try:
            df.ix[:,c] = df.ix[:, c].apply(lambda x: re.sub('[\n\r]',' ', x))
        except:
            pass
        
    df.to_csv('C:/workspace/analysis_reporting/joe/milk_china/anchor_urls.csv', sep='\t', encoding='utf-8')
    df.sort('alexa_cc_rank',ascending=1)\
    .ix[:,['alexa_cc_rank','source_domain', 
           'alexa_title','alexa_desc', 
           'alexa_title_en','alexa_desc_en']].drop_duplicates()\
        .to_csv('C:/workspace/analysis_reporting/joe/milk_china/anchor_domain.csv', sep='\t', encoding='utf-8')

fpath = 'df_rank_cn.csv'
if not os.path.exists(fpath):
    domain_rank_cn = []
    for i, sd in enumerate(df_cn.source_domain.unique()):
        res, resp = get_alexa_rank(sd)
        domain_rank_cn.append(res)

        if i > 0 and not (i % 10):
            try:
                print '{1} processed {0}. res: {2}'.format(sd, i, json.dumps(res))
            except:
                print '{1} processed {0}. res:'.format('', i, json.dumps(res))
    df_rank_cn = DataFrame(domain_rank_cn)
    df_rank_cn.to_csv(fpath,index=0, encoding='utf-8')
else:
    df_rank_cn = read_csv(fpath)
                

# join the alexa data with backlinks data frame
df_cn_ = merge(
    df_cn,
    df_rank_cn,
    left_on='source_domain',
    right_on='alexa_domain',
    how='left'
)

def output(df):
    df.to_csv(k, sep='\t', encoding='utf-8')
    df.sort('alexa_cc_rank',ascending=1)\
    .ix[:,['alexa_cc_rank','source_domain', 
           'alexa_title','alexa_desc', 
           'alexa_title_en','alexa_desc_en']].drop_duplicates()\
        .to_csv('C:/workspace/analysis_reporting/joe/milk_china/anchor_domain.csv', sep='\t', encoding='utf-8')
        
output(df_cn3)