Adding a column via loop to dataframe

user_17459162 0
Viewed 61

I'm trying to create a loop which sets up a dataframe with an index (df1) and loops through a selected folder, finds a txt file and extracts out the second column (known as counts) and adds it to df1. It then carries on through the folder and does the same with the next file, adding it to df1. As a result, it should give me a processed txt file with the index and a column for the counts of the first file, the next column containing the counts from the second file and so on.

I'm having real issues with the loop and can't get it to stop overwriting the first txt file counts. On top of this, it keeps treating the new column header as a data cell which sets everything off balance. As it stands it just overwrites and leaves a random integer in the first row of what is meant to be the next column.

Any help would be greatly appreciated. Apologies for the number of print lines, I just wanted to be certain I understood what each step was doing.

    def changeFolder(self):
    folder = QFileDialog.getExistingDirectory(None, 'Project Data', '.csv files')
    print(folder)
    if folder == None:
        return
    else:
        print(folder)

    from glob import glob
    import pandas as pd
    import numpy as np
    import os
    # create lag variable for the time lag array from -50 to 50
    lag = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
           21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46,
           47, 48, 49, 50, 51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99]
    #generate data frame with the lag time in one column
    df1 = pd.DataFrame(index=lag)
    #print
    print('df1', df1)
    #for every file in the directory folder specified
    for file in os.listdir(folder):
        print('folder', folder)
        if file.endswith(".txt"):
            print('file', file)
            selfolder = folder
            newpath = os.path.abspath(os.path.join(selfolder, file))
            print('newpath', newpath)
            #read the file in the loop
            df2 = pd.read_csv(newpath, delimiter=" ", dtype="Int64", header=None)
            df2.to_string(index=False)
            #df2.columns = ['Lag', 'Counts']
            #take the second column of said folder and save it to the original dataframe
            print('df2', df2)
            #counts = df2.iloc[:,1]
            print('now for the counts')
            print(df2.iloc[:,1])
            df2['count'] = df2.iloc[:,1]
            df1['df1count'] = df2['count']
            df1.df1count = df1.df1count.astype(float)
            print(df1.df1count)
            count_df = pd.DataFrame(data={len(df2['count'].groupby(df2['count']))}, columns=['test'])
            new_df = pd.concat([df1, count_df], axis=1)
            print(new_df)
        continue
    savepath = newpath[:-4]
    #save and convert to .txt
    new_df.to_csv(savepath + ' processed.txt')

    ##Dialogue box in case of success
    mbox = QMessageBox()
    mbox.setText("Hopefully this worked!")
    mbox.setDetailedText("")
    mbox.setStandardButtons(QMessageBox.Ok)
    mbox.setWindowTitle('CSV Batch Processor')
    mbox.exec_()
pandas dataframe python loops
1 Answers

Could you explain which data frame contains what? That would help answering with code that fits your example. Assuming it is new_df, the pd.concat returns the concatenation of the list you have provided to it, namely [df, count_df] and hence the reference to the previous concatenation result gets lost. If you instanciate new_df beforehand and include it in the list to concatenate it should be ok:

new_df = pd.DataFrame()
for file in os.listdir(folder):
        print('folder', folder)
        if file.endswith(".txt"):
            print('file', file)
            selfolder = folder
            newpath = os.path.abspath(os.path.join(selfolder, file))
            print('newpath', newpath)
            #read the file in the loop
            df2 = pd.read_csv(newpath, delimiter=" ", dtype="Int64", header=None)
            df2.to_string(index=False)
            #df2.columns = ['Lag', 'Counts']
            #take the second column of said folder and save it to the original dataframe
            print('df2', df2)
            #counts = df2.iloc[:,1]
            print('now for the counts')
            print(df2.iloc[:,1])
            df2['count'] = df2.iloc[:,1]
            df1['df1count'] = df2['count']
            df1.df1count = df1.df1count.astype(float)
            print(df1.df1count)
            count_df = pd.DataFrame(data={len(df2['count'].groupby(df2['count']))}, columns=['test'])
            # Change the previous assignment to:
            new_df = pd.concat([new_df, df1, count_df], axis=1)
            print(new_df)
        continue
# etc.

This way each time, the previous results get concatenated and you should achieve what you want

user_130074670
Related