python/ pandas how to convert a list to a single cell and store in excel or in cvs format

Question

[I am expecting the output as shown in the left side, i am getting the output as shown in the right side]

1I have a list:

listA = ['Vlan            VN-Segment', '====            ==========', '800             30800', '801             30801', '3951            33951']

My output should be

vlan     vn-segment
====     ==========
800      30800
801      30801
3951     33951

But all the 4 rows show be in a single CELL in Excel. as above

I tried the following, but the output will be in 4 different rows in the Excel/cvs

my_input_file = open('n9k-1.txt')
my_string = my_input_file.read().strip()
my_list = json.loads(my_string)
#print(type(my_list))
x = (my_list[2])
print(x)
t = StringIO('\n'.join(map(str, x)))
df = pd.read_csv(t)
df2 = df.to_csv('/Users/masam/Python-Scripts/new.csv', index=False)

The question makes sense, but what have you tried so far? – David Erickson Jul 21 '20 at 22:25 — David Erickson, Jul 21 '20 at 22:25

score 0 · Answer 1 · answered Jul 21 '20 at 23:04

listA = ['Vlan            VN-Segment', '====            ==========', '800             30800', '801             30801', '3951            33951']
listA1 = []
listA2 = []

for i in listA:
    itm = i.split(' ')
    listA1.append(itm[0])
    listA2.append(itm[len(itm)-1])
    
listA1[1]='----' #replacing '====' because excel will not accept it
listA2[1]='----------' #replacing '==========' because excel will not accept it

df = pand.DataFrame.from_dict({listA1[0]:listA1,listA2[0]:listA2})
df.to_excel('C:/Users/COUNT DEXTER/Documents/my_python_source/data.xlsx', header=True, index=False)````

I hope this solves the problem.

Thanks for your help, the output take 6 rows in excel or CSV format, i need the output in only one CELL, because every cell will have a single output from the switch — Aslam Mohammed, Jul 21 '20 at 23:51

Seyi Daniel · Accepted Answer · 2020-07-23T07:33:39.880

0

from xlsxwriter.workbook import Workbook
for i in listA:
    itm = i.split(' ')
    listA1 += f'\n{itm[0]}'
    listA2 += f'\n{itm[len(itm)-1]}'
    
workbook = Workbook('data.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 20)
worksheet.set_column('B:B', 20)
# Add a cell format with text wrap on.
cell_format = workbook.add_format({'text_wrap': True})

# Write a wrapped string to a cell.
worksheet.write('A1', listA1, cell_format)
worksheet.write('B1', listA2, cell_format)
workbook.close()```
https://stackoverflow.com/questions/43537598/write-strings-text-and-pandas-dataframe-to-excel

edited Jul 23 '20 at 07:33

answered Jul 22 '20 at 01:14

Seyi Daniel

2,259
2
8
18

I need only one clarification, most of the code is working fine, I get an error with the following lines "workbook = Workbook('data.xlsx')", not able to find the "Workbook".. since there is no "Workbook" rest of the code fails. I did some research and modified the code but could not get the desired results. please help looks like 90% of the code is working, – Aslam Mohammed Jul 22 '20 at 10:46
Modifiy that line to point to your working directory or any other directory you want python to write the workbook to. For example "C:/Users/your_user/Documents/my_python_source/data.xlsx" – Seyi Daniel Jul 22 '20 at 10:52
I agree but "workbook = Workbook('/home/users/data.xlsx')" it is not the path of the file, it is "Workbook" below commands are not working, i am i missing some importing modules ? workbook = Workbook('data.xlsx') worksheet = workbook.add_worksheet() worksheet.set_column('A:A', 20) worksheet.set_column('B:B', 20) – Aslam Mohammed Jul 22 '20 at 13:40
workbook = Workbook('data.xlsx') .. What is this Workbook ?, is it a function or a class or a module which is getting imported and if it yes, what is there in "Workbook" – Aslam Mohammed Jul 22 '20 at 13:47
Thank you very much, It helped me a lot – Aslam Mohammed Jul 23 '20 at 15:24

python/ pandas how to convert a list to a single cell and store in excel or in cvs format

2 Answers2