user2023
Reputation: 478
Pandas selecting the values from Multilevel columns
I have a below small tested pandas code, where i have an excl file called Firmware_sheet.xlsx where we have multiple details which i see divided into multilevel columns, So from there i need to extract some information.
I am just learning how to work in multilevel Columns, i tried but not able to get it for now..
Pandas code:
#!/usr/bin/python3
import pandas as pd
pd.set_option('display.width', None)
pd.set_option('expand_frame_repr', True)
firmware = pd.ExcelFile("Firmware_sheet.xlsx")
df = firmware.parse("Sheet1")
print(df)
DataFrame output:
$ ./Firmware_pandas.py
Applicance Name Appliance FQDN Location Applicance Model Appliance Details BTfra Details Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10 Unnamed: 11 Unnamed: 12 Unnamed: 13
0 NaN NaN NaN NaN Composer Synergy 20Gb BTterconnect LBTk Module NaN NaN Synergy 12Gb SAS Connection Module NaN NaN Virtual Connect SE 40Gb F8 Module for Synergy NaN NaN
1 NaN NaN NaN NaN Firmware Verion Firmware Version Compatible with Composer ? Supported Version Firmware Verion Compatible with Composer ? Supported Version Firmware Verion Compatible with Composer ? Supported Version
2 ov0001 ov0001.pti.TN-cpq01.test.com TN-CPQ01 Synergy Composer 4.20.02-0395878 NA# NA# NA# 1.2.4.0 No 1.5.11.0 1.3.1.1003 No 1.4.1.1003
3 ov0002 ov0002.pti.mx-gdl01.test.com MX-GDL01 Synergy Composer 4.20.02-0395878 NA# NA# NA# NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
4 ov0004 ov0004.pti.AM-aAM01.test.com AM-AAM01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
5 ov1001 ov1001.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
6 ov1002 ov1002.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.08 No 2.04 NA# NA# NA# 1.2.0.1005 No 1.4.1.1003
7 ov2001 ov2001.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
8 ov2002 ov2002.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
9 ov2003 ov2003.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.3.1002 No 1.4.1.1003
10 ov2004 ov2004.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.3.1002 No 1.4.1.1003
11 ov2005 ov2005.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
12 ov2006 ov2006.pti.AM-PTC01.test.com AM-PTC02 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
13 ov2007 ov2007.pti.AM-PTC01.test.com AM-PTC01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
14 ov3001 ov3001.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
15 ov3002 ov3002.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
16 ov3003 ov3003.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
17 ov3004 ov3004.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
18 ov3005 ov3005.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
19 ov3006 ov3006.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# NA# NA# NA# 1.5.0.1004 NaN NA#
20 ov3007 ov3007.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# NA# NA# NA# 1.5.0.1004 NaN NA#
21 ov3008 ov3008.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# NA# NA# NA# 1.5.0.1004 NaN NA#
22 ov4001 ov4001.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
23 ov4002 ov4002.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
24 ov4003 ov4003.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
25 ov4004 ov4004.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
26 ov4005 ov4005.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
27 ov4006 ov4006.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# 1.5.105.0 NA# NA# 1.5.0.1004 NaN NA#
28 ov4007 ov4007.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# NA# NA# NA# 1.5.0.1004 NaN NA#
29 ov4008 ov4008.pti.HL-PTC01.test.com HL-PTC01 Synergy Composer 5.00.01-0410269 1.18 NA# NA# NA# NA# NA# 1.5.0.1004 NaN NA#
30 ov6001 ov6001.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
31 ov6002 ov6002.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
32 ov6003 ov6003.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.18 No 2.04 NA# NA# NA# 1.5.0.1004 No NA#
33 ov6004 ov6004.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
34 ov6005 ov6005.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
35 ov6006 ov6006.BT-blr01.test.com BT-BLR01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
36 ov7001 ov7001.cn-sha01.test.com CN-SHA01 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.1.1003 No 1.4.1.1003
37 ov8001 ov8001.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
38 ov8002 ov8002.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
39 ov8003 ov8003.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
40 ov8004 ov8004.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
41 ov8005 ov8005.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
42 ov8006 ov8006.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
43 ov8007 ov8007.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
44 ov8008 ov8008.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.16 No 2.04 NA# NA# NA# 1.3.0.1005 No 1.4.1.1003
45 ov8009 ov8009.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 4.20.02-0395878 1.17 No 2.04 NA# NA# NA# 1.4.0.1003 No 1.4.1.1003
46 OV8010 ov8010.pti.BT-nda02.test.com BT-NDA02 Synergy Composer 5.00.00-0400525 1.18 No 3.01 NA# NA# NA# 1.5.0.1004 Yes NA#
47 ovd001 ovd001.pti.HL-htc01.test.com HL-HTC01 Synergy Composer 4.20.02-0395878 1.17 No 2.04 1.5.11.0 Yes NA# 1.4.0.1003 No 1.4.1.1003
48 ovd002 ovd002.pti.HL-htc01.test.com HL-HTC01 Synergy Composer 5.00.01-0410269 1.18 No 3.01 NA# NA# NA# 1.5.0.1004 Yes NA#
49 ovt001 ovt001.cc.HL-htc01.test.com HL-HTC01 Synergy Composer 4.20.02-0395878 NA# NA# NA# 1.4.6.0 No 1.5.11.0 1.3.1.1003 No 1.4.1.1003
Though i have given the df details above but just to give a view to understand how this excl looks like is as follows..
Expected output:
Applicance Name Composer Firmware Verion Synergy 20Gb BTterconnect LBTk Module Firmware Version Synergy 12Gb SAS Connection Module Firmware Virtual Connect SE 40Gb F8 Module for Synergy Firmware Verion
4.20.02-0395878 1.17 1.17 1.17
Thanks for the any help on this.
Edit: (df.columns.tolist()) output as Jezrael asked.
[('Applicance Name', 'Unnamed: 0_level_1', 'Unnamed: 0_level_2'), ('Appliance FQDN', 'Unnamed: 1_level_1', 'Unnamed: 1_level_2'), ('Location', 'Unnamed: 2_level_1', 'Unnamed: 2_level_2'), ('Applicance Model', 'Unnamed: 3_level_1', 'Unnamed: 3_level_2'), ('Appliance Details', 'Composer', 'Firmware Verion'), ('BTfra Details', 'Synergy 20Gb BTterconnect LBTk Module', 'Firmware Version'), ('BTfra Details', 'Synergy 20Gb BTterconnect LBTk Module', 'Compatible with Composer ?'), ('BTfra Details', 'Synergy 20Gb BTterconnect LBTk Module', 'Supported Version'), ('BTfra Details', 'Synergy 12Gb SAS Connection Module', 'Firmware Verion'), ('BTfra Details', 'Synergy 12Gb SAS Connection Module', 'Compatible with Composer ?'), ('BTfra Details', 'Synergy 12Gb SAS Connection Module', 'Supported Version'), ('BTfra Details', 'Virtual Connect SE 40Gb F8 Module for Synergy', 'Firmware Verion'), ('BTfra Details', 'Virtual Connect SE 40Gb F8 Module for Synergy', 'Compatible with Composer ?'), ('BTfra Details', 'Virtual Connect SE 40Gb F8 Module for Synergy', 'Supported Version')]
Desired Columns..
[('Applicance Name', 'Unnamed: 0_level_1', 'Unnamed: 0_level_2'),('Appliance Details', 'Composer', 'Firmware Verion'),('BTfra Details', 'Synergy 20Gb BTterconnect LBTk Module', 'Firmware Version'),('BTfra Details', 'Synergy 12Gb SAS Connection Module', 'Firmware Verion'), ('BTfra Details', 'Virtual Connect SE 40Gb F8 Module for Synergy', 'Firmware Verion')]
Upvotes: 2
Views: 74
Answers (1)
jezrael
Reputation: 863291
One idea is use header=[0,1,2] forMultiIndex:
df = pd.read_excel("Firmware_sheet.xlsx", header=[0,1,2], sheet_name='Sheet1')
Then remove unnamed values and flaten columns:
df.columns = [' '.join(y for y in x if 'Unnamed' not in y) for x in df.columns]
Last select columns names - first column and all columns with Firmware Verion text:
df = df.filter(regex='Applicance Name|Firmware Verion')
Upvotes: 1
Related Questions
- Select rows in pandas MultiIndex DataFrame by giving values for several (but not all) levels
- Selecting the Sub-columns In MultiIndex DataFrame Pandas
- Select multi-index columns based on multiple second level labels in pandas data frame
- How to select a subset of values from a named column level in a DataFrame?
- How can multiple columns from different levels be selected?
- pandas select rows in a multi-level index dataframe
- Selecting sublevels of Multiindex columns in pandas
- how to select multiple values from a single level of a dataframe multiindex
- Selecting values from a Pandas DataFrame with a hierarchical index
- Selecting a column with multiple levels of column naming