A. Split the data based on unique values in the column “ThreadID;”<\/p>\n
B. Do the cleaning work in Part I and Part II on one subset;<\/p>\n
C. Apply the work done in “Task B” to other subsets.<\/p>\n
The reason to include python is that it automates the process in Tasks A and C, which is especially important with a large dataset. Whereas my previous two blog posts\u00a0speak more to my own project, I hope this one can be helpful\u00a0for a more general situation when you want to automate the OpenRefine workflow on many subsets.<\/p>\n
Needed tools:<\/p>\n
I. OpenRefine;<\/p>\n
II. pandas: a python library for processing dataframes (here is the link for installation<\/a>);<\/p>\n III. refine-python: a client library for interacting with OpenRefine in python (here is the link for its GitHub page<\/a>, and yes, it’s still working under OpenRefine 2.6).<\/p>\n Link for the hypothetical data used in this demonstration: Example_3Threads.csv (no longer available via Google Drive) (for simplification the time data is ready).<\/p>\n Task A:<\/p>\n What I thought I could do: Export subsets based on a facet\u00a0of the column “ThreadID” in OpenRefine.<\/p>\n Is it possible to do? Yes. Although it is not an explicit functionality in OpenRefine, I found a workaround using Javascript provided by one developer. You can find the method here<\/a>.<\/p>\n Problem: The code was written for exporting Excel files. It did not work when I changed “xls” to “csv” in the first two lines. Given the dire situation that I had zero knowledge in Javascript, for an easy workaround of the workaround, I turned to pandas in python.<\/p>\n Python code using pandas:<\/p>\n Line 2: Create a dataframe from the sample csv file.<\/p>\n Line 3: Create a list of unique values in the column “ThreadID.”<\/p>\n Line 4: Locate a subdirectory under the working directory to store all split files.<\/p>\n Lines 5 to 8: Loop through “ThreadIDList,” do the split, and export to csv with corresponding file names. The key is line 6: rows are selected based on values in the column “ThreadID.”<\/p>\n Result: Three split files based on thread IDs have been stored in the folder “SplitFiles.”<\/p>\n Task B:<\/p>\n In OpenRefine, do the work covered in Part II on any one of the subsets.<\/a><\/p>\n Task C:<\/p>\n OpenRefine stores every step that changes the data in an operation history. History of one project can be extracted, saved in JSON, and applied to other projects. Therefore, the logic of completing Task C is straightforward: C1. When Task B is finished, extract and save the history as JSON; C2. Use refine-python to automate the process of applying the operation history to the other two subsets.<\/p>\n C1: Extract operation history.<\/p>\n\n\n
\n \n 1\n2\n3\n4\n5\n6\n7\n8<\/pre>\n<\/td>\n
\n import<\/span> pandas<\/span> as<\/span> pd<\/span>\ntest_df =<\/span> pd.<\/span>read_csv('Example_3Threads.csv'<\/span>)\nThreadIDList =<\/span> test_df.<\/span>ThreadID.<\/span>unique()\nroot =<\/span> 'SplitFiles'<\/span>\nfor<\/span> i in<\/span> ThreadIDList:\n spliti_df =<\/span> test_df.<\/span>loc[test_df['ThreadID'<\/span>] ==<\/span> i]\n FileNamei =<\/span> 'Example_3Threads-'<\/span> +<\/span> str<\/span>(i) +<\/span> '.csv'<\/span>\n spliti_df.<\/span>to_csv(root +<\/span> '\/'<\/span> +<\/span> FileNamei, index =<\/span> False<\/span>, encoding =<\/span> 'utf-8'<\/span>)\n<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
![\"Screenshot:](\"https:\/\/sites.temple.edu\/tudsc\/files\/2016\/12\/Screen-Shot-2016-12-08-at-7.27.59-PM.png\")
<\/a><\/p>\n