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)

Seyi Daniel · Answer

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

Seyi Daniel · Answer

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.

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

2 Answers

Add your own answers!

Ask a Question