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.
Please help !!!
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 !!!