MAPpreprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "#this cell outputs a preprocessed main file, a file for building edges, and a file for deduplicating news sources \n",
    "\n",
    "#tables data obtained from dump.sql, syntax converted to sqlite for easy .sqlite export to .csv\n",
    "\n",
    "#read in category tables data \n",
    "dftemp_A = pd.read_csv(\"C:\\\\datasources\\\\CategoryArticle.csv\", sep='|')\n",
    "dftemp_B = pd.read_csv(\"C:\\\\datasources\\\\Category.csv\", sep='|', usecols=['cid','url'])\n",
    "\n",
    "#combine key tables\n",
    "d = pd.Series(dftemp_B.url.values,index=dftemp_B.cid).to_dict()\n",
    "dftemp_A['tag'] = dftemp_A['cid'].map(d)\n",
    "\n",
    "#create multiindex hierarchy\n",
    "dftemp_A.set_index(['ArticleId','cid'], inplace=True)\n",
    "dftemp_A.sort_index(inplace=True)\n",
    "\n",
    "#transform dataframe to dictionary where an ArticleId input returns correct tags list\n",
    "v = dftemp_A.groupby('ArticleId')['tag'].apply(lambda t: list(t)).to_dict()\n",
    "#check it by inserting ArticleId for XXXX in \"print(v[XXXX])\" \n",
    "\n",
    "#create file for building category to store edges in graph\n",
    "dftemp_A.to_csv(\"C:\\\\datasources\\\\tagedges.csv\", sep='|', index=True, encoding='utf-8')\n",
    "\n",
    "#read in raw articles table data\n",
    "dftemp_DB = pd.read_csv(\"C:\\\\datasources\\\\Article.csv\", sep='|', usecols=['ArticleId','Title','PublicationDate','Publication','Links','Description','Priority','url','url2'])\n",
    "\n",
    "#make some unique wtk urls \n",
    "makeurl = dftemp_DB['url'].astype(str)\n",
    "dftemp_DB['wtkURL'] = \"https://www.wanttoknow.info/a-\" + makeurl\n",
    "  \n",
    "#make tags column and populate with dictionary v\n",
    "dftemp_DB['tags'] = dftemp_DB['ArticleId'].map(v)  \n",
    "\n",
    "#check it with something like dftemp_DB.head(12)\n",
    "\n",
    "#send to \"|\" delim .csv as preprocessed file\n",
    "dftemp_DB.to_csv(\"C:\\\\datasources\\\\WTKpreprocessed.csv\", sep='|', index=False, encoding='utf-8')\n",
    "\n",
    "#'mediacondense' list of dictionaries is built for when there are more than 2 variations of a media source\n",
    " \n",
    "mediacondense = []\n",
    "\n",
    "abc = ['ABC News Australia', 'ABC News blog', 'ABC6', 'abcnews.com', 'WCPO - Cincinnatis ABC Affiliate', 'ABC News', 'ABC News blogs', 'ABC News Blog', 'ABC News Good Morning America', 'ABC New', 'ABC News Nightline', 'ABC15', 'ABC News 20', 'abc4.com', 'ABC Action News', 'ABCs Arizona Affiliate','WXYZ - Detroits ABC News Affiliate']\n",
    "mcat1 = \"ABC\"\n",
    "m1 = dict.fromkeys(abc, mcat1)\n",
    "mediacondense.append(m1)\n",
    "\n",
    "ode = ['Ode Magazine, June 2005 Issue', 'Ode Magazine, July 2005 Issue', 'Ode magazine']\n",
    "mcat2 = \"Ode Magazine\"\n",
    "m2 = dict.fromkeys(ode, mcat2)\n",
    "mediacondense.append(m2)\n",
    "\n",
    "nbc = ['NBC Milwaukee Affiliate', 'NBC Chicago', 'NBC Miami', 'NBC Washington', 'NBC Los Angeles', 'NBC Oklahoma City', 'NBC News']\n",
    "mcat3 = \"NBC\"\n",
    "m3 = dict.fromkeys(nbc, mcat3)\n",
    "mediacondense.append(m3)\n",
    "\n",
    "vfr = ['Vanity Fair August 2006 Issue', 'Vanity Fair September 2005 Issue', 'Vanity Fair magazine']\n",
    "mcat4 = \"Vanity Fair\"\n",
    "m4 = dict.fromkeys(vfr, mcat4)\n",
    "mediacondense.append(m4)\n",
    "\n",
    "nyt = ['The New York Times', 'New York Times Blog', 'New York Times blog']\n",
    "mcat5 = \"New York Times\"\n",
    "m5 = dict.fromkeys(nyt, mcat5)\n",
    "mediacondense.append(m5)\n",
    "\n",
    "unw = ['U.S. News and World Report', 'U.S. News & World Report', 'U.S. News & World Report blog', 'US News & World Report magazine', 'US News & World Report']\n",
    "mcat6 = \"US News and World Report\"\n",
    "m6 = dict.fromkeys(unw, mcat6)\n",
    "mediacondense.append(m6)\n",
    "\n",
    "nwk = ['Newsweek magazine', 'Newsweek blog', 'Newsweek Magazine', 'Newsweek magazine blog']\n",
    "mcat7 = \"Newsweek\"\n",
    "m7 = dict.fromkeys(nwk, mcat7)\n",
    "mediacondense.append(m7)\n",
    "\n",
    "bbc = ['BBC Radio', 'BBC News blog', 'BBC Blogs', 'BBC News']\n",
    "mcat8 = \"BBC\"\n",
    "m8 = dict.fromkeys(bbc, mcat8)\n",
    "mediacondense.append(m8)\n",
    "\n",
    "fop = ['Foreign Policy Magazine May', 'Foreign Policy Journal']\n",
    "mcat9 = \"Foreign Policy\"\n",
    "m9 = dict.fromkeys(fop, mcat9)\n",
    "mediacondense.append(m9)\n",
    "\n",
    "wap = ['Washington Post blog', 'washingtonpost.com', 'Washingon Post', 'Washginton Post', 'The Washington Post']\n",
    "mcat10 = \"Washington Post\"\n",
    "m10 = dict.fromkeys(wap, mcat10)\n",
    "mediacondense.append(m10)\n",
    "\n",
    "tlg = ['The Telegraph blogs', 'Daily Telegraph', 'Telegraph']\n",
    "mcat11 = \"The Telegraph\"\n",
    "m11 = dict.fromkeys(tlg, mcat11)\n",
    "mediacondense.append(m11)\n",
    "\n",
    "nsa = ['U.S. National Security Agency Website', 'National Security Agency  Website', 'NSA Technical Journal, Vol. XI', 'National Security Agency  Website, NSA Technical Journal, Vol. XI']\n",
    "mcat12 = \"NSA Website\"\n",
    "m12 = dict.fromkeys(nsa, mcat12)\n",
    "mediacondense.append(m12)\n",
    "\n",
    "msn = ['MSN Money', 'MSN of Australia', 'MSN Canada', 'MSN']\n",
    "mcat13 = \"MSN News\"\n",
    "m13 = dict.fromkeys(msn, mcat13)\n",
    "mediacondense.append(m13)\n",
    "\n",
    "tim = ['Time magazine', 'Time Magazine', 'Time Magazine blog']\n",
    "mcat14 = \"Time\"\n",
    "m14 = dict.fromkeys(tim, mcat14)\n",
    "mediacondense.append(m14)\n",
    "\n",
    "psc = ['Popular Science - March 2007 Issue', 'Popular Science Magazine', 'Popular Science magazine']\n",
    "mcat15 = \"Popular Science\"\n",
    "m15 = dict.fromkeys(psc, mcat15)\n",
    "mediacondense.append(m15)\n",
    "\n",
    "cnn = ['CNN blog', 'CNN Money', 'CNN International', 'CNN The Situation Room', 'CNN Lou Dobbs Tonight', 'CNN Video Clip', 'CNN Larry King Live', 'CNN News']\n",
    "mcat16 = \"CNN\"\n",
    "m16 = dict.fromkeys(cnn, mcat16)\n",
    "mediacondense.append(m16)\n",
    "\n",
    "cbs = ['CBS Las Vegas Affiliate', 'CBS Philly', 'CBS News', 'KCBS', 'CBS Atlanta', 'CBS Affiliate KUTV', 'CBS News Chicago, Associated Press', 'CBS News, Sacramento Affiliate', 'WCBS News - New York CBS Affiliate', 'CBS News 60 Minutes', 'CBS News 60 Minutes Overtime', 'CBS Los Angeles', 'CBS 60 Minutes', 'CBS News blog', 'CBS News, Stockton Affiliate']\n",
    "mcat17 = \"CBS\"\n",
    "m17 = dict.fromkeys(cbs, mcat17)\n",
    "mediacondense.append(m17)\n",
    "\n",
    "yho = ['Yahoo! News', 'Yahoo!', 'Yahoo! Finance', 'Yahoo! News Australia', 'Yahoo News', 'Yahoo Finance']\n",
    "mcat18 = \"Yahoo\"\n",
    "m18 = dict.fromkeys(yho, mcat18)\n",
    "mediacondense.append(m18)\n",
    "\n",
    "wsj = ['The Wall Street Journal', 'Wall Street Journal blog', 'Full Page Ad in Wall Street Journal', 'Wall Street Journal Article by Former FBI Director Louis Freeh', 'Wall Street Journal Blog']\n",
    "mcat19 = \"Wall Street Journal\"\n",
    "m19 = dict.fromkeys(wsj, mcat19)\n",
    "mediacondense.append(m19)\n",
    "\n",
    "fox = ['Fox News Chicago', 'Fox News video clip', 'FOX News', 'Fox 19', 'Fox News Affiliate']\n",
    "mcat20 = \"FOX\"\n",
    "m20 = dict.fromkeys(fox, mcat20)\n",
    "mediacondense.append(m20)\n",
    "\n",
    "icp = ['The Intercept With Glenn Greenwald', 'The Intercept with Glenn Greenwald']\n",
    "mcat21 = \"The Intercept\"\n",
    "m21 = dict.fromkeys(icp, mcat21)\n",
    "mediacondense.append(m21)\n",
    "\n",
    "lat = ['Los Angeles Times blog', 'The Los Angeles Times', 'LA Times']\n",
    "mcat22 = \"Los Angeles Times\"\n",
    "m22 = dict.fromkeys(lat, mcat22)\n",
    "mediacondense.append(m22)\n",
    "\n",
    "pbs = ['PBS Nova Program', 'PBS Frontline', 'PBS, CBS, Fox compilation', 'PBS News', 'PBS Bill Moyers Journal', 'PBS Newshour', 'PBS Blog']\n",
    "mcat23 = \"PBS\"\n",
    "m23 = dict.fromkeys(pbs, mcat23)\n",
    "mediacondense.append(m23)\n",
    "\n",
    "ecn = ['The Economist blog', 'The Economist Magazine', 'The Economist magazine']\n",
    "mcat24 = \"The Economist\"\n",
    "m24 = dict.fromkeys(ecn, mcat24)\n",
    "mediacondense.append(m24)\n",
    "\n",
    "npr = ['NPR All Things Considered', 'National Public Radio', 'NPR News', 'NPR blog', 'NPR Blog']\n",
    "mcat25 = \"NPR\"\n",
    "m25 = dict.fromkeys(npr, mcat25)\n",
    "mediacondense.append(m25)\n",
    "\n",
    "sfc = ['The San Francisco Chronicle', 'San Francisco Chronicle SFs leading newspaper)']\n",
    "mcat26 = \"San Francisco Chronicle\"\n",
    "m26 = dict.fromkeys(sfc, mcat26)\n",
    "mediacondense.append(m26)\n",
    "\n",
    "cbc = ['Canadian Broadcasting Corporation', 'CBC News', 'CBC News [Canadas Public Broadcasting System]']\n",
    "mcat27 = \"CBC\"\n",
    "m27 = dict.fromkeys(cbc, mcat27)\n",
    "mediacondense.append(m27)\n",
    "    \n",
    "frb = ['Forbes Magazine', 'Forbes blog', 'Forbes magazine', 'Forbes.com', 'Forbes.com blog', 'Forbes India Magazine']\n",
    "mcat28 = \"Forbes\"\n",
    "m28 = dict.fromkeys(frb, mcat28)\n",
    "mediacondense.append(m28)\n",
    "    \n",
    "rst = ['Rolling Stone blog', 'Rolling Stone magazine']\n",
    "mcat29 = \"Rolling Stone\"\n",
    "m29 = dict.fromkeys(rst, mcat29)\n",
    "mediacondense.append(m29)\n",
    "    \n",
    "grd = ['A Guardian blog', 'The Guardian blog', 'Guardian']\n",
    "mcat30 = \"The Guardian\"\n",
    "m30 = dict.fromkeys(grd, mcat30)\n",
    "mediacondense.append(m30)\n",
    "    \n",
    "ngc = ['NationalGeographic.com', 'National Geographic October 2004 Issue', 'National Geographic News', 'NationalGeographic.com blog']\n",
    "mcat31 = \"National Geographic\"\n",
    "m31 = dict.fromkeys(ngc, mcat31)\n",
    "mediacondense.append(m31)\n",
    "    \n",
    "mbc = ['MSNBC News', 'MSNBC Today', 'MSNBC: Keith Olbermann blog', 'MSNBC The Rachel Maddow Show']\n",
    "mcat32 = \"MSNBC\"\n",
    "m32 = dict.fromkeys(mbc, mcat32)\n",
    "mediacondense.append(m32)\n",
    "    \n",
    "rut = ['Reuters News Agency', 'Reuters News', 'Reuters Health', 'Reuters blog']\n",
    "mcat33 = \"Reuters\"\n",
    "m33 = dict.fromkeys(rut, mcat33)\n",
    "mediacondense.append(m33)\n",
    "\n",
    "blb = ['Bloomberg News Service', 'Businessweek', 'BusinessWeek', 'Bloomberg Businessweek', 'Bloomberg News']\n",
    "mcat34 = \"Bloomberg\"\n",
    "m34 = dict.fromkeys(blb, mcat34)\n",
    "mediacondense.append(m34)\n",
    "\n",
    "#the list of dictionaries is then turned into a new little dataframe\n",
    "\n",
    "key = []\n",
    "val = []\n",
    "for i in mediacondense:\n",
    "    for k,v in i.items():\n",
    "        key.append(k)\n",
    "        val.append(v)\n",
    "\n",
    "mediakeys = pd.DataFrame({'asis': key,\n",
    "                  'clean': val})\n",
    "\n",
    "#replacement values are mapped from new dataframe to complete main dataframe \n",
    "mediakeys.set_index('asis', inplace=True)\n",
    "\n",
    "mediakeys.to_csv(\"C:\\\\datasources\\\\mediakeys.csv\", sep='|', index=True, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#begin again fresh or after restarting kernel/with preprocessed files \n",
    "from bs4 import BeautifulSoup as bs\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "#read in preprocessed file\n",
    "dfP = pd.read_csv(\"C:\\\\datasources\\\\WTKpreprocessed.csv\", sep='|', encoding='utf-8')\n",
    "\n",
    "#split raw story into summary and note. \n",
    "dfP['Summary'], dfP['Note'] = dfP['Description'].str.split('Note:', 1).str \n",
    "\n",
    "#create unique tag columns to make values quickly mappable for graph generation\n",
    "dfP['tags'] = dfP['tags'].astype(str).apply(lambda v: v.replace('\\'', ''))\n",
    "dfP.tags = dfP.tags.str[1:-1].str.split(',').tolist()\n",
    "dfT = pd.DataFrame(dfP.tags.values.tolist(), dfP.index).add_prefix('tag_')\n",
    "\n",
    "form = lambda f: 'tag_{}'.format(f + 1)\n",
    "pd.DataFrame(\n",
    "    dfP.tags.values.tolist(),\n",
    "    dfP.index, dtype=object\n",
    ").fillna('').rename(columns=form)\n",
    "\n",
    "df = pd.concat([dfP, dfT], axis=1)\n",
    "\n",
    "#add tagcount column for convenient advanced indexing\n",
    "df['tagcount'] = df.tags.apply(lambda l: len(l))\n",
    "\n",
    "#initialize some lists for data extraction and cleaning\n",
    "Quotes = []\n",
    "Summaries = []\n",
    "Note_text = []\n",
    "Adtl_ref_links = []\n",
    "Note_ref_links = []\n",
    "Media_sources = []\n",
    "\n",
    "#set variables up for parsing markup\n",
    "stories = df['Summary'].astype(str).values.tolist()\n",
    "notes = df['Note'].astype(str).values.tolist()\n",
    "medias = df['Publication'].astype(str).values.tolist()\n",
    "\n",
    "#some loops to extract desired text and links\n",
    "for i in stories:\n",
    "    c = bs(i,'lxml')\n",
    "    try:\n",
    "        quote = c.strong.text\n",
    "    except (AttributeError):\n",
    "        quote = c.text.split('. ')[0]\n",
    "    summary = c.text\n",
    "    ref_links = []\n",
    "    for n in c.find_all('a'):\n",
    "        xlinks = n.get('href')\n",
    "        ref_links.append(xlinks)\n",
    "    Quotes.append(quote)\n",
    "    Summaries.append(summary)\n",
    "    Adtl_ref_links.append(ref_links)\n",
    "\n",
    "#switch parser from lxml for notes parsing because different parsers are good for different things    \n",
    "for i in notes:\n",
    "    c = bs(i,'html.parser')\n",
    "    note = c.text    \n",
    "    note_links = []\n",
    "    for n in c.find_all('a'):\n",
    "        nlinks = n.get('href')\n",
    "        note_links.append(nlinks)\n",
    "    Note_text.append(note)\n",
    "    Note_ref_links.append(note_links)\n",
    "\n",
    "#initial cleanup of pub. bs4 282 User Warning may show up because some media sources are urls. no biggie    \n",
    "for i in medias:\n",
    "    c = bs(i,'html.parser')\n",
    "    media = c.text\n",
    "    Media_sources.append(media)\n",
    "    \n",
    "#make new dataframe from extracted data    \n",
    "df3 = pd.DataFrame({'quote': Quotes,\n",
    "                  'description': Summaries,\n",
    "                  'note': Note_text,\n",
    "                  'pub': Media_sources,   \n",
    "                  'Summary_ref_links': Adtl_ref_links,\n",
    "                  'Note_links': Note_ref_links})    \n",
    "\n",
    "#join new dataframe to big raw dataframe\n",
    "dfR = pd.concat([df, df3], axis=1)\n",
    "\n",
    "dfR.drop(['Publication', 'Summary', 'url', 'url2'], axis=1, inplace=True)\n",
    "\n",
    "#begin cleanup of media sources\n",
    "dfR['pub'], dfR['pub2'] = dfR['pub'].str.split('/', 1).str\n",
    "dfR['pub'], dfR['pubdetail'] = dfR['pub'].str.split('(', 1).str\n",
    "dfR['pubdetail'] = dfR['pubdetail'].astype(str).apply(lambda u: u.strip(')'))\n",
    "dfR['pub'] = dfR['pub'].str.strip()\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda u: u.strip('\"'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('\\'',''))\n",
    "\n",
    "#use dfR.pub.unique() with pd.set_option('display.max_colwidth', 1000) to view raw media sources list\n",
    "#dfR['pub'].value_counts()\n",
    "\n",
    "#combine variations of media source into single item\n",
    "\n",
    "#one-off translations are easy, but should eventually be moved to mediacondense list\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Seattle times', 'Seattle Times'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Scientific American Blog', 'Scientific American'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Sacramento Bee', 'Sacramento Bee'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Mother Jones Magazine', 'Mother Jones'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Sydney Mountain Herald', 'Sydney Morning Herald'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Nation magazine', 'The Nation'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Chicago Sun-Times News Group', 'Chicago Sun-Times'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Wired magazine', 'Wired'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The New Yorker magazine', 'The New Yorker'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Fortune magazine', 'Fortune'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Atlantic Monthly', 'The Atlantic'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('Tikkun Magazine - March', 'Tikkun Magazine'))\n",
    "dfR['pub'] = dfR['pub'].astype(str).apply(lambda r: r.replace('The Daily Mail', 'Daily Mail'))\n",
    "\n",
    "#dfR.pub.head(3)\n",
    "\n",
    "#read in preprocessed mediacondense file\n",
    "mkeys = pd.read_csv(\"C:\\\\datasources\\\\mediakeys.csv\", sep='|', usecols=['asis','clean'], encoding='utf-8')\n",
    "\n",
    "mkeys.set_index('asis', inplace=True)\n",
    "\n",
    "dx = pd.Series(mkeys.clean.values,index=mkeys.index).to_dict()\n",
    "\n",
    "dfR['cpub'] = dfR['pub'].map(dx).fillna(dfR['pub'])\n",
    "\n",
    "dfR.head(3)\n",
    "\n",
    "#save full raw dataframe\n",
    "dfR.to_csv(\"C:\\\\datasources\\\\fullraw.csv\", sep='|', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#make the demo map by commenting out dfR.to_csv above and running the following\n",
    "\n",
    "#create new dataframe with final column order, naming, and sort specification\n",
    "dfF = pd.DataFrame(dfR[['ArticleId', 'tags', 'Title', 'quote', 'description', 'Links', 'cpub', 'PublicationDate', 'note', 'wtkURL', 'Priority']].sort_values(by='Priority', ascending=False))\n",
    "\n",
    "dfF.drop(dfF.index[1000:], inplace=True)\n",
    "\n",
    "#convert tags list column to kumu specification for export \n",
    "dfF['tags'] = dfF['tags'].astype(str).apply(lambda t: t.strip('['))\n",
    "dfF['tags'] = dfF['tags'].astype(str).apply(lambda u: u.strip(']'))\n",
    "dfF['tags'] = dfF['tags'].str.replace(',', '|')\n",
    "dfF['tags'] = dfF['tags'].str.replace('\\'', '')\n",
    "\n",
    "dfF.columns = ['Label', 'Tags', 'Title', 'Quote', 'Description', 'SourceLink', 'Publisher', 'Date', 'Note', 'SummaryURL', 'Rating']\n",
    "dfF.insert(loc=1, column='Type', value=\"Report\", allow_duplicates=True)\n",
    "\n",
    "#get labels to match to tags edge list\n",
    "idsused = dfF['Label'].unique()\n",
    "\n",
    "#dfF['Publisher'].value_counts()\n",
    "dfF.shape\n",
    "\n",
    "#read in tag edges after confirming everything looks okay\n",
    "taged = pd.read_csv(\"C:\\\\datasources\\\\tagedges.csv\", sep='|', usecols=['ArticleId','tag'], encoding='utf-8')\n",
    "\n",
    "#then create some correctly shaped element frames and append to main dataframe\n",
    "\n",
    "list=[]\n",
    "p = dfF['Publisher'].unique()\n",
    "for i in p:\n",
    "    list.append([i, \"NewsSource\", '','','','','','','','','',''])\n",
    "dflist=pd.DataFrame(list,columns=['Label', 'Type', 'Tags', 'Title', 'Quote', 'Description', 'SourceLink', 'Publisher', 'Date', 'Note', 'SummaryURL', 'Rating'])\n",
    "\n",
    "L2=[]\n",
    "g = taged['tag'].unique()\n",
    "\n",
    "for i in g:\n",
    "    L2.append([i, \"Topic\", '','','','','','','','','',''])\n",
    "dfL2=pd.DataFrame(L2,columns=['Label', 'Type', 'Tags', 'Title', 'Quote', 'Description', 'SourceLink', 'Publisher', 'Date', 'Note', 'SummaryURL', 'Rating'])\n",
    "\n",
    "dfL2 = dfL2.append(dflist,ignore_index=True)\n",
    "dfF[['Label', 'Rating']] = dfF[['Label', 'Rating']].astype('object', copy=False)\n",
    "elements = pd.concat([dfF, dfL2],ignore_index=True)\n",
    "\n",
    "#elements.tail()\n",
    "#start making some edges\n",
    "dfEdges = dfF[['Publisher', 'Label']]\n",
    "dfEdges.columns=['From', 'To']\n",
    "e2 = pd.DataFrame(taged.loc[taged['ArticleId'].isin(idsused)],columns=['ArticleId','tag'])\n",
    "e2.columns=['From', 'To']\n",
    "connections = pd.concat([dfEdges, e2],ignore_index=True)\n",
    "connections.tail()\n",
    "\n",
    "writer = pd.ExcelWriter(\"C:\\\\datasources\\\\kumutestmap.xlsx\")\n",
    "elements.to_excel(writer,'Sheet1', index=False)\n",
    "connections.to_excel(writer,'Sheet2', index=False)\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#this cell finds common wtk links in notes and outputs a file of the connections\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "#create new dataframe from preprocessed data\n",
    "dfT = pd.read_csv(\"C:\\\\datasources\\\\fullraw.csv\", sep='|', usecols=['ArticleId', 'tags', 'Title', 'quote', 'description', 'Links', 'cpub', 'PublicationDate', 'note', 'wtkURL', 'Priority', 'Summary_ref_links', 'Note_links'], encoding='utf-8')\n",
    "dfF = pd.DataFrame(dfT.sort_values(by='PublicationDate', ascending=False))\n",
    "\n",
    "#make some reference link cols\n",
    "dfF['Note_links'] = dfF['Note_links'].astype(str).apply(lambda v: v.replace('\\'', ''))\n",
    "dfF.Note_links = dfF.Note_links.str[1:-1].str.split(',').tolist()\n",
    "dfNlinks = pd.DataFrame(dfF.Note_links.values.tolist(), dfF.index).add_prefix('Note_link_')\n",
    "\n",
    "form = lambda f: 'Note_link_{}'.format(f + 1)\n",
    "pd.DataFrame(\n",
    "    dfF.Note_links.values.tolist(),\n",
    "    dfF.index, dtype=object\n",
    ").fillna('').rename(columns=form)\n",
    "\n",
    "dfF['Summary_ref_links'] = dfF['Summary_ref_links'].astype(str).apply(lambda z: z.replace('\\'', ''))\n",
    "dfF.Summary_ref_links = dfF.Summary_ref_links.str[1:-1].str.split(',').tolist()\n",
    "dfSreflinks = pd.DataFrame(dfF.Summary_ref_links.values.tolist(), dfF.index).add_prefix('Ref_link_')\n",
    "\n",
    "form = lambda q: 'Ref_link_{}'.format(q + 1)\n",
    "pd.DataFrame(\n",
    "    dfF.Summary_ref_links.values.tolist(),\n",
    "    dfF.index, dtype=object\n",
    ").fillna('').rename(columns=form)\n",
    "\n",
    "df = pd.concat([dfF, dfNlinks, dfSreflinks], axis=1)\n",
    "df.drop_duplicates('Title')\n",
    "\n",
    "#find all wtk summary urls in notes to identify some connections. just looking at Note_link_0 finds over 95 percent \n",
    "substr = \"www.wanttoknow.info/a-\"\n",
    "ind = pd.Series(df['Note_link_0'])\n",
    "df['Note_link_tx'] = ind.str.contains(substr, regex=False)\n",
    "dfcnct = df.loc[df.Note_link_tx]\n",
    "dfcnct = dfcnct[['Title', 'Note_link_0']]\n",
    "dfcnct['Note_link_0'] = dfcnct['Note_link_0'].astype(str).apply(lambda fix: fix.replace('https://', ''))\n",
    "dfcnct['Note_link_0'] = dfcnct['Note_link_0'].astype(str).apply(lambda fx2: fx2.replace('http://', ''))\n",
    "dfcnct.set_index('Title', inplace=True)\n",
    "\n",
    "#correctly place identified urls\n",
    "cnct = pd.Series(dfcnct.Note_link_0.values,index=dfcnct.index).to_dict()\n",
    "\n",
    "df['find'] = df['wtkURL']\n",
    "df['find'] = df['find'].astype(str).apply(lambda fx3: fx3.replace('https://', ''))\n",
    "df['find'] = df['find'].astype(str).apply(lambda fx4: fx4.replace('http://', ''))\n",
    "df.set_index('find', inplace=True)\n",
    "\n",
    "cnct2 = pd.Series(df.Title.values,index=df.index).to_dict()\n",
    "\n",
    "df['connect'] = df['Title'].map(cnct)\n",
    "df['connect2'] = df['connect'].map(cnct2)\n",
    "\n",
    "#make and export a little dataframe of article relationships as identified by note url matching\n",
    "dfed = df[['Title', 'connect', 'connect2']].dropna()\n",
    "\n",
    "c = dfed[['Title', 'connect2']]\n",
    "o = dfed[['connect2', 'Title']]\n",
    "co = pd.concat([c, o], ignore_index=True)\n",
    "co.drop_duplicates(subset='Title').shape\n",
    "\n",
    "#check shape of dataframe\n",
    "df.shape\n",
    "#dfed.to_csv(\"C:\\\\datasources\\\\notelinks.csv\", sep='|', encoding='utf-8')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}