Python: How can I create a correspondence matrix between values on rows and columns?

Question

I need to write in excel, and I need to know based on the correspondence between groups and names where to write the 'x', so I need a structure where to keep the group name and the corespondent column and rows

n1 - is on row 1 n2 - is on row 2

A(group name) - is on column 2 B(group name) - is on column 3 c- is on column 4 D - is on column 5

I have to set a correspondence between users and groups in a matrix, and writing to excel. The working data:

Groups = A,B,C, D,E, F ...
Name  =  N1,N2,N3,N4 .....

N1 => A, C,D, F
N2= B,C,D
N3= A, E, F

The expected result is:

    A  B  C   D  E  F
N1  x     x   x     x

N2     x  x   x

N3  x            x  x


N1, N2, N3 - are on rows

A,B, c,D  - are on columns

I need to write in excel, and I need to know based on the correspondence between groups and names where to write the 'x', so I need a structure where to keep the group name and the corespondent column and rows

N1 - is on row 1
N2 - is on row 2

A(group name) - is on column 2
B(group name)  - is on column 3
C is on column 4
D  is on column 5

Example:

for N1 I need to write on (row1,col2), (row1,column4), (row1,column5), (row1,column7)
for N2 I need to write on (row2,col3), (row2,column4), (row2,column5)
for N3 I need to write on (row3,col2), (row1,column3), (row1,column6), (row1,column7)

I received the data one by one per user, from a db/csv file. I dont know all the groups at the beginning, I just add unique groups on the columns after I check in their not have been added for a previous user.

If a group is new, not appeared yet, I will add a new column with the group name. If exist I check the position of the group on column and write x in the row.

I tried with using 2 lists or 2 dictionaries, but the correspondence on row I couldn't set it correctly.

for a in b
.....
 if group_name not in groups_pos.items():
        groups_pos[col_pos] = group_name
        name_group[row_pos] = group_name
        col_pos += 1   
row_pos += 1
......

for col, value in groups_pos.items():
    sheet.cell(row=1, column=2+col).value = value
    for row, value_p in groups_pos.items():
      sheet.cell(row=row, column=2+col).value = 'x'`

Don't use `....` paste the full code, it may contain some vital control statements which may be missing? — ZdaR, May 03 '15 at 09:01
And kinfly define how do you receive the data ? It's a bit unclear here. — ZdaR, May 03 '15 at 09:02
1. I used ..., because the code is from different functions that contains also code that do other things, which is not relevant for the question. — user3541631, May 03 '15 at 09:11
OK, But can you briefly explain how are you taking input from the user ? — ZdaR, May 03 '15 at 09:14
I receive dat from a db or csv, in the format name, group1, group2,group3, group4. The groups are not unique to an user. I need to create an excel matrix, on rows is the name of the user and on the column is the group name. — user3541631, May 03 '15 at 09:34

score 1 · Accepted Answer · edited May 23 '17 at 11:46

Since libraries used, upstream/downstream code or desired implementations are not discussed, here is a pure python implementation

groups = ['A','C','D','F','B','C','D','A','E','F'] # ...
names = ['N1','N1','N1','N1','N2','N2','N2','N3','N3','N3'] # ...
row_names, col_names, data_table = [], [], [[]]

# for loop used to simulate getting data one at a time
for name, group in zip(names, groups):

    # see if that name exists in table
    if name in row_names:
        row = row_names.index(name)
    else:
        row_names.append(name)
        row = len(row_names)-1
        # add new row of empty strings
        data_table.append(['' for _ in xrange(len(data_table[0]))])

    # see if that group exists in table
    if group in col_names:
        col = col_names.index(group)
    else:
        col_names.append(group)
        col = len(col_names)-1
        # add empty string to every column
        for i,r in enumerate(data_table):
            data_table[i].append('')

    # add to table
    data_table[row][col] = 'x'

# display
print 'Table:'
print '  ', '    '.join(col_names)
for rowname, row in zip(row_names, data_table):
    print rowname, '    '.join([' ' if x == '' else x for x in row])

output

table:
   A    C    D    F    B    E
N1 x    x    x    x          
N2      x    x         x     
N3 x              x         x

Once all the data is in data_table, do the excel writing, idk what lib is imported...so this part is untested:

# add col headings
for c, col_name in enumerate(col_names):
    sheet.cell(row=1, column=c+2).value = col_name
for r, row in enumerate(data_tables):
    # add row heading on first column
    sheet.cell(row=r+2, column=1).value = row_names[r]
    # add each column to row
    for c, col in enumerate(row):
        sheet.cell(row=r+2, column=c+2).value = data_table[r][c]

Or you can use a library like pandas! If you have that installed or can get it, 2D data munging is much easier.

Python: How can I create a correspondence matrix between values on rows and columns?

1 Answers1