user2023
Reputation: 488
Pandas to parse the text data and assign the columns
I have below text data what I want to be parsed with pandas into column data. I need to have four columns as per four rows.
In the My trial output we need VC_VERSION to be splited into VC_ACTIVE_VERSION & VC_STANDBY_VERSION
raw data:
========= enc1001 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1002 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1003 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1004 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1005 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1006 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1007 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1008 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.40
2 HP VC Flex-10/10D Module 4.40
========= enc1009 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2001 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2002 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2003 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2004 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2005 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2006 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2007 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2008 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2009 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2011 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2013 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3020 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc3021 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc3022 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc3026 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.45
2 HP VC Flex-10/10D Module 4.45
========= enc3027 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3028 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3029 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3030 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3031 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4021 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4023 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4024 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4025 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4026 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4027 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4028 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4029 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4030 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4031 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4032 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4033 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4034 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc6002 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6011 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6012 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6013 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6014 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6015 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6016 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc6017 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.60
========= enc7002 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
========= enc7003 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
========= enc7004 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
========= enc7009 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1010 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1011 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1012 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1013 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1014 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1015 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1016 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1017 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1018 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc1025 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc1026 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2010 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2012 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2014 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2015 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2016 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2018 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2019 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2020 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2021 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2022 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc2023 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3033 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3034 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc3036 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc4020 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4022 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.41
2 HP VC Flex-10/10D Module 4.41
========= enc4035 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc7005 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc7006 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC FlexFabric 10Gb/24-Port Module 4.50
2 HP VC FlexFabric 10Gb/24-Port Module 4.50
========= enc7007 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc7008 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8001 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc8017 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc8018 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc8019 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc8021 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.50
2 HP VC Flex-10/10D Module 4.50
========= enc8022 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8023 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8024 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8025 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8026 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8027 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8028 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.62
2 HP VC Flex-10/10D Module 4.62
========= enc8033 =========
1 BladeSystem c7000 DDR2 Onboard Administrator with KVM 4.85
1 HP VC Flex-10/10D Module 4.40
2 HP VC Flex-10/10D Module 4.40
My trial:
df = pd.read_csv("file.txt", names=["col1"])
df = df.col1.str.split(' ', expand = True)
df = df.drop(df.columns[[0, 2, 3, 4, 5, 6, 7, 8, 11]], axis=1)
df = df.rename(columns={ 1: 'ENC_NAME', 9: 'VC_VERSION', 10: 'OA_VERSION'})
print(df)
ENC_NAME VC_VERSION OA_VERSION
0 enc1001 None None
1 KVM 4.85
2 4.50 None
3 4.50 None
4 enc1002 None None
5 KVM 4.85
6 4.50 None
7 4.50 None
8 enc1003 None None
9 KVM 4.85
10 4.50 None
11 4.50 None
12 enc1004 None None
13 KVM 4.85
14 4.50 None
15 4.50 None
----------------- data snipped ---------------
Desired:
ENC_NAME OA_VERSION VC_ACTIVE VC_STDN
enc1001 4.85 4.50 4.50
enc1002 4.85 4.50 4.50
enc1003 4.85 4.50 4.50
enc1004 4.85 4.50 4.50
enc1005 4.85 4.50 4.50
enc1006 4.85 4.50 4.50
enc1007 4.85 4.50 4.50
enc1008 4.85 4.40 4.40
enc1009 4.85 4.50 4.50
enc2001 4.85 4.50 4.50
enc2002 4.85 4.50 4.50
enc2003 4.85 4.50 4.50
Please help to get with pandas. Thanks in advance.
Upvotes: 1
Views: 68
Answers (2)
Stef
Reputation: 30679
The following takes the ENC_NAME from the header rows (with = signs) and the last value from all other rows assuming that rows are in the desired order. We then assign row and col numbers and pivot.
The advantage is that it doesn't depend on the number of spaces in your data.
df = pd.read_csv('file.txt', sep='\n',header=None, names=['col1'])
df['row'] = df.col1.str.contains('===').cumsum()
df['col'] = df.groupby('row').cumcount()
df['value'] = df.col1.str.extract('=== (.*) ===')
m = df.value.isna()
df.loc[m,'value'] = df.loc[m,'col1'].str.extract('(\d+\.\d+)$').values
df = df.pivot('row','col','value')
df.columns = ['ENC_NAME', 'OA_VERSION', 'VC_ACTIVE', 'VC_STDN']
Result:
ENC_NAME OA_VERSION VC_ACTIVE VC_STDN
row
1 enc1001 4.85 4.50 4.50
2 enc1002 4.85 4.50 4.50
3 enc1003 4.85 4.50 4.50
4 enc1004 4.85 4.50 4.50
5 enc1005 4.85 4.50 4.50
6 enc1006 4.85 4.50 4.50
7 enc1007 4.85 4.50 4.50
8 enc1008 4.85 4.40 4.40
9 enc1009 4.85 4.50 4.50
10 enc2001 4.85 4.50 4.50
11 enc2002 4.85 4.50 4.50
12 enc2003 4.85 4.50 4.50
13 enc2004 4.85 4.50 4.50
14 enc2005 4.85 4.50 4.50
15 enc2006 4.85 4.50 4.50
16 enc2007 4.85 4.50 4.50
17 enc2008 4.85 4.50 4.50
18 enc2009 4.85 4.50 4.50
19 enc2011 4.85 4.50 4.50
20 enc2013 4.85 4.50 4.50
21 enc3020 4.85 4.41 4.41
22 enc3021 4.85 4.41 4.41
23 enc3022 4.85 4.41 4.41
24 enc3026 4.85 4.45 4.45
25 enc3027 4.85 4.50 4.50
26 enc3028 4.85 4.50 4.50
27 enc3029 4.85 4.50 4.50
28 enc3030 4.85 4.50 4.50
29 enc3031 4.85 4.50 4.50
30 enc4021 4.85 4.41 4.41
31 enc4023 4.85 4.41 4.41
32 enc4024 4.85 4.41 4.41
33 enc4025 4.85 4.41 4.41
34 enc4026 4.85 4.50 4.50
35 enc4027 4.85 4.50 4.50
36 enc4028 4.85 4.50 4.50
37 enc4029 4.85 4.50 4.50
38 enc4030 4.85 4.50 4.50
39 enc4031 4.85 4.50 4.50
40 enc4032 4.85 4.50 4.50
41 enc4033 4.85 4.50 4.50
42 enc4034 4.85 4.50 4.50
43 enc6002 4.60 NaN NaN
44 enc6011 4.60 NaN NaN
45 enc6012 4.60 NaN NaN
46 enc6013 4.60 NaN NaN
47 enc6014 4.60 NaN NaN
48 enc6015 4.60 NaN NaN
49 enc6016 4.60 NaN NaN
50 enc6017 4.60 NaN NaN
51 enc7002 4.85 NaN NaN
52 enc7003 4.85 NaN NaN
53 enc7004 4.85 NaN NaN
54 enc7009 4.85 4.50 4.50
55 enc1010 4.85 4.50 4.50
56 enc1011 4.85 4.50 4.50
57 enc1012 4.85 4.50 4.50
58 enc1013 4.85 4.50 4.50
59 enc1014 4.85 4.50 4.50
60 enc1015 4.85 4.50 4.50
61 enc1016 4.85 4.50 4.50
62 enc1017 4.85 4.50 4.50
63 enc1018 4.85 4.50 4.50
64 enc1025 4.85 4.62 4.62
65 enc1026 4.85 4.50 4.50
66 enc2010 4.85 4.50 4.50
67 enc2012 4.85 4.50 4.50
68 enc2014 4.85 4.50 4.50
69 enc2015 4.85 4.50 4.50
70 enc2016 4.85 4.50 4.50
71 enc2018 4.85 4.50 4.50
72 enc2019 4.85 4.50 4.50
73 enc2020 4.85 4.50 4.50
74 enc2021 4.85 4.50 4.50
75 enc2022 4.85 4.50 4.50
76 enc2023 4.85 4.50 4.50
77 enc3033 4.85 4.50 4.50
78 enc3034 4.85 4.50 4.50
79 enc3036 4.85 4.50 4.50
80 enc4020 4.85 4.41 4.41
81 enc4022 4.85 4.41 4.41
82 enc4035 4.85 4.50 4.50
83 enc7005 4.85 4.50 4.50
84 enc7006 4.85 4.50 4.50
85 enc7007 4.85 4.62 4.62
86 enc7008 4.85 4.62 4.62
87 enc8001 4.85 4.50 4.50
88 enc8017 4.85 4.50 4.50
89 enc8018 4.85 4.50 4.50
90 enc8019 4.85 4.50 4.50
91 enc8021 4.85 4.50 4.50
92 enc8022 4.85 4.62 4.62
93 enc8023 4.85 4.62 4.62
94 enc8024 4.85 4.62 4.62
95 enc8025 4.85 4.62 4.62
96 enc8026 4.85 4.62 4.62
97 enc8027 4.85 4.62 4.62
98 enc8028 4.85 4.62 4.62
99 enc8033 4.85 4.40 4.40
Upvotes: 1
nstudenski
Reputation: 316
I would try downfilling the ENC_NAME and then doing a pivot. I wasn't sure exactly how to identify the values that you were looking for.
I assumed that :
1 HP VC Flex-10/10D Module corresponds with VC_ACTIVE
and
2 HP VC Flex-10/10D Module corresponds with VC_STDN
If there's a better way to identify the values, you could change the filter conditions in my code below.
Code
df = pd.read_csv("file.txt", names=["col1"])
df = df.col1.str.split(' ', expand = True)
df[1] = df[1].replace("",None).ffill()
df["colname"] = np.where(
df[9] == "KVM", "OA_VERSION", np.where(
df[2] == "1", "VC_ACTIVE", np.where(
df[2] == "2", "VC_STDN", None)))
df["value"] = np.where(
df[9] == "KVM", df[10], np.where(
df[2] == "1", df[9], np.where(
df[2] == "2", df[9], None)))
df["ENC_NAME"] = df[1]
df = df.pivot(index="ENC_NAME", columns="colname", values="value")\
[["OA_VERSION", "VC_ACTIVE", "VC_STDN"]]\
.reset_index()
df.columns.name = None
print(df)
Output:
ENC_NAME OA_VERSION VC_ACTIVE VC_STDN
enc1001 4.85 4.50 4.50
enc1002 4.85 4.50 4.50
enc1003 4.85 4.50 4.50
enc1004 4.85 4.50 4.50
enc1005 4.85 4.50 4.50
enc1006 4.85 4.50 4.50
enc1007 4.85 4.50 4.50
enc1008 4.85 4.40 4.40
enc1009 4.85 4.50 4.50
Upvotes: 1
Related Questions
- how to parse strings and apply them to dataframe
- Parsing Data From Pandas Dataframe
- Parsing data using pandas
- Parsing output (text) into dataframe with columns and rows
- Extracting columns of data using python
- how to read text data and Convert to pandas dataframe
- Pandas to parse the text data and aligned the columns based on condition
- Parsing a text file using pandas dataframe
- Python Parse a Text file and convert it to a dataframe
- parsing text file into pandas dataframe