Reading Excel Files

Use pandas' read_excel() function to load data from Excel files. Excel workbooks often contain multiple sheets, so reading involves specifying both the file path and the target sheet. While you can load multiple sheets simultaneously, this typically returns a dictionary of DataFrames, which complicates subsequent operations. Therefore, it's generally preferable to load one sheet at a time.

Loading a single sheet returns a DataFrame object, which represents data in a tabular format. Common usage patterns include:

# Load the first sheet by default
df = pd.read_excel("sample_data.xlsx")

# Load sheet by name
df = pd.read_excel("sample_data.xlsx", sheet_name="sales_data")

# Load sheet by index (0-based)
df = pd.read_excel("sample_data.xlsx", sheet_name=0)

DataFrame Structure

DataFrames can be created with or without headers. By default, pandas treats the first row as column headers and the remaining rows as data. When header=None is specified, all row are treated as data without header interpretation.

For DataFrames with headers:

• The first row becomes the column labels
• Row indices are automatically generated (starting from 0)
• Column indices are automatically generated (starting from 0)

For DataFrames without headers:

• All rows are treated as data values
• Row and column indices are generated automatically

Acessing Data with .values

Basic Methods

• df.values: Returns all data as a 2D numpy array
• df.index.values: Returns row indices as a 1D array
• df.columns.values: Returns column labels as a 1D array

Data Selection Patterns

# All data
data_matrix = df.values

# Single value at row i, column j
single_value = df.values[i, j]

# Entire row i
row_data = df.values[i]

# Multiple rows i1, i2, i3
multiple_rows = df.values[[i1, i2, i3]]

# Entire column j
column_data = df.values[:, j]

# Multiple columns j1, j2, j3
multiple_columns = df.values[:, [j1, j2, j3]]

# Slice from row i1 to i2 (exclusive) and column j1 to j2 (exclusive)
data_slice = df.values[i1:i2, j1:j2]

Example Implementation

import pandas as pd

data_frame = pd.read_excel("sample_data.xlsx")

print("Complete dataset:")
print(data_frame.values)

print("\nValue at row 1, column 2:")
print(data_frame.values[1, 2])

print("\nThird row data:")
print(data_frame.values[2])

print("\nSecond and third rows:")
print(data_frame.values[[1, 2]])

print("\nSecond column:")
print(data_frame.values[:, 1])

print("\nSecond and third columns:")
print(data_frame.values[:, [1, 2]])

print("\nRows 1-3, columns 2-4:")
print(data_frame.values[1:3, 2:4])

Using loc and iloc for Data Access

Method Comparison

• loc uses label-based indexing
• iloc uses integer position-based indexing
• For DataFrames with headers, use loc with column names and iloc with numeric indices
• Slicing differs: loc encludes both endpoints, iloc uses half-open intervals

Access Patterns

# All data
all_data_loc = df.loc[:, :].values
all_data_iloc = df.iloc[:, :].values

# Single value
# Without headers
value1 = df.loc[i, j]
value2 = df.iloc[i, j]

# With headers
value3 = df.loc[i, "ID"]
value4 = df.iloc[i, j]

# Entire row
row_loc = df.loc[i].values
row_iloc = df.iloc[i].values

# Multiple rows
rows_loc = df.loc[[i1, i2, i3]].values
rows_iloc = df.iloc[[i1, i2, i3]].values

# Entire column
# Without headers
col_loc = df.loc[:, j].values
col_iloc = df.iloc[:, j].values

# With headers
col_name = df.loc[:, "Name"].values
col_index = df.iloc[:, j].values

# Multiple columns
# Without headers
cols_loc = df.loc[:, [j1, j2]].values
cols_iloc = df.iloc[:, [j1, j2]].values

# With headers
cols_names = df.loc[:, ["Name", "Age"]].values
cols_indices = df.iloc[:, [j1, j2]].values

# Data slices
# Without headers
slice_loc = df.loc[i1:i2, j1:j2].values  # Inclusive
slice_iloc = df.iloc[i1:i2, j1:j2].values  # Exclusive end

# With headers
slice_names = df.loc[i1:i2, "ID":"Name"].values
slice_indices = df.iloc[i1:i2, j1:j2].values

Practical Example

import pandas as pd

data_frame = pd.read_excel("sample_data.xlsx")

print("Complete dataset:")
print(data_frame.iloc[:, :].values)

print("\nValue at row 1, column 2:")
print(data_frame.iloc[1, 2])

print("\nThird row:")
print(data_frame.iloc[2].values)

print("\nSecond column:")
print(data_frame.iloc[:, 1].values)

print("\nName in row 5:")
print(data_frame.loc[5, "Name"])

print("\nRows 1-2, columns 2-3:")
print(data_frame.iloc[1:2, 2:3].values)

Real-World Application: API Parameterization

Excel Data Handler

import pandas as pd

def extract_column_data(column_index, start_row=0):
    """Extract specific column data starting from given row"""
    dataframe = pd.read_excel("parameters.xlsx", sheet_name='Sheet1')
    
    result_list = []
    for current_row in range(start_row, len(dataframe.index.values)):
        cell_value = dataframe.values[current_row, column_index]
        result_list.append(cell_value)
    
    return result_list

def extract_multiple_columns(col1, col2):
    """Extract data from multiple columns"""
    dataframe = pd.read_excel("parameters.xlsx", sheet_name='Sheet1')
    return dataframe.iloc[:, [col1, col2]].values

if __name__ == '__main__':
    print(extract_column_data(6))

API Integration Module

import json
from read_excel import extract_column_data
import requests

class APIClient:
    def process_image_url(self, image_url):
        """Send image URL to processing API"""
        endpoint = "https://api.example.com/image-processor"
        request_data = json.dumps({"url": image_url})
        
        headers = {'Content-Type': 'application/json'}
        response = requests.post(endpoint, headers=headers, data=request_data)
        return response.text

if __name__ == '__main__':
    client = APIClient()
    for url in extract_column_data(6):
        api_response = client.process_image_url(url)
        print(api_response)