Adding an extra column to the combined/merged Excel files

Question

I am wondering how it is possible to add an extra column, to the combined/merged Excel files (see also my previous question).

The Excel files look like:

What I have is:

import xlwt
import os
import pandas as pd
from os.path import basename

files = os.listdir('C:\\Kids')

files_xls = []
for f in files:
    if f[-3:] == 'xls':
        fff = 'C:\\Kids\\' + f
        files_xls.append(fff)

df = pd.DataFrame()

for f in files_xls:
    data = pd.read_excel(f
    team_name = basename(f)[0:basename(f).find(' ')-1]
    team_code = basename(f)[basename(f).find(' ')+1 : basename(f).find('.xls')]

    data.index = [team_code] * len(data)   # I can have either ‘team_code’ or
                                           # ‘team_name’ added into the 1st column, 
                                           # but I want them to be added into 2 
                                           # different columns

    df = df.append(data)

df.to_excel("C:\\Kids\\combined2.xls")

As a result, there’s only 1 column can be added to what I want, however I need both the ‘team_name’ and ‘team_code’ listed in separate columns. Like:

How can I do that? i tried something like:

df.insert(0, 'new_col', team_code)

but it doesn't work.

Answer 1

You have to use a multiIndex.

data.index = pd.MultiIndex.from_tuples([(team_name,team_code)] * len(data))

also to avoid the team_name cells from merging you can use

df.to_excel("C:\\Kids\\combined2.xls",merge_cells = False)