Pandas, how to reset? - Shape of passed values is (1,1), indices imply (3,1)

Question

I'm currently writing some code and am using pandas to export all of the data into csv files. My program runs multiple iterations until it has gone through all of the necessary files. Pandas is re-writing one file each iteration but when it moves onto the next file I need it to reset all of the data (I think).

Structure is roughly:

While loop>a few variables are named>program runs>dataframe=(pandas.DataFrame(averagepercentagelist,index=namelist,columns=header))

This part works with no problem for one file. When moving onto the next file, all of the arrays I use are reset and this I think is why pandas gives the error Shape of passed values is (1,1), indices imply (3,1).

Please let me know if I need to explain it better.

EDIT:

While True:
    try:
        averagepercentagelist=[]
        namelist=[]
        columns=[]
        for row in database:
            averagepercentagelist=["12","23"]
            namelist=["Name0","Name1"]
            columns=["Average percentage"]
            dataframe=(pandas.DataFrame(averagepercentagelist,index=namelist,columns=header))
    except Exception as e:
        print e
        break

SNIPPET:

dataframe= (pandas.DataFrame(averagepercentagelist,index=namelist,columns=header))

currentcalculatedatafrane = 'averages' + currentcalculate

dataframeexportpath = os.path.join(ROOT_PATH,'Averages',currentcalculatedatafrane)

dataframe.to_csv(dataframeexportpath)

FULL PROGRAM SO FAR:

import csv
import os
import re
import pandas
import tkinter as tk
from tkinter import messagebox
from os.path import isfile, join
from os import listdir
import time



ROOT_PATH = os.path.dirname(os.path.abspath(__file__))
indexforcalcu=0
line_count=0
testlist=[]
namelist=[]
header=['Average Percentage']



def clearvariables():
    indexforcalcu=0
    testlist=[]

def findaverageofstudent(findaveragenumber,numoftests):
    total=0
    findaveragenumber = findaveragenumber/numoftests
    findaveragenumber = round(findaveragenumber, 1)
    return findaveragenumber





def removecharacters(nameforfunc):
    nameforfunc=str(nameforfunc)
    elem=re.sub("[{'}]", "",nameforfunc)
    return elem





def getallclasses():
    onlyfiles = [f for f in listdir(ROOT_PATH) if isfile(join(ROOT_PATH, f))]
    onlyfiles.remove("averagecalculatorv2.py")
    return onlyfiles





def findaveragefunc():
    indexforcalcu=-1
    while True:
        try:
            totaltests=0
            line_count=0
            averagepercentagelist=[]
            indexforcalcu=indexforcalcu+1
            allclasses=getallclasses()
            currentcalculate=allclasses[indexforcalcu]
            classpath = os.path.join(ROOT_PATH, currentcalculate)
            with open(classpath) as csv_file:
                classscoredb = csv.reader(csv_file, delimiter=',')
                for i, row in enumerate(classscoredb):
                    if line_count == 0:
                        while True:
                            try:
                                totaltests=totaltests+1
                                rowreader= {row[totaltests]}
                            except:
                                totaltests=totaltests-1
                                line_count = line_count + 1
                                break
                    else:
                        calculating_column_location=1
                        total=0

                        while True:
                            try:
                                total = total + int(row[calculating_column_location])
                                calculating_column_location = calculating_column_location + 1
                            except:
                                break

                        i=str(i)
                        name=row[0]
                        cleanname=removecharacters(nameforfunc=name)
                        namelist.append(cleanname)
                        findaveragenumbercal=findaverageofstudent(findaveragenumber=total,numoftests=totaltests)
                        averagepercentagelist.append(findaveragenumbercal)
                        line_count = line_count + 1
                        dataframe= (pandas.DataFrame(averagepercentagelist,index=namelist,columns=header))
                        currentcalculatedatafrane = 'averages' + i + currentcalculate
                        dataframeexportpath = os.path.join(ROOT_PATH,'Averages',currentcalculatedatafrane)
                        dataframe.to_csv(dataframeexportpath)
                        i=int(i)


        except Exception as e:
            print("ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n\n",e)
            break





def makenewclass():
    global newclassname
    getclassname=str(newclassname.get())

    if getclassname == "":
        messagebox.showerror("Error","The class name you have entered is invalid.")
    else:
        classname = getclassname + ".csv"
        with open(classname, mode='w') as employee_file:
            classwriter = csv.writer(employee_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
            classwriter.writerow(["Name","Test 1"])











root=tk.Tk()




root.title("Test result average finder")


findaveragebutton=tk.Button(root,text="Find Averages",command=findaveragefunc())
findaveragebutton.grid(row=2,column=2,padx=(10, 10),pady=(0,10))


classnamelabel=tk.Label(root, text="Class name:")
classnamelabel.grid(row=1, column=0,padx=(10,0),pady=(10,10))


newclassname = tk.Entry(root)
newclassname.grid(row=1,column=1,padx=(10, 10))


newclassbutton=tk.Button(root,text="Create new class",command=makenewclass)
newclassbutton.grid(row=1,column=2,padx=(0, 10),pady=(10,10))



root.mainloop()

Thanks in advance, Sean

Answer 1

Use:

import glob, os
import pandas as pd


ROOT_PATH = os.path.dirname(os.path.abspath(__file__))

#extract all csv files to list    
files = glob.glob(f'{ROOT_PATH}/*.csv')
print (files)

#create new folder if necessary
new = os.path.join(ROOT_PATH,'Averages')
if not os.path.exists(new):
    os.makedirs(new)

#loop each file
for f in files:
    #create DataFrame and convert first column to index
    df = pd.read_csv(f, index_col=[0])
    #count average in each row, rond and create one colum DataFrame
    avg = df.mean(axis=1).round(1).to_frame('Average Percentage')
    #remove index name if nncessary
    avg.index.name = None
    print (avg)

    #create new path
    head, tail = os.path.split(f)
    path = os.path.join(head, 'Averages', tail)
    print (path)  

    #write DataFrame to csv
    avg.to_csv(path)