AttributeError for analyzing multiple sheets in python pandas

Question

I included sheet_name=None to analyze every sheet but then I have a problem with reading columns.

import pandas

df = pd.read_excel('file.xlsx', sheet_name=None, index_col=[0])

df.columns = df.columns.str.split('_', expand=True)

I got this error message

    df.columns = df.columns.str.split('_', expand=True)
AttributeError: 'dict' object has no attribute 'columns'

It is working perfectly on one sheet, why won't work for multiple sheets?

Answer 1

Solution:

dfs = pd.read_excel('file.xlsx', sheet_name=None, index_col=[0])

with pd.ExcelWriter('output_file.xlsx') as writer:
    for name, df in dfs.items():
        print(name)
        df.columns = df.columns.str.split('_', expand=True)