python pandas dataframe add column with values while reading excel file

Question

Is there a way that I can add column with certain value while reading excel file using python pandas?

Now I have two steps, but I need to do it more than 30 files so I want to find elegant way to do it!

1) df_2007 = pd.read_excel('may2007_dl.xls')
2) df_2007['year'] = 2007

Thanks.

Answer 1

There's nothing wrong with your code. But if you'd like to do it in one line you can use assign, which assigns new columns to a DataFrame and returns a new object (a copy) with all the original columns in addition to the new ones.

df_2007 = pd.read_excel('may2007_dl.xls').assign(y=2007)

Demonstration:

In [68]: pd.read_csv('/tmp/test.csv')
Out[68]: 
  Unnamed: 0  one  two
0          a  1.0  1.0
1          b  2.0  2.0
2          c  3.0  3.0
3          d  NaN  4.0

In [69]: pd.read_csv('/tmp/test.csv').assign(a=2007)
Out[69]: 
  Unnamed: 0  one  two     a
0          a  1.0  1.0  2007
1          b  2.0  2.0  2007
2          c  3.0  3.0  2007
3          d  NaN  4.0  2007