Difference between revisions of "ISSS608 2017-18 T3 Assign Manu George Mathew Data Preparation"
(Created page with "<div style=background:#ae946e border:#ae946e> 165px <b><font size = 6; color="#FFFFFF"> VAST Challenge 2018:Suspense at the Wildlife Preserve </f...") |
|||
(One intermediate revision by the same user not shown) | |||
Line 74: | Line 74: | ||
</ul> | </ul> | ||
</p> | </p> | ||
− | + | ||
− | <p>We are also provided with a list of 20 people that the insider finds suspicious. | + | <p>We are also provided with a list of 20 people that the insider finds suspicious.</p> |
<p>Alex Hall, Lizbeth Jindra, Patrick Lane, Richard Fox, Sara Ballard, May Burton, Glen Grant, Dylan Ballard, Meryl Pastuch, Melita Scarpaci, Augusta Sharp, Kerstin Belveal, Rosalia Larroque, Lindsy Henion, Julie Tierno, Jose Ringwald, Ramiro Gault, Tobi Gatlin, Refugio Orrantia, and Jenice Savaria.</p> | <p>Alex Hall, Lizbeth Jindra, Patrick Lane, Richard Fox, Sara Ballard, May Burton, Glen Grant, Dylan Ballard, Meryl Pastuch, Melita Scarpaci, Augusta Sharp, Kerstin Belveal, Rosalia Larroque, Lindsy Henion, Julie Tierno, Jose Ringwald, Ramiro Gault, Tobi Gatlin, Refugio Orrantia, and Jenice Savaria.</p> | ||
− | <font size="5"><font color="#ae946e">'''Steps followed Data Preparation'''</font></font> | + | <font size="5"><font color="#ae946e">'''Steps followed for Data Preparation'''</font></font> |
+ | <p>The data preparation for the assignment was done entirely in r if not for the dashboard specific calculations done in Tableau. The following packages in R was used.</p> | ||
+ | <ul> | ||
+ | <li>tidyverse - For Data manipulation and cleaning in smaller datasets.</li> | ||
+ | <li>data.table - For cleaning and summarising larger datasets.</li> | ||
+ | <li>lubridate - For manipulating dates.</li> | ||
+ | </ul> | ||
+ | <table> | ||
+ | <table border='1'> | ||
+ | |||
+ | <th>Stage</th> | ||
+ | <th>Steps Followed</th> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 1.Importing the originally available data </b> | ||
+ | |||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>The datasets were read in using fread function in data table.</li> | ||
+ | <li>The 4 large data sets for calls, meetings, emails and purchases were concatenated to get a large dataset of 26,046,071 rows</li> | ||
+ | <li>The same was done for the suspicious datasets to come up with a smaller table with 137 records.</li> | ||
+ | <li>Here onwards, any operation done on the larger dataset is done conccurently on the suspicious dataset unless mentioned otherwise. </li> | ||
+ | <li>Also, any additional transformations or mutations to the data are done such that a seperate column is created so that, the integrity of the original data is not lost.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | |||
+ | <tr> | ||
+ | <td><b> 2.Renaming and Creating Recoded columns to match the given data description. </b> | ||
+ | |||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>The column names in the imported data were different from the provided description. So they were renamed accordingly.</li> | ||
+ | <li>The Etype column was coded in integer format. A new column <b>Label</b> was created that had the descriptions of the codes as given in the data description.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 3.Convert the TimeStamp column into the right format </b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>We were given information that the time stamp is an integer counted in seconds from May 2015 14:00. This was converted using R to the right format and assigned to a new column <b>adjusted_time</b></li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 4.Mark out the suspicious transactions into the larger dataset </b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>A new column <b>Is_Suspicious_Edge</b> was created in the larger dataset to keep track of the suspicious transactions.</li> | ||
+ | <li>After cross checking, we find out that the suspicious purchase is not marked in the bigger data as suspicious</li> | ||
+ | <li>Checking for the reason, it was found that the time Stamp was not matching in the record. the record was manually marked as suspicious.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 5.Prepare the Data for working in Gephi </b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <br>The big dataset here fits the description of a edge table in Gephi and the Employee Index data fits the description of the node table. | ||
+ | <ul> | ||
+ | <li>In the Edge data, The column "Destination" was renamed to "Target" as that is the name Gephi recognizes.</li> | ||
+ | <li>The Employee Index table was taken and a <b>Label</b> column was created by concatenating the "First" and "Last" Column.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 6.Mark out Suspicious Nodes in the nodes table and their association time with Kasios(Needed for Question 3).</b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>A list of suspicious people was extracted from the suspicious data by taking unique values of Employee ID# in the "Source" and "Target" column.</li> | ||
+ | <li>A <b>start_date</b> and <b>end_date</b> column was created for these employees by summarising the edge table, looking for transactions where these parties are involved either as the "Source" or the "Target".</li> | ||
+ | <li>Convert the above 2 columns into string format dates, so that gephi reads them in without error for creating intervals.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 7.Label the Source and Target Nodes in the edges table using the Employee Index Table.</b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>2 new columns, named <b>Source_Label</b> and <b>Target_Label</b> were created to hold these labels.</li> | ||
+ | <li>Save the Data onto a .CSV File</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | |||
+ | <tr> | ||
+ | <td><b> 8.(Question 2 and 3 Specific).</b> | ||
+ | </td> | ||
+ | <td> | ||
+ | <ul> | ||
+ | <li>Filter out only the suspicious nodes (Extended Suspicious Nodes in Question 3) and their related edges in the Nodes and Edges table respectively.</li> | ||
+ | <li>Convert the adjusted time stamp column into a string so that gephi reads in the date intervals properly.</li> | ||
+ | <li>Save the Data.</li> | ||
+ | </ul> | ||
+ | </td> | ||
+ | </tr> | ||
+ | </table> |
Latest revision as of 18:42, 8 July 2018
|
|
|
|
|
|
|
Data Preparation
Given Data Description
We are provided data OF PAST 2.5 YEARS from across the company. There are call records, emails, purchases, and meetings. The data only includes the source of each transaction, the recipient (destination), and the time of the transaction. Contents of emails or phone calls are not available. All of the provided data files have the same format.
The data are provided in comma-separated format with four columns:
- Source (contains the company ID# for the person who called, sent an email, purchased something, or invited people to a meeting)
- Etype (contains a number designating what kind of connection is made)
- 0 is for calls
- 1 is for emails
- 2 is for purchases
- 3 is for meetings
- Destination (contains company ID# for the person who is receiving a call, receiving an email, selling something to a buyer, or being invited to a meeting).
- Time stamp – in seconds starting on May 11, 2015 at 14:00.
There is a company index that shows the name of everyone in the company and their associated ID#. There are 642,631 individuals in the index.
There are four data files that cover the whole company:
- calls.csv has information on 10.6 million calls (251 MB uncompressed)
- emails.csv has information on 14.6 million emails (345 MB uncompressed)
- purchases.csv has information on 762 thousand purchases (18.8 MB uncompressed)
- meetings.csv has information on 127 thousand meetings (3.26 MB uncompressed)
There are four data files that contain information about individuals that the Insider has indicated as suspicious:
- Suspicious_calls.csv (1.76 KB uncompressed)
- Suspicious_emails.csv (1.55 KB uncompressed)
- Suspicious_purchases.csv (27 B uncompressed)
- Suspicious_meetings.csv (130 B uncompressed)
We are also provided with a list of 20 people that the insider finds suspicious.
Alex Hall, Lizbeth Jindra, Patrick Lane, Richard Fox, Sara Ballard, May Burton, Glen Grant, Dylan Ballard, Meryl Pastuch, Melita Scarpaci, Augusta Sharp, Kerstin Belveal, Rosalia Larroque, Lindsy Henion, Julie Tierno, Jose Ringwald, Ramiro Gault, Tobi Gatlin, Refugio Orrantia, and Jenice Savaria.
Steps followed for Data Preparation
The data preparation for the assignment was done entirely in r if not for the dashboard specific calculations done in Tableau. The following packages in R was used.
- tidyverse - For Data manipulation and cleaning in smaller datasets.
- data.table - For cleaning and summarising larger datasets.
- lubridate - For manipulating dates.
Stage | Steps Followed |
---|---|
1.Importing the originally available data |
|
2.Renaming and Creating Recoded columns to match the given data description. |
|
3.Convert the TimeStamp column into the right format |
|
4.Mark out the suspicious transactions into the larger dataset |
|
5.Prepare the Data for working in Gephi |
|
6.Mark out Suspicious Nodes in the nodes table and their association time with Kasios(Needed for Question 3). |
|
7.Label the Source and Target Nodes in the edges table using the Employee Index Table. |
|
8.(Question 2 and 3 Specific). |
|