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

Felipe Useda 0
Viewed 50

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))
csv json python
3 Answers

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)
rv.kvetch0

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)
Dmitriy Neledva0

A solution for modified question.

if you provide data like this:

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

then this code:

# finding indecies for 'ch1', 'ch2', etc
#{'CH1': 0, 'CH2': 0, 'CH3': 0, 'CH4': 0, 'CH5': 1, 'CH6': 1, 'None': 2, 'CH7': 3, 'CH8': 3, 'CH9': 3}
dict_with_idx = {}
for d in [{j:idx for j in q} for idx, q in enumerate([['None', 'None', 'None', 'None'] if i == [] else i for i in answer])]:
    dict_with_idx |=d

#getting comdined data for finale csv writing 
#[['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', 'None', 'None', 'None', 'None'], ['XXXX', 'CC', 33, 'C1', 'CH7'], ['XXXX', 'CC', 33, 'C1', 'CH8'], ['XXXX', 'CC', 33, 'C1', 'CH9']]
combined_data = [list(_id[v] +[k]) if k != 'None' else list(_id[v] +['None', 'None', 'None', 'None']) for k,v in dict_with_idx.items()]

#final csv writing
def write_csv(data:list):
    with open('stack2.csv','w') as f:
        writer = csv.writer(f)
        writer.writerow(['id', 'var_x', 'var_y', 'var_z','answer'])
        for i in data:
            writer.writerow(i)

write_csv(combined_data)

will give you this:

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

if you do not need 'None' you can change it in code to get rid of it

Dmitriy Neledva0
Related