Data Cleaning Based on URL

This notebook was mostly for testing and looking at why articles of other languages ended up in. Most of them seem to have URLs that have endings that indicate this, so at the bottom there is a python function which can verify if a URL should be inserted into the datawarehouse. A simple True False check should account for most of the foreign language articles

TODO

Still need to explore ways to clean the body articles


In [43]:
import pandas as pd
import psycopg2

In [19]:
conn = psycopg2.connect("dbname='cap' user='postgres' host='ec2-35-163-99-253.us-west-2.compute.amazonaws.com' port=9000 password ='secret'")
cursor = conn.cursor()

cursor.execute("SELECT * FROM articles limit 10000") ##This is limited for testing (don't want to query all articles every time I make a change)

data = cursor.fetchall()
data = pd.DataFrame(data)
data.columns = ['site', 'title','author','secondary_authors','published_on','accessed_on','url','body' ,'html','newspaper_keywords','newspaper_summary','id']

In [23]:
data


Out[23]:
site title author secondary_authors published_on accessed_on url body html newspaper_keywords newspaper_summary id
0 Tech Cruch Wayfair takes on Pinterest with its own visual... Sarah Perez 2017-05-16 2017-05-16 12:31:10.595570 https://techcrunch.com/2017/05/16/wayfair-take... Pinterest and Google’s recent moves to leverag... <div><img src="https://tctechcrunch2011.files.... {visual,products,wayfair,wayfairs,feature,shop... Pinterest and Google’s recent moves to leverag... 65756
1 Tech Cruch CreditHero wants to help fix credit scores for... Matthew Lynley 2017-05-15 2017-05-16 12:31:23.991562 https://techcrunch.com/2017/05/15/credithero-w... With apps like Robinhood and services like Wea... <div><img src="https://tctechcrunch2011.files.... {user,wants,afraid,credit,improve,point,fix,ex... With apps like Robinhood and services like Wea... 65757
2 Tech Cruch DeepMind NHS health data deal had ‘no lawful b... Natasha Lomas 2017-05-15 2017-05-16 12:31:30.334092 https://techcrunch.com/2017/05/15/deepmind-nhs... DeepMind’s inaugural data-sharing deal with th... <div><img src="https://tctechcrunch2011.files.... {letter,ndg,royal,patient,care,deal,deepmind,s... DeepMind’s inaugural data-sharing deal with th... 65758
3 The Next Web Have questions for your favorite TNW Conferenc... Caroline Nisenson 2017-05-16 2017-05-16 12:32:08.105026 https://thenextweb.com/insider/2017/05/16/ques... Got a question for one or more of the upcoming... <div><p>Got a question for one or more of the ... {conference,questions,covered,answers,youre,fa... Look no further than our dedicated TNW Answers... 65759
4 The Next Web How Microsoft wants us all to get creative wit... Alice Bonasio ['Technology Writer For', 'Seth Godin'] 2017-05-16 2017-05-16 12:32:12.437484 https://thenextweb.com/contributors/2017/05/16... It is hardly surprising that Artificial Intell... <div><p>It is hardly surprising that Artificia... {microsofts,ai,wants,artificial,vision,service... It is hardly surprising that Artificial Intell... 65760
5 The Next Web Making It in the US – Selling B2B from Lithuania Justas Markus ['Founder Ceo'] 2017-05-16 2017-05-16 12:32:18.084658 https://thenextweb.com/contributors/2017/05/16... Tired of reading about startup success stories... <div><p>Tired of reading about startup success... {business,b2b,lithuanian,efforts,market,team,t... Companies are easy to talk to, and that helps ... 65761
6 The Next Web Someone posted the codes to every United cockp... Rachel Kaser 2017-05-15 2017-05-16 12:32:23.376356 https://thenextweb.com/insider/2017/05/15/some... Over the weekend, someone inadvertently leaked... <div><p>Over the weekend, someone inadvertentl... {pilots,united,online,breach,door,posted,inadv... Over the weekend, someone inadvertently leaked... 65762
7 The Next Web Disney film held for Bitcoin ransom by hackers Rachel Kaser 2017-05-16 2017-05-16 12:32:26.749146 https://thenextweb.com/insider/2017/05/16/disn... It looks like Disney’s latest film about pirat... <div><p>It looks like Disney&#8217;s latest fi... {hollywood,pirates,film,release,unless,hackers... It looks like Disney’s latest film about pirat... 65763
8 The Next Web Local digital marketing is more important than... John Doherty ['John Doherty Is The Founder Of'] 2017-05-16 2017-05-16 12:32:31.333029 https://thenextweb.com/marketing-seo/2017/05/1... I remember when I started in digital marketing... <div><p dir="ltr">I remember when I started in... {digital,business,marketing,facebook,amazon,wo... The year of local digital marketing?\nSmall lo... 65764
9 The Next Web 4 reasons you should create long-form blog con... Sabih Javed 2017-05-16 2017-05-16 12:32:36.959040 https://thenextweb.com/contributors/2017/05/16... Are you struggling to rank individual blog pos... <div><p>Are you struggling to rank individual ... {user,content,articles,words,article,blog,stud... However, the average word count for long-form ... 65765
10 The Next Web Why your recruiters should think more like mar... Katie Burke ['Katie Burke Is The Director Of Media', 'Anal... 2017-05-16 2017-05-16 12:32:40.578184 https://thenextweb.com/contributors/2017/05/16... There’s one big thing that keeps me up at nigh... <div><p>There&#8217;s one big thing that keeps... {candidates,talent,social,marketers,inbound,co... Amazing engineers, marketers, and salespeople ... 65766
11 The Next Web FCC Chairman Ajit Pai reading Mean Tweets is j... Rachel Kaser 2017-05-15 2017-05-16 12:32:44.935299 https://thenextweb.com/insider/2017/05/15/fcc-... FCC Chairman Ajit Pai is attempting to win ove... <div><p>FCC Chairman Ajit Pai is attempting to... {mean,video,cringeworthy,little,reading,oliver... FCC Chairman Ajit Pai is attempting to win ove... 65767
12 The Next Web Local digital marketing is more important than... John Doherty ['John Doherty Is The Founder Of'] 2017-05-16 2017-05-16 12:32:50.604340 https://thenextweb.com/marketing-seo/2017/05/1... I remember when I started in digital marketing... <div><p dir="ltr">I remember when I started in... {digital,business,marketing,facebook,amazon,wo... The year of local digital marketing?\nSmall lo... 65768
13 The Next Web Have questions for your favorite TNW Conferenc... Caroline Nisenson 2017-05-16 2017-05-16 12:33:00.083177 https://thenextweb.com/insider/2017/05/16/ques... Got a question for one or more of the upcoming... <div><p>Got a question for one or more of the ... {conference,questions,covered,answers,youre,fa... Look no further than our dedicated TNW Answers... 65769
14 The Next Web BlackBerry KEYone review: Yes, you still want ... Matthew Hughes 2017-05-16 2017-05-16 12:33:06.540268 https://thenextweb.com/gadgets/2017/05/16/blac... The smartphone world has all but moved on from... <div><p>The smartphone world has all but moved... {keyboard,keyone,2017,phone,physical,bit,black... Excluding the physical keyboard (which we’ll g... 65770
15 The Next Web How Microsoft wants us all to get creative wit... Alice Bonasio ['Technology Writer For'] 2017-05-16 2017-05-16 12:33:12.894386 https://thenextweb.com/contributors/2017/05/16... It is hardly surprising that Artificial Intell... <div><p>It is hardly surprising that Artificia... {microsofts,ai,wants,artificial,vision,service... It is hardly surprising that Artificial Intell... 65771
16 The Next Web Fake WhatsApp.com URL gets users to install ad... Napier Lopez 2017-05-15 2017-05-16 12:33:16.327793 https://thenextweb.com/google/2017/05/15/fake-... Next time someone links you to whatsapp.com, m... <div><p>Next time someone links you to <a href... {fake,web,youre,urls,visit,users,url,whatsappc... There’s some adware currently circulating arou... 65772
17 The Next Web 4 reasons you should create long-form blog con... Sabih Javed 2017-05-16 2017-05-16 12:33:21.790541 https://thenextweb.com/contributors/2017/05/16... Are you struggling to rank individual blog pos... <div><p>Are you struggling to rank individual ... {user,content,articles,words,article,blog,stud... However, the average word count for long-form ... 65773
18 The Next Web Microsoft Fluent Design System: Breaking down ... Napier Lopez 2017-05-15 2017-05-16 12:33:25.289054 https://thenextweb.com/microsoft/2017/05/15/mi... Windows 10 is getting a huge makeover that’s s... <div><p><a href="https://thenextweb.com/topic/... {fluent,acrylic,design,10s,apps,youre,differen... It helps establish a relationship between UI e... 65774
19 The Next Web Making It in the US – Selling B2B from Lithuania Justas Markus ['Founder Ceo'] 2017-05-16 2017-05-16 12:33:28.739959 https://thenextweb.com/contributors/2017/05/16... Tired of reading about startup success stories... <div><p>Tired of reading about startup success... {business,b2b,lithuanian,efforts,market,team,t... Companies are easy to talk to, and that helps ... 65775
20 The Next Web Have questions for your favorite TNW Conferenc... Caroline Nisenson 2017-05-16 2017-05-16 12:33:35.093440 https://thenextweb.com/insider/2017/05/16/ques... Got a question for one or more of the upcoming... <div><p>Got a question for one or more of the ... {conference,questions,covered,answers,youre,fa... Look no further than our dedicated TNW Answers... 65776
21 The Next Web New Super Nintendo game coming out after almos... Rachel Kaser 2017-05-15 2017-05-16 12:33:38.432337 https://thenextweb.com/gaming/2017/05/15/new-s... It must be Opposite Day 2017, because someone ... <div><p>It must be Opposite Day 2017, because ... {zero,shubibinman,choujin,physical,20,game,mak... It must be Opposite Day 2017, because someone ... 65777
22 CNET Why the weird lack of 'Twin Peaks' trailers is... None [] None 2017-05-20 11:45:58.692417 https://www.cnet.com/news/twin-peaks-david-lyn... Enlarge Image Suzanne Tenner/Showtime In a wo... <div><span><img src="https://cnet1.cbsistatic.... {good,twin,lack,series,weird,thing,peaks,lynch... "Twin Peaks" has always been about what lurks ... 72765
23 The Next Web Making It in the US – Selling B2B from Lithuania Justas Markus ['Founder Ceo'] 2017-05-16 2017-05-16 12:33:44.022044 https://thenextweb.com/contributors/2017/05/16... Tired of reading about startup success stories... <div><p>Tired of reading about startup success... {business,b2b,lithuanian,efforts,market,team,t... Companies are easy to talk to, and that helps ... 65778
24 The Next Web So you’ve launched a product, now what? Adam Fine 2017-05-16 2017-05-16 12:33:49.392566 https://thenextweb.com/contributors/2017/05/16... Congratulations. After the long, arduous, idea... <div><p>Congratulations. After the long, arduo... {feedback,youve,brand,product,work,multiple,he... While the perfect world scenario would have yo... 65779
25 The Next Web Throttle Pro dumps the spam and junk from your... Tnw Deals ['Hot Deals Courtesy Of The Next Web.'] 2017-05-15 2017-05-16 12:33:55.832137 https://thenextweb.com/offers/2017/05/15/throt... We’re all under constant assault from spam ema... <div><p>We&#8217;re all under constant assault... {inbox,service,single,junk,emails,spam,email,c... Throttle Pro takes a serious approach to weedi... 65780
26 The Next Web PropTech firm YOPA closes £15m Series B, bring... Yessi Bello Perez ['Senior Reporter', 'Tech City News'] 2017-05-16 2017-05-16 12:34:01.586075 http://techcitynews.com/2017/05/16/proptech-yo... London-based PropTech firm YOPA has raised £15... <div><p>London-based PropTech firm YOPA has ra... {31m,total,closes,series,estate,savills,brings... London-based PropTech firm YOPA has raised £15... 65781
27 The Next Web Why your recruiters should think more like mar... Katie Burke ['Katie Burke Is The Director Of Media', 'Anal... 2017-05-16 2017-05-16 12:34:08.099545 https://thenextweb.com/contributors/2017/05/16... There’s one big thing that keeps me up at nigh... <div><p>There&#8217;s one big thing that keeps... {candidates,talent,social,marketers,inbound,co... Amazing engineers, marketers, and salespeople ... 65782
28 The Next Web Mintigo raises $10 million to help B2B firms t... Paul Sawers [] 2017-05-15 2017-05-16 12:34:12.268310 https://venturebeat.com/2017/05/15/mintigo-rai... Mintigo, an enterprise-focused predictive mark... <div><p><a href="http://www.mintigo.com">Minti... {million,raises,b2b,mintigo,partners,marketing... Mintigo, an enterprise-focused predictive mark... 65783
29 The Next Web So you’ve launched a product, now what? Adam Fine 2017-05-16 2017-05-16 12:34:16.701506 https://thenextweb.com/contributors/2017/05/16... Congratulations. After the long, arduous, idea... <div><p>Congratulations. After the long, arduo... {feedback,youve,brand,product,work,multiple,he... While the perfect world scenario would have yo... 65784
... ... ... ... ... ... ... ... ... ... ... ... ...
9970 NBC Juanes Unfiltered: Why Hasn’t He Sung with Sha... Jack Rico [] None 2017-05-23 09:36:11.854998 http://www.nbcnews.com/news/latino/juanes-unfi... NEW YORK — Steps from the gleaming lights of T... <div><p> NEW YORK &#8212; Steps from the gleam... {latin,reggaeton,album,unfiltered,singing,pop,... Why Juanes hasn't collaborated with ShakiraIn ... 75728
9971 NBC 'Underwraps' builds first agency for Muslim, m... Kristi Eaton [] None 2017-05-23 09:36:41.797219 http://www.nbcnews.com/news/asian-america/unde... As a Muslim fashion model, Azizah Hosein's car... <div><p> As a Muslim fashion model, Azizah Hos... {agency,muslim,model,builds,underwraps,models,... As a Muslim fashion model, Azizah Hosein's car... 75729
9972 NBC Manafort, Stone Turn Over Russia Documents to ... Ken Dilanian [] None 2017-05-23 09:36:46.317050 http://www.nbcnews.com/news/us-news/manafort-s... Two former associates of President Trump — Pau... <div><p> Two former associates of President Tr... {flynn,source,committee,subpoena,senate,stone,... Two former associates of President Trump — Pau... 75730
9973 NBC Trump Trip: President Meets With Palestinian P... F. Brinley Bruton [] None 2017-05-23 09:37:06.374374 http://www.nbcnews.com/storyline/trump-s-first... Donald Trump met with Palestinian Authority Pr... <div><p> Donald Trump met with Palestinian Aut... {president,israeli,bethlehem,secretary,state,p... Donald Trump met with Palestinian Authority Pr... 75731
9974 NBC Texas Senate Approves ‘Religious Refusal’ Adop... Associated Press [] None 2017-05-23 09:37:11.925010 http://www.nbcnews.com/feature/nbc-out/texas-s... AUSTIN, Texas — Texas Republicans pushed the s... <div><p> AUSTIN, Texas &#8212; Texas Republica... {adoption,agencies,approves,bill,groups,refusa... AUSTIN, Texas — Texas Republicans pushed the s... 75732
9975 NBC Manchester Arena Suicide Bombing: 22 Die at Ar... Andrew Blankstein ['Robert Windrem', 'Alex Johnson', 'Tom Winter'] None 2017-05-23 09:37:42.359636 http://www.nbcnews.com/news/world/deaths-injur... Children were among 22 people killed in a suic... <div><p> Children were among 22 people killed ... {incident,explosion,grande,terrorist,victims,s... Children were among 22 people killed in a suic... 75733
9976 NBC See how this grandma's garage was transformed ... Julie Pennell [] None 2017-05-23 09:37:46.087121 http://www.today.com/home/grandma-s-200-square... share tweet pin email Home is where the heart... <div><p> ... {garage,cutest,movementtake,rubiotake,carey,ti... Slideshow Photos Steve Niedorf Take a tour of ... 75734
9977 NBC Conservative Reps Urge Trump to Fire NIH Chief... Maggie Fox [] None 2017-05-23 09:37:49.595825 http://www.nbcnews.com/health/health-news/cons... A group of conservative members of Congress wr... <div><p> A group of conservative members of Co... {private,cell,nih,reps,embryos,dr,cells,used,u... They specifically object to Collins's support ... 75735
9978 NBC U.S. Offers Six Month Extensions to Some Haiti... The Associated Press ['Ben Remaly'] None 2017-05-23 09:38:10.274386 http://www.nbcnews.com/news/us-news/u-s-offers... WASHINGTON — The Trump administration said Mon... <div><p> WASHINGTON &#8212; The Trump administ... {affairs,recipients,program,haitian,advises,ha... Senior officials at the Department of Homeland... 75736
9979 NBC WHO Spends More on Travel Than on AIDS, Malaria Associated Press [] None 2017-05-23 09:38:16.599279 http://www.nbcnews.com/health/health-news/who-... Dr. Margaret Chan, director-general of the Wor... <div><p> Dr. Margaret Chan, director-general o... {aids,agency,chan,million,costs,spends,class,t... Despite WHO's numerous travel regulations, Jef... 75737
9980 NBC Kim Jong Un After Missile Test: ‘The Entire Wo... Eoghan Macguire [] None 2017-05-23 09:38:27.300335 http://www.nbcnews.com/news/north-korea/kim-jo... North Korea has disclosed a series of images o... <div><p> North Korea has disclosed a series of... {koreas,earth,missile,looks,beautiful,ballisti... KCNA added: "Viewing the images of the Earth b... 75738
9981 NBC Death threats may prompt Thunder’s Enes Kanter... None [] 2017-05-22 2017-05-23 09:38:31.948151 http://nba.nbcsports.com/2017/05/22/death-thre... Oklahoma City center Enes Kanter said Monday h... <div><p class="article-inline-ad" id="article-... {threats,kanter,controlling,enes,citizen,coup,... Oklahoma City center Enes Kanter said Monday h... 75739
9982 NBC Instagram Ranked Worst for Young People’s Ment... Ben Popken [] None 2017-05-23 09:38:38.268616 http://www.nbcnews.com/business/consumer/insta... A new study says that Instagram is the worst s... <div><p> A new study says that Instagram is th... {terms,social,worst,young,instagram,mental,ran... A new study says that Instagram is the worst s... 75740
9983 NBC 'Alien: Covenant' offers ghoulish fun but 'Tin... Seth Shostak [] None 2017-05-23 09:38:47.942448 http://www.nbcnews.com/mach/space/alien-covena... They're baaaaack, and as peevish as ever. Wel... <div><p> They're baaaaack, and as peevish as e... {ridley,movie,planet,aliens,scotts,tinsel,cove... To say that the nasty aliens in Ridley Scott's... 75741
9984 NBC 'Fixer Upper' star Chip Gaines says he's proud... Ree Hines [] None 2017-05-23 09:38:53.968628 http://www.today.com/home/fixer-upper-star-chi... share tweet pin email Body-shaming doesn't al... <div><p> ... {head,hes,good,stevens,dad,rockin,shape,star,t... That's why "Fixer Upper" star Chip Gaines stop... 75742
9985 NBC 13 home brands you need to know now (that aren... Chassie Post [] None 2017-05-23 09:39:15.491395 http://www.today.com/home/where-buy-home-organ... share tweet pin email Looking for some stylis... <div><p> ... {ikea,z,storemoma,brands,basket,know,magnolia,... Style expert Chassie Post ventured deep into t... 75743
9986 NBC Online Psychotherapy Start-Up Talkspace Is Boo... Michelle Castillo [] None 2017-05-23 09:39:18.631281 http://www.nbcnews.com/tech/tech-news/online-p... Business has been booming for online counselin... <div><p> Business has been booming for online ... {startup,frank,talkspace,stress,online,psychot... Business has been booming for online counselin... 75744
9987 NBC Director Alejandro González Iñárritu’s Virtual... Associated Press [] None 2017-05-23 09:39:22.013348 http://www.nbcnews.com/news/latino/director-al... You walk along a high metal fence that once di... <div><p> You walk along a high metal fence tha... {immigrants,cannes,border,mexico,iñárritus,fil... This is part of Alejandro Iñárritu's "Carne y ... 75745
9988 WashingtonPost Carolyn Hax: You’re not pregnant yet, so do mo... Carolyn Hax Started Her Advice Column In 2017-05-22 2017-05-23 09:44:47.653400 https://www.washingtonpost.com/lifestyle/style... Adapted from a recent online discussion. Hi, ... <div> <p class="intro"> <a href="https://live.... {getting,pushing,baby,hax,carolyn,ive,youre,re... Hi, Carolyn: I’ve been trying to get pregnant ... 75778
9989 NBC Trump Budget Would Cut Safety Net Programs, Bo... Benjy Sarlin [] None 2017-05-23 09:39:28.184729 http://www.nbcnews.com/politics/white-house/tr... President Donald Trump's 2018 budget proposal ... <div><p> President Donald Trump's 2018 budget ... {social,billion,safety,programs,tax,net,cut,ho... President Donald Trump's 2018 budget proposal ... 75746
9990 NBC It’s Not Just the Assembly Lines — Robots Coul... Ben Popken [] None 2017-05-23 09:39:44.540474 http://www.nbcnews.com/business/business-news/... The death of retail is about to become a mass ... <div><p> The death of retail is about to becom... {wont,stores,lines,assembly,shoppers,workers,j... A new study finds that robots could destroy up... 75747
9991 NBC OpEd: The Punishment of Deanna and Mya Cook is... Rep. Bonnie Watson Coleman [] None 2017-05-23 09:40:03.520344 http://www.nbcnews.com/news/nbcblk/oped-punish... This week, we celebrate the anniversary of Bro... <div><p> This week, we celebrate the anniversa... {school,deanna,girls,oped,black,mya,bigger,bra... The latest example is twins Deanna and Mya Coo... 75748
9992 NBC Botulism Confirmed in California Nacho Cheese ... Maggie Fox [] None 2017-05-23 09:40:07.068222 http://www.nbcnews.com/health/health-news/botu... Gas station nacho cheese that sickened 10 peop... <div><p> Gas station nacho cheese that sickene... {cheese,contaminated,confirmed,nacho,food,outb... Gas station nacho cheese that sickened 10 peop... 75749
9993 NBC Five Asian Players Making Waves in Major Leagu... Gabe Salgado [] None 2017-05-23 09:40:20.724381 http://www.nbcnews.com/news/asian-america/five... Major League Baseball's history of diversity, ... <div><p> Major League Baseball's history of di... {games,hes,cubs,waves,2017,players,baseball,as... Major League Baseball's history of diversity, ... 75750
9994 NBC Homeland Security: No Credible Threat at U.S. ... Nbc News ['Associated Press'] None 2017-05-23 09:40:34.658557 http://www.nbcnews.com/storyline/manchester-co... The Department of Homeland Security said there... <div><p> The Department of Homeland Security s... {explosion,terrorist,security,credible,public,... The Department of Homeland Security said there... 75751
9995 NBC Flynn ‘Lied to Investigators’ About Russia Tri... Alex Moe ['Courtney Kube', 'Ken Dilanian', 'Kasie Hunt'] None 2017-05-23 09:41:03.773846 http://www.nbcnews.com/news/us-news/flynn-lied... Former National Security Adviser Michael Flynn... <div><p> Former National Security Adviser Mich... {lied,flynn,russian,asked,rt,house,trip,invest... "Specifically, the Committee has obtained a Re... 75752
9996 NBC Trump: I Never Said ‘Israel’ in Meeting With R... Ali Vitali [] None 2017-05-23 09:41:32.652062 http://www.nbcnews.com/storyline/trump-s-first... JERUSALEM — President Donald Trump said Monday... <div><p> JERUSALEM &#8212; President Donald Tr... {israel,president,reporters,source,minister,ru... JERUSALEM — President Donald Trump said Monday... 75753
9997 NBC Ford CEO Fields Ousted in Massive Shake-Up Paul A. Eisenstein [] None 2017-05-23 09:41:46.398190 http://www.nbcnews.com/business/autos/ford-ceo... Facing increasing pressure as its sales, earni... <div><p> Facing increasing pressure as its sal... {shakeup,massive,sales,head,ousted,price,ceo,t... Facing increasing pressure as its sales, earni... 75754
9998 NBC Israeli-Palestinian Settlement Debate Complica... Tal Axelrod [] None 2017-05-23 09:41:49.624091 http://www.nbcnews.com/storyline/trump-s-first... On Tuesday, President Trump's motorcade will l... <div><p> On Tuesday, President Trump's motorca... {settlements,israelipalestinian,israeli,trumps... On Tuesday, President Trump's motorcade will l... 75755
9999 NBC Witnesses Describe Chaos After Manchester Aren... Tim Stelloh ['E.D. Cauchi', 'Ali Gostanian'] None 2017-05-23 09:42:05.787722 http://www.nbcnews.com/storyline/manchester-co... Thousands fled in a state of panic after a sui... <div><p> Thousands fled in a state of panic af... {explosion,website,screaming,witnesses,phone,t... Play FacebookTwitterEmbed Witness recounts pan... 75756

10000 rows × 12 columns


In [29]:
from collections import defaultdict

url_endings = defaultdict(list)

for i, url in enumerate(data['url']):
    url = url.split('//')[-1].split('/')[0].split('.')[-1]
    url_endings[url].append(i)
    
#print(url_endings)

def get_articles_by_end(ending):
    indices = url_endings[ending]
    articles = data.iloc[indices]
    return articles

articles = get_articles_by_end('kr')
articles['body']


Out[29]:
1293    ADVERTISEMENT  5월 17일, 피우진 예비역 중령이 국가보훈처장에 임명됐...
1295    WASHINGTON, USA - MAY 10: US President Donald ...
1298    ADVERTISEMENT  5월 17일, 피우진 예비역 중령이 국가보훈처장에 임명됐...
1302    게시됨: 2017년 05월 16일 20시 50분 KST  게시됨: 2017년 05월...
1304    게시됨: 2017년 05월 17일 18시 29분 KST  게시됨: 2017년 05월...
1311    이집트 맥도날드에서 판매하는 이 햄버거에는 '팔라펠'이 패티 대신 들어 있다. '팔...
1315    ADVERTISEMENT    문명고 입학식이 열린 지난 3월2일 오전 경북 경산시...
1319    HOLLYWOOD, : Former US gold medal decathlete B...
1320    게시됨: 2017년 05월 17일 17시 00분 KST  게시됨: 2017년 05월...
1329    게시됨: 2017년 05월 17일 15시 47분 KST  게시됨: 2017년 05월...
1335    ADVERTISEMENT  피우진 예비역 중령이 문재인 정부의 첫 국가보훈처장으로 ...
1353    ADVERTISEMENT  5월 17일, 피우진 예비역 중령이 국가보훈처장에 임명됐...
1380    게시됨: 2017년 05월 17일 15시 18분 KST  게시됨: 2017년 05월...
1387    WASHINGTON, USA - MAY 10: US President Donald ...
1390    ADVERTISEMENT  김동철 국민의당 신임 원내대표가 16일 민주당과 통합론에...
1391    게시됨: 2017년 05월 17일 09시 54분 KST  게시됨: 2017년 05월...
1392    ADVERTISEMENT  피우진 예비역 중령이 문재인 정부의 첫 국가보훈처장으로 ...
1400    게시됨: 2017년 05월 17일 10시 31분 KST  게시됨: 2017년 05월...
1424    ADVERTISEMENT  박근혜 전 대통령과 최순실씨의 ‘연결고리’인 이영선 청와...
1425    ADVERTISEMENT  워싱턴포스트는 지난 15일 도널드 트럼프 미국 대통령이 ...
1426    이집트 맥도날드에서 판매하는 이 햄버거에는 '팔라펠'이 패티 대신 들어 있다. '팔...
1431    게시됨: 2017년 05월 17일 18시 29분 KST  게시됨: 2017년 05월...
1439    게시됨: 2017년 05월 17일 19시 46분 KST  게시됨: 2017년 05월...
1440    게시됨: 2017년 05월 16일 20시 22분 KST  게시됨: 2017년 05월...
1446    ADVERTISEMENT  박근혜 전 대통령과 최순실씨의 ‘연결고리’인 이영선 청와...
1449    게시됨: 2017년 05월 17일 14시 45분 KST  게시됨: 2017년 05월...
1459    ADVERTISEMENT  김동철 국민의당 신임 원내대표가 16일 민주당과 통합론에...
1461    A general view shows Gyeongbokgung palace and ...
1462    ADVERTISEMENT  KBS '개그콘서트' 측이 900회 논란과 관련해 입장을...
1474    게시됨: 2017년 05월 17일 17시 25분 KST  게시됨: 2017년 05월...
                              ...                        
9215    TOPSHOT - This picture taken on May 14, 2017 a...
9219    UNITED NATIONS HEADQUARTERS, NEW YORK, NY, UNI...
9223    게시됨: 2017년 05월 22일 14시 21분 KST  게시됨: 2017년 05월...
9225    ADVERTISEMENT  *영화 '겟아웃'에 대한 스포일러가 있습니다.  조던 필...
9226    ADVERTISEMENT  도널드 트럼프 대통령 탄핵에 대한 심각한 논의가 진행중이...
9240    South Korea's President Lee Myung-bak waves to...
9257    ADVERTISEMENT  *영화 '겟아웃'에 대한 스포일러가 있습니다.  조던 필...
9259    Ivanka Trump is seen at a ceremony where her f...
9262    A worker processes silk at the Kim Jong Suk Si...
9263    게시됨: 2017년 05월 22일 16시 18분 KST  게시됨: 2017년 05월...
9264    Park Geun-hye (L) uses a writing brush to writ...
9268    게시됨: 2017년 05월 22일 16시 27분 KST  게시됨: 2017년 05월...
9269    TOPSHOT - This picture taken on May 14, 2017 a...
9280    ADVERTISEMENT  tvN 드라마 '혼술남녀'의 고 이한빛 PD 측과 CJ ...
9291    A worker processes silk at the Kim Jong Suk Si...
9293    Photocalls Vittorio Zunino Celotto via Getty I...
9295    게시됨: 2017년 05월 22일 17시 12분 KST  게시됨: 2017년 05월...
9297    게시됨: 2017년 05월 22일 17시 30분 KST  게시됨: 2017년 05월...
9303    ADVERTISEMENT  캐나다의 한 항구에서 포착된 영상이 화제가 되고 있다. ...
9309    ADVERTISEMENT  tvN 드라마 '혼술남녀'의 고 이한빛 PD 측과 CJ ...
9319    US President Donald Trump (C) walks away after...
9320    게시됨: 2017년 05월 22일 14시 21분 KST  게시됨: 2017년 05월...
9325    게시됨: 2017년 05월 22일 16시 18분 KST  게시됨: 2017년 05월...
9329    US President Donald Trump takes part in a bila...
9330    Rex Tillerson, U.S. Secretary of State, pauses...
9334    게시됨: 2017년 05월 22일 17시 30분 KST  게시됨: 2017년 05월...
9335    US President Donald Trump (C) walks away after...
9347    게시됨: 2017년 05월 22일 11시 57분 KST  게시됨: 2017년 05월...
9353    ADVERTISEMENT  *영화 '겟아웃'에 대한 스포일러가 있습니다.  조던 필...
9354    게시됨: 2017년 05월 22일 16시 18분 KST  게시됨: 2017년 05월...
Name: body, Length: 201, dtype: object

In [44]:
for key in url_endings:
    print(key)


com

uk
org
au
de
ca
kr
in
jp
fr
es
mx
za
io
google
it
net
edu
gov
gr
co
my
mt

In [5]:
# Want to make sure we get all bad endings
# bad endings tested: kr, cn, ua, dk, ca some french, it, mx, es, fr, de, gr, pk
#articles = get_articles_by_end('gr')
#articles['body']

In [45]:
def is_valid_url( url ):
    invalid_urls = ['kr', 'cn', 'ua', 'dk', 'it', 'mx', 'es', 'fr', 'de', 'gr', 'pk']
    url_ending = url.split('//')[-1].split('/')[0].split('.')[-1]
    if url_ending not in invalid_urls:
        return True
    else:
        return False

In [10]:
print(data['url'][900])
is_valid_url( data['url'][900])


https://www.washingtonpost.com/news/the-fix/wp/2017/04/02/a-judge-rules-trump-may-have-incited-violence-and-trump-again-has-his-own-mouth-to-blame/?tid=pm_pop
Out[10]:
True

In [46]:
not_valid = []
for index, row in data.iterrows():
    if is_valid_url(row['url']) is False:
        not_valid.append(row['id'])

In [50]:
## Delete all articles where ID is in not_valid
len(not_valid)


Out[50]:
557

In [ ]: