Python - writing values to a CSV file based on another value-CodePudding

I modified the original question since I realized it was not properly presented.

I am working on a project in which I extract some values from a JSON and then pass them to a CSV file. The problem I currently have is that some of those values should be placed individually. However, I, so far, have only been able to placed them in the same group.

This is an example of what I currently have:

id,var_x,var_y,var_z,answer 
ABCD, AA , 11 , A1 ,['CH1','CH2','CH3','CH4']
DCBA, BB , 22 , B1 ,['CH5','CH6']
TTTT,
XXXX, CC , 33 , C1, ['CH7','CH8','CH9']

This is what I am trying to get:

    id,var_x,var_y,var_z,answer
[ 'ABCD’ , ’AA’ , ’11’ , ’A1’ , ’CH1' ]
[ 'ABCD’ , ’AA’ , ’11’ , ’A1’ , ’CH2’ ]
[ 'ABCD’ , ’AA’ , ’11’ , ’A1’ , ’CH3’ ]
[ 'ABCD’ , ’AA’ , ’11’ , ’A1’ , ’CH4’ ]
[ 'DCBA’ , ’BB , ’22’ ,  ’B1’ , ’CH5’ ]
[ 'DCBA’ , ’BB’ , ’22’ , ’B1’ , ’CH6’ ]
['TTTT']
[ 'XXXX’ , ’CC’ , ’33’ , ’C1’ , ’CH7’ ]
[ 'XXXX’ , ’CC’ , ’33’ , ’C1’ , ’CH8’ ]
[ 'XXXX’ , ’CC’ , ’33’ , ’C1’ , ’CH9’ ]

As you can see the value of 'id',var_x ,var_y and var_z should repeat depending on the value 'answer'. There are some cases in which the value 'answer' does not have information which is fine.

This is the code I have:

data=response.json()
id, answer,  = [], [],
for item in data:
    id.append(item.get('id',''))
    answer.append(item.get('answer',''))


header = ['id','answer']
with open('stack2.csv','w') as file:
    writer = csv.writer(file)
    writer.writerow(header)
    writer.writerows((id,answer))

CodePudding user response：

Simply update your for loop to account for the case when answer is provided, and the value is a non-empty list:

id, answer  = [], []

def add_row(_id_, _answer_):
    id.append(_id_)
    answer.append(_answer_)

for item in data:
    id = item.get('id', '')
    answer: 'list | None' = item.get('answer','')
    
    if answer:
        for a in answer:
            add_row(id, answer)        
    else:
        add_row(id, answer)

CodePudding user response：

As I can get it this part of your code:

data=response.json()
id, answer,  = [], [],
for item in data:
    id.append(item.get('id',''))
    answer.append(item.get('answer',''))

gives _id and answer in the following state:

_id = [ 'ABCD', 'DCBA', 'TTTT', 'XXXX']
answer = [['CH1','CH2','CH3','CH4'],['CH5','CH6'],[],['CH7','CH8','CH9']]

but you want to see it in this state:

['ABCD', 'CH1']
['ABCD', 'CH2']
['ABCD', 'CH3']
['ABCD', 'CH4']
['DCBA', 'CH5']
['DCBA', 'CH6']
['TTTT', None]
['XXXX', 'CH7']
['XXXX', 'CH8']
['XXXX', 'CH9']

for further proper writing of it into a csv file.

If I get it write here is the code that solves the problem:

import csv

_id = [ 'ABCD', 'DCBA', 'TTTT', 'XXXX']
answer = [['CH1','CH2','CH3','CH4'],['CH5','CH6'],[],['CH7','CH8','CH9']]

answer = dict(list(zip(_id, answer)))

combined_data = []
for k,v in answer.items():
    if v != []:
        for i in v:
            combined_data.append([k,i])
    else:
        combined_data.append([k, None])

def write_csv(data):
    with open('stack2.csv','w') as f:
        writer = csv.writer(f)
        writer.writerow(['id','answer'])
        for i in data:
            print(i)
            writer.writerow(i)

write_csv(combined_data)