Merging files bases on partially matching file names
<p>I am measuring the dependent variable vs independent variable (let's say current vs voltage from a device measurement) and the measurement set up will give me a separate file for positive measurement and negative measurement values. Each file is an excel file and has 2 columns, one for voltage and current each. I can name them whatever I want so I name them as device1_pos, device1_neg, device2_pos, device2_neg, device3_pos, device3_neg and so on. Additionally, I could have a repeat measurement for a given device so I will name it as device1_pos_meas2, device1_neg_meas2. After I have collected all my data I would like to merge the positive and negative measurement values in a single file for a given device. So I would like to have files like device1 (combining device1_pos and device1_neg however I will combine the second measurement for the same device in a separate file like device1_meas2) and so on for every device data.</p>

<p>Is there a way I can automate this process in python or shell script? If there is a smarter way I could be naming my files to make the process easier, that will be a helpful suggestion as well.</p>

<p>Adding more information to my initial question- I guess I am OK with merging the 2 files as shown below. I am concatenating the 2 files but since I don't want the header and row index, I read it line by line into a csv file (not the most efficient way but one that I could figure out).</p>

<pre><code>import os
import pandas as pd
from xlrd import open_workbook
import xlwt
</code></pre>

<p>os.chdir('C:\Users\fg7xmx\Documents\Projects\ESD\TestBench\Measurement\100616')</p>

<pre><code>path=os.getcwd()
file_pos=raw_input("Enter pos data file:")
file_neg=raw_input("Enter neg data file:")
file_allData=raw_input("Enter all data file name:")
file_csv=raw_input("Enter csv file name:")
file1=pd.read_excel(file_pos)
file2=pd.read_excel(file_neg)
file3=pd.concat([file1,file2],axis=0)
file3.to_excel(file_allData)
wb=open_workbook(file_allData)
for sheet in wb.sheets():
    workbook=xlwt.Workbook()
    newSheet = workbook.add_sheet('TLP_IV')
    for row in range(sheet.nrows):
        if row==0: continue
        for col in range(sheet.ncols):
            if col==0: continue
            newSheet.write(row-1,col-1,sheet.cell_value(row,col))
workbook.save(file_csv)
</code></pre>

<p>However, as you see, I am manually entering each file name which is not reasonable for large number of files. My actual file names look like </p>

<p>Mod5_pin10_pin8_pos_dev1_10-06-16_10'01'21_AM.xls</p>

<p>I know using regular expressions I can match the given pattern but here I need to group together the files which have same mod number, same pin number,  same measurement domain (pos or neg), same dev number, same date stamp and ignore the time stamp. I am not sure what command can I use for such grouping.</p>

<p>Here is how I went about doing this</p>

<pre><code>groups= defaultdict(list)
group_sweep=defaultdict(list)
for filename in os.listdir('C:\\Users\\TLP_IV'):
    basename, extension = os.path.splitext(filename)
    mod, name, pin1, pin2, sweep, dev, meas, date, time, hour=basename.split('_')
    groups[mod, name, pin1, pin2, dev, meas, date].append(filename)
    group_sweep[sweep].append(filename)
</code></pre>

<p>I made sure that I named each file in the same naming convention with '_' as a separator. Once I could create a dictionary with a list of various attributes, I could group by keys and read the values for a given key as below</p>

<pre><code>for keys,values in group_sweep.items():
    i=""
    for key in keys:
        if key=='n':
            for value in values:
                print value
</code></pre>

python shell