CODEWITHSUNDEEP
pythonexcelpandasdataframe
goldfinger
goldfinger

Reputation: 1115

accessing Pandas dataframe by Excel cell index

I have imported an Excel spreadsheet into a dataframe. I wish to access data as though it was an Excel reference: e.g. df.get("A1") instead of df.iloc[0,0]. Does a nice method already exist for accessing dataframe data with Excel indexing - something like my imaginary get function above?

Upvotes: 1

Views: 1406

Answers (2)

Oliver.R
Oliver.R

Reputation: 1368

You could write a simple function to do the conversion from Excel index to numerical index:

import regex as re

def index_transform(excel_index):
    match = re.match(r"^([a-z]+)(\d+)$", excel_index.lower())
    if not match:
        raise ValueError("Invalid index")

    x_cell = -1
    for idx, char in enumerate(match.group(1)[::-1]):
        x_cell += (26 ** idx) * (ord(char) - 96)  # ord('a') == 97

    y_cell = int(match.group(2)) - 1

    return y_cell, x_cell

# Usage
df.iloc[*index_transform("A1")]  # The * unpacks the returned tuple

# Example outputs
>>> index_transform("A1")
(0, 0)
>>> index_transform("E1")
(0, 4)
>>> index_transform("A5")
(4, 0)
>>> index_transform("e5")
(4, 4)
>>> index_transform("AA27")
(26, 26)
>>> index_transform("coffee1337")
(1336, 42608414)

Upvotes: 2

DRFeinberg
DRFeinberg

Reputation: 77

Not Pandas but xlswriter does this: https://xlsxwriter.readthedocs.io/working_with_cell_notation.html

Upvotes: 1

Related Questions