[Python] Pandas- Combine multiple rows into a single row and create new columns

I am working with order data using pandas. Each order can contain up to two rows (one row for each component of the order, which has a max of two components).

My goal is to take two rows and turn them into one.

example input:

Order_Number​

INVENTORY CODE description1

4304​

STDROFENHBSM608.511WH 8-1/2 x 11, 60# Soporset Digital HiBright Smooth

4304​

STNDEN695WOL 6 x 9.5 DBL WDW ENVELOPE

example output:

Order_Number​

INVENTORY CODE description1 INVENTORY CODE2 description2

4304​

STDROFENHBSM608.511WH 8-1/2 x 11, 60# Soporset Digital HiBright Smooth STNDEN695WOL 6 x 9.5 DBL WDW ENVELOPE

Here is my current code:

#name of infile to be the name of the file being ingested
infile= ('file1.csv')
infile2= ('file2.csv')
infile3= ('file3.csv')
#length of the infile for naming purposes
size = len(infile)
size2 = len(infile2)
#name outfile to be the name of the desired output file
outfile = (infile[:size -4]+"_"+infile2[:size2 -4]+"_output.csv")


#Data read in as dataframe
df = pd.read_csv(infile, encoding = "ISO-8859-1", engine= 'python')
df2 = pd.read_csv(infile2, encoding = "ISO-8859-1", engine= 'python')
df3 = pd.read_csv(infile3, encoding = "ISO-8859-1", engine= 'python')

df_merge =df.merge(df2, on='Order_Number', how='left')
df_final = pd.merge(df_merge,df3[['PkgID?','Printer', 'Inserter']],on='PkgID?', how='left')




The above code combines the various data I am working with into a single dataframe, but has duplicate rows based off order number as mentioned above.

Continue reading...