Python Forum
How to extract specific key value pair from string?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to extract specific key value pair from string?
#1
Hi,

I am new to python programming and need your help.

I have a Notes Column in excel file which contains data as below

ROW 1: Country:USA

State:Virginia
Serial Number: 00 DB C0 B1 E3 D5 05 7B 57 BE 3A BB FF D1 62 D6 A7
Address: 23 xys lane

SSN:2345550404
Zip : 22102


ROW 2 : Country:India

State:Virginia
Serial Number: 01 DB C0 B1 E3 D5 05 7B 57 BE 3A BB FF D1 62 D6 A7
SSN:2345550404
ZIP:22033
City: Fairfax

I need to extract the Serial Number from the string and create a new column for it.

I have the below code to split based on (: & \n )but data is not consistent in each row so it is not splitting correctly.

 import openpyxl
import pandas as  pd

book = openpyxl.load_workbook(r'C:\Users\OneDrive - Fannie Mae\Info\test.xlsx')
user_data = book.get_sheet_by_name(str('test'))
df= pd.DataFrame([str(user_data[x][0].value) for x in range(1,user_data.max_row)])
df.columns=['PluginOutput']
df.columns = df.columns.str.replace(' ', '')
df1 = df.PluginOutput.str.split('\n',expand=True)

Please help !!!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  extract substring from a string before a word !! evilcode1 3 491 Nov-08-2023, 12:18 AM
Last Post: evilcode1
  Using locationtagger to extract locations found in a specific country/region lord_of_cinder 1 1,222 Oct-04-2022, 12:46 AM
Last Post: Larz60+
  How to extract specific data from .SRC (note pad file) Shinny_Shin 2 1,225 Jul-27-2022, 12:31 PM
Last Post: Larz60+
  Getting the maximum value:key pair from a dictionary sean1 2 1,404 Jan-17-2022, 01:04 PM
Last Post: DeaD_EyE
  Extract a string between 2 words from a text file OscarBoots 2 1,827 Nov-02-2021, 08:50 AM
Last Post: ibreeden
Question [SOLVED] Delete specific characters from string lines EnfantNicolas 4 2,144 Oct-21-2021, 11:28 AM
Last Post: EnfantNicolas
  Extract specific sentences from text file Bubly 3 3,339 May-31-2021, 06:55 PM
Last Post: Larz60+
Question How to extract multiple text from a string? chatguy 2 2,315 Feb-28-2021, 07:39 AM
Last Post: bowlofred
  Extract continuous numeric characters from a string in Python Robotguy 2 2,583 Jan-16-2021, 12:44 AM
Last Post: snippsat
  Auto re-pair / re-sync Controller via Script? User3000 2 2,281 Nov-30-2020, 11:42 AM
Last Post: User3000

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020