Covert UiPath flow to Python code/Split Excel into rows

Help Activities
, , , , , ,
1

Hello,

I had the tasks of splitting my excel file in different file based of pairs of same “ID”. I think it is a bit like candy crush. We want to find matching rows based on column “ID” and then paste it in a single row as shown below:
image

Then it become like this:
image

Thankfully, @Hiba_B solved it in UiPath at this link: Splitting Excel in various columns - #16 by Hiba_B

But now I wanted to know if we could write a python code to do exactly what the Robot is doing?
Is that possible?
Can you give me some guide on where to start?
Thanks!
UIPath_Excel_Scratch.xlsx (121.1 KB)
From_separate_lines_to_shortned_ones.xaml (24.8 KB)

1 Like
3

Hi @Yudhisteer_Chintaram1

Try the code below

import numpy as np
import pandas as pd

inputExcelPath = r"C:\Users\username\Downloads\input.xlsx" #path of input file
inputExcelSheetNo = 0 
outputPath = r"C:\Users\username\Downloads\output.xlsx" #output file path

df = pd.read_excel(inputExcelPath, sheet_name=inputExcelSheetNo)
indexList = [i for i in range(len(df))]
new_df=pd.DataFrame(None, columns=['ID', 'Time In', 'Time Out', 'Weight In', 'Weight Out'])
checkList = []

for row in df.itertuples():
    toggle = -1
    newRow=[]
    for item in indexList:
        if row[1] == df.iloc[item][0] and not item in checkList:
            toggle = toggle + 1
            if toggle == 0:
                newRow.extend([df.iloc[item][0], df.iloc[item][1], df.iloc[item][2]])
                checkList.append(item) 
            if toggle == 1:
                newRow.extend([df.iloc[item][1], df.iloc[item][2]])
                checkList.append(item) 
               
    if not len(newRow) == 0:
        new_df.loc[len(new_df.index)] = newRow

new_df.to_excel(outputPath, index=False)

Please change the input path, sheet no and output path accordingly.