How do I select an excel Column based on the column heading value in Python

Question

I have a python dataframe that i paste into an excel sheet using the following code:

df.to_excel(writer, columns = [Weeknum, Weeknum1, Weeknum2], sheet_name = 'QTY SLS', startrow = 5, startcol = 8, header = False, index = False)

The columns selected in the dataframe weeknum, Weeknum1 and Weeknum2 are inputs earlier in the code (eg Weeknum = Week 14). So these could potentially be ['Week 16', 'Week 15', 'Week 14'] as an example. My question is how do I select a start col based on the Weeknum input. so the Excel sheet I am pasting to looks like the following:

Store	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16
A	1	4	4	2	1	5	4

So I would need the code for Startcol to start at Week 14 if I put that i the input or Week 10 if I put that in the input.

Hope that makes sense.

Laurent Laurent · Accepted Answer · 2021-06-17T07:11:02

You can find the start column like this:

import pandas as pd

# Toy dataframe
df = pd.DataFrame(
    {
        "Week 1": [1, 1, 1],
        "Week 2": [2, 2, 2],
        "Week 3": [3, 7, 3],
        "Week 4": [4, 8, 4],
        "Week 5": [7, 2, 9],
    }
)

Weeknum = "Week 3"  # Position in the df header index == 2
Weeknum1 = "Week 4"
Weeknum2 = "Week 5"

# Get the position of df column with value of Weeknum
print(list(df.columns).index(Weeknum))
# Outputs 2

Thus, you could modify your code like this:

df.to_excel(
    writer,
    columns=[Weeknum, Weeknum1, Weeknum2],
    sheet_name="QTY SLS",
    startrow=5,
    startcol=list(df.columns).index(Weeknum),
    header=False,
    index=False,
)

How do I select an excel Column based on the column heading value in Python

1 Answers