Fill column with conditional mode of another column

Question

Given the below list, I'd like to fill in the 'Color Guess' column with the mode of the 'Color' column conditional on 'Type' and 'Size' and ignoring NULL, #N/A, etc.

For example, what's the most common color for SMALL CATS, what's the most common color for MEDIUM DOGS, etc.

Type  Size    Color   Color Guess
Cat   small   brown   
Dog   small   black   
Dog   large   black   
Cat   medium  white   
Cat   medium  #N/A    
Dog   large   brown   
Cat   large   white   
Cat   large   #N/A    
Dog   large   brown   
Dog   medium  #N/A    
Cat   small   #N/A    
Dog   small   white   
Dog   small   black   
Dog   small   brown   
Dog   medium  white   
Dog   medium  #N/A    
Cat   large   brown   
Dog   small   white   
Dog   large   #N/A

Answer 1

As BarMar already stated in the comments, we can use pd.Series.mode here from the linked answer. Only trick here is, that we have to use groupby.transform, since we want the data back in the same shape as your dataframe:

df['Color Guess'] = df.groupby(['Type', 'Size'])['Color'].transform(lambda x: pd.Series.mode(x)[0])

---

   Type    Size  Color Color Guess
0   Cat   small  brown       brown
1   Dog   small  black       black
2   Dog   large  black       brown
3   Cat  medium  white       white
4   Cat  medium    NaN       white
5   Dog   large  brown       brown
6   Cat   large  white       brown
7   Cat   large    NaN       brown
8   Dog   large  brown       brown
9   Dog  medium    NaN       white
10  Cat   small    NaN       brown
11  Dog   small  white       black
12  Dog   small  black       black
13  Dog   small  brown       black
14  Dog  medium  white       white
15  Dog  medium    NaN       white
16  Cat   large  brown       brown
17  Dog   small  white       black
18  Dog   large    NaN       brown