User Input pd.read_excel gives "ValueError: Invalid file path or buffer object type" - Pandas

How many rows down can the header be? Let us assume it is within the first 5: Would this approach make sense?

import pandas as pd

data = '''\
245                 867               
Reddit              Facebook          
ColumnNeeded        ColumnNeeded      
RedditInsight       FacbookInsights   
RedditText          FacbookText
'''

fileobj = pd.compat.StringIO(data)
df = pd.read_csv(fileobj, sep='\s+', header=None)

print(df.head(5))

inp = input('Which row is header?')
n = int(inp)

df.columns = df.loc[n].values
df = df.loc[n+1:]
print(df)

Or define a function with a loop:

def change_header(df, i=5):
    n = 0
    while True:
        print(df.loc[n:n+i])
        inp = input('Which row is header? (number or (n)ext or (r)estart)')
        if inp.isdigit():
            n = int(inp)
            if n < len(df):
                break
            else:
                n = 0
                print('error')
                continue
        elif inp.lower().startswith('r'):
            n = 0
            continue
        elif inp.lower().startswith('n'):
            if (n+i) < len(df):
                n += i
            continue
        else:
            print('Try something else')

    df.columns = df.loc[n].values
    df = df.loc[n+1:]
    return df

df = change_header(df, 5)

You can use os library and call the files like this:

import os
import pandas as pd
excel_file_dfs = []
directory = 'C:/your_directory_here'
for filename in os.listdir(directory):
    if filename.endswith('.xlsx'):
        header_number = print('Enter row number you want to make header: ')
        df = pd.read_excel(filename, header=int(header_number))
        excel_file_dfs.append(df)
final_df = pd.concat(excel_file_dfs)
final_df

This way initially you can ask for headers and the take the os and call for the directory and take all the excel sheets. Hope it cleared your question. :)