Difference between revisions of "IISSS608 2017-18 T3 Assign Vigneshwar Ramachandran Vadivel- Data Cleaning"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
{{Template:MC3 Header}} | {{Template:MC3 Header}} | ||
| + | |||
<!------- Main Navigation Bar----> | <!------- Main Navigation Bar----> | ||
<center> | <center> | ||
| Line 22: | Line 23: | ||
<center> | <center> | ||
{| style="background-color:#ffffff ; margin: 3px 10px 3px 10px;" width="80%"| | {| style="background-color:#ffffff ; margin: 3px 10px 3px 10px;" width="80%"| | ||
| + | | style="font-family:Open Sans, Arial, sans-serif; font-size:15px; text-align: center; border:solid 1px #f5f5f5; background-color: #f5f5f5" width="150px" | [[ISSS608_2017-18_T3_Assign_Vigneshwar Ramachandran Vadivel_Approach|<font color="#3c3c3c"><strong>Main Page</strong></font>]] | ||
| + | |||
| style="font-family:Open Sans, Arial, sans-serif; font-size:15px; text-align: center; border-top:solid #f5f5f5; background-color: #fff" width="150px" | | | style="font-family:Open Sans, Arial, sans-serif; font-size:15px; text-align: center; border-top:solid #f5f5f5; background-color: #fff" width="150px" | | ||
| − | + | ||
| − | + | [[ISSS608_2017-18_T3_Assign_Vigneshwar Ramachandran Vadivel-_Data_Cleaning|<font color="#3c3c3c"><strong>Data Cleaning</strong></font>]] | |
| − | |||
| − | [[ | ||
| style="font-family:Open Sans, Arial, sans-serif; font-size:15px; text-align: center; border:solid 1px #f5f5f5; background-color: #f5f5f5" width="150px" | | | style="font-family:Open Sans, Arial, sans-serif; font-size:15px; text-align: center; border:solid 1px #f5f5f5; background-color: #f5f5f5" width="150px" | | ||
| Line 37: | Line 38: | ||
<!------- End of Secondary Navigation Bar----> | <!------- End of Secondary Navigation Bar----> | ||
| − | = | + | =Data Cleaning= |
| − | + | ==Communication Data== | |
| + | Initial part of our analysis requires us to explore the communication pattern of the company. In order to analyse the pattern, we have to combine the four-communication dataset of Kasios into one single data to have the holistic view. | ||
| + | Using Tableau prep all the four data can be merged and transformed into our necessary format.<br> | ||
| + | The date format of the data has been given in Unix epoch timestamp beginning from May 11, 2015 at 14:00. To transform this into our timestamp value we can derive the following calculated field, | ||
| − | + | ==Network Data== | |
| + | Once we transform our data we can explore the communication pattern and growth of the company with this merged dataset. | ||
| − | + | For our later part of the analysis the data has to be transformed in such a way that it would be readable on Gephi for basic network visualisations. Using the insider kasios suspicious data, I | |
| − | + | The following flow shows the data processing involved in this process using Tableau prep,<br> | |
| − | [[File: | + | [[File:A1_MC3_Data_Flow.PNG|1000px|centre|frameless|link=ISSS608_2017-18_T3_Assign_Vigneshwar Ramachandran Vadivel |
]] | ]] | ||
| + | ===Aggregated Communication Data=== | ||
| + | To create the edges csv file: | ||
| + | <ol><li>1. Concatenate communication data for all different transactions into one source of information.</li> | ||
| + | <li>Change column data type of "From" and "To" from continuous to nominal. | ||
| + | <li>Rename columns "From" and "To" to "Source" and "Target".</li> | ||
| + | <li>Create a column "Type" and ensure that all the fields of the column are filled with "Directed" - This helps to specify the direction of the communication.</li> | ||
| + | <li>Create a new column with the formula "Source || Target", as this concatenates the Source and Target column.</li> | ||
| + | <li>Use the tabulate function to count the number of times the concatenated cell appears in the column - this helps to find out the weight of the unique direction of communication.</li> | ||
| + | <li>Update the table with the weight of the column. Change the column name to "Weight".</li> | ||
| − | + | To create the nodes csv file: | |
| − | + | <ol><li> Use the tablulate function on JMP to count the number of times a certain ID has appeared in both the "Source" and "Target" column. Merge the files together and tabulate again to find out the unique nodes present in the edges csv file.</li> | |
| − | < | + | <li>Name the column "id", so that Gephi can detect the ids of the nodes.</li></ol> |
| − | + | ===Communication Data from 11am to 130pm on Sunday=== | |
| − | + | To create the edges csv file: | |
| − | + | <ol><li>Carry out Steps 2-5 above for the communication data on Sunday.</li> | |
| − | + | <li>Retain only the communication data between 11am and 1.30pm.</li> | |
| − | + | <li>To create the Timestamp column, use the current timestamp format, mm/dd/yyyy hh:mm.</li> | |
| − | = | + | |
| − | + | </ol> | |
| − | + | To create the nodes csv file: | |
| − | To create the edges csv file:< | + | <ol><li> Merge the company index with the combined file together and find out the unique nodes present in the edges csv file.</li> |
| − | + | <li>Name the column "id", so that Gephi can detect the ids of the nodes.</li> | |
| − | 2 | + | <li>Add the label as name of the employee involved in the transaction</li></ol> |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | To create the nodes csv file:< | ||
| − | |||
| − | |||
| − | |||
| − | </ | ||
Revision as of 10:38, 3 July 2018
| Main Page |
Contents
Data Cleaning
Communication Data
Initial part of our analysis requires us to explore the communication pattern of the company. In order to analyse the pattern, we have to combine the four-communication dataset of Kasios into one single data to have the holistic view.
Using Tableau prep all the four data can be merged and transformed into our necessary format.
The date format of the data has been given in Unix epoch timestamp beginning from May 11, 2015 at 14:00. To transform this into our timestamp value we can derive the following calculated field,
Network Data
Once we transform our data we can explore the communication pattern and growth of the company with this merged dataset.
For our later part of the analysis the data has to be transformed in such a way that it would be readable on Gephi for basic network visualisations. Using the insider kasios suspicious data, I
The following flow shows the data processing involved in this process using Tableau prep,
Aggregated Communication Data
To create the edges csv file:
- 1. Concatenate communication data for all different transactions into one source of information.
- Change column data type of "From" and "To" from continuous to nominal.
- Rename columns "From" and "To" to "Source" and "Target".
- Create a column "Type" and ensure that all the fields of the column are filled with "Directed" - This helps to specify the direction of the communication.
- Create a new column with the formula "Source || Target", as this concatenates the Source and Target column.
- Use the tabulate function to count the number of times the concatenated cell appears in the column - this helps to find out the weight of the unique direction of communication.
- Update the table with the weight of the column. Change the column name to "Weight". To create the nodes csv file:
- Use the tablulate function on JMP to count the number of times a certain ID has appeared in both the "Source" and "Target" column. Merge the files together and tabulate again to find out the unique nodes present in the edges csv file.
- Name the column "id", so that Gephi can detect the ids of the nodes.
- Carry out Steps 2-5 above for the communication data on Sunday.
- Retain only the communication data between 11am and 1.30pm.
- To create the Timestamp column, use the current timestamp format, mm/dd/yyyy hh:mm.
- Merge the company index with the combined file together and find out the unique nodes present in the edges csv file.
- Name the column "id", so that Gephi can detect the ids of the nodes.
- Add the label as name of the employee involved in the transaction
Communication Data from 11am to 130pm on Sunday
To create the edges csv file:
To create the nodes csv file:
