Transforming excel index to pandas index

Question

To give a bit of backstory, I created and excel sheet that transforms the excel column index to pandas index. Which in essense is just a simple Vlookup, on a defined table e.g Column A=0, Column B=1. It gets the job done, however it's not as efficient as I would like it to be.

I use these index on my function to rename those fields to follow our current nomenclature. e.g

df = df.rename(columns={df.columns[5]: "Original Claim Type",
                        df.columns [1]:"Date of Loss",
                        df.columns[3]:"Date Reported (tpa)",
                        df.columns[2]:"Employer Report Date",
                        df.columns[4]:"Original Status",
                        df.columns[6]:"Date Closed",
                        df.columns[27]:"(net)Total Paid",
                        df.columns[23]:"(net) Total Incurred",
                        df.columns[25]:"NET Paid(Med)",
                        df.columns[26]:"NET Paid(Exp)",
                        df.columns[24]:"NET Paid (Ind)",
                        df.columns[18]:"Original Litigation",
                        df.columns[7]:"Date of Hire",
                        df.columns[8]:"Date of Birth",
                        df.columns[9]:"Benefit State",
                        df.columns[15]:"Original Cause",
                        df.columns[17]:"Body Part",
                        df.columns[32]:"TTD Days"})

My new solution was to create a Dictionairy that maps the values, and their corresponding index.

excel_index={'A':0,'B':1,'C':2}
test={"Claim Number":[0,1,2,3,4,5]}
test=pd.DataFrame(test)
test=test.rename(columns={ test.columns[excel_index['A']]: "Frog"})

It works, however the only problem I have is that I would have to manually type out all the index values beforehand.

What would be a more efficient way to carry this out?

-Brandon

could you show an example of what your data looks like in excel, and what you want it to look like in pandas? If you have a row of column names in excel, you can specify that upon import to pandas. — Andrew
– Andrew, Commented Aug 7, 2017 at 13:59
unless I am not understanding your request, the answer below should work. — Andrew
– Andrew, Commented Aug 7, 2017 at 14:29
Negative, what i'm trying to do is transform Excels column index e.g [A,B,C,D,E,F,G] to Pandas respective index [0,1,2,3,4,5,6] in order be used in my renaming formula. — Bjc51192
– Bjc51192, Commented Aug 7, 2017 at 14:32
index_col=0 would make the index here A B C D... I guess I'm not really understanding your request — Andrew
– Andrew, Commented Aug 7, 2017 at 14:37

Andrew · Accepted Answer · 2017-08-07 14:19:44Z

1

If you have a column of index names, such as the on in your example, you can specify that in the pd.read_excel command. Since your index column is 5, it would read like this:

df = pd.read_excel('yourfilename.xlsx', index_col=5)

answered Aug 7, 2017 at 14:19

Andrew

9708 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transforming excel index to pandas index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related