CODEWITHSUNDEEP
pythonexcelcsv
snehil singh
snehil singh

Reputation: 554

How to write list of values for single key from list of dictionary to the excel/csv


might be my title is bit confusing so please let me explain it i have two list in which:
First list: Contains the columns name
2nd list: It contains the list of dictionary in which every key have list of values and that values is mixture of different datatypes let me show the sample data my code which i have implemented for that:

    import csv
    csv_columns = ['subStatus','Sub-Tasks','Component name','License Duration']
    dict_data = [
    {'Component name':['Alex','Alex','Alex','Alex'],
     'subStatus':['', '', '', '', '', '', '', '', ''],
    'License Duration':[[{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}], 
                        [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}],
                       [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}],
                       [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]],
    'Sub-Tasks':[[], [], [], [], [], [], [], []]},
    ]
    csv_file = r'C:\Users\snsingh\Desktop\Names.csv'
    try:
        with open(csv_file, 'w') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=csv_columns)
            writer.writeheader()
            for data in dict_data:
                writer.writerow(data)
    except Exception as e:
        print(e)
        print("I/O error")

as you can see here all the key have different values which can be a list of list or list of dicts and so on but i just want to split all the value into multiple rows problem is my code putting all the values into single row i'm not able to split to different rows
my expected csv file should look like:

subStatus   Sub-Tasks Component name    License Duration                                                   

""          []        Alex              [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]

""          []        Alex              [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]  

""          []        Alex              [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]  

""          []        Alex              [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]  

""          []                          [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]  

""          []                          [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}] 

""          []                          [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}] 

""          []                                                         

""

I have one more question If possible answer it as show in the sample excel sheet it have different numbers of value in each list is it possible to have like that or we should have same length in each list?.
please suggest me a better way to do it if possible i want to execute the same program through pandas also any help will be appreciated.
Thanks in Advance

Upvotes: 0

Views: 60

Answers (1)

Amal Thachappilly
Amal Thachappilly

Reputation: 94

import csv
import pandas as pd
csv_columns = ['subStatus','Sub-Tasks','Component name','License Duration']

dict_data = [
{'Component name':['Alex','Alex','Alex','Alex'],
 'subStatus':['', '', '', '', '', '', '', '', ''],
'License Duration':[[{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}], 
                    [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}],
                   [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}],
                   [{'self': 'https://jira.infinera.com/rest/api/2/customFieldOption/34024', 'value': 'Perpetual', 'id': '34024'}]],
'Sub-Tasks':[[], [], [], [], [], [], [], []]},
]


data = dict_data[0]

df = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in data.items() ]))

df.to_csv("output.csv", index = False)

If you want to change the order of columns, then add this line of code before saving csv

df = df[['subStatus','Sub-Tasks','Component name','License Duration']]

Upvotes: 1

Related Questions