title: cleaning data within groups in R
body: <p>I have data from survey that comes with typos, missing data and others. I need to populate/clean data within groups, considering that if there is any valid value (making sure there is just one valid value) for the variable within the group, it should replace the other values in variable in same group. See below my mock data and the result expected. I am being reading about fill, mutate using group_by but still have no idea on how to do it.</p>
  <p><strong>Idea:</strong> if value is not in (&quot;Unknown&quot;, &quot;NA, &quot;N/A&quot;, &quot;null&quot;), then copy that value in the variable within the group</p>
  <p>Data:</p>
  <pre><code>data &lt;- data.frame(group = c(&quot;A1&quot;, &quot;A1&quot;, &quot;A1&quot;, &quot;A1&quot;, &quot;A2&quot;, &quot;A2&quot;, &quot;B4&quot;, &quot;B4&quot;, &quot;B4&quot;, &quot;C1&quot;),
                     number.persons = c(&quot;4&quot;,NA,NA, &quot;N/A&quot;, &quot;unknow&quot;, &quot;2&quot;, &quot;3&quot;, &quot;3&quot;, NA,&quot;1&quot;),
                     own.rent = c(&quot;own&quot;, &quot;own&quot;, NA, &quot;N/A&quot;, &quot;rent&quot;, NA, &quot;own&quot;, &quot;N/A&quot;, &quot;own&quot;, &quot;own&quot;),
                     car      =c(&quot;yes&quot;, &quot;yes&quot;, NA, &quot;unkwon&quot;, &quot;no&quot;, NA, &quot;no&quot;, &quot;no&quot;, &quot;unknwon&quot;, &quot;no&quot;))
  </code></pre>
  <p><a href="https://i.stack.imgur.com/roKGW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/roKGW.png" alt="enter image description here" /></a></p>
  <p>Desired result:</p>
  <pre><code>new.data &lt;- data.frame(group = c(&quot;A1&quot;, &quot;A1&quot;, &quot;A1&quot;, &quot;A1&quot;, &quot;A2&quot;, &quot;A2&quot;, &quot;B4&quot;, &quot;B4&quot;, &quot;B4&quot;, &quot;C1&quot;),
                         number.persons = c(&quot;4&quot;,&quot;4&quot;,&quot;4&quot;, &quot;4&quot;, &quot;2&quot;, &quot;2&quot;, &quot;3&quot;, &quot;3&quot;, &quot;3&quot;,&quot;1&quot;),
                         own.rent = c(&quot;own&quot;, &quot;own&quot;, &quot;own&quot;, &quot;own&quot;, &quot;rent&quot;, &quot;rent&quot;, &quot;own&quot;, &quot;own&quot;, &quot;own&quot;, &quot;own&quot;),
                         car      =c(&quot;yes&quot;, &quot;yes&quot;, &quot;yes&quot;, &quot;yes&quot;, &quot;no&quot;, &quot;no&quot;, &quot;no&quot;, &quot;no&quot;, &quot;no&quot;, &quot;no&quot;))
  </code></pre>
  <p><a href="https://i.stack.imgur.com/nXJkt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nXJkt.png" alt="enter image description here" /></a></p>
  <p>One solution I came up:</p>
  <pre><code>data.pre &lt;- data %&gt;%
              mutate(flag.number.persons = ifelse(!number.persons %in% c(&quot;unknow&quot;, &quot;unkwon&quot;,&quot;unknwon&quot;,&quot;null&quot;, &quot;na&quot;, &quot;n/a&quot;, &quot;N/A&quot;, &quot;NA&quot;, &quot;&quot;) &amp; !is.na(number.persons), 1, 0),
                     flag.own.rent = ifelse(!own.rent %in% c(&quot;unknow&quot;, &quot;unkwon&quot;,&quot;null&quot;, &quot;unknwon&quot;,&quot;na&quot;, &quot;n/a&quot;, &quot;N/A&quot;, &quot;NA&quot;, &quot;&quot;) &amp; !is.na(own.rent), 1, 0),
                     flag.car      = ifelse(!car %in% c(&quot;unknow&quot;, &quot;unkwon&quot;,&quot;null&quot;, &quot;unknwon&quot;,&quot;na&quot;, &quot;n/a&quot;, &quot;N/A&quot;, &quot;NA&quot;, &quot;&quot;) &amp; !is.na(car), 1, 0)) %&gt;%
              group_by(group) %&gt;%
              mutate(number.persons2 = ifelse(flag.number.persons==0, number.persons[flag.number.persons==1], number.persons),
                     own.rent2      = ifelse(flag.own.rent == 0, own.rent[flag.own.rent==1], own.rent),
                     car2            = ifelse(flag.car == 0 , car[flag.car==1], car))
  </code></pre>
  <p>Looking at the new data with new variables to see how it worked my code:</p>
  <p><code> subset(data.pre, select=c(group, number.persons2, own.rent2, car2))</code></p>
  <p><a href="https://i.stack.imgur.com/EjKlN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EjKlN.png" alt="enter image description here" /></a></p>
  <p>My solution so far is kind of tedious since I am making new variables as flags to tag the &quot;valid value&quot; according conditions stablished and then mutate replacing with the valid values ( I am actually making another variables just to test code), but first, making so many variables is slow and a mess, and second I have like 500 variables then making flags for each one doesn't make any sense. Is there a faster/elegant way to do this? Maybe a function that receive the variables I want to clean and the list of no-valid values?</p>
  <h1>Edition</h1>
  <p>I've trying the options received and getting errors when there is only one observation in the group and any variable is missing</p>
  <p><strong>Data edited</strong>
  <a href="https://i.stack.imgur.com/WztJP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WztJP.png" alt="enter image description here" /></a></p>
  <p>Code used from Rui Barradas</p>
  <pre><code>data %&gt;%
   group_by(group) %&gt;%
   mutate(across(everything(), ~ if_else(.x == &quot;N/A&quot;, NA_character_, .x)),
   across(everything(), ~ if_else(grepl(&quot;^unk&quot;, .x), NA_character_, .x))) %&gt;%
   mutate(across(everything(), na.locf))
  </code></pre>
  <p>Error</p>
  <p><a href="https://i.stack.imgur.com/G0729.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/G0729.png" alt="enter image description here" /></a></p>