Update values in one dataframe using values from another as the row index for the first

Question

Dataframes:

# frames
winter1 = pd.DataFrame({"Location #1":{"1":0.999103,"2":0.999103,"3":0.999103,"4":0.999103,"5":0.999103,"6":0.999103,"7":0.999103,"8":0.999103,"9":0.999103,"10":0.999103,"11":0.999103,"12":0.999103,"13":0.999103,"14":0.999103,"15":0.999103,"16":0.999103,"17":0.999103,"18":0.999103,"19":0.999103,"20":0.999103,"21":0.999103,"22":0.999103,"23":0.999103,"24":0.999103},"Location #2":{"1":1.001673,"2":1.001673,"3":1.001673,"4":1.001673,"5":1.001673,"6":1.001673,"7":1.001673,"8":1.001673,"9":1.001673,"10":1.001673,"11":1.001673,"12":1.001673,"13":1.001673,"14":1.001673,"15":1.001673,"16":1.001673,"17":1.001673,"18":1.001673,"19":1.001673,"20":1.001673,"21":1.001673,"22":1.001673,"23":1.001673,"24":1.001673},"Location #3":{"1":0.999382,"2":0.999382,"3":0.999382,"4":0.999382,"5":0.999382,"6":0.999382,"7":0.999382,"8":0.999382,"9":0.999382,"10":0.999382,"11":0.999382,"12":0.999382,"13":0.999382,"14":0.999382,"15":0.999382,"16":0.999382,"17":0.999382,"18":0.999382,"19":0.999382,"20":0.999382,"21":0.999382,"22":0.999382,"23":0.999382,"24":0.999382},"Location #4":{"1":0.998544,"2":0.998544,"3":0.998544,"4":0.998544,"5":0.998544,"6":0.998544,"7":0.998544,"8":0.998544,"9":0.998544,"10":0.998544,"11":0.998544,"12":0.998544,"13":0.998544,"14":0.998544,"15":0.998544,"16":0.998544,"17":0.998544,"18":0.998544,"19":0.998544,"20":0.998544,"21":0.998544,"22":0.998544,"23":0.998544,"24":0.998544},"Location #5":{"1":1.00267,"2":1.00267,"3":1.00267,"4":1.00267,"5":1.00267,"6":1.00267,"7":1.00267,"8":1.00267,"9":1.00267,"10":1.00267,"11":1.00267,"12":1.00267,"13":1.00267,"14":1.00267,"15":1.00267,"16":1.00267,"17":1.00267,"18":1.00267,"19":1.00267,"20":1.00267,"21":1.00267,"22":1.00267,"23":1.00267,"24":1.00267}})

df_winter_min_error = pd.DataFrame({"Location #1":{"Position of min value":2,"Position of max value":19},"Location #2":{"Position of min value":4,"Position of max value":19},"Location #3":{"Position of min value":5,"Position of max value":15},"Location #4":{"Position of min value":5,"Position of max value":20},"Location #5":{"Position of min value":7,"Position of max value":20}})

I have a pandas dataframe called winter1 which looks as follows. It consists of values for 24 hours for five different locations.

Location #1 Location #2 Location #3 Location #4 Location #5
Hours                   
1   0.999103    1.001673    0.999382    0.998544    1.00267
2   0.999103    1.001673    0.999382    0.998544    1.00267
3   0.999103    1.001673    0.999382    0.998544    1.00267
4   0.999103    1.001673    0.999382    0.998544    1.00267
5   0.999103    1.001673    0.999382    0.998544    1.00267
6   0.999103    1.001673    0.999382    0.998544    1.00267
7   0.999103    1.001673    0.999382    0.998544    1.00267
8   0.999103    1.001673    0.999382    0.998544    1.00267
9   0.999103    1.001673    0.999382    0.998544    1.00267
10  0.999103    1.001673    0.999382    0.998544    1.00267
11  0.999103    1.001673    0.999382    0.998544    1.00267
12  0.999103    1.001673    0.999382    0.998544    1.00267
13  0.999103    1.001673    0.999382    0.998544    1.00267
14  0.999103    1.001673    0.999382    0.998544    1.00267
15  0.999103    1.001673    0.999382    0.998544    1.00267
16  0.999103    1.001673    0.999382    0.998544    1.00267
17  0.999103    1.001673    0.999382    0.998544    1.00267
18  0.999103    1.001673    0.999382    0.998544    1.00267
19  0.999103    1.001673    0.999382    0.998544    1.00267
20  0.999103    1.001673    0.999382    0.998544    1.00267
21  0.999103    1.001673    0.999382    0.998544    1.00267
22  0.999103    1.001673    0.999382    0.998544    1.00267
23  0.999103    1.001673    0.999382    0.998544    1.00267
24  0.999103    1.001673    0.999382    0.998544    1.00267

The values of certain columns for certain Hours need to be 1 based on values in two rows of another pandas dataframe called df_winter_min_error, which looks as follows:

df_winter_min_error.iloc[-2:,:]

Location #1 Location #2 Location #3 Location #4 Location #5
Hours                   
Position of min value   2.0 4.0 5.0 5.0 7.0
Position of max value   19.0    19.0    15.0    20.0    20.0

Based on the above value in df_winter_min_error, the specific value in winter1 needs to be changed into 1. e.g. for Location #1, the position of min value and max value are 2 and 19 hours respectively. Therefore, I need to make the values for winter1.loc[2,"Location #1] and winter1.loc[19,"Location #1] to 1 by replacing the existing values. I want to automate the process as it also needs to be done for all locations for the respective hours.

I tried the following code, however, it does not seem to change anything. I also tried to modify the code, however, that resulted in different errors such as KeyError and IndexingError.

locations = ["Location #1", "Location #2","Location #3","Location #4","Location #5"]
for location in locations:
    for index, row in winter1.iterrows():
        for location in locations:
            if [index] in df_winter_min_error[location][-2:].astype(int).values.tolist():
                row.loc[index,location]=1

What could be the appropriate way to automate this process as it needs to be applied to each row (Hours) and each column (Locations)?

MDR · Accepted Answer · 2021-08-23 11:13:24Z

Maybe, given:

# frames
winter1 = pd.DataFrame({"Location #1":{"1":0.999103,"2":0.999103,"3":0.999103,"4":0.999103,"5":0.999103,"6":0.999103,"7":0.999103,"8":0.999103,"9":0.999103,"10":0.999103,"11":0.999103,"12":0.999103,"13":0.999103,"14":0.999103,"15":0.999103,"16":0.999103,"17":0.999103,"18":0.999103,"19":0.999103,"20":0.999103,"21":0.999103,"22":0.999103,"23":0.999103,"24":0.999103},"Location #2":{"1":1.001673,"2":1.001673,"3":1.001673,"4":1.001673,"5":1.001673,"6":1.001673,"7":1.001673,"8":1.001673,"9":1.001673,"10":1.001673,"11":1.001673,"12":1.001673,"13":1.001673,"14":1.001673,"15":1.001673,"16":1.001673,"17":1.001673,"18":1.001673,"19":1.001673,"20":1.001673,"21":1.001673,"22":1.001673,"23":1.001673,"24":1.001673},"Location #3":{"1":0.999382,"2":0.999382,"3":0.999382,"4":0.999382,"5":0.999382,"6":0.999382,"7":0.999382,"8":0.999382,"9":0.999382,"10":0.999382,"11":0.999382,"12":0.999382,"13":0.999382,"14":0.999382,"15":0.999382,"16":0.999382,"17":0.999382,"18":0.999382,"19":0.999382,"20":0.999382,"21":0.999382,"22":0.999382,"23":0.999382,"24":0.999382},"Location #4":{"1":0.998544,"2":0.998544,"3":0.998544,"4":0.998544,"5":0.998544,"6":0.998544,"7":0.998544,"8":0.998544,"9":0.998544,"10":0.998544,"11":0.998544,"12":0.998544,"13":0.998544,"14":0.998544,"15":0.998544,"16":0.998544,"17":0.998544,"18":0.998544,"19":0.998544,"20":0.998544,"21":0.998544,"22":0.998544,"23":0.998544,"24":0.998544},"Location #5":{"1":1.00267,"2":1.00267,"3":1.00267,"4":1.00267,"5":1.00267,"6":1.00267,"7":1.00267,"8":1.00267,"9":1.00267,"10":1.00267,"11":1.00267,"12":1.00267,"13":1.00267,"14":1.00267,"15":1.00267,"16":1.00267,"17":1.00267,"18":1.00267,"19":1.00267,"20":1.00267,"21":1.00267,"22":1.00267,"23":1.00267,"24":1.00267}})

df_winter_min_error = pd.DataFrame({"Location #1":{"Position of min value":2,"Position of max value":19},"Location #2":{"Position of min value":4,"Position of max value":19},"Location #3":{"Position of min value":5,"Position of max value":15},"Location #4":{"Position of min value":5,"Position of max value":20},"Location #5":{"Position of min value":7,"Position of max value":20}})

And as a sample of winter1 looking like..

    Location #1     Location #2     Location #3     Location #4     Location #5
2   0.999103        1.001673        0.999382        0.998544        1.00267
19  0.999103        1.001673        0.999382        0.998544        1.00267

And df_winter_min_error:

                        Location #1     Location #2     Location #3     Location #4     Location #5
Position of min value   2               4               5               5               7
Position of max value   19              19              15              20              20

To change all columns, try:

for col in winter1.columns:
    mask = winter1.index.isin(df_winter_min_error[col].astype(str))
    winter1[col][mask] = 1

Output sample of winter1:

winter1.loc[['2', '4', '5', '7', '15', '19', '20']]

    Location #1     Location #2     Location #3     Location #4     Location #5
2   1.000000 <      1.001673        0.999382        0.998544        1.00267
4   0.999103        1.000000 <      0.999382        0.998544        1.00267
5   0.999103        1.001673        1.000000 <      1.000000 <      1.00267
7   0.999103        1.001673        0.999382        0.998544        1.00000 <
15  0.999103        1.001673        1.000000 <      0.998544        1.00267
19  1.000000 <      1.000000 <      0.999382        0.998544        1.00267
20  0.999103        1.001673        0.999382        1.000000 <      1.00000 <

Thank you. This worked for me. The winter1.index was of type int. So I also converted df_winter_min_error[col].astype(int), which was of type float.

Collectives™ on Stack Overflow

Update values in one dataframe using values from another as the row index for the first

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related