In [5]:
# imports a library 'pandas', names it as 'pd'
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import json
# enables inline plots, without it plots don't show up in the notebook
%matplotlib inline
//anaconda/lib/python2.7/site-packages/matplotlib/__init__.py:872: UserWarning: axes.color_cycle is deprecated and replaced with axes.prop_cycle; please use the latter.
warnings.warn(self.msg_depr % (key, alt_key))
In [6]:
!ls -al
total 78952
drwxr-xr-x 16 satishterala staff 544 Dec 21 11:49 .
drwxr-xr-x 17 satishterala staff 578 Dec 21 02:38 ..
drwxr-xr-x 3 satishterala staff 102 Dec 21 09:42 .ipynb_checkpoints
-rw-r--r-- 1 satishterala staff 39986 Dec 21 11:49 Hackathon-Data-Analysis.ipynb
-rw-r--r-- 1 satishterala staff 264478 Dec 21 02:38 Intro_to_Pandas.ipynb
-rw-r--r-- 1 satishterala staff 2484241 Dec 21 09:48 cl-app2app.json
-rw-r--r-- 1 satishterala staff 7810071 Dec 21 09:47 cl-cleanapps.json
-rw-r--r-- 1 satishterala staff 1799611 Dec 21 09:46 cl-grmmember.json
-rw-r--r-- 1 satishterala staff 682056 Dec 21 09:45 cl-users-grp.json
-rw-r--r-- 1 satishterala staff 8004319 Dec 21 09:45 cl-users.json
-rw-r--r-- 1 satishterala staff 7225068 Dec 21 00:13 cmdb_ci_service_list.json
-rw-r--r-- 1 satishterala staff 2333220 Dec 21 00:17 cmdb_rel_ci_list.json
-rw-r--r-- 1 satishterala staff 1685529 Dec 21 00:14 sys_user_grmember_list.json
-rw-r--r-- 1 satishterala staff 634398 Dec 21 00:10 sys_user_group_list.json
-rw-r--r-- 1 satishterala staff 7427069 Dec 21 00:07 sys_user_list.json
-rw-r--r-- 1 satishterala staff 8805 Dec 21 00:15 u_business_application_list.json
In [44]:
def clean_json_return_df(filename,tempfilename):
with open(filename) as f:
rec_dict = json.load(f)
dict_list=[]
for line in rec_dict['records']:
int_dicts = {}
for key,val in line.iteritems():
int_dicts[key]=val
dict_list.append(int_dicts)
with open(tempfilename,'w')as outfile:
json.dump(dict_list,outfile)
return pd.read_json(tempfilename)
In [45]:
from pymongo import MongoClient
def insert_clean_json_mongo(filename,tempfilename,collection_name):
client = MongoClient("mongodb://sandwich:sandwich@ds035385.mongolab.com:35385/sandwich")
db = client.sandwich
df = clean_json_return_df(filename,tempfilename)
records = json.loads(df.T.to_json()).values()
db.collection_name.insert(records)
In [ ]:
users = clean_json_return_df('sys_user_list.json','cl-users.json')
users_grp = insert_clean_json_mongo('sys_user_group_list.json','cl-users-grp.json')
grmmember = clean_json_return_df('sys_user_grmember_list.json','cl-grmmember.json')
app_to_app = clean_json_return_df('cmdb_rel_ci_list.json','cl-app2app.json')
app_to_app = clean_json_return_df('cmdb_rel_ci_list.json','cl-app2app.json')
In [53]:
apps = clean_json_return_df('cmdb_ci_service_list.json','cl-apps.json')
In [52]:
app_cols = ["support_group","subcategory","u_business_unit","busines_criticality","u_type","u_wmc_app_id","u_wmc_central_category","used_for","u_support_tier_2","u_level","u_business_domain"]
apps[app_cols]
Out[52]:
support_group
subcategory
u_business_unit
busines_criticality
u_type
u_wmc_app_id
u_wmc_central_category
used_for
u_support_tier_2
u_level
u_business_domain
0
Asset Allocation Support
Information Technology
4 - Important - Day Two
Business Facing
SFIRAN
None
Production
Alternatives Support
(1)
Risk
1
Fixed Income Support
Trading
4 - Important - Day Two
Business Facing
FIMON
Client
Production
FIPC
(1)
Fixed Income Trading
2
Fixed Income Support
Investment
4 - Important - Day Two
MC
FRS
None
Production
FIPC
(1)
Portfolio Construction
3
Fixed Income Support
Investment
4 - Important - Day Two
Business Facing
MACDBA
None
Production
FIRST (Fixed Income Rapid Solutions)
(1)
Fixed Income Portfolio Management
4
Derivatives Operations App Team
Investment Administration
4 - Important - Day Two
Business Facing
DOSTCC
None
Production
DOS Support
(1)
Derivatives Operations
5
Derivatives Operations App Team
Investment Administration
6 - Non-Critical - When Ready
Business Facing
DOSMGN
None
Production
Derivatives Operations App Team
(1)
Derivatives Operations
6
Non-IT Supported Apps
Investment Administration
4 - Important - Day Two
Business Facing
BZ0124
None
Production
Non-IT Supported Apps
(1)
Fund Administration
7
Telecom Support
Information Technology
0 - Critical - Infrastructure
Business Facing
AVMXE
None
Production
Telecom Support
(1)
Telephone
8
Non-IT Supported Apps
Investment
5 - Necessary - End of Week
Business Facing
BZ0346
None
Production
Non-IT Supported Apps
(1)
Account Management
9
Trading Support
Trading
1 - Critical - Immediate
Business Facing
CTM
None
Production
Trading Support
(1)
Equity Trading
10
Derivatives Support
Investment
2 - Essential - Intraday
Business Facing
DCE
None
Production
Derivatives Support
(1)
Derivatives
11
Geneva Support
Investment Administration
1 - Critical - Immediate
Module - Business Facing
GEN
None
Production
Geneva Development
(1)
Portfolio Accounting Geneva
12
Non-IT Supported Apps
Client and Global Relationship Group
5 - Necessary - End of Week
Business Facing
BZ0203
None
Production
Non-IT Supported Apps
(1)
Client GRG Reporting
13
Solutions Center
Information Technology
4 - Important - Day Two
Desktop Software
PRJECT
None
Production
Desktop Support
(1)
Desktop Services
14
Non-IT Supported Apps
Investment Administration
5 - Necessary - End of Week
Business Facing
BZ0043
None
Production
Non-IT Supported Apps
(1)
Investment Coordination
15
Trading Support
Trading
3 - Required - End of Day
Business Facing
EQMTM
None
Production
GTS EQ Support
(1)
Equity Trading
16
Trading Support
Trading
1 - Critical - Immediate
Module - Resource Facing
GEQLNCH
None
Production
Trading GTS Equity Development
(1)
Equity Trading
17
DataFlux Support
Information Technology
4 - Important - Day Two
IT Facing
DFLX
None
Production
DataFlux Support
Database
18
Information Technology
9 - TBD
Business Facing
None
Production
(1)
19
Non-IT Supported Apps
Investment Administration
5 - Necessary - End of Week
Business Facing
BZ0151
None
Production
Non-IT Supported Apps
(1)
Investment Coordination
20
Legal and Compliance Systems Support
Legal and Compliance
8 - Non-Production
Business Facing
CLE
None
Production
Legal & Compliance Tier 2 Support
(1)
Compliance and Legal
21
Trading Support
Trading
1 - Critical - Immediate
MR
OMSFX1
None
Production
Trading Support
(1)
Fixed Income Trading
22
Fixed Income Support
Information Technology
1 - Critical - Immediate
MC
IV2K
None
Production
FIPC
(1)
Developer Tools
23
Solutions Center
Information Technology
2 - Essential - Intraday
Business Facing
MYPC
Firm
Production
Citrix Support
(1)
Windows Servers
24
Geneva Development
Information Technology
9 - TBD
Business Facing
None
Production
Geneva Development
(1)
25
Alternatives Support
Investment
2 - Essential - Intraday
Business Facing
AAMD
None
Production
Asset Allocation Support
(1)
Asset Allocation
26
Non-IT Supported Apps
Investment Administration
4 - Important - Day Two
Business Facing
BZ0162
None
Production
Non-IT Supported Apps
(1)
Fund Administration
27
GRG Client Support - Tier 1 Team
Client and Global Relationship Group
6 - Non-Critical - When Ready
Business Facing
QPRO
Client
Production
GRG Client Support
(1)
Relationship Management
28
Logical Data Architecture
Information Technology
2 - Essential - Intraday
IT Facing
WMCSS
None
Production
Logical Data Architecture
(1)
Database
29
Solutions Center
Information Technology
3 - Required - End of Day
IT Facing
VDI
None
Production
Citrix Support
(1)
Windows Servers
...
...
...
...
...
...
...
...
...
...
...
...
2614
Corporate Applications App Team
Corporate
6 - Non-Critical - When Ready
Business Facing
UNIFLO
None
Production
Corporate Applications App Team
(1)
Corporate
2615
Facilities
Information Technology
9 - TBD
Business Function
None
Production
Facilities
1
2616
Data Center Management
Information Technology
0 - Critical - Infrastructure
IT Function
None
Production
1
2617
Performance Support
Investment Administration
4 - Important - Day Two
Module - Business Facing
PMTDR
None
Production
Performance Support
(1)
Performance & Attribution
2618
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2619
Incentive Comp Support
Finance
5 - Necessary - End of Week
Module - Business Facing
IPM
None
Production
Corporate Applications App Team
(1)
Finance
2620
Security Operations
Client and Global Relationship Group
2 - Essential - Intraday
Business Facing
AKAMAI
None
Production
Security Operations
(1)
Internet Services
2621
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2622
Batch Cycle Support
Information Technology
9 - TBD
IT Function
None
Production
1
2623
Non-IT Supported Apps
Investment Administration
5 - Necessary - End of Week
Business Facing
BZ0095
None
Production
Non-IT Supported Apps
(1)
Fund Administration
2624
Fixed Income Support
Investment
6 - Non-Critical - When Ready
Module - Resource Facing
PMTCES
None
Production
Fixed Income Support
(1)
Portfolio Construction
2625
GRG Client Support - Tier 1 Team
Client and Global Relationship Group
4 - Important - Day Two
Business Facing
PCSBK
None
Production
PCS Publishing Systems
(1)
Document Management
2626
Batch Cycle Support
Information Technology
4 - Important - Day Two
IT Facing
WLMP
None
Production
Batch Cycle Support
(1)
Developer Tools
2627
Sharepoint Collaboration Team
Client and Global Relationship Group
4 - Important - Day Two
Resource Facing
MTLGX
None
Production
Sharepoint Collaboration Team
(1)
Document Management
2628
IC Support
Investment Administration
9 - TBD
Business Function
None
Production
ISP Support
1
2629
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2630
Solutions Center
Human Resources
5 - Necessary - End of Week
Business Facing
SPHRC
Self
Production
Corporate HR Applications Support
(1)
Human Resources
2631
Desktop Support
Information Technology
9 - TBD
IT Function
None
Production
1
2632
Non-IT Supported Apps
Client and Global Relationship Group
6 - Non-Critical - When Ready
Business Facing
BZ0247
None
Production
Non-IT Supported Apps
(1)
Client GRG Reporting
2633
Solutions Center
Information Technology
4 - Important - Day Two
Desktop Software
ADOFL
None
Production
Desktop Support
(1)
Desktop Services
2634
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2635
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2636
Non-IT Supported Apps
Investment
3 - Required - End of Day
Business Facing
BZ0292
None
Production
Non-IT Supported Apps
(1)
Account Management
2637
Fixed Income Support
Investment
3 - Required - End of Day
IT Facing
VS
None
Production
FIPC
(1)
Portfolio Construction
2638
Information Technology
9 - TBD
Business Facing
None
Production
(1)
2639
Desktop Support
Information Technology
0 - Critical - Infrastructure
Business Facing
TREIK
None
Production
Market Data Support
(1)
Market Data
2640
Legal and Compliance Systems Support
Legal and Compliance
4 - Important - Day Two
Business Facing
RMS
None
Production
Legal & Compliance Tier 2 Support
(1)
Regulatory
2641
Wintel Support
Information Technology
8 - Non-Production
IT Facing
FILE
None
Production
Wintel Support
(1)
Windows Servers
2642
Derivatives Operations App Team
Investment Administration
3 - Required - End of Day
Module - Business Facing
VMAT
None
Production
Derivatives Operations App Team
(1)
Derivatives Operations
2643
Legal and Compliance Systems Support
Legal and Compliance
5 - Necessary - End of Week
Module - Business Facing
CAT2
None
Production
Legal & Compliance Tier 2 Support
(1)
Compliance and Legal
2644 rows × 11 columns
In [54]:
app2app = clean_json_return_df('cmdb_rel_ci_list.json','cl-app2appp.json')
In [56]:
app2app.columns.tolist()
Out[56]:
[u'__status',
u'child',
u'connection_strength',
u'dv_child',
u'dv_connection_strength',
u'dv_parent',
u'dv_percent_outage',
u'dv_port',
u'dv_sys_created_by',
u'dv_sys_created_on',
u'dv_sys_id',
u'dv_sys_mod_count',
u'dv_sys_tags',
u'dv_sys_updated_by',
u'dv_sys_updated_on',
u'dv_type',
u'parent',
u'percent_outage',
u'port',
u'sys_created_by',
u'sys_created_on',
u'sys_id',
u'sys_mod_count',
u'sys_tags',
u'sys_updated_by',
u'sys_updated_on',
u'type']
In [58]:
app2app_cols=["child","__status","connection_strength","dv_parent","dv_child"]
In [59]:
app2app[app2app_cols]
Out[59]:
child
__status
connection_strength
dv_parent
dv_child
0
221650f4d40b9100f36d07695bd780af
success
occasional
Security
OpenID Connect Service
1
85112e27692b950426cdb070c6084ef9
success
occasional
Network
Riverbed2
2
00d7f3aa59906404fe1fd7f06d36fc58
success
occasional
Portfolio Accounting Geneva
Geneva Clients
3
0153c71d29e29d8826cd3a40979ed213
success
occasional
Equity Trading
GTS EQ Launcher
4
0153c71d29e29d8826cd3a40979ed213
success
always
GTS EQ
GTS EQ Launcher
5
05bcffc40ffcc6c4b62ad23be1050e99
success
occasional
Equity Trading
GTS EQ Data Maintenance Application
6
09d2c42a0a0a3c1b0014b99fc2251fd9
success
occasional
Client GRG Reporting
ER Administrator
7
09d663260a0a3c1b00eb26498f1553a2
success
occasional
Client GRG Reporting
ER Console
8
0b3531b8995b740426cdb28bf7bcea9a
success
occasional
Client GRG Reporting
ER - IDDS (ER for IDDS)
9
0cc6ec9119cdd90408b7c38d86c5167f
success
occasional
Email and Messaging
Message Stats
10
0d4196d49439644426cd898a26eb8a6d
success
occasional
Document Management
Sharepoint Batch Printing
11
0e2547dd29e29d8826cd3a40979ed2ea
success
always
GTS EQ
GTS EQ Client
12
0e2547dd29e29d8826cd3a40979ed2ea
success
occasional
Equity Trading
GTS EQ Client
13
117ca2480f777940661b563be1050e4b
success
occasional
Email and Messaging
AirWatch Secure Content Locker
14
13d478bf7dadf440f36d35fa4279e960
success
occasional
Human Resources
United Healthcare Link
15
15de279724427800f36d32d19034eee8
success
occasional
Finance
Peoplesoft Financials - Resource Management
16
18f372b2856df90008b7c4afef06d689
success
occasional
Infrastructure Monitoring
GM-Global Performance POC
17
19024ee7458f858008b71684cad65559
success
occasional
Compliance and Legal
Compliance Simulator & OOC
18
20aca1fb09779d84f36d81dbab024310
success
occasional
Equity Trading
GTS EQ Deal Interest Monitor
19
22a8e689fc6d2404f36d615ec484bf91
success
occasional
Client GRG Reporting
Hedge Fund OBI Application
20
2465c9b09957740426cdb28bf7bcea47
success
occasional
Client GRG Reporting
ER - Fund Administration (FA ER)
21
2559f9690a0a3c1b0165407d8d1ac9e9
success
occasional
Cash and Investment Support Liaison Group
Cash Monitoring Batch
22
281589f294f0e80026cd898a26eb8ae0
success
occasional
Asset Allocation
AA Risk Metrics
23
2838b282f512650026cd8769764e2d64
success
occasional
Windows Servers
WMC_MYPC_DEFAULT_PRINTER_FIX_01
24
2ac2332f7de5f440f36d35fa4279e995
success
always
Contracts Management System (CMS)
Documentum - CMS
25
2ac2332f7de5f440f36d35fa4279e995
success
always
Documentum
Documentum - CMS
26
2ac2332f7de5f440f36d35fa4279e995
success
occasional
Document Management
Documentum - CMS
27
2bef942b6d04fd00b0951825954ce068
success
occasional
Architecture
SLA Dashboard Test Driver
28
2c8cb9aac860f50008b78f4db8fb8a29
success
occasional
Compliance and Legal
BEAST - EQ
29
2dcd6f5724427800f36d32d19034eed4
success
occasional
Finance
Peoplesoft Financials - eProcurement
...
...
...
...
...
...
2768
6963efbf0a0a3c1b00628afaac8f5541
success
always
Alpha Rank System
Factset Workstation
2769
6963efbf0a0a3c1b00628afaac8f5541
success
occasional
Dow Jones Islamic Index
Factset Workstation
2770
6963efbf0a0a3c1b00628afaac8f5541
success
occasional
Dow Jones Wilshire US Family Indexes
Factset Workstation
2771
6963efbf0a0a3c1b00628afaac8f5541
success
occasional
Market Data
Factset Workstation
2772
6963f1030a0a3c1b0008e38c18e614e7
success
always
GTS EQ
Reuters Market Data System (RMDS)
2773
6963f1030a0a3c1b0008e38c18e614e7
success
occasional
Market Data
Reuters Market Data System (RMDS)
2774
6963f1030a0a3c1b0008e38c18e614e7
success
always
EPS - Enterprise Price Server
Reuters Market Data System (RMDS)
2775
8a04e2bf0a0a3c1b01e762bbb7f0e2f1
success
occasional
Market Data
EZ Edgar
2776
8a10b5df0a0a3c1b007e2958681fb5d7
success
occasional
Market Data
EZ Insider
2777
8e1864b90a0a3c1b01af6776fb814a8b
success
occasional
Market Data
IPC Turrets
2778
9a17447f7d69f440f36d35fa4279e91f
success
occasional
Market Data
IPTV
2779
a27347130a0a3c57011df2e8cd73362a
success
always
FIQS - FX Models
Bloomberg - MW
2780
a27347130a0a3c57011df2e8cd73362a
success
occasional
Market Data
Bloomberg - MW
2781
a6d130650a0a3c5701ae3690ee12a951
success
occasional
Market Data
NYSE FIX
2782
a76b1fe57d797080f36d35fa4279e99b
success
occasional
Market Data
IPC Unigy
2783
ad7b6be60a0a3c1b014ec2339202f175
success
occasional
Market Data
Simfund
2784
b24e4fa10a0a3c57000cdc56c90fae3c
success
occasional
Market Data
TradeWeb MDLink
2785
b88234b57c743940b0956ea5f4c38616
success
occasional
Market Data
DataStream DSGATE
2786
cbb1ae950a0a3c1b019986668c672b5f
success
occasional
Market Data
WONDA
2787
d51bad330a0a3c57014229097ebec9f2
success
occasional
Market Data
IPC Hoot (Enterprise Hoot and Intercom - EHI)
2788
d635724809cc61c4f36d81dbab02435f
success
occasional
Infrastructure Monitoring
IPC AFM
2789
efab06c20a0a3c1b0008fe2fd83a48a7
success
occasional
Market Data
Barra Flat Files
2790
f003e47e0a0a3c1b00719777bea51b11
success
occasional
Market Data
Mercer MPA
2791
f4880ca10a0a3c1b00c53f47ada63d02
success
occasional
Market Data
TNS FIX
2792
f4920f290a0a3c1b001fe3bf7fb282bd
success
always
GTS EQ
BT Radianz rFIX
2793
f4920f290a0a3c1b001fe3bf7fb282bd
success
always
OTS (Linux)
BT Radianz rFIX
2794
f4920f290a0a3c1b001fe3bf7fb282bd
success
always
GTS EQD
BT Radianz rFIX
2795
f4920f290a0a3c1b001fe3bf7fb282bd
success
occasional
Market Data
BT Radianz rFIX
2796
f4991bc80a0a3c1b013abcf0fd8564a7
success
occasional
Market Data
DTCC SMART
2797
ffbfbbfdf4633440f36db551f0141ea4
success
occasional
Market Data
Thomson Reuters Eikon
2798 rows × 5 columns
In [60]:
apps = clean_json_return_df('cmdb_ci_service_list.json','cl-apps.json')
In [64]:
Out[64]:
discovery_source
due_in
fault_count
sys_mod_count
count
0
0
2644
2644.000000
mean
NaN
NaN
0
10.182678
std
NaN
NaN
0
9.919593
min
NaN
NaN
0
0.000000
25%
NaN
NaN
0
3.000000
50%
NaN
NaN
0
7.000000
75%
NaN
NaN
0
16.000000
max
NaN
NaN
0
60.000000
In [ ]:
In [ ]:
In [ ]:
In [ ]:
Content source: satishkt/sudo_make_me_sandwich
Similar notebooks: