After MultiIndex DataFrame object is created with additional information

[Li.]

I'm trying to write automated program to convert excel table to hierarchical graph.

I load my excel table and have such data:

self.df = pd.read_excel(self.file, sheet_name="Checklist", engine="openpyxl", header=[10])
 print(self.df)

Test case name Testing status 2023-01-01 Testing status 2023-01-02 Testing status 2023-02-20 Testing status 2023-03-15
0 SW password PASS FAILED FAILED PASS
1 Access levels PASS NOT TESTED PASS PASS
2 Local license server NOT TESTED NOT TESTED PASS PASS
3 High level security NOT TESTED PASS PASS PASS
4 Interruption in communication FAILED PASS PASS PASS
5 Writing parameters FAILED FAILED FAILED FAILED



Then I use pd.MultiIndex to group data and get result I want

index = pd.MultiIndex.from_frame(self.df)                 
print(index)

MultiIndex([( 'SW password', 'PASS', 'FAILED', ...),
( 'Access levels', 'PASS', 'NOT TESTED', ...),
( 'Local license server', 'NOT TESTED', 'NOT TESTED', ...),
( 'High level security', 'NOT TESTED', 'PASS', ...),
names=['Test case name', 'Testing status 2023-01-01', 'Testing status 2023-01-02', 'Testing status 2023-02-20', 'Testing status 2023-03-15'])

After this I create a DataFrame object and see that appears additional corrupted columns. How to fix it ?

self.dataFrame = pd.DataFrame(data=self.df, index=index)
print(self.dataFrame)

Test case name ... Testing status 2023-03-15
Test case name Testing status 2023-01-01 Testing status 2023-01-02 Testing status 2023-02-20 Testing status 2023-03-15 ...

SW password PASS FAILED FAILED PASS NaN ... NaN
Access levels PASS NOT TESTED PASS PASS NaN ... NaN
Local license server NOT TESTED NOT TESTED PASS PASS NaN ... NaN
High level security NOT TESTED PASS PASS PASS NaN ... NaN
Interruption in communication FAILED PASS PASS PASS NaN ... NaN
Writing parameters FAILED FAILED FAILED FAILED NaN ... NaN
[6 rows x 5 columns]

 

[FResher]

Syntax: df.iloc [row index range, column index range]

look at this tuto :

What is pandas and a pandas dataframe? What are the top 10 ways to filter pandas dataframe? Read our blog to learn more...

This blog is a step-by-step tutorial to create a pandas dataframe and use the top 10 ways to filter pandas dataframe. This tutorial also includes the Python source code for all the examples in a IPython Notebook.

www.youngwonks.com

as your excel sheet is long and with blank fields, you have to apply a constraint on the selected rows and columns to retrieve only the intersting fields.

 

[Li.]

Syntax: df.iloc [row index range, column index range]

look at this tuto :

What is pandas and a pandas dataframe? What are the top 10 ways to filter pandas dataframe? Read our blog to learn more...

This blog is a step-by-step tutorial to create a pandas dataframe and use the top 10 ways to filter pandas dataframe. This tutorial also includes the Python source code for all the examples in a IPython Notebook.

www.youngwonks.com

as your excel sheet is long and with blank fields, you have to apply a constraint on the selected rows and columns to retrieve only the intersting fields.

Thanks. I was thinking about it, but how can I manage it if my table of data each time will have different length of columns and rows ? With "for ... in .... " in df.iloc row ?
I tried some command to filter to show only columns with values and don't show columns with NaN, but it not helped.

df.drop.nan()

df=df[df['str_field'].str.len() > 0]

With this code I still get corrupted data

self.dataFrame = pd.DataFrame(data=self.df, index=index)
self.dataFrame.loc[:,['Testing status' in i for i in self.dataFrame.columns]]
print(self.dataFrame)