Блог

Главная - Вопросы по программированию - Обновите значения в одном фрейме данных, используя значения из другого в качестве индекса строки для первого

Обновите значения в одном фрейме данных, используя значения из другого в качестве индекса строки для первого

#python #python-3.x #pandas #dataframe #for-loop

Вопрос:

Фреймы данных:

 # 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}})

У меня есть фрейм данных pandas, winter1 который выглядит следующим образом. Он состоит из значений за 24 часа для пяти различных местоположений.

 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

Значения определенных столбцов для определенных часов должны быть равны 1 на основе значений в двух строках другого вызываемого фрейма данных pandas df_winter_min_error , который выглядит следующим образом:

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

Исходя из приведенного выше значения в df_winter_min_error , конкретное значение в winter1 необходимо изменить на 1. например, для местоположения № 1 минимальное значение и максимальное значение равны 2 и 19 часам соответственно. Поэтому мне нужно сделать значения для winter1.loc[2,»Местоположение № 1″] и winter1.loc[19, «Местоположение № 1»] равными 1, заменив существующие значения. Я хочу автоматизировать этот процесс, так как это также необходимо сделать для всех мест в соответствующие часы.

Я попробовал следующий код, однако, похоже, он ничего не меняет. Я также попытался изменить код, однако это привело к различным ошибкам, таким как ошибка ключа и ошибка индексирования.

 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

Каким может быть подходящий способ автоматизации этого процесса, поскольку его необходимо применять к каждой строке (часам) и каждому столбцу (местоположениям)?

Ответ №1:

Может быть, учитывая:

 # 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}})

И как образец winter1 того, как это выглядит..

     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

И 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

Чтобы изменить все столбцы, попробуйте:

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

Выходной образец 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 <

Комментарии:

1. Спасибо. Это сработало для меня. Индекс winter1.имел тип int. Поэтому я также преобразовал df_winter_min_error[col].astype(int), который был типа float.

Метки: ,