Python dataframe row and column

Question

I am a newB python modeller and currently experiencing some issues with a line of code which may be very basic for a lot of you.

I am using python 2.7 and have successfully used xlwings to copy a named range from external workbook in to the pd.dataframe format. Everything thing works fine except the df.index and df.columns. Currently the code is assigning 1 to n (based on number of rows and columns) numbers as a index and column names.

is there a way, I use the first column values of my imported data as df.index and first row as df.column?

Can some one please assist me with getting something like this:

 df = pd.DataFrame(myExcelRange, df.index = 'first column values', df.columns = 'first row values')

The shape and name of myExcelRange could be different each time.

Any guideline will be much appreciated.

Example:

> myExcelRange

ITEM    Dan Jane    Fan 
A   77  78  40
B   89  53  72  
C   20  19  79  
D   81  54  93  
E   77  76  99

pandas is returning

    0   1   2   3
0   ITEM    Dan Jane    Fan
1   77  78  40  0
2   89  53  72  0
3   20  19  79  0
4   81  54  93  0
5   77  76  99  0

desired

ITEM    Dan Jane    Fan 
A   76  89  100 
B   59  72  24  
C   69  73  19  
D   70  92  43  
E   65  94  30

Can you add some sample like myExcelRange = ['a','b','c'] or myExcelRange = [['a','b','c'],['d','e','f']] and desired output? — jezrael
Or you need select first value in column and index by df = pd.DataFrame(myExcelRange).iat[0,0] or df = pd.DataFrame(myExcelRange).iloc[0,0]? Or rename only first index value and first column value? — jezrael
I have been playing with it and used df = df.set_index(0) - this seems to be used the first column values as the index. however, I need to find some set_column kind of function — Py_junior

jezrael jezrael · Accepted Answer · 2017-01-20T06:50:28

You can set_index with first column and then select first row by iloc and assign to df.columns, last remove first row from data by iloc too:

myExcelRange = [['a','b','c'],['d','e','f'],['g','h','i']]
df = pd.DataFrame(myExcelRange)
print (df)
   0  1  2
0  a  b  c
1  d  e  f
2  g  h  i

df = df.set_index(0)
df.columns = df.iloc[0,:]
#for nicer df remove index and column names
df.index.name = None
df.columns.name = None

print (df.iloc[1:,:])
   b  c
d  e  f
g  h  i

As Alex Fung mentioned, maybe is possible use read_excel with parameter index_col:

df = pd.read_excel('file.xlsx', index_col=0)
print (df)
      Dan  Jane  Fan
ITEM                
A      77    78   40
B      89    53   72
C      20    19   79
D      81    54   93

Python dataframe row and column

1 Answers