How to read excel file column references(A..B) into pandas dataframe

I have a excel file which do not have any column headers. Like the one shown below:

I have a separate JSON file which explains me that column A is number of customers, column B is number of products they bought...etc.

{
    
    "excel_template": "template.xlsx",
    "excel_sheets": [
            {"sheet_name": "Daily",
             "id_column": "A",
             "frequency": "daily"
             "header" : "Number of Customers"
             }
           ]
}

I read this excel file as a pandas data frame using pandas.read_excel(headers=None) function.

In the data frame now I want to establish the column references like the column A is number of customers and print column A.

Without manually calculating the index of the column by the alphabetic reference of the column in excel file, how can I do it. Thanks

CodePudding user response：

You need to know how the JSON file you have is recorded in order to give you an accurate answer.

However, by default, columns in pandas can be output as df.columns if you want to change

df.columns = ['name1', 'name2',.....]

You can do this. df.rename(columns = {'A':'name1',F':'name5'},inplace=True)

I don't know how the column is recorded in JSON, but...

After import json

read the json and make this column name into a list with df.columns = json_column_name or

df2 = pd.read_json('jsonfile') so if they are in columns then df.columns = df2.columns would also be possible.

If the column order in the JSON is different from the excel file, you need to create a dict and rename it.

CodePudding user response：

You can use names parameter:

namesarray-like, default None
List of column names to use.

An simple example:

df = pd.read_excel('tmp.xls', header=None, names=['ID', 'name'])

You can extract the names from your json file as a string list then fill in names parameter.

For details refer to pandas document.