append.py

import pandas as pd
import numpy as np

meta = pd.read_excel('./Search Lookup Table.xlsx')
meta['Raw file name'] = meta['Raw file name'].fillna(False)
meta['address'] = zip(meta['Country'], meta['file name']) 
# meta
temp_col = meta.Template.unique()
# temp_col 

root = '.'
outputAll = pd.DataFrame(columns=np.append(temp_col, 'Brand'))
for add in meta.address.unique():
    print add
    c, f = add
    sample = meta[meta.address == add]
    sample.reset_index(drop=True, inplace=True)
    # print sample.shape
    output = pd.DataFrame(columns= temp_col)
    data = pd.read_excel(root +'/' + c + '/' + f + '.xlsx')
    for i in range(len(sample)):
        # print i, sample['Raw file name'][i], sample.Template[i]
        if sample['Raw file name'][i]:
            output[sample.Template[i]] = data[sample['Raw file name'][i]]
    output['Brand'] = f
    outputAll = outputAll.append(output, ignore_index=True)
    
outputAll.to_excel('append_data.xlsx')

# doublecheck the following file names in both folders 'US' and 'CA':
# 'Can-Am ORV_PAC.xlsx'
# 'Ski-Doo_PAC.xlsx'