title: Change column value based on calculated mean of other column in python pandas
body: <p>I am newbie to pandas. I have gone through many questions, but doesn't found an answer.  </p>
  
  <p>I have the following datasets.</p>
  
  <pre><code>Name    || Price    || Cuisine Category || City || Region || Cuisine Types || Rating Types || Rating
  
  Pizza  || 600  || Fast Food,Pizza || Ajmer ||   Ana Saga || Quick Bites || Good || 3.9
  
  ...     ...     ...     ...     ...     ...     ...     ...     ...
  
  Chawla's || 300 || Beverages || Ajmer || Sagar Lake     || Cafe || Average || 3.3
  
  Masala || 0 || North,South Indian || Ajmer || Ram Ganj || Mess || None || NEW
  </code></pre>
  
  <p>I want to change the value of: </p>
  
  <ul>
  <li><p>Rating where it is a NEW, on the basis of Average Rating of that particular Cuisine Types and then Rating Types based on the calculated Ratings</p></li>
  <li><p>Price where it is a 0, on the basis of Average Price of that particular Region </p></li>
  </ul>
  
  <p>My try for changing price:</p>
  
  <p>Reading CSV File</p>
  
  <pre><code>data = pd.read_csv('/content/Ajmer.csv')
  </code></pre>
  
  <p>calculate Region wise mean of Price </p>
  
  <pre><code>gregion = round(data.groupby('Region')['Price'].mean()) 
  </code></pre>
  
  <p>Trying to replace 0 of price column</p>
  
  <pre><code>data['Price'] = data['Price'].replace(0, gregion[data['Region']])
  </code></pre>
  
  <p>But my price column is unchanged.</p>
  
  <p>My try for changing Rating:</p>
  
  <p>Reading CSV file</p>
  
  <pre><code>data2 = pd.read_csv('/content/Ajmer.csv')
  </code></pre>
  
  <p>Creating separate data frame, so that it wont affect mean value.</p>
  
  <pre><code>filtered_rating = data2[(data2['Rating'] == 'NEW') | (data2['Rating'] == '-') | (data2['Rating'] == 'Opening')]
  </code></pre>
  
  <p>Dropping from original data2</p>
  
  <pre><code>data2.drop(data2.loc[data['Rating']=='NEW'].index, inplace=True)
  data2.drop(data2.loc[data['Rating']=='-'].index, inplace=True)
  data2.drop(data2.loc[data['Rating']=='Opening'].index, inplace=True)
  </code></pre>
  
  <p>Calculating Cuisine Types wise mean of Rating</p>
  
  <pre><code>c = round(data2.groupby('Cuisine Types')['Rating'].mean(),1)
  </code></pre>
  
  <p>which gives me output as follows:</p>
  
  <pre><code>Cuisine Types
  
  Bakery            3.4
  
  Confectionery     3.4
  
  Dessert Parlor    3.5
  
  ...
  
  Quick Bites       3.4
  
  Sweet Shop        3.4
  
  Name: Rating, dtype: float64
  </code></pre>
  
  <p>Trying to replace values</p>
  
  <pre><code>filtered_rating['Rating'].replace('NEW', c[data2['Region']], inplace=True)
  filtered_rating['Rating'].replace('-', c[data2['Region']], inplace=True)
  filtered_rating['Rating'].replace('Opening', c[data2['Region']], inplace=True)
  </code></pre>
  
  <p>But my Rating column is unchanged.</p>
  
  <p>Expected output</p>
  
  <ul>
  <li><p>Mean Price of that particular Region of row where the price is zero in Price column</p></li>
  <li><p>Mean Rating of that particular Cuisine Type of row where the Rating is NEW in Rating column</p></li>
  </ul>
  
  <p>Can anyone help me out to do this?</p>
  
  <p>Thanks in Advance!
  I will be more than happy for your help</p>