pythonopenpyxltxtanaconda3

How to write a python program that converts numeric values from a txt file to write them as integers type in an excel file?


The task requires to write a Python program that can read some data about the scores of 6 students of two different exams from a txt file named AI.txt,and write the data of this file into a sheet named "qq" in an Excel file. Code must open the "AI.txt" file first and then convert the current lines into a list. Write the data in the cur list into the "qq" sheet, covert the numeric data into integer type data, and add a column with the average of the data in the first two columns. Finally save this Excel into a file named "AI56.xlsx". I complied with the above requirements, but the data of the two scores columns in the xlsx file is still shown as text type and not integer type. I'm Windows user, using Anaconda's environment coding with PyCharm. (I'm also new to Python)

import openpyxl

with open('AI.txt', 'r') as file:
    lines = file.readlines()

workbook = openpyxl.Workbook()

sheet = workbook.create_sheet(title='qq')

for line in lines:
    
    cur = line.strip().split()

    
    sheet.append([int(val) for val in cur])


for row in sheet.iter_rows(min_row=2, max_col=2, max_row=sheet.max_row):
    for cell in row:
        cell.value = int(cell.value)

   
    avg_value = sum([cell.value for cell in row]) / len(row)
    sheet.cell(row=row[0].row, column=3, value=avg_value)


workbook.save('AI56.xlsx')

This how the txt file looks like This is the excel after running the code

My code uses a list comprehension to convert each element val in the cur list to an integer type. But still doesn't seem to work properly. Just the average column is shown as integer type. What is it that I'm missing?

for line in lines:

cur = line.strip().split()

sheet.append([int(val) for val in cur])

Solution

  • Just use Pandas and keep it clean:

    import pandas as pd 
    data = pd.read_csv('AI.txt', sep='\s+', index_col='Name')
    data['mean']=  data.mean(axis=1)
    data.to_excel('AI56.xlsx',sheet_name='qq')