CODEWITHSUNDEEP
pythonexcelcsv
DPdl
DPdl

Reputation: 755

write a list to a csv/excel in python

I have the following lists each of which will form a row in csv/excel:

row1 = ['E411', 'name1', ['issue1', 'issue2', 'issue3']]
row2 = ['E473', 'name2', ['issue1', 'issue2']

I want to have a csv or excel file with three columns with column headers ['Title', 'Name', 'Issues'] where the first element of row1 is the title, second element of row 1 is the name and third element (without the quotes and commas, possibly with a linebreak within the cell) is the issues.

The following I tried gives only the first item from the third element of the list.

headers = ['Title','Name','Issues'
a = pd.DataFrame(row1, columns=headers)
a.to_csv('issues.csv', model='a', header=False)

Upvotes: 1

Views: 79

Answers (4)

Deepak Tripathi
Deepak Tripathi

Reputation: 3233

Do you want to split the Issue column in multiple records then you can use this :

import pandas as pd
import numpy as np
row1 = ['E411', 'name1', ['issue1', 'issue2', 'issue3']]
row2 = ['E473', 'name2', ['issue1', 'issue2']]
df = pd.DataFrame([row1, row2], columns=['Title', 'Name', 'Issues'])
lens = [len(item) for item in df['Issues']]
df1 = pd.DataFrame( {"Title" : np.repeat(df['Title'].values,lens),
"Name" : np.repeat(df['Name'].values,lens),
                        "Issues" : np.concatenate(df['Issues'].values)})
df1.to_csv('file_name.csv', index=False)
'''
  Title   Name  Issues
0  E411  name1  issue1
1  E411  name1  issue2
2  E411  name1  issue3
3  E473  name2  issue1
4  E473  name2  issue2
'''

Upvotes: 1

DirtyBit
DirtyBit

Reputation: 16772

Using pandas:

import pandas as pd

row1 = ['E411', 'name1', ['issue1', 'issue2', 'issue3']]
row2 = ['E473', 'name2', ['issue1', 'issue2']]

data = {'Title': ['E411','E473'],
        'Name': ['name1','name2'],
        'Issues': [",".join(['issue1', 'issue2', 'issue3']), ",".join(['issue1', 'issue2'])]
        }

df = pd.DataFrame(data, columns = ['Title', 'Name', 'Issues'])

print(df)
# df.to_csv('filename.csv', index=False) # save as .csv

OUTPUT:

  Title   Name                Issues
0  E411  name1  issue1,issue2,issue3
1  E473  name2         issue1,issue2

Upvotes: 1

Aaron Jones
Aaron Jones

Reputation: 1170

From what it looks like in your question, you are missing a ] at the end of your row2 line. You can always do something similar to the code below.

import csv

#The list to write
row1 = [['TestData', 'TestData2', 'TestData3'], ['Random', 'Random2', 'Random3']]

output = open('output.csv', 'a+', newline='')

with output:
    write = csv.writer(output)
    write.writerows(row1)

Upvotes: 1

idar
idar

Reputation: 610

This should do the job:

row1 = ['E411', 'name1', ['issue1', 'issue2', 'issue3']]
row2 = ['E473', 'name2', ['issue1', 'issue2']]
allrows = [row1, row2]
for n in range(len(allrows)):
    allrows[n][-1] = '\n'.join(allrows[n][-1])
import pandas as pd
pd.DataFrame(allrows).to_csv('outcsvfilename.csv', index=False, header=['Title','Name','Issues'])

Upvotes: 1

Related Questions