[FIXED] add title to a dataframe, save it then read it in and display result not what I expected

Issue

I have a simple dataframe that I read in and display. Then I add a title to the dataframe. I save the titled dataframe, then read it in and display it but the result is NOT what I expected. I repeats the title in each column. Code is shown below.

# read in a simple dataframe
csvpath=r'C:\Users\tfuser\Documents\2023 Taxes\test1.csv'
df=pd.read_csv(csvpath)
display(df)
# add a title to the dataframe and display the result
text='  TITLE'
df.columns = pd.MultiIndex.from_product([[text], df.columns])
display(df)
# save the titled dataframe, then read it in and display it
save_path=r'C:\Users\tfuser\Documents\2023 Taxes\saved.csv'
df.to_csv(save_path, index=False)
recovered_df=pd.read_csv(save_path)
display(recovered_df)

The resultant the outputs are shown below:

A   B   C
0   A1  B1  C1
1   A2  B2  C2
2   A3  B3  C3


TITLE
A   B   C
0   A1  B1  C1
1   A2  B2  C2
2   A3  B3  C3


  TITLE TITLE.1 TITLE.2
0   A     B     C
1   A1    B1    C1
2   A2    B2    C2
3   A3    B3    C3

I know it has something to do with the muti index but I don't know how to fix it to get a single title as shown in the second output.

Solution

You have to specify the number of headers to use pd.read_csv:

df = pd.read_csv(save_path, header=[0, 1])  # 2 header lines

Output:

>>> df
    TITLE        
        A   B   C
0      A1  B1  C1
1      A2  B2  C2
2      A3  B3  C3

>>> df.columns
MultiIndex([('  TITLE', 'A'),
            ('  TITLE', 'B'),
            ('  TITLE', 'C')],
           )

Answered By - Corralien