Geek Answers Handbook

Python: how to create a correspondence matrix between values ​​for rows and columns?

I need to write in excel, and I need to know, based on the correspondence between groups and names, where to write "x", so I need a structure in which I need to save the group name and the columns and rows of the corresponding answers

n1 - is on line 1 n2 - is on line 2

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

I have to establish correspondence between users and groups in the matrix and write in excel. 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

Expected Result:

    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 "x", so I need a structure in which I need to save the group name and the columns and rows of the corresponding answers

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)

, db/csv. , , , .

, . , x .

2 2 , .

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'`
+4
1

, , python

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])


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

data_table, excel, idk lib... :

# 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]

, pandas! , 2D- .

+1

More articles:


All Articles