Pandas how to auto skip excel row?-CodePudding

I made a system that automatically extracts data. It pulls the data and saves it to the excel file, but I can't sort the row it saved.

i = 0
k = 1

prefix = "/html/body/app-root/app-admin/mat-sidenav-container/mat-sidenav-content/div/app-orders-layout/div/div/ngb-tabset/div/div/app-all-orders-remake/div[3]/div/div/div[2]/div/table/tbody[1]/tr["
suffix = "]/td[15]/div/button"

while i < int(k):
    link = prefix   str(k)   suffix
    chat = driver.find_element(By.XPATH, (link))
    chat.click()
    time.sleep(1)
    name = "//*[@class='table table-striped table-sm m-0 border']/tbody/tr[1]/td[2]"
    name = driver.find_element(By.XPATH, (name))
    mail = "//*[@class='table table-striped table-sm m-0 border']/tbody/tr[2]/td[2]"
    mail = driver.find_element(By.XPATH, (mail))
    adress = "//*[@class='table table-striped table-sm m-0']/tbody/tr[2]/td[2]"
    adress = driver.find_element(By.XPATH, (adress))
    area = "//*[@class='table table-striped table-sm m-0']/tbody/tr[3]/td[2]"
    area = driver.find_element(By.XPATH, (area))
    number = "//*[@class='table table-striped table-sm m-0']/tbody/tr[4]/td[2]"
    number = driver.find_element(By.XPATH, (number))
    df = pd.DataFrame({'Name': [(name.text)],
                       'Mail': [(mail.text)],
                       'Adress': [(adress.text)],
                       'Area': [(area.text)],
                       'Number': [(number.text)]})

    df.to_excel('./test.xlsx', sheet_name='Test', index=False)
    time.sleep(2)
    webdriver.ActionChains(driver).send_keys(Keys.ESCAPE).perform()
    time.sleep(3)

    k = k   1

    if k==101:
        time.sleep(1)
        driver.find_element(By.XPATH, "/html/body/app-root/app-admin/mat-sidenav-container/mat-sidenav-content/div/app-orders-layout/div/div/ngb-tabset/div/div/app-all-orders-remake/div[3]/div/div/div[3]/div[2]/div/span[5]").click()
        time.sleep(3)
        k = 1
        continue

I did something like this, the system repeats itself, but it always saves on the same line. How can I increase the number of rows by 1 in each loop?

CodePudding user response：

It saves one row in the output because you pass lists with one object. The best way is to make a list of ducts and save that to excel file using pandas.

CodePudding user response：

You should start by creating an empty data frame to hold all the data.

df = pd.DataFrame(columns = ['Name', 'Mail', 'Adress', 'Area', 'Number'])

Now, within the loop, append each row of data to that data frame.

df = df.append({'Name': [(name.text)],
                       'Mail': [(mail.text)],
                       'Address': [(adress.text)],
                       'Area': [(area.text)],
                       'Number': [(number.text)]}, ignore_index=True)

Finally, outside the loop, write all the data to Excel.

df.to_excel('./test.xlsx', sheet_name='Test', index=False)