I want to update the numbers in idx field if there is any matching letter between the vals from two consecutive row.

Input data = '''pos\tidx\tvals
    23\t4\tabc
    25\t7\tatg
    29\t8\tctb
    35\t1\txyz
    37\t2\tmno
    39\t3\tpqr
    41\t6\trtu
    45\t5\tlfg'''

Explantion: Since there is letter amatching between idx 4 and 7 the idx at pos 25 will be updated to 4, but again there ist matching between vals at pos 25 and 29 we update the idx at 29 also to 4 instead of just 7.

#Expected output to a file (tab separated):
pos    idx    vals
23    4    abc
25    4    atg
29    4    ctb
35    1    xyz
37    2    mno
39    3    pqr
41    3    rtu
45    5    lfg

I have written the given workable code (below) so far, and would also like to

• write the expected output to a file
• optimize the code for the work I am doing.
• the answer has to follow my method of reading two consecutive rows (as keys, values) pairs at a time in ordered way. The reason is this question is just a trial of other problem I am trying to solve. Other parts of the code can be optimized in any pythonic way.

Code:

import csv
import itertools
import collections
import io
from itertools import islice

data = '''pos\tidx\tvals
23\t4\tabc
25\t7\tatg
29\t8\tctb
35\t1\txyz
37\t2\tmno
39\t3\tpqr
41\t6\trtu
45\t5\tlfg'''


data_As_Dict = csv.DictReader(io.StringIO(data), delimiter='\t')
grouped = itertools.groupby(data_As_Dict, key=lambda x: x['idx'])

''' Function to read the data as key, val pairs in Ordered way.'''
def accumulate(data):
    acc = collections.OrderedDict()
    for d in data:
        for k, v in d.items():
            acc.setdefault(k, []).append(v)
    return acc
        
        
''' Store data as keys,values '''
grouped_data = collections.OrderedDict()
for k, g in grouped:
    grouped_data[k] = accumulate(g)


''' Print the very first k1. After this we only need to print k2 and update the idx '''
header_with_1stK1 = io.StringIO(data).read().split('\n')[0:2]
print('\n'.join(header_with_1stK1))

''' make an empty new_k2 value. This k2 value is updated and carried on base on match between vals from two different rows. '''
k2_new = ''

for n in range(2):
    if n > 0:
        break. # just to run the loop one time and to prevent resetting of k2_new values to ‘’

    ''' Now, read as keys, values pairs for two consecutive keys '''
    for (k1, v1), (k2, v2) in zip(grouped_data.items(), islice(grouped_data.items(), 1, None)):
    
        v1_vals = ''.join(v1['vals'])
        v2_vals = ''.join(v2['vals'])
    
        v1_list = list(v1_vals)
        v2_list = list(v2_vals)
    
        ''' to check if there is any matching element '''
        commons = [x for x in v1_list if x in v2_list]
    
        v2_pos = ''.join(v2['pos'])
    
    
        ''' start updating the idx values '''
        if k2_new == '':
            if len(commons) > 0:
                k2_new = k1
                print('\t'.join([v2_pos, k1, v2_vals]))
        
            else:
                k2_new = ''
                print('\t'.join([v2_pos, k2, v2_vals]))
        
        
        elif k2_new != '':
            if len(commons) > 0:
                k2_new = k2_new
                print('\t'.join([v2_pos, k2_new, v2_vals]))
        
            else:
                k2_new = ''
                print('\t'.join([v2_pos, k2, v2_vals]))
        

print('\nUpdated the idx values')