{"id":2555,"date":"2016-11-29T12:00:09","date_gmt":"2016-11-29T16:00:09","guid":{"rendered":"https:\/\/sites.temple.edu\/tudsc\/?p=2555"},"modified":"2017-12-01T19:43:30","modified_gmt":"2017-12-01T23:43:30","slug":"preparing-data-with-openrefine-part-i-time-in-sequence","status":"publish","type":"post","link":"https:\/\/sites.temple.edu\/tudsc\/2016\/11\/29\/preparing-data-with-openrefine-part-i-time-in-sequence\/","title":{"rendered":"Preparing Data With OpenRefine Part I – Time in Sequence"},"content":{"rendered":"

By Luling Huang<\/p>\n

<\/p>\n

As a continuation of my previous post on webscrapping\u00a0a political discussion forum<\/a>, I will show how to prepare the time column for doing Relevant Event Modeling in R’s “relevent” (Butts, 2015). I used OpenRefine for data cleaning.<\/p>\n

The goal is to create a dyadic edgelist for each thread. The edgelist has three columns: time information, sender, and receiver. To use the modeling function “rem.dyad” in R, time must be relative to the start of observation (Butts, 2015).<\/p>\n

In my data, time is recorded as it is shown on the website. When the data was loaded in OpenRefine, it looks like this:<\/p>\n

\"screen-shot-2016-11-17-at-9-48-17-am\"<\/a><\/p>\n

Also, some information is in the form “Today, xx:xx PM.” Therefore, here is a list of what should be done:<\/p>\n

(1) Get rid of\u00a0\ufffd\ufffd (Originally on the website, it is “&nbsp;,” which is a no break space. The scraper should have done a better job to delete it).<\/p>\n

(2) Recode “Today” into “10\/22”, and “Yesterday” into “10\/21.” The scraper was run on 10\/22 (technically, it was run from 11:10 PM 10\/21 to 3:53 AM 10\/22, which creates a real problem of how to recode “Today” and “Yesterday.” To fix it, manual verification should be done for each of the 203 “Yesterday” and the 87 “Today” cells. For this post, I’ll simplify this process and do aggregate transformation).<\/p>\n

(3) Transform all “12:xx AM” and “12:xx PM” into “00:xx AM” and “00:xx PM.” The reason is that, somehow, OpenRefine does not like “12:xx AM\/PM.” When converting to time data, without transformation, OpenRefine would recognize “12:xx AM” as “12:xx PM,” and would not convert “12:xx PM” at all. I followed Little’s (2015, p. 32) trick to fix this issue.<\/p>\n

(4) For each thread, create a column for onset time, which should be the time of the first post in thread.<\/p>\n

(5) For each thread, create another column. This column is the original time subtracted by onset time.<\/p>\n

I used GREL (General Refine Expression Language).<\/p>\n

(1) For the column “PostDateAndTime,” apply the function “value.replace(“\ufffd\ufffd”, ” “).\"screen-shot-2016-11-17-at-10-52-57-am\"<\/a><\/p>\n

(2) With the same function, recode “Today” and “Yesterday” cells into “10\/22” and “10\/21.”<\/p>\n

(3) With the same function, recode\u00a0“12:xx AM” and “12:xx PM” into “00:xx AM” and “00:xx PM.”<\/p>\n

(4) Apply facet to the column “ThreadID” and select a single thread (in order to perform the following actions only to a single thread). Apply another facet to the column “PostPosition” and select the row for the first post. For the column”PostDateAndTime,” use “Edit column -> Add a column based on this column” to fill the cell in the new column with the first post’s time. Cancel the facet for the column “PostPosition” and “Fill down” the new column. Now we have a new column (named “onset time”).<\/p>\n

\"screen-shot-2016-11-17-at-11-14-39-am\"<\/a><\/p>\n

(5) For the column “onset time,” use “Edit column -> Add a column based on this column” to create another new column “t.” “t” is the difference between original time and onset time. Use the function diff():<\/p>\n

\"screen-shot-2016-11-17-at-12-25-57-pm\"<\/a><\/p>\n

a. cells[“PostDateAndTime”].value.toDate()<\/em>: Locate the original time and convert it to time format.<\/p>\n

b. cells[“onset time”].value.toDate()<\/em>: Locate the onset time and convert it to time format.<\/p>\n

c. “seconds”<\/em>: Designate second as the calculation unit.<\/p>\n

In the new column “t”, for example, “50160” (for the fourth post in thread) means that there are 50160 seconds between the start of the thread and the fourth post.<\/p>\n

References<\/p>\n

Butts, C. (2015). Package ‘relevent’ [R package documentation]. Retrieved from\u00a0https:\/\/cran.r-project.org\/web\/packages\/relevent\/relevent.pdf<\/p>\n

Little, J. (2015). OpenRefine: Introduction: Workbook [Online tutorial slide]. Retrieved from https:\/\/docs.google.com\/presentation\/d\/1YkArEiaws0dMcyFZEppg4eZ7CxvqCTckjY78ao93zIw\/edit#slide=id.p<\/p>\n","protected":false},"excerpt":{"rendered":"

By Luling Huang<\/p>\n","protected":false},"author":3163,"featured_media":2571,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[303,2],"tags":[179],"class_list":["post-2555","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-computer-science","category-grad-students","tag-openrefine"],"_links":{"self":[{"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/posts\/2555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/users\/3163"}],"replies":[{"embeddable":true,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/comments?post=2555"}],"version-history":[{"count":0,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/posts\/2555\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/media\/2571"}],"wp:attachment":[{"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/media?parent=2555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/categories?post=2555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sites.temple.edu\/tudsc\/wp-json\/wp\/v2\/tags?post=2555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}