In [1]:
import pandas as pd
In [2]:
df = pd.read_excel('device_inventory.xlsx')
In [3]:
# execute df command to see the contents in an excel like formatted dataframe
# last line shows the number of rows x number of columns in your dataframe
df
Out[3]:
Hostname Category Site ID Metallic Country Region Model Version IOS
0 012-ce01 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S5
1 012-ce02 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S3
2 017-ce01 Router SITE-017 Bronze Argentina LATAM ISR4331/K9 15.4(3)S4
3 039-ce01 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
4 039-ce02 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
... ... ... ... ... ... ... ... ...
9722 020-cs01 Switch SITE-020 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
9723 021-as01 Switch SITE-021 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9724 021-cs01 Switch SITE-021 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
9725 025-as01 Switch SITE-025 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9726 025-cs01 Switch SITE-025 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8

9727 rows × 8 columns

In [4]:
#execute .head() to see top5 rows
df.head()
Out[4]:
Hostname Category Site ID Metallic Country Region Model Version IOS
0 012-ce01 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S5
1 012-ce02 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S3
2 017-ce01 Router SITE-017 Bronze Argentina LATAM ISR4331/K9 15.4(3)S4
3 039-ce01 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
4 039-ce02 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
In [5]:
#execute .tail() to see last5 rows
df.tail()
Out[5]:
Hostname Category Site ID Metallic Country Region Model Version IOS
9722 020-cs01 Switch SITE-020 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
9723 021-as01 Switch SITE-021 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9724 021-cs01 Switch SITE-021 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
9725 025-as01 Switch SITE-025 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9726 025-cs01 Switch SITE-025 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
In [6]:
#specify the number of rows you want to see from top down
df.head(2)
Out[6]:
Hostname Category Site ID Metallic Country Region Model Version IOS
0 012-ce01 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S5
1 012-ce02 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S3
In [7]:
#specify the number of rows you want to see from bottom up
df.tail(2)
Out[7]:
Hostname Category Site ID Metallic Country Region Model Version IOS
9725 025-as01 Switch SITE-025 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9726 025-cs01 Switch SITE-025 Copper Vietnam AMENA WS-C3560V2-24PS-E 15.0(2)SE8
In [8]:
#Select a specific column
df['Hostname']
Out[8]:
0       012-ce01
1       012-ce02
2       017-ce01
3       039-ce01
4       039-ce02
          ...   
9722    020-cs01
9723    021-as01
9724    021-cs01
9725    025-as01
9726    025-cs01
Name: Hostname, Length: 9727, dtype: object
In [9]:
#select list of columns
df[['Hostname', 'Category']]
Out[9]:
Hostname Category
0 012-ce01 Router
1 012-ce02 Router
2 017-ce01 Router
3 039-ce01 Router
4 039-ce02 Router
... ... ...
9722 020-cs01 Switch
9723 021-as01 Switch
9724 021-cs01 Switch
9725 025-as01 Switch
9726 025-cs01 Switch

9727 rows × 2 columns

In [10]:
#you can chain any method on any df selection like below
df[['Hostname', 'Category']].head()
Out[10]:
Hostname Category
0 012-ce01 Router
1 012-ce02 Router
2 017-ce01 Router
3 039-ce01 Router
4 039-ce02 Router
In [11]:
#view rows, columns
df.shape
Out[11]:
(9727, 8)
In [12]:
#view df general info
# Column - gives column names
# Non-Null Count - how many rows are non null - helps to know if there are N/A values or blank cells for a column
# Dtypes - datatype of the values in that column
# memory usage. In this case 608KB.
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9727 entries, 0 to 9726
Data columns (total 8 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   Hostname     9727 non-null   object
 1   Category     9727 non-null   object
 2   Site ID      9727 non-null   object
 3   Metallic     9727 non-null   object
 4   Country      9727 non-null   object
 5   Region       9727 non-null   object
 6   Model        9727 non-null   object
 7   Version IOS  9727 non-null   object
dtypes: object(8)
memory usage: 608.1+ KB
In [13]:
#Find number of unique values in each column
df.nunique()
Out[13]:
Hostname       7014
Category          2
Site ID         822
Metallic         15
Country          70
Region            6
Model           162
Version IOS     191
dtype: int64
In [14]:
#Find unique values in a particular column
df['Hostname'].unique()
Out[14]:
array(['012-ce01', '012-ce02', '017-ce01', ..., '010-csm1', '016-csm1',
       '007-dmz01'], dtype=object)
In [15]:
#Find unique value counts in a column
#Gives the count of each unique value in a column
df['Category'].value_counts()
Out[15]:
Switch    7290
Router    2437
Name: Category, dtype: int64
In [16]:
#Select all routers
df[df['Category'] == 'Router']
# The df got filtered to 2437 rows × 8 columns where category column == 'Router'
#Similarly you can select all switches like
# df[df['Category'] == 'Switch']
Out[16]:
Hostname Category Site ID Metallic Country Region Model Version IOS
0 012-ce01 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S5
1 012-ce02 Router SITE-012 Gold Argentina LATAM ISR4331/K9 15.5(3)S3
2 017-ce01 Router SITE-017 Bronze Argentina LATAM ISR4331/K9 15.4(3)S4
3 039-ce01 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
4 039-ce02 Router SITE-039 Gold Argentina LATAM CISCO3925-CHASSIS 15.1(4)M8
... ... ... ... ... ... ... ... ...
2432 600-vpn1 Router SITE-600 Silver Venezuela LATAM 2811 12.4(15)T12
2433 007-vpn1 Router SITE-007 Copper Vietnam AMENA ISR4331/K9 16.3.7
2434 020-vpn1 Router SITE-020 Copper Vietnam AMENA ISR4331/K9 16.3.7
2435 021-vpn1 Router SITE-021 Copper Vietnam AMENA CISCO1921/K9 15.1(4)M1
2436 025-vpn1 Router SITE-025 Copper Vietnam AMENA CISCO1921/K9 15.1(4)M1

2437 rows × 8 columns

In [17]:
#Find all routers in United States
df[(df['Category'] == 'Router') & (df['Country'] == 'United States')]
Out[17]:
Hostname Category Site ID Metallic Country Region Model Version IOS
1390 002-ce01 Router SITE-002 Gold United States LATAM ISR4331/K9 15.5(3)S2
1391 002-ce02 Router SITE-002 Gold United States LATAM ISR4331/K9 15.5(3)S3
1392 002-ce03 Router SITE-002 Gold United States LATAM ISR4331/K9 16.3.6
1393 016-ce01 Router SITE-016 Platinum United States NORTH AMERICA ASR1004 15.5(3)S6b
1394 016-ce02 Router SITE-016 Platinum United States NORTH AMERICA ASR1006-X 16.3.4
... ... ... ... ... ... ... ... ...
2407 ce01-ffa Router SITE-993 Platinum United States NORTH AMERICA CISCO2921/K9 15.1(4)M4
2408 993-ce01 Router SITE-993 Platinum United States NORTH AMERICA ISR4451-X/K9 16.3.7
2409 993-ce02 Router SITE-993 Platinum United States NORTH AMERICA ISR4451-X/K9 16.3.7
2410 994-ce01 Router SITE-994 Bronze+ United States NORTH AMERICA ISR4351/K9 15.5(3)S5
2411 997-ce01 Router SITE-997 Bronze+ United States NORTH AMERICA ISR4351/K9 16.6.8

1022 rows × 8 columns

In [18]:
#Find all routers in United States that belong to Gold Metallic
df[(df['Category'] == 'Router') & (df['Country'] == 'United States') & (df['Metallic'] == 'Gold')]
Out[18]:
Hostname Category Site ID Metallic Country Region Model Version IOS
1390 002-ce01 Router SITE-002 Gold United States LATAM ISR4331/K9 15.5(3)S2
1391 002-ce02 Router SITE-002 Gold United States LATAM ISR4331/K9 15.5(3)S3
1392 002-ce03 Router SITE-002 Gold United States LATAM ISR4331/K9 16.3.6
1406 1001-ce01 Router SITE-1001 Gold United States NORTH AMERICA ISR4451-X/K9 16.3.5
1407 1001-ce02 Router SITE-1001 Gold United States NORTH AMERICA ISR4451-X/K9 16.3.6
... ... ... ... ... ... ... ... ...
2402 990-ce01 Router SITE-990 Gold United States NORTH AMERICA ISR4451-X/K9 15.5(3)S5
2403 990-ce02 Router SITE-990 Gold United States NORTH AMERICA ISR4451-X/K9 16.3.6
2404 ce01-ffa Router SITE-991 Gold United States NORTH AMERICA CISCO2921/K9 15.1(4)M4
2405 991-ce01 Router SITE-991 Gold United States NORTH AMERICA ISR4451-X/K9 15.5(3)S5
2406 991-ce02 Router SITE-991 Gold United States NORTH AMERICA ISR4351/K9 16.3.6

194 rows × 8 columns

In [19]:
# May be you want to find out all devices that are running 16.3.6
df[df['Version IOS'] == '16.3.6']
Out[19]:
Hostname Category Site ID Metallic Country Region Model Version IOS
5 040-ce01 Router SITE-040 Silver Argentina LATAM ISR4331/K9 16.3.6
6 040-vpn1 Router SITE-040 Silver Argentina LATAM ISR4331/K9 16.3.6
16 006-vpn1 Router SITE-006 Silver Australia AMENA ISR4351/K9 16.3.6
18 028-ce01 Router SITE-028 Bronze+ Australia AMENA ISR4331/K9 16.3.6
21 001-vpn01 Router SITE-001 Copper Azerbaijan ESSA ISR4331/K9 16.3.6
... ... ... ... ... ... ... ... ...
9716 007-as02 Switch SITE-007 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9717 007-as03 Switch SITE-007 Copper Vietnam AMENA WS-C3650-24PS 16.3.6
9719 007-dmz01 Switch SITE-007 Copper Vietnam AMENA WS-C3650-24TS 16.3.6
9723 021-as01 Switch SITE-021 Copper Vietnam AMENA WS-C3650-48PS 16.3.6
9725 025-as01 Switch SITE-025 Copper Vietnam AMENA WS-C3650-48PS 16.3.6

1409 rows × 8 columns

In [20]:
# Find all devices that have -dmz in the name
df[df['Hostname'].str.contains('-dmz')]
Out[20]:
Hostname Category Site ID Metallic Country Region Model Version IOS
3682 241-dmz1 Switch SITE-241 Gold India AMENA C9300-48U 16.9.3
4210 612-dmz01 Switch SITE-612 Gold Mexico LATAM WS-C3750G-24TS-S1U 12.2(55)SE8
4485 067-dmz1 Switch SITE-067 Gold+ Poland ESSA C9300-48U 16.9.3
5513 036-dmz01 Switch SITE-036 Silver Turkey ESSA WS-C3650-24TS 16.3.8
7723 414-dmz01 Switch SITE-414 Gold United States NORTH AMERICA WS-C3650-24TS 16.6.8
7857 468-dmz01 Switch SITE-468 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8230 591-dmz01 Switch SITE-591 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8333 629-dmz01 Switch SITE-629 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8417 661-dmz01 Switch SITE-661 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8557 707-dmz01 Switch SITE-707 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8672 747-dmz01 Switch SITE-747 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8696 754-dmz01 Switch SITE-754 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
8768 790-dmz01 Switch SITE-790 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.6
8834 809-dmz01 Switch SITE-809 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
9495 980-dmz01 Switch SITE-980 Platinum United States NORTH AMERICA WS-C3650-24TS 16.3.6
9628 988-dmz01 Switch SITE-988 Gold+ United States NORTH AMERICA WS-C3650-24TS 16.3.8
9640 990-dmz01 Switch SITE-990 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
9719 007-dmz01 Switch SITE-007 Copper Vietnam AMENA WS-C3650-24TS 16.3.6
In [21]:
#Select row where hostname is 707-dmz01
df[df['Hostname'] == '707-dmz01']
Out[21]:
Hostname Category Site ID Metallic Country Region Model Version IOS
8557 707-dmz01 Switch SITE-707 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
In [22]:
#the above can also be done using .loc method which is more preferred.
df.loc[df['Hostname'] == '707-dmz01']
# Read it like, find the .loc(location) where Hostname == '707-dmz01'
Out[22]:
Hostname Category Site ID Metallic Country Region Model Version IOS
8557 707-dmz01 Switch SITE-707 Gold United States NORTH AMERICA WS-C3650-24TS 16.3.8
In [23]:
#Sort by Hostname
df.sort_values(by=['Hostname'])
Out[23]:
Hostname Category Site ID Metallic Country Region Model Version IOS
3722 001-as01 Switch SITE-001 Gold Mexico LATAM C9300-24P 16.9.3
4354 001-as01 Switch SITE-001 Silver Pakistan AMENA WS-C3650-48PS 16.6.8
4352 001-as01 Switch SITE-001 Bronze Norway ESSA WS-C3850-12X48U 03.07.04E
4340 001-as01 Switch SITE-001 Silver New Zealand AMENA WS-C3650-48PS 16.3.8
4322 001-as01 Switch SITE-001 Silver Netherlands ESSA WS-C3650-12X48UQ 16.3.6
... ... ... ... ... ... ... ... ...
5306 cs02-ffa Switch SITE-002 Gold Singapore AMENA WS-C3850-12XS 16.3.8
2880 cs02-ffa Switch SITE-051 Gold Canada NORTH AMERICA WS-C2960G-8TC-L 12.2(50)SE5
9138 cs03-ffa Switch SITE-940 Bronze+ United States NORTH AMERICA WS-C3650-48PS 03.07.04E
3789 ffa-as01 Switch SITE-010 Gold Mexico LATAM WS-C3650-24PS 16.3.8
3790 ffa-cs01 Switch SITE-010 Gold Mexico LATAM C9300-24S 16.12.1

9727 rows × 8 columns

In [24]:
#Sort by Hostname first and then Country
df.sort_values(by=['Hostname', 'Country'])
Out[24]:
Hostname Category Site ID Metallic Country Region Model Version IOS
2471 001-as01 Switch SITE-001 Silver Australia AMENA WS-C3650-48PS 16.3.8
2502 001-as01 Switch SITE-001 Copper+ Belarus ESSA WS-C3650-48PS 16.3.6
2525 001-as01 Switch SITE-001 Silver Bosnia ESSA WS-C3650-48PS 16.6.8
2741 001-as01 Switch SITE-001 Bronze+ Canada NORTH AMERICA C9300-24UX 16.9.3
3085 001-as01 Switch SITE-001 Bronze+ Chile LATAM WS-C3650-48PS 16.3.8
... ... ... ... ... ... ... ... ...
7289 cs02-ffa Switch SITE-304 Gold United States NORTH AMERICA WS-C3560G-48PS-S 12.2(50)SE1
9137 cs02-ffa Switch SITE-940 Bronze+ United States NORTH AMERICA WS-C3650-48PS 03.07.04E
9138 cs03-ffa Switch SITE-940 Bronze+ United States NORTH AMERICA WS-C3650-48PS 03.07.04E
3789 ffa-as01 Switch SITE-010 Gold Mexico LATAM WS-C3650-24PS 16.3.8
3790 ffa-cs01 Switch SITE-010 Gold Mexico LATAM C9300-24S 16.12.1

9727 rows × 8 columns

In [25]:
# Find all 3750 switches in inventory
df[df['Model'].str.contains('3750')]
Out[25]:
Hostname Category Site ID Metallic Country Region Model Version IOS
2461 039-as01 Switch SITE-039 Gold Argentina LATAM WS-C3750X-24T-S 12.2(55)SE5
2481 005-as08 Switch SITE-005 Silver Australia AMENA WS-C3750-48PS-E 12.2(55)SE12
2513 014-cs01 Switch SITE-014 Silver Belgium ESSA WS-C3750X-24P-E 15.2(4)E10
2514 014-cs02 Switch SITE-014 Silver Belgium ESSA WS-C3750X-24P-E 15.2(4)E10
3213 085-as01 Switch SITE-085 Silver China AMENA WS-C3750V2-48PS-E 15.0(2)SE8
... ... ... ... ... ... ... ... ...
9556 988-as16 Switch SITE-988 Gold+ United States NORTH AMERICA WS-C3750G-12S-S 12.2(50)SE1
9581 988-as41 Switch SITE-988 Gold+ United States NORTH AMERICA WS-C3750G-12S-S 12.2(50)SE1
9695 002-as01 Switch SITE-002 Silver Venezuela LATAM WS-C3750-24PS-E 12.2(55)SE3
9699 002-as05 Switch SITE-002 Silver Venezuela LATAM WS-C3750-48PS-S 12.2(55)SE11
9701 003-cs01 Switch SITE-003 Silver Venezuela LATAM WS-C3750-48PS-S 12.2(55)SE8

90 rows × 8 columns

In [26]:
# See how many unique models of 3750 we have
df[df['Model'].str.contains('3750')]['Model'].value_counts()
Out[26]:
WS-C3750G-12S-S       19
WS-C3750V2-48PS-S     13
WS-C3750-48PS-S        8
WS-C3750V2-48PS-E      6
WS-C3750X-48P-E        5
WS-C3750X-24P-E        4
WS-C3750X-12S-S        3
WS-C3750G-48TS-S       3
WS-C3750X-48P-L        3
WS-C3750X-24P-S        3
WS-C3750-24FS-S        2
WS-C3750G-24TS-S1U     2
WS-C3750X-24S-E        2
WS-C3750X-24T-E        2
WS-C3750X-24T-S        2
WS-C3750X-48P-S        2
WS-C3750V2-24PS-E      2
WS-C3750X-48T-L        1
WS-C3750-24PS-E        1
WS-C3750-48PS-E        1
WS-C3750V2-48TS-S      1
WS-C3750E-48TD-E       1
WS-C3750X-12S-E        1
WS-C3750G-24PS-S       1
WS-C3750-48TS-S        1
WS-C3750G-12S-E        1
Name: Model, dtype: int64
In [27]:
# See how many 3750s do we have
df[df['Model'].str.contains('3750')]['Model'].count()
Out[27]:
90
In [28]:
# See how many unique 3750 models do we have
df[df['Model'].str.contains('3750')]['Model'].unique()
Out[28]:
array(['WS-C3750X-24T-S', 'WS-C3750-48PS-E', 'WS-C3750X-24P-E',
       'WS-C3750V2-48PS-E', 'WS-C3750V2-48PS-S', 'WS-C3750X-48P-S',
       'WS-C3750X-24P-S', 'WS-C3750X-48P-E', 'WS-C3750-48PS-S',
       'WS-C3750G-24TS-S1U', 'WS-C3750E-48TD-E', 'WS-C3750X-48P-L',
       'WS-C3750G-12S-S', 'WS-C3750V2-24PS-E', 'WS-C3750X-48T-L',
       'WS-C3750-48TS-S', 'WS-C3750G-48TS-S', 'WS-C3750V2-48TS-S',
       'WS-C3750X-12S-E', 'WS-C3750X-24S-E', 'WS-C3750G-24PS-S',
       'WS-C3750G-12S-E', 'WS-C3750X-24T-E', 'WS-C3750X-12S-S',
       'WS-C3750-24FS-S', 'WS-C3750-24PS-E'], dtype=object)
In [29]:
# See how many unique 3750 models in count do we have
df[df['Model'].str.contains('3750')]['Model'].nunique()
Out[29]:
26
In [30]:
df['Version IOS'].unique()
Out[30]:
array(['15.5(3)S5', '15.5(3)S3', '15.4(3)S4', '15.1(4)M8', '16.3.6',
       '16.3.7', '15.5(3)S6', '16.6.8', '15.1(4)M1', '15.5(3)S4b',
       '15.5(3)S2', '15.5(2)S3', '16.3.5', '16.6.4', '15.5(3)M5',
       '15.5(3)M6a', '15.1(4)M', '12.4(10b)', '16.12.4', '15.1(4)M5',
       '15.6(3)M8', '12.4(12)', '15.1(4)M6', '12.4(24)T8', '15.2(4)M6',
       '15.5(2)S1c', '15.4(3)M1', '16.6.3', '15.1(4)M4', '15.5(3)S1a',
       '16.4.2', '15.5(1)T1', '15.4(3)M4', '15.4(3)M3', '15.4(1)T1',
       '16.9.2', '15.5(3)S6b', '16.4.1', '12.4(15)T12', '15.3(2)T',
       '12.4(9)T7', '15.1(2)T5', '12.4(24)T6', '16.8.1', '15.2(4)M3',
       '15.4(3)S2', '12.4(23a)', '12.4(15)T7', '15.5(3)M8', '15.2(4)M6a',
       '15.3(3)S3', '15.3(3)S4', '12.4(24)T7', '12.4(24)T3', '15.4(3)M2',
       '15.1(1)T', '15.2(4)M5', '15.3(3)M2', '16.7.1', '15.5(2)T2',
       '16.3.8', '16.3.4', '15.1(4)M3', '15.1(4)M12a', '12.4(25e)',
       '12.4(15)T8', '12.4(11)T', ' -- ', '16.3.3', '15.3(3)M4',
       '12.4(3)', '03.06.04.E', '03.07.04.E', '03.08.05.E', '03.03.05SE',
       '12.2(55)SE5', '03.06.05.E', '03.07.05.E', '15.0(2)SE8',
       '12.2(55)SE12', '15.2(4)E3', '03.06.06E', '15.2(7)E2',
       '15.2(4)E10', '15.2(4)E6', '03.07.04E', '15.2(3)E1', '16.9.3',
       '03.07.05E', '03.03.03SE', '16.3.2', '03.11.57.EMP', '15.2(4)E',
       '15.2(3)E', '15.2(4)E5', '03.08.06.E', '12.2(55)SE8', '15.2(4)E7',
       '15.0(2)SE11', '12.2(50)SE5', '03.08.03.E', '15.1(2)SY8',
       '12.2(50)SE1', '15.2(4)E8', '12.2(55)SE7', '03.08.10.E',
       '15.0(2)SE5', '12.2(58)SE1', '15.2(2)SY1', '15.2(2)SY2',
       '16.3.8prd2', '16.6.5', '16.9.5', '16.3.5b', '15.0(2)SE10a',
       '03.06.06.E', '15.1(2)SY2', '15.2(7)E1', '15.2(4)E4', '03.06.03.E',
       '03.06.03E', '16.9.4', '16.11.1', '15.2(2)E5', '03.08.01.E',
       '16.12.1', '16.6.6', '12.2(58)SE2', '03.03.04SE', '16.12.3a',
       '16.12.02', '12.2(50)SE2', '12.2(55)SE3', '03.07.02E', '16.6.7',
       '03.07.03E', '12.2(40)SE', '12.2(55)SE6', '15.2(5)E2',
       '12.2(33)SXI5', '12.2(46)SE', '15.0(1)SE3', '12.2(50)SE4',
       '15.0(2)SE6', '12.2(44)SE1', '15.0(2)SE10', '15.2(5)E1',
       '15.2(1)SY6', '12.2(55)SE10', '03.07.03.E', '15.0(2)EX5',
       '15.2(1)E1', '15.2(2)E3', '12.2(55)SE', '15.0(2a)EX5',
       '03.03.02.XO', '03.03.00.XO', '15.2(2)E4', '15.0(2)EX4',
       '15.2(6)E', '15.2(2)E9', '15.3(1)SY', '15.5(1)SY1', '15.5(1)SY2',
       '12.2(18)SXF6', '12.2(18)SXF7', '12.2(31)SGA9', '12.2(33)SXI4a',
       '15.4(1)SY1', '15.2(4)E2', '15.3(1)SY2', '12.2(53)SG1',
       '12.2(33)SXH5', '03.08.02.E', '15.1(2)SY4a', '16.6.2',
       '12.2(53)SG6', '12.2(25)SEE2', '12.2(55)SE9', '03.07.00E',
       '15.2(3)E2', '15.1(2)SY5', '12.1(22)EA13', '03.08.04.E',
       '15.2(4)EA9', '12.2(25)SEE1', '12.2(53)SE', '15.4(1)SY2',
       '15.0(1)SE', '12.2(55)SE11', '15.0(2)SG10'], dtype=object)
In [31]:
# By visual inspection we can tell that all the IOS versions we have start with
# 12.x, 15.x, 16.x or 03.x
# What if we need to find out number of devices for each unique IOS version
df.groupby('Version IOS')['Hostname'].count()
Out[31]:
Version IOS
 --             12
03.03.00.XO      1
03.03.02.XO     27
03.03.03SE       1
03.03.04SE       3
              ... 
16.8.1           3
16.9.2          16
16.9.3         903
16.9.4          20
16.9.5           1
Name: Hostname, Length: 191, dtype: int64
In [32]:
df.groupby('Version IOS')['Hostname'].unique()
Out[32]:
Version IOS
 --            [1244-ce01, 1339-ce02, 313-as03, 313-as07, 313...
03.03.00.XO                                           [024-cs08]
03.03.02.XO    [024-cs03, 024-cs04, 049-cs03, 049-cs04, 915-a...
03.03.03SE                                            [011-cs01]
03.03.04SE                        [126-cs01, 084-cs03, 084-cs04]
                                     ...                        
16.8.1                            [629-ce01, 722-vpn1, 010-ce01]
16.9.2         [003-vpn1, 241-ce01, 241-ce02, 067-ce01, 067-c...
16.9.3         [006-cs01, 001-as01, 001-as99, 001-cs01, 003-a...
16.9.4         [241-as09, 241-as10, 241-as11, 241-as12, 241-a...
16.9.5                                                [007-as03]
Name: Hostname, Length: 191, dtype: object
In [33]:
df.groupby('Version IOS')['Hostname'].nunique()
Out[33]:
Version IOS
 --             12
03.03.00.XO      1
03.03.02.XO     27
03.03.03SE       1
03.03.04SE       3
              ... 
16.8.1           3
16.9.2          16
16.9.3         881
16.9.4          20
16.9.5           1
Name: Hostname, Length: 191, dtype: int64
In [34]:
df.groupby('Version IOS')['Hostname'].value_counts()
Out[34]:
Version IOS  Hostname 
 --          027-cs01     1
             047-as14     1
             085-as02     1
             1237-ds01    1
             1244-ce01    1
                         ..
16.9.4       241-as12     1
             241-as13     1
             701-cs01     1
             910-as02     1
16.9.5       007-as03     1
Name: Hostname, Length: 9108, dtype: int64
In [35]:
# Imagine we want to know the number of devices running per each major IOS release.
# Number of devices running 12.x, 15.x, 16.x and so on.
In [36]:
# use regex to extract the value befor first decimal in the IOS version field and assign it to a new column called First Index
df['First index'] = df['Version IOS'].str.split('(\d+)\.', expand=True)[1]
In [37]:
df['First index'].value_counts()
Out[37]:
16    6246
15    2276
03     794
12     399
Name: First index, dtype: int64
In [38]:
df.groupby('First index')['Hostname'].value_counts()
Out[38]:
First index  Hostname
03           001-cs01    9
             003-cs01    6
             002-as01    5
             012-cs01    5
             001-as01    4
                        ..
16           as33-ffa    1
             as34-ffa    1
             ce01-ffa    1
             ffa-as01    1
             ffa-cs01    1
Name: Hostname, Length: 7960, dtype: int64
In [ ]: