how to convert data of 536 files into excel having 536 columns using python

Muhammad Zohaib Khan 0
Viewed 29

I have 536 files in fasta format. each file has 5000 records. I have extracted all the records from the 536 files using python. Now I have to convert this data into excel so that each file name appears as a heading in the excel and each heading has its own records

import pandas as pd
list1 = [10,20]
list2 = [40,30]
col1 = "X"
col2 = "Y"
data = pd.DataFrame({col1:list1,col2:list2})
data.to_excel('sample_data.xlsx', sheet_name='sheet1', index=False)

this can not be done manually. I need help in automating this.

pandas excel python
1 Answers

Since you extracted the records already I assume you have the list of file names (here files) and the data (here combined in record_list) available.

files = ['file1', 'file2']
record_list = [[10, 20], [30, 40]]
data_dict = {file: record  for file, record in zip(files, record_list)} # combines file_name and corresponding record in a dict
data = pd.DataFrame(data_dict)
data.to_excel('sample_data.xlsx', sheet_name='sheet1', index=False)
userOctl10
Related