I am attempting to quality check a dataframe of data that contains weblinks for images. Ultimately I need to know if the links are in an acceptable format, and if the link works. I don't need to retrieve any of the data, just flagging any broken/incorrect links that shouldn't be displayed downstream.
I want to do this in parallel/async to speed it up. I have a few hundred thousand links to verify on a regular basis. (Some of these links can be a delimited list)
It looks like aiohttp might be the modern application of doing this instead of requests and url_exists? --but i'm not sure how to apply it to a dataframe instead of a list. Or how to create a sub if-loop to handle the ~|~ delimiter when it exists.
Am I heading in the right direction, or is there a better way to process all these requests? (When I use the list of 100 sites at the bottom of this post, it takes about 20 seconds to get results. In the demo of this code it showed it taking about 3 seconds. I'm not sure why I have such a slowdown.)
Ultimately I want to get to a list of ID's and fields that are bad or broken.
Example Input:
url_df = pd.DataFrame({
'ID':[1,2,5,25,26],
'link1':['apple', 'www.google.com', 'gm@yahoo.com', 'http://www.youtube.com', '888-555-5556 Ryan Parkes rp@abc.io'],
'link2':['http://www.bing.com','http://www.linkedin.com','',' please call now','http://www.reddit.com' ],
'link3':['http://www.stackoverflow.com~|~http://www.ebay.com', 'http://www.imdb.com', 'http://www.google.co.uk','more random text that could be really long and annoying','over the hills and through the woods']
})
Eventual Desired Output:
output = pd.DataFrame({
'ID':[1,5,5,5,25,25,26,26],
'Column':['link1','link1','link2','link3','link2','link3','link1','link3'],
'Status':['bad','bad','bad','broken','bad','bad','bad','bad',]
})
The old way:
import requests, pandas as pd, time
url_df = pd.DataFrame({
'ID':[1,2,5,25,26],
'link1':['apple', 'www.google.com', 'gm@yahoo.com', 'http://www.youtube.com', '888-555-5556 Ryan Parkes rp@abc.io'],
'link2':['http://www.bing.com','http://www.linkedin.com','',' please call now','http://www.reddit.com' ],
'link3':['http://www.stackoverflow.com~|~http://www.ebay.com', 'http://www.imdb.com', 'http://www.google.co.uk','more random text that could be really long and annoying','over the hills and through the woods']
})
amount = url_df.count(axis='columns').sum()
def url_exists(url):
r = requests.get(url, stream=True)
if r.status_code == 200:
return True
else:
return False
start = time.time()
for col in url_df.columns:
for i in url_df[col]:
if i:
try:
url_exists(i)
except:
print('Invalid URL', i)
continue
else:
print('Blank', i)
end = time.time()
print("Took {} seconds to pull {} websites.".format(end - start, amount))
PseudoCode using "new" way:
import asyncio
import aiohttp
import pandas as pd
#real df is 30 columns wide, 200k+ rows long
url_df = pd.DataFrame({
'ID':[1,2,5,25,26],
'link1':['apple', 'www.google.com', 'gm@yahoo.com', 'http://www.youtube.com', '888-555-5556 Ryan Parkes rp@abc.io'],
'link2':['http://www.bing.com','http://www.linkedin.com','',' please call now','http://www.reddit.com' ],
'link3':['http://www.stackoverflow.com~|~http://www.ebay.com', 'http://www.imdb.com', 'http://www.google.co.uk','more random text that could be really long and annoying','over the hills and through the woods']
})
async def get(url):
try:
async with aiohttp.ClientSession() as session:
async with session.get(url=url) as response:
resp = await response.read()
print("Successfully got url {} with response of length {}.".format(url, len(resp)))
except Exception as e:
print("Unable to get url {} due to {}.".format(url, e.__class__))
async def main(urls, amount):
ret = await asyncio.gather(*[get(url) for url in urls])
print("Finalized all. ret is a list of len {} outputs.".format(len(ret)))
amount = url_df.count(axis='columns').sum()
start = time.time()
asyncio.run(main(url_df, amount))
end = time.time()
print("Took {} seconds to pull {} websites.".format(end - start, amount))
#Using a list of websites as demo data:
websites = """https://www.youtube.com
https://www.facebook.com
https://www.baidu.com
https://www.yahoo.com
https://www.amazon.com
https://www.wikipedia.org
http://www.qq.com
https://www.google.co.in
https://www.twitter.com
https://www.live.com
http://www.taobao.com
https://www.bing.com
https://www.instagram.com
http://www.weibo.com
http://www.sina.com.cn
https://www.linkedin.com
http://www.yahoo.co.jp
http://www.msn.com
http://www.uol.com.br
https://www.google.de
http://www.yandex.ru
http://www.hao123.com
https://www.google.co.uk
https://www.reddit.com
https://www.ebay.com
https://www.google.fr
https://www.t.co
http://www.tmall.com
http://www.google.com.br
https://www.360.cn
http://www.sohu.com
https://www.amazon.co.jp
http://www.pinterest.com
https://www.netflix.com
http://www.google.it
https://www.google.ru
https://www.microsoft.com
http://www.google.es
https://www.wordpress.com
http://www.gmw.cn
https://www.tumblr.com
http://www.paypal.com
http://www.blogspot.com
http://www.imgur.com
https://www.stackoverflow.com
https://www.aliexpress.com
https://www.naver.com
http://www.ok.ru
https://www.apple.com
http://www.github.com
http://www.chinadaily.com.cn
http://www.imdb.com
https://www.google.co.kr
http://www.fc2.com
http://www.jd.com
http://www.blogger.com
http://www.163.com
http://www.google.ca
https://www.whatsapp.com
https://www.amazon.in
http://www.office.com
http://www.tianya.cn
http://www.google.co.id
http://www.youku.com
https://www.example.com
http://www.craigslist.org
https://www.amazon.de
http://www.nicovideo.jp
https://www.google.pl
http://www.soso.com
http://www.bilibili.com
http://www.dropbox.com
http://www.xinhuanet.com
http://www.outbrain.com
http://www.pixnet.net
http://www.alibaba.com
http://www.alipay.com
http://www.chrome.com
http://www.booking.com
http://www.googleusercontent.com
http://www.google.com.au
http://www.popads.net
http://www.cntv.cn
http://www.zhihu.com
https://www.amazon.co.uk
http://www.diply.com
http://www.coccoc.com
https://www.cnn.com
http://www.bbc.co.uk
https://www.twitch.tv
https://www.wikia.com
http://www.google.co.th
http://www.go.com
https://www.google.com.ph
http://www.doubleclick.net
http://www.onet.pl
http://www.googleadservices.com
http://www.accuweather.com
http://www.googleweblight.com
http://www.answers.yahoo.com"""
urls = websites.split("\n")
amount = len(urls)
asyncio.run(main(urls, amount))
EDIT:
The following works for iterating through the dataframe. Now to figure out how to store the output I want and then deal with the delimited list.
async def get(url):
try:
async with aiohttp.ClientSession() as session:
async with session.get(url=url) as response:
resp = await response.read()
print("Successfully got url {} with response of length {}.".format(url, len(resp)))
except Exception as e:
print("Unable to get url {} due to {}.".format(url, e.__class__))
async def main(urls):
ret = await asyncio.gather(*[get(url) for col in urls.columns #for each column in the dataframe
for url in urls[col] #for each row in the column
if url]) #if the item isn't null
print("Finalized all. ret is a list of len {} outputs.".format(len(ret)))outputs.".format(len(ret)))
asyncio.run(main(urls))
UPDATE 8/17:
After doing some validation and dealing with timeout and session issues, i have managed to process around 800k fields containing 250k possible links in right at 2 hours. I'm not sure if this is considered "good"?
From here, I still don't know how to incorporate handling delimited fields, or how to pass the extra data like ID and column name through the functions to the desired output.
import asyncio, aiohttp, time, pandas as pd
from validator_collection import checkers
url_df = pd.DataFrame({
'ID':[1,2,5,25,26],
'link1':['apple', 'www.google.com', 'gm@yahoo.com', 'http://www.youtube.com', '888-555-5556 Ryan Parkes rp@abc.io'],
'link2':['http://www.bing.com','http://www.linkedin.com','',' please call now','http://www.reddit.com' ],
'link3':['http://www.stackoverflow.com~|~http://www.ebay.com', 'http://www.imdb.com', 'http://www.google.co.uk','more random text that could be really long and annoying','over the hills and through the woods']
})
async def get(url, sem, session):
try:
async with sem, session.get(url=url,raise_for_status=True, timeout=20) as response:
resp = await response.read()
print("Successfully got url {} with response of length {}.".format(url, len(resp)))
except Exception as e:
print("Unable to get url {} due to {}.".format(url, e.__class__))
async def main(urls):
sem = asyncio.BoundedSemaphore(50)
async with aiohttp.ClientSession() as session:
ret = await asyncio.gather(*[get(url, sem, session) for col in urls.columns #for each column in the dataframe
for url in urls[col] #for each row in the column
if url #if the item isn't null
if checkers.is_url(url)==True]) #if url is valid
print("Finalized all. ret is a list of len {} outputs.".format(len(ret)))
amount = url_df.count(axis='columns').sum()
start = time.time()
asyncio.run(main(url_df))
end = time.time()
print("Took {} seconds to pull {} websites.".format(end - start, amount))