How to update the Xth row of a `.loc` result?

user_1549322 0
Viewed 33

I have a quite simple DataFrame, which stores clock-in and -out times for a specific day. This can happen multiple times a day, so for example there are multiple rows for day 2 of the month, the DataFrame df_03 represents:

>>> df_03.loc[df_03['Date'] == 2]
   Date DOW   from  until  ... counted time  target time  balance        time_sum
1     2  MO  08:00  08:45  ...          NaN          NaN      NaN 0 days 00:45:00
2     2  MO  09:43  17:22  ...         7.65          7.5     0.15 0 days 07:39:00
[2 rows x 9 columns]

What I want to do is to summarize the time_sum values of all rows per day and add the result to a new column called day_sum, but only for the last row of the result .loc.
So, the result should look like this:

>>> df_03.loc[df_03['Date'] == 2]
   Date DOW   from  until  ...   target time  balance        time_sum         day_sum
1     2  MO  08:00  08:45  ...           NaN      NaN 0 days 00:45:00             NaT
2     2  MO  09:43  17:22  ...           7.5     0.15 0 days 07:39:00 0 days 08:24:00
[2 rows x 9 columns]

I have tried it like this, but I always end up changing a subset copy of the original DataFrame:

>>> df_03.loc[df_03['Date'] == 2].iloc[-1] = df_03.loc[df_03['Date'] == 2]['time_sum'].sum()
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

>>> day = df_03.loc[df_03['Date'] == 2]
>>> day.iloc[-1]['day_sum'] = df_03.loc[df_03['Date'] == 2]['time_sum'].sum()
A value is trying to be set on a copy of a slice from a DataFrame
pandas python
1 Answers

Solution working for unique index values, so if necessary create it:

df_03 = df_03.reset_index(drop=True)

You can get index of last 2 value and assign in loc like scalar:

df_03 = pd.DataFrame({
        'A':list('abcdef'),
         'Date':[2,5,2,2,5,4],
         'time_sum':[7,8,9,4,2,3]
})

mask = df_03['Date'] == 2
idx = df_03.index[mask][-1]
df_03.loc[idx, 'day_sum'] = df_03.loc[mask, 'time_sum'].sum()
print (df_03)
   A  Date  time_sum  day_sum
0  a     2         7      NaN
1  b     5         8      NaN
2  c     2         9      NaN
3  d     2         4     20.0
4  e     5         2      NaN
5  f     4         3      NaN

If possible no value solution above failed, you can use next with iter trick for return some default value if no match:

mask = df_03['Date'] == 20

#[::-1] fr get last value, because next return FIRST value
idx = next(iter(df_03.index[mask][::-1]), [])
print (idx)
[]

df_03.loc[idx, 'day_sum'] = df_03.loc[mask, 'time_sum'].sum()
print (df_03)
   A  Date  time_sum  day_sum
0  a     2         7      NaN
1  b     5         8      NaN
2  c     2         9      NaN
3  d     2         4      NaN
4  e     5         2      NaN
5  f     4         3      NaN
user_29010020
Related