Sep-02-2018, 05:59 PM
(This post was last modified: Sep-02-2018, 07:07 PM by Gribouillis.)
Hi I am absolute beginner. I have a problem in slicing string in a excel file using python.
My excel file contains the following info:
in excel i have used the below code and it worked fine:
I know I have done mistake
Kindly help.
My excel file contains the following info:
Output:ordercode quantity
PMC11-AA1L1FAVWJA 10
PMC21-AA1A1CBVXJA 2
PMP11-AA1L1FAWJJ 11
PMP21-AA1A1FBWJJ 4
PMP23-AA1A1FA3EJ+JA 1
PTP31B-AA3D1HGBVXJ 4
PTC31B-AA3D1CGBWBJA 8
PTP33B-AA3D1HGB1JJ 1
I want to slice the string in column "ordercode" based on whether it is "PMC11"/"PMC21"/"PMP21"/"PMP11"/"PMP23"/PTP31B/PTP33B/PTC31B" at different position and save it in new column "pressurerange".in excel i have used the below code and it worked fine:
Output:=IF(OR(ISNUMBER(SEARCH("PMC11",A2)),ISNUMBER(SEARCH("PMC21",A2)),ISNUMBER(SEARCH("PMP11",A2)),ISNUMBER(SEARCH("PMP21",A2)),ISNUMBER(SEARCH("PMP23",A2))),MID(A2,11,2),MID(A2,12,2))
but in python i used the below coding it didnt work properly.import pandas as pd #Assigning the worksheet to file file="Stratification_worksheet.xlsx" #Loading the spreadsheet data= pd.ExcelFile(file) #sheetname print(data.sheet_names) #loading the sheetname to df1 df=data.parse("Auftrag") print(df) #creating a new column preessurerange and slicing the pressure range from order code for index,row in df.iterrows(): if "PMC11" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(10,12) elif "PMC21" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(10,12) elif "PMP11" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(10,12) elif "PMP21" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(10,12) elif "PMP23" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(10,12) elif "PTP31B" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(11,13) elif "PTP33B" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(11,13) elif "PTC31B" in df.loc[index,"ordercode"]: df["pressurerange"]=df["ordercode"].str.slice(11,13) else: df["pressurerange"]="NONE" print(df.loc[:,["pressurerange"]]) breakHere what it does is it checked the first IF condition and it sliced the string at the position (10,12) for all the column.
I know I have done mistake
df["pressurerange"]=df["ordercode"].str.slice(10,12)
here. but i dont know what is the exact coding to use.Kindly help.