{"id":539,"date":"2015-12-20T17:39:54","date_gmt":"2015-12-20T22:39:54","guid":{"rendered":"http:\/\/csclub.uwaterloo.ca\/~k55chen\/?p=539"},"modified":"2017-04-23T13:34:21","modified_gmt":"2017-04-23T17:34:21","slug":"use-python-to-extract-intelligence-indexing-fields-in-factiva-articles","status":"publish","type":"post","link":"https:\/\/www.kaichen.work\/?p=539","title":{"rendered":"Use Python to extract Intelligence Indexing fields in Factiva articles"},"content":{"rendered":"

First of all, I acknowledge that I benefit a lot from Neal Caren’s blog post Cleaning up LexisNexis Files<\/a>. Thanks Neal.<\/p>\n

Factiva (as well as LexisNexis Academic) is a comprehensive repository of newspapers, magazines, and other news articles. I first describe the data elements of a Factiva news article. Then I explain the steps to extract those data elements and write them into a more machine-readable table using Python.<\/p>\n

Data Elements in Factiva Article<\/strong><\/p>\n

Each news article in Factiva, no matter how it looks like, contains a number of data elements. In Factiva’s terminology, those data elements are called Intelligence Indexing Fields. The following table lists the label and name for each data element (or, field) along with what is contained in each:<\/p>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
Field Label<\/th>Field Name<\/th>What It Contains<\/th>\n<\/tr>\n<\/thead>\n
HD<\/td>Headline<\/td>Headline<\/td>\n<\/tr>\n
CR<\/td>Credit Information<\/td>Credit Information (Example: Associated Press)<\/td>\n<\/tr>\n
WC<\/td>Word Count<\/td>Number of words in document<\/td>\n<\/tr>\n
PD<\/td>Publication Date<\/td>Publication Date<\/td>\n<\/tr>\n
ET<\/td>Publication Time<\/td>Publication Time<\/td>\n<\/tr>\n
SN<\/td>Source Name<\/td>Source Name<\/td>\n<\/tr>\n
SC<\/td>Source Code<\/td>Source Code<\/td>\n<\/tr>\n
ED<\/td>Edition<\/td>Edition of publication (Example: Final)<\/td>\n<\/tr>\n
PG<\/td>Page<\/td>Page on which article appeared (Note: Page-One Story is a Dow Jones Intelligent Indexing\u00aa term)<\/td>\n<\/tr>\n
LA<\/td>Language<\/td>Language in which the document is written<\/td>\n<\/tr>\n
CY<\/td>Copyright<\/td>Copyright<\/td>\n<\/tr>\n
LP<\/td>Lead Paragraph<\/td>First two paragraphs of an article<\/td>\n<\/tr>\n
TD<\/td>Text<\/td>Text following the lead paragraphs<\/td>\n<\/tr>\n
CT<\/td>Contact<\/td>Contact name to obtain additional information<\/td>\n<\/tr>\n
RF<\/td>Reference<\/td>Notes associated with a document<\/td>\n<\/tr>\n
CO<\/td>Dow Jones Ticker Symbol<\/td>Dow Jones Ticker Symbol<\/td>\n<\/tr>\n
IN<\/td>Industry Code<\/td>Dow Jones Intelligent Indexing\u00aa Industry Code<\/td>\n<\/tr>\n
NS<\/td>Subject Code<\/td>Dow Jones Intelligent Indexing\u00aa Subject Code<\/td>\n<\/tr>\n
RE<\/td>Region Code<\/td>Dow Jones Intelligent Indexing\u00aa Region Code<\/td>\n<\/tr>\n
IPC<\/td>Information Provider Code<\/td>Information Provider Code<\/td>\n<\/tr>\n
IPD<\/td>Information Provider Descriptors<\/td>Information Provider Descriptors<\/td>\n<\/tr>\n
PUB<\/td>Publisher Name<\/td>Publisher of information<\/td>\n<\/tr>\n
AN<\/td>Accession Number<\/td>Unique Factiva.com identification number assigned to each document<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n

Please note that not every news article contains all those data elements, and that the table may not list all data elements used by Factiva (Factiva may make updates). Depending on which display option you select when downloading news articles from Factiva, you may not be able to see certain data elements. But they are there and used by Factiva to organize and structure its proprietary news article data.<\/p>\n

How to Extract Data Elements in Factiva Article<\/strong><\/p>\n

\"flow\"<\/a><\/p>\n

You can follow three steps outlined in the above diagram to extract data elements in news articles and for further processing (e.g., calculate tone of full text represented by both LP and TD element; or group by news subject, i.e., by NS element). I explain them one by one as follows.<\/p>\n

Step 1: Download Articles from Factiva in RTF Format<\/strong><\/em><\/p>\n

It is a lot of pain to download a large number of news articles from Factiva: it is technically difficult\u00a0to download articles in an automated fashion; you can only download 100 articles at a time, also those 100 articles cannot exceed the word count limit, i.e., 180,000. As a result, it requires a lot of tedious work if you want to gather tens of thousands news articles. While I can do nothing about both issues in this post, I can say a bit more about them.<\/p>\n

Firstly, you may see some people discuss methods for automatic downloading (a so-called “webscraping” technique. See here<\/a>). However, this needs more hacking\u00a0after\u00a0Factiva introduced CAPTCHA to determine whether or not the user is a human. You may not be familiar with the term “CAPTCHA”, but you must experience the circumstance where you are asked to input characters or numbers shown in an image before you can download a file or go to the next webpage. That is CAPTCHA. Both Factiva and LexisNexis Academic have introduced CAPTCHA to prohibit robotic downloading. Though CAPTCHA is not unbeatable, it requires advanced technique.<\/p>\n

Secondly, the Factiva licence expressly prohibits data mining. However, the licence does not define clearly what constitutes data mining. I was informed that downloading a large number of articles in a short period of time would be red flagged as data mining. But the threshold speed set by Factiva is low and any trained and adept person can beat that threshold speed easily. If you are red flagged by Factiva, things could go ugly. So, do not be too fast, even this may slow down your research.<\/p>\n

Let’s get back to the topic. When you manually download news articles from Factiva, the most important thing is to select the right display option. Please select the third one: Full Article\/Report plus Indexing as indicated by the following graph:<\/p>\n

\"Factiva\"<\/a><\/p>\n

Then you have to download articles in RTF \u2013 Article Format, as indicated by the following graph:<\/p>\n

\"Factiva2\"<\/a><\/p>\n

After the download is completed, you will get an RTF document. If you open it, you will find news articles look like this:<\/p>\n

\"Factiva3\"<\/a><\/p>\n

The next step is to convert RTF to plain TXT, because Python can process TXT documents more easily. After Python finishes its job, the final product will be a table: each row of the table represents a news article; and each column of the table is a data element<\/strong>.<\/p>\n

Step 2: Convert RTF to TXT<\/strong><\/em><\/p>\n

Well, this can surely be done by Python. But so far I have not written a Python program to do this. I will complete this “hole” when I have time. For my research, I simply take advantage of the convenience of the default text editor shipped with Mac OS, TextEdit. I select Format \u2013 Make Plain Text from the menu bar, and then save the document in TXT format. You can make this happen in an automatic fashion using Automator in Mac OS.<\/p>\n

Step 3: Extract Data Elements and Save to a Table<\/strong><\/em><\/p>\n

This is where Python does the dirty work. To run the Python program correctly, please save the Python program in the directory where you put all plain TXT documents created in Step 2 before you run the program. This program will:<\/p>\n

    \n
  1. Read in each TXT document;<\/li>\n
  2. Extract data elements of each article and write them to an SQLite database;<\/li>\n
  3. Export data to a CSV file for easy processing in other software such as Stata.<\/li>\n<\/ol>\n

    I introduce an intermediate step which writes data to an SQLite database, simply because this can facilitate manipulation of news article data using Python for other purposes. Of course, you can directly write data to a CSV file.<\/p>\n

    import glob\r\nimport re\r\nimport sqlite3\r\nimport csv\r\n\r\ndef parser(file):\r\n\r\n    # Open a TXT file. Store all articles in a list. Each article is an item\r\n    # of the list. Split articles based on the location of such string as\r\n    # 'Document PRN0000020080617e46h00461'\r\n\r\n    articles = []\r\n    with open(file, 'r') as infile:\r\n        data = infile.read()\r\n    start = re.search(r'\\n HD\\n', data).start()\r\n    for m in re.finditer(r'Document [a-zA-Z0-9]{25}\\n', data):\r\n        end = m.end()\r\n        a = data[start:end].strip()\r\n        a = '\\n   ' + a\r\n        articles.append(a)\r\n        start = end\r\n\r\n    # In each article, find all used Intelligence Indexing field codes. Extract\r\n    # content of each used field code, and write to a CSV file.\r\n\r\n    # All field codes (order matters)\r\n    fields = ['HD', 'CR', 'WC', 'PD', 'ET', 'SN', 'SC', 'ED', 'PG', 'LA', 'CY', 'LP',\r\n              'TD', 'CT', 'RF', 'CO', 'IN', 'NS', 'RE', 'IPC', 'IPD', 'PUB', 'AN']\r\n\r\n    for a in articles:\r\n        used = [f for f in fields if re.search(r'\\n   ' + f + r'\\n', a)]\r\n        unused = [[i, f] for i, f in enumerate(fields) if not re.search(r'\\n   ' + f + r'\\n', a)]\r\n        fields_pos = []\r\n        for f in used:\r\n            f_m = re.search(r'\\n   ' + f + r'\\n', a)\r\n            f_pos = [f, f_m.start(), f_m.end()]\r\n            fields_pos.append(f_pos)\r\n        obs = []\r\n        n = len(used)\r\n        for i in range(0, n):\r\n            used_f = fields_pos[i][0]\r\n            start = fields_pos[i][2]\r\n            if i < n - 1:\r\n                end = fields_pos[i + 1][1]\r\n            else:\r\n                end = len(a)\r\n            content = a[start:end].strip()\r\n            obs.append(content)\r\n        for f in unused:\r\n            obs.insert(f[0], '')\r\n        obs.insert(0, file.split('\/')[-1].split('.')[0])  # insert Company ID, e.g., GVKEY\r\n        # print(obs)\r\n        cur.execute('''INSERT INTO articles\r\n                       (id, hd, cr, wc, pd, et, sn, sc, ed, pg, la, cy, lp, td, ct, rf,\r\n                       co, ina, ns, re, ipc, ipd, pub, an)\r\n                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,\r\n                       ?, ?, ?, ?, ?, ?, ?, ?)''', obs)\r\n\r\n# Write to SQLITE\r\nconn = sqlite3.connect('factiva.db')\r\nwith conn:\r\n    cur = conn.cursor()\r\n    cur.execute('DROP TABLE IF EXISTS articles')\r\n    # Mirror all field codes except changing 'IN' to 'INC' because it is an invalid name\r\n    cur.execute('''CREATE TABLE articles\r\n                   (nid integer primary key, id text, hd text, cr text, wc text, pd text,\r\n                   et text, sn text, sc text, ed text, pg text, la text, cy text, lp text,\r\n                   td text, ct text, rf text, co text, ina text, ns text, re text, ipc text,\r\n                   ipd text, pub text, an text)''')\r\n    for f in glob.glob('*.txt'):\r\n        print(f)\r\n        parser(f)\r\n\r\n# Write to CSV to feed Stata\r\nwith open('factiva.csv', 'w', newline='') as csvfile:\r\n    writer = csv.writer(csvfile)\r\n    with conn:\r\n        cur = conn.cursor()\r\n        cur.execute('SELECT * FROM articles WHERE hd IS NOT NULL')\r\n        colname = [desc[0] for desc in cur.description]\r\n        writer.writerow(colname)\r\n        for obs in cur.fetchall():\r\n            writer.writerow(obs)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    First of all, I acknowledge that I benefit a lot from Neal Caren’s blog post Cleaning up LexisNexis Files. Thanks Neal. Factiva (as well as LexisNexis Academic) is a comprehensive repository of newspapers, magazines, and other news articles. I first … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"_links":{"self":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/539"}],"collection":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=539"}],"version-history":[{"count":68,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/539\/revisions"}],"predecessor-version":[{"id":743,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=\/wp\/v2\/posts\/539\/revisions\/743"}],"wp:attachment":[{"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kaichen.work\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}