Updating pandas DataFrame column does not work when iterating through rows in a for loop

Question

I have the following code, through which I am trying to iterate through several .csv files and finding the minimum value of some of the columns (excluding 0 values) for each of the files and then putting these minimum values in a master spreadsheet:

import pandas as pd
import glob

main = pd.read_csv("main.csv")

for fname in glob.glob("Folder/*.csv"):
    df = pd.read_csv(fname)
    bims = [100, 101, 102, 103]
    for bim in bims:
        for module in range(1, 5):
            column = f"Module {module} Voltage"
            min_v = df[column].loc[df[column] != 0].min()
            main.loc[main["BIM_Number"] == bim, f"M{module}_Vmin"] = min_v

where bims is a list of integers indicating which row of the main spreadsheet to update. What this code should do (and it works if I try it for each bim value individually, i.e. not using a loop) is take the minimum value of four columns (Module voltage 1, 2, 3, 4) from the .csv file and append each value in the main dataframe object in columns M1_Vmin, M2_Vmin, M3_Vmin, M4_Vmin respectively). However the code runs fine but in the end the main dataframe is not updated. As I mentioned if I try this by just manually inputting a value from the bim list the main dataframe for the row containing that bim value is updated just fine? Any thoughts?

score 0 · Answer 1 · answered Aug 18 '22 at 14:26

Without any example data, it's difficult to give concrete advice, but it looks like you can (and probably should) get rid of the loops and perform the whole operation in one step.

This solution assumes the M{i}_Vmin and Module {i} Voltage columns are adjacent so you can select them with ranges (note that the ranges are inclusive in this case). If they are not adjacent, replace the "M1_Vmin":"M4_Vmin" and "Module 1 Voltage":"Module 4 Voltage" with lists of the column names or use .filter.

main.loc[main["BIM_Number"].isin(bims), "M1_Vmin":"M4_Vmin"] = (df.loc[:, "Module 1 Voltage":"Module 4 Voltage"]
                                                                  .replace(0, pd.NA) # since .min ignores NaN
                                                                  .min().values)

Updating pandas DataFrame column does not work when iterating through rows in a for loop

1 Answers1