In [ 49 ]: df_1.columns = columns
df_2.columns = columns
Indeed, the output of the two DataFrame objects now resembles the spreadsheet style
pretty well:
In [ 50 ]: df_1
Out[50]: A B C D E F G H
0 1 9 17 25 33 41 49 57
1 2 10 18 26 34 42 50 58
2 3 11 19 27 35 43 51 59
3 4 12 20 28 36 44 52 60
4 5 13 21 29 37 45 53 61
5 6 14 22 30 38 46 54 62
6 7 15 23 31 39 47 55 63
7 8 16 24 32 40 48 56 64
In [ 51 ]: df_2
Out[51]: A B C D E F G H
0 1 2 3 4 5 6 7 8
1 9 10 11 12 13 14 15 16
2 17 18 19 20 21 22 23 24
3 25 26 27 28 29 30 31 32
4 33 34 35 36 37 38 39 40
5 41 42 43 44 45 46 47 48
6 49 50 51 52 53 54 55 56
7 57 58 59 60 61 62 63 64
Similarly, pandas allows us to write the data to Excel spreadsheet files:
In [ 52 ]: df_1.to_excel(path + ‘new_book_1.xlsx’, ‘my_sheet’)
Note that when writing DataFrame objects to spreadsheet files pandas adds both column
names and index values, as seen in Figure 12-3.
Of course, pandas-generated Excel workbooks can be read as before with the xlrd
library:
In [ 53 ]: wbn = xlrd.open_workbook(path + ‘new_book_1.xlsx’)
In [ 54 ]: wbn.sheet_names()
Out[54]: [u’my_sheet’]
To write multiple DataFrame objects to a single spreadsheet file, one needs an
ExcelWriter object:
In [ 55 ]: wbw = pd.ExcelWriter(path + ‘new_book_2.xlsx’)
df_1.to_excel(wbw, ‘first_sheet’)
df_2.to_excel(wbw, ‘second_sheet’)
wbw.save()
Let us inspect if we indeed have generated the two sheets in the single spreadsheet file:
In [ 56 ]: wbn = xlrd.open_workbook(path + ‘new_book_2.xlsx’)
In [ 57 ]: wbn.sheet_names()
Out[57]: [u’first_sheet’, u’second_sheet’]
