Add for loop results to excel spreadsheet with Python

Question

I am trying to create a small app which is identifying the names of the folders in a directory, and places those in a nice excel spreadsheet.

However, whenever I run the app, the results of the for loop are overwritten in the excel spreadsheet and I always end up with only one row, instead of all of the rows resulted from the loop.

Could you please have a look at my code and advise on what do I do wrong?

import os
import pandas as pd

CCM = "C:\Electronical Files\Drivers\CCM"

f = open("CCMs.txt", "w", encoding="utf-8")
f.close()

for crew in os.listdir(CCM):
    full_name = crew.split(", ")
    first_name = full_name[1]
    last_name = full_name[0]

    df = pd.DataFrame({"First name": [first_name], "Last name": [last_name]})
    df.to_excel("./CMMs.xlsx", sheet_name="CMMs")

    f = open("CCMs.txt", "a", encoding="utf-8")
    f.write(crew + "\n")
    f.close()

    print(first_name + " " + last_name)

Regards, Alex

Answer 1

Hold the dataframe creation and exporting to excel until after the loop is finished. Instead you can collect the names in a list that will be used to create the dataframe

names = []
for crew in os.listdir(CCM):
    names.append(crew.split(', ')[::-1])

df = pd.DataFrame(names, columns=['First Name', 'Last Name'])
df.to_excel('./CMMs.xlsx', sheet_name='CMMs')
with open('CCMs.txt', 'w', encoding='utf-8') as f:
    f.write('\n'.join(names))