Python Forum
Slicing String cell by cell
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Slicing String cell by cell
#1
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:
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"]])
    break
Here 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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Calling cell / modules in PyCharm Community erdemath 0 1,304 Jul-29-2021, 07:16 PM
Last Post: erdemath
  Match string return different cell string Kristenl2784 0 1,399 Jul-20-2020, 07:54 PM
Last Post: Kristenl2784
  Python read Excel cell data validation anantpatil 0 4,107 Jan-31-2020, 04:57 PM
Last Post: anantpatil
  Excel cell(non uniform) to Database Vinci141 5 3,201 Nov-29-2018, 01:13 PM
Last Post: Vinci141
  Dropping all rows of multiple columns after the max of one cell Thunberd 2 2,918 Jun-01-2018, 10:18 PM
Last Post: Thunberd
  python array/cell/indexing python 1 2,762 Feb-08-2018, 11:18 AM
Last Post: DeaD_EyE
  Importing matlab cell array (.mat) into a python list scanato 0 8,603 Nov-15-2017, 11:04 AM
Last Post: scanato
  Concatenate Specific Cell Interval With Pandas vidividi12 0 2,818 Oct-01-2017, 02:16 PM
Last Post: vidividi12
  How to concatenate elements of different type in a table cell? Felipe 8 4,894 Jul-07-2017, 10:59 AM
Last Post: Felipe

Forum Jump:

User Panel Messages

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