How to query a dataframe and change data based on another column in R?

user_1062094 0
Viewed 75

I have simple time tracking data that looks like this:

df = tribble(~Date, ~Name,  ~Team,  ~Status,    ~Hours_Type,    ~Hours, ~Standard,  ~Deficit,   ~Overtime,  ~Leave,
"April 16 2021",    "Jeff", "Coastal",  "FT",   "Billable", 40, 40, 0,  0,  0,
"April 23 2021",    "Jeff", "Coastal",  "FT",   "Billable", 40, 40, 0,  0,  0,
"April 16 2021",    "Jeff", "Coastal",  "FT",   "Leave",    0,  0,  0,  0, 0,
"April 23 2021",    "Jeff", "Coastal",  "FT",   "Leave",    0,  0,  0,  0, 0,
"April 16 2021",    "Megan",    "Coastal",  "FT",   "Billable", 40, 40, 0,  0,  0,
"April 23 2021",    "Megan",    "Coastal",  "FT",   "Billable", 40, 40, 0,  0,  0,
"April 16 2021",    "Megan",    "Coastal",  "FT",   "Leave",    0, 0,   0,  0, 0,
"April 23 2021",    "Megan",    "Coastal",  "FT",   "Leave",    0, 0,   0,  0, 0,
"April 16 2021",    "Minden",   "Coastal",  "FT",   "Billable", 16, 16, 24, 0,  0,
"April 23 2021",    "Minden",   "Coastal",  "FT",   "Billable", 28, 28, 12, 0,  0,
"April 16 2021",    "Minden",   "Coastal",  "FT",   "Leave",    24, 0,  0,  0, 24,
"April 23 2021",    "Minden",   "Coastal",  "FT",   "Leave",    0,  0,  0,  0, 0)

# A tibble: 12 x 10
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16      24        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0

How do I query the Leave column and check if the Deficit column from the same Date should actually be 0 because it is not actually a deficit, as it is covered by leave from the same date?

For example, how would I have R check the Deficit, Leave, Name, and Date columns in order to modify this table and change Minden's April 16th 24 hour Deficit to 0 (row 9), given that it is covered by his 24 hours of Leave on April 16th (row 11)?

enter image description here

This would be the expected result, and the relevant code could be generalized across the entire dataset:

# A tibble: 12 x 10
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16       0        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0

Note: I have to keep the Leave column because I'm using it in a stacked bar chart that is visualizing this data - see in this instance the 24 Deficit for Minden should actually be Leave, but I don't know how to make this change automatically, only manually: enter image description here

dplyr tidyverse r
5 Answers

I this this strategy will be most efficient (though your example doesn't include other possible scenarios)

df %>% group_by(Date, Name) %>%
  mutate(Deficit = ifelse(Hours_Type == "Billable", Deficit - Leave[Hours_Type == "Leave"], Deficit))

# A tibble: 12 x 10
# Groups:   Date, Name [6]
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16       0        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0

Let us take another scenario, Jeff has a deficit of 12 hours on 16th and leave of 6 hours. Megan has deficit of 15 hours on 23rd not covered by any leave. df in that case would be

# A tibble: 12 x 10
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40      12        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     6
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40      15        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16      24        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0

And output

# A tibble: 12 x 10
# Groups:   Date, Name [6]
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       6        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     6
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40      15        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16       0        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0

It should match your expectation and logic provided. Revised scenario would be (only Billable Hours)

enter image description here

user_28848590

Would this work? It doesn't preserve all the original rows, but as far as I understand it would give you the values that seem to be going into the chart.

library(tidyverse)
df %>%
  mutate(Deficit = Deficit - Leave) %>%
  mutate(Leave = 0) %>%  # leave this line out if you want to show the original Leave as well as its reduction on Deficit
  group_by(Date, Name, Team, Status) %>%
  summarize(across(Standard:Leave, sum)) %>%
  ungroup()

Result

# A tibble: 6 x 8
  Date          Name   Team    Status Standard Deficit Overtime Leave
  <chr>         <chr>  <chr>   <chr>     <dbl>   <dbl>    <dbl> <dbl>
1 April 16 2021 Jeff   Coastal FT           40       0        0     0
2 April 16 2021 Megan  Coastal FT           40       0        0     0
3 April 16 2021 Minden Coastal FT           16       0        0     0
4 April 23 2021 Jeff   Coastal FT           40       0        0     0
5 April 23 2021 Megan  Coastal FT           40       0        0     0
6 April 23 2021 Minden Coastal FT           28      12        0     0
user_68518250

This is how I would tackle it:

df %>% 
  pivot_longer(cols = c(Hours, Standard, Deficit, Overtime, Leave)) %>% 
  arrange(Date, Name) %>% 
  select(-c(Hours_Type)) %>% 
  group_by(Date, Name, name) %>% 
  summarise(value = max(value)) %>% 
  pivot_wider(id_cols = c(Date, Name),
              names_from = name,
              values_from = value) %>% 
  mutate(Deficit = Deficit - Leave,
         Hours = Standard + Leave) %>%
  pivot_longer(-c(Date, Name)) %>% 
  filter(name != "Hours") %>%
  rename(hours = value) %>% 
  ggplot(aes(x = Name, y = hours, fill = name)) +
  geom_bar(stat = "identity", position = "stack") +
  geom_text(aes(label = ifelse(hours != 0,
                               paste(name,
                                     hours,
                                     sep = " = "), "")),
            position = position_stack(vjust = 0.5)) +
  facet_wrap(~ Date, ncol = 1) +
  theme_bw(base_size = 16) +
  theme(axis.title.x = element_blank())

example_1.png

user_129573400 
df %>% 
   group_by(Date, Name, Team) %>%
   mutate(Deficit = Deficit*(sum(Deficit)!=sum(Leave)))


# A tibble: 12 x 10
# Groups:   Date, Name, Team [6]
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16       0        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0
user_83802720

Update: Comment AnilGoyal added Name to dplyr::group_by(Date) %>%

Behind the scenes here I assume that if the sum of Leave and Deficit are equally in the same Date then there should be no Deficit e.g. 0. If not then keep the Deficit

df %>% 
  dplyr::group_by(Date, Name) %>% 
  dplyr::mutate(Deficit = case_when(sum(Leave)== sum(Deficit) ~ 0,
                             TRUE ~ Deficit))

Output:

# Groups:   Date [2]
   Date          Name   Team    Status Hours_Type Hours Standard Deficit Overtime Leave
   <chr>         <chr>  <chr>   <chr>  <chr>      <dbl>    <dbl>   <dbl>    <dbl> <dbl>
 1 April 16 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 2 April 23 2021 Jeff   Coastal FT     Billable      40       40       0        0     0
 3 April 16 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 4 April 23 2021 Jeff   Coastal FT     Leave          0        0       0        0     0
 5 April 16 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 6 April 23 2021 Megan  Coastal FT     Billable      40       40       0        0     0
 7 April 16 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 8 April 23 2021 Megan  Coastal FT     Leave          0        0       0        0     0
 9 April 16 2021 Minden Coastal FT     Billable      16       16       0        0     0
10 April 23 2021 Minden Coastal FT     Billable      28       28      12        0     0
11 April 16 2021 Minden Coastal FT     Leave         24        0       0        0    24
12 April 23 2021 Minden Coastal FT     Leave          0        0       0        0     0
user_133216470
Related