Get excel data in order using python-CodePudding

I'm using python to get a list functions from the excel in order. Excel image

I want to get result like that

Login
Submit
forgot password
Delete
(Submitted)
Next>confirm
save
cancel
upload
delete
...

I struggling with the excel indexes.

I think my logic is totally wrong.

import pandas as pd

df = pd.read_excel(fileNameMatrix,sheet_name="doTestCase")
lastRowNumber = len(df)
newDict = df.to_dict()
newJson = df.to_json()
document = docx.Document()
# print(newDict)
datalist = []
def sorting():
    controller = True
    dataexist = ""
    j = 0
    listindex = 0
    while controller:
        try:    
            if j == 0:
                menulist = []
                func2 = newDict['Menu']
                for i in range(len(func2)):
                    if type(func2[i]) == str:
                        menulist.append()
                        listindex = listindex   1
                        dataexist = "1"
                datalist.append(menulist)
            elif j > 0:
                func2 = newDict['Unnamed: ' str(j)]
                unnamelist = []
                for i in range(len(func2)):
                    if type(func2[i]) == str:
                            dataexist = "1"
                            unnamelist.append()
                datalist.append(unnamelist)
                # unnamelist = []
            if dataexist == "1":
                j = j 1
        except:
            return datalist
            controller = False
sorting()

Result

[['Login', 'delete ', 'userinfo'], ['Submit', '(submitted)', 'test'], ['forgot password', 'Next>confirm', 'Save'], ['save', 'delete'], ['cancel'], ['upload']]

Any logic suggesting would be appreciated.

CodePudding user response：

IIUC, use bfill and dropna:

df = (pd.read_excel(fileNameMatrix,sheet_name='doTestCase', header=None)
        .bfill(axis=1)[0].dropna())
print(df)

# Output
0               Login
1              Submit
2     forgot password
5              delete
6         (submitted)
8        Next>confirm
9                save
10             cancel
11             upload
12             delete
13               Save
15               test
18           userinfo
Name: 0, dtype: object