ISSS608 2017-18 T3 Assign Chen Runpu

VAST Challenge 2018: Mini-Challenge 3

This is an interesting story, it starts from one person whose name is Mitch Vogel. Mitch Vogel left his work at Mistford College but has not forgotten the Rose-Crested Blue Pipit. Soon after arriving in the small town of Sulev in Northern Europe, Mitch started to see the telltale signs of Methylosmolene damage at the nearby Panteleimon Aviary Sanctuary. Mitch hears from local bird watchers that populations of the Greater Eurasian Red-Throated Pipit have been affected. Since arriving in Sulev, Mitch also noticed that the local university’s office furniture was built in a nearby EuroKasios factory. Sure enough, EuroKasios is a subsidiary of Kasios International, Inc. – a huge multinational conglomerate that is also the parent company of Kasios Office Furniture back in Mitch’s hometown of Mistford. Mitch is immediately suspicious that EuroKasios is contaminating the aviary Sanctuary and endangering the Greater Eurasian Red-Throated Pipit. Worse yet, Mitch wonders if Kasios International may be contaminating pipit habitats around the world. Mitch spends weeks reviewing press coverage of Kasios International and public statements made by the company’s CEO. Many of the articles tout the company’s environmentally friendly practices and even talk about recent scientific research that proves the safety of AGOC-3A, an environmentally friendly replacement for Methylosmolene. Mitch is skeptical that the company has halted production and use of Methylosmolene so he contacts the company and asks to meet with representatives from their environmental compliance division. To his disappointment Mitch is not granted a meeting. Then Mitch receives an anonymous letter from someone who’s willing to help. A fellow pipit lover who works at Kasios International has gathered up a variety of company data and identified a suspicious group within the company. Attached to the letter Mitch receives is a USB drive with phone, email, meeting, and procurement records for Kasios International over the past 2 1/2 years. Mitch wonders if the fate of the Eurasian Pipit lies somewhere in that data. Mitch intends to put this data together to see if the problems with Kasios are much larger than he initially suspected. Here is the challenge:

• To make sure whether Kasios International has grown up for two years. I try to combine all the csv files together, namely “calls.csv”, “emails.csv”, “phrases.csv” and “meetings.csv”, then show the number of transaction happened in these years, if it increases, which means Kasios International has grown up.
• For the second question, I need to find more suspicious people who has connection with the suspicious people provided by an anonymous guy.
• Then I try to build a social network and next question should base on this network to dig out more key figures and find more insights.

Data Preparation

1. Creating big dataset with all transactions Combining all csv file together vertically not horizontally, I use python to do it and generate new file, namely “E_C_P_M.csv”

2. Expanding the range of possible suspects Using the same way to combine other csv files, “Suspicious_calls.csv”, “Suspicious_emails.csv”, “Suspicious_meetings.csv”, “Suspicious_purchases.csv” and “Other_suspicious_purchases.csv”, then matching suspicious people from “E_C_P_M.csv”, no matter this guy is “source” or “target”, the transaction will be recoded in new file.

3. Creating node and edge for social analysis. For this part I need to build an interactive network for all the suspicious people, Gephi is a good choice, so here still using python to do it. I only show the code about how can I create “node.csv” blew.

Data Visualization

1. According to the company’s communications and purchase habits, is the company growing? From graph blew, generally, this company has grown up, but this change was extremely obvious between 2015 Q2 and 2015 Q3, you can see there is a sharp increase happen in that time for the number of emails and calls. Also, the number of meetings continues to grow from 21 in 2015 Q2 to 30810 in 2017 Q4. Now challenge 1 done.

2. Is there anyone else appears to be closely associated with suscipious group? Now you can find there is guy whose name is Beth Wilensky, and you cannot find this name from anonymous letter, which means I found more people involved in the transaction. I also can export data to csv file, then matching the name already existed in that letter and removing them, so in rest part where I can find more suspicious people.

3. What should this social network look like? I list suspicious name copied from anonymous letter (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.), also you can see in the picture above, the key figure, which is big point, can be found in this list, which means this social network result should be reasonable. From the graph blew, you can see that name like Richard Fox, Tobi Gatlin Lindsy Henion and Patrick Lane can be founded in that suspicious name list, also based on that list I expand this network, here you can find people like Alpha Chessor, Laure Pelkey and Beth Wilensky are associated with those guy, now if we want to further research who involve transaction we can start from these new suspicious people.

4. Who is Gail Feindt? There is a people in this network only connect with other key figure, I show you here.

Gail Feindt is not involved in that suspicious list but this guy only connects with big point, which means he is a very import people, from “CompanyIndex.csv” I find his ID is “2040565”, then using filter function in Tableau to find more insights. I do a dashboard to compare the difference between excluding “2040565” and including “2040565”.

From the above picture, we can find that almost all purchases are related to Gail Feindt, which means that other key figure will contact him when purchasing chemicals. Therefore, further exploration of Gail Feindt's background can basically determine whether the company has been buying and using Chemicals.

Deliverable

Please visit Dataset Revealed.