Exporting a list as a new column in a pandas dataframe as part of a nested for loop

Question

I am inputting multiple spreadsheets with multiple columns of data. For each spreadsheet, the maximum value of each column is found. Then, for each element in the column, the element is divided by the maximum value of that column. The output should be a value (between 0 and 1) for each element in the column in ascending order. This is appended to a list which should be added to the source spreadsheet as a column.

Currently, the nested loops are performing correctly apart from the final step, as far as I understand. Each column is added to the spreadsheet EXCEPT the values are for the final column of the source spreadsheet rather than values related to each individual column.

I have tried changing the indents to associate levels of the code with different parts (as I think this is the problem) and tried moving the appended column along in the dataframe, to no avail.

for i in distlist: #listname = i[4:] + '_norm' df2 = pd.read_excel(i,header=0,index_col=None, skip_blank_lines=True) df3 = df2.dropna(axis=0, how='any')

cols = []
for column in df3:
    cols.append(column)

    for x in cols:
        listname = x + ' norm'
        maxval = df3[x].max()
        print(maxval)
        mylist = []

        for j in df3[x]:
            findNL = (j/maxval)
            mylist.append(findNL)
        df3[listname] = mylist


        saveloc = 'E:/test/'  
        filename = i[:-18] + '_Normalised.xlsx'
        df3.to_excel(saveloc+filename, index=False) 

New columns are added to the output dataframe with bespoke headings relating to the field headers in the source spreadsheet and renamed according to (listname). The data in each one of these new columns is identical and relates to the final column in the spreadsheet. To me, it seems to be overwriting the values each time (as if looping through the entire spreadsheet, not outputting for each column), and adding it to the spreadsheet.

Any help would be much appreciated. I think it's something simple, but I haven't managed to work out what...

Answer 1

If I understand you correctly, you are overcomplicating things. You dont need a for loop for this. You can simplify your code:

# Make example dataframe, this is not provided
df = pd.DataFrame({'col1':[1, 2, 3, 4],
                  'col2':[5, 6, 7, 8]})

print(df)
   col1  col2
0     1     5
1     2     6
2     3     7
3     4     8

Now we can use DataFrame.apply and use add_suffix to give the new columns _norm suffix and after that concat the columns to one final dataframe

df_conc = pd.concat([df, df.apply(lambda x: x/x.max()).add_suffix('_norm')],axis=1)

print(df_conc)
   col1  col2  col1_norm  col2_norm
0     1     5       0.25      0.625
1     2     6       0.50      0.750
2     3     7       0.75      0.875
3     4     8       1.00      1.000