Transpose few columns and reshaping them

Question

I am looking to transpose only certain columns in my dataframe and their values by keeping certain columns fixed.

WO.No           LineSubType      ProductName  Desc  Problem
WO-00201182     P-A-T                        
WO-00201182     Parts           18112135     FLOW  Liquid
WO-00201182     Parts           18112200    ArmF9  Liquid

Result Table

WO.No           LineSubType     ProductName+Desc1 ProductName+Desc2  Problem
WO-00201182     P-A-T/Parts     18112135 + FLOW  18112200 + ArmF9   Liquid

a picture does not tell a thousand words! data as text and what have you tried. It's absolutely un clear what is you source structure and desired target structure — Rob Raymond, Jul 20 '21 at 09:04
provide the data as text, in relational terms it's a simple self join, in pandas a merge. it's not good to expect an answer to have to do OCR first — Rob Raymond, Jul 20 '21 at 09:46
I am quite new to Python. I have tried many things but not able to do this. Example -- index = pd.MultiIndex.from_tuples(tuples, names=["Work Order Number","Technician: Member Name","Work Order Type:","Billing Type","Created Date","Scheduled Date Time","Closed On","Record Type.1","Line Type", "Line Number","Part: Product Name","Description","Problem"]) df_WO1 = pd.DataFrame(np.random.randn(8, 2), index=index, columns=["Line Sub Type", "Line Sub Type"]) — sanju, Jul 20 '21 at 09:59
copy the text from your excel into the question.... SO best practice for data questions is provide the data, not an image of the data. screenshots are the realms of lazy UAT testers aligned to corporate IT departments ;-) — Rob Raymond, Jul 20 '21 at 10:04

score 0 · Accepted Answer · answered Jul 20 '21 at 11:24

have used approach of self join
clearly sub-type columns could be merged into one if required

df = pd.read_csv(io.StringIO("""WO.No           LineSubType  LineNumber    ProductName  Desc  Problem
WO-00201182     P-A-T        WL-01014402                
WO-00201182     Parts        WL-01014744   18112135     FLOW  Liquid"""), sep="\s+")


df = (
    df.loc[df["LineSubType"].eq("P-A-T")]
    .drop(columns=["ProductName", "Desc", "Problem"])
    .merge(
        df.loc[df["LineSubType"].eq("Parts")],
        on="WO.No",
        suffixes=(" .P.A.T.", " Parts"),
    )
)

df

	WO.No	LineSubType .P.A.T.	LineNumber .P.A.T.	LineSubType Parts	LineNumber Parts	ProductName	Desc	Problem
0	WO-00201182	P-A-T	WL-01014402	Parts	WL-01014744	1.81121e+07	FLOW	Liquid

Transpose few columns and reshaping them

1 Answers1