Convert an interaction matrix into a ternary relation

Question

I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access.

The spreadsheet data are in this pattern:

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

There are n activities as Excel headings, m names in the first column of each data row, and zero or more specific weeks entered wherever a person (name) did an activity. If the n were small and fixed, I could keep the relation as a matrix, but n can get quite large and the matrix will be very sparse.

Thus the desired relational table structure (I'll call it Assignment) is like this:

Assignment: Name, Activity, Week

Is there an easy tool in either Excel or Access for facilitating this data conversion?

A psuedocode algorithm would be

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

While I've coded in VBA (years ago), I've never used it in conjunction with Excel. I'm leaning towards using Python and CSV files. Maybe there's a good SQL technique (even if non-Access-specific)? Or is it something to do with "pivoting" (I never understood what that was about)?

Do you have any experience you'd like to share with this sort of conversion?

Answer 1

My solution is to save the spreadsheet as a tab-delimited "matrix" file, run the following "data structure conversion" program, then import the resulting tab-delimited "relation" file into Access.

The Python 3.4 code:

import csv
source_path = 'matrix.txt'
target_path = 'relation.txt'
source_deli = '\t'
target_deli = '\t'
target_head = ("Name", "Activity", "Week")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.DictReader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
writer.writerow (target_head)
for row in reader:
    for key in row:
        if key != "Name" and row[key]:
            target_row = (row["Name"], key, row[key])
            writer.writerow (target_row)
fin.close()
fout.close()