Module Assembly Report Automation

Introduction

If you haven’t read the article about Shimming Magnets in a Particle Accelerator, I would highly recommend doing that first before you read this one. In that article, I introduced the plan to upgrade the Advanced Photon Source by replacing the electron storage ring with 200 new modules, each of them containing an array of magnets. I also talked about the challenges of magnet positioning and alignment and detailed some of the work I did to mitigate those problems. The results of the magnet alignment relate to the contents in the module assembly report.

For every module, an assembly report, containing relevant measurements and statistics, must be generated and submitted for review. Proper alignment of the magnets is verified by analyzing the errors of the magnets’ positions and orientations. These reports are essential in the review process that guarantees that the magnets will precisely deflect the electron beam as designed.

Problem Statement

Raw data logs of the position and orientation errors were being generated as CSV files. We would need to generate 200 reports, one for each of the modules. Hence, it was desirable to automate this process of converting raw data logs into standardized, easy-to-read PDFs. Along with the error measurements and their statistics, a list of magnets names, links to the specific part in the Component Database (CDB), and their identification label was to be included in the report.

Implementation

I opted to perform this task using Python because it was the coding language I was most familiar at the time. I used the libraries Xlwings and OpenPyXl to write the Excel files and then export them as PDFs. The snippet of code below populates the Excel file with data from the raw logs and a list of magnets in the module.

def generate_excel_report(module_name):
    filename_report = module_name + '/Report ' + module_name + ' Assembly Survey.xlsx'
    filename_report = os.path.abspath(filename_report)
    shutil.copy('Form_DLM_SurveyReport.xlsx', filename_report)
    
    thin = Side(border_style="thin", color="00000000")
    regular = Side(border_style="thin", color="00D3D3D3")
    thick = Side(border_style="thick", color="00000000")
    
    df = read_csv(module_name+'/INFO.csv')
    data = extract_csv_data(df,['Survey Date:','Surveyor(s):','Instrument s/n:','SA Version:','SA Filename:'])
    data[4][0] = data[4][0][data[4][0].rfind('\\')+1:]
    data = [item[0] for item in data]
    data.append(date.today().strftime("%B %d, %Y"))
    write_excel_col(filename_report,'Alignment Summary',data,'C3')
    write_excel_col(filename_report,'Alignment Summary',[module_name],'B1')
    
    df = read_csv(module_name+'/CENTERS.csv',col_names=True)
    append_df_to_excel(filename_report,df,sheet_name="Alignment Summary",startcol=1,startrow=24)
    
    try:
        df = read_csv(module_name+'/M1_VERTEX.csv',col_names=True)
        M1_data = []
        M1_data.append(str(df.index.name))
        for i in df.columns:
            M1_data.append(float(i))
        write_excel_row(filename_report,'Alignment Summary',M1_data,'B41')
    except:
        print("M1 data excluded...")
    
    name, url, serial = extract_magnet_list(module_name)
    write_excel_col(filename_report, 'Alignment Summary', name, start_index='B11')
    write_excel_col(filename_report, 'Alignment Summary', url, start_index='C11')
    write_excel_col(filename_report, 'Alignment Summary', serial, start_index='E11')

View Full Code

A simple user interface was created. Only one input, the module name, was required to run the program. The raw data logs were named predictably, which made importing the right data for each module straightforward. Print statements are outputted to the bottom of the UI, so that the user can view the progress of the report generation, as well as if they were any errors or missing data.

Documentation

Here is the comprehensive documentation on the program.

Results

All 200 assembly reports were generated in the matter of weeks, with the bottleneck being the retrieval of the raw data logs. The user of the report compiler program stated that the process of using the tool was quick and hassle-free.