Read Excel Data by Column in Python Pandas – Python Pandas Tutorial

We have known how to read data in excel by row using python pandas.

Read Excel Data by Row in Python Pandas – Python Pandas Tutorial

In this tutorial, we will introduce how to read data by column, which is very useful when we plan to read data from an excel.

How to read excel data by column in python pandas?

We will use an example to show you how to do.

For example:

If our excel file looks like:

We can create a function below:

import  pandas  as pd
def getRows(excel_file, sheet_name, start_row = 0, offset = 1):

    excel_data = pd.read_excel(excel_file, sheet_name = sheet_name, header=None)
    # print(excel_data)
    size = excel_data.shape
    print(size)
    row_num = size[0]
    datax = []
    for i in range(start_row, row_num):
        data = excel_data.iloc[i].values.tolist()
        datax.append(data)
    if offset == 1:
        header = datax[0]
        datax = datax[1:] #[(),()]
        header_x = {}
        header_name = []
        for u in header:
            header_name.append(u)
        column_num = len(header_name)
        for i in range(column_num):
            u = header_name[i]
            header_x[u] = [d[i] for d in datax]
        datax = header_x
    return datax

d = getRows(excel_file = r'C:\Users\yuzhilun\Desktop\testsa.xlsx', sheet_name = 'Sheet1')
print(d)

Run this code, we will get:

(11, 5)
{'No': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1], 'Score': [23, 23, 23, 23, 23, 23, 23, 23, 23, 23], 'Aage': [24, 25, 26, 27, 28, 29, 30, 31, 32, 33], 'Height': [24, 25, 26, 27, 28, 29, 30, 31, 32, 33], 'Width': [24, 25, 26, 27, 28, 29, 30, 31, 32, 33]}

It is easy to save data to a new excel file when we read data by column, we can refer this tutorial to save data.

Add Hyperlink to Excel Using Python Pandas: A Step Guide- Python Pandas Tutorial