The Problem
===========

I currently have a pandas dataframe with property information from [this](https://www.kaggle.com/new-york-city/nyc-property-sales) kaggle dataset. The following is an example dataframe from that set:

```
| neighborhood  | borough | block | year built | ... |
------------------------------------------------------
| Annadale      | 5       | 5425  | 2015       | ... |
| Woodside      | 4       | 2327  | 1966       | ... |
| Alphabet City | 1       | 396   | 1985       | ... |
| Alphabet City | 1       | 405   | 1996       | ... |
| Alphabet City | 1       | 396   | 1986       | ... |
| Alphabet City | 1       | 396   | 1992       | ... |
| Alphabet City | 1       | 396   | 0          | ... |
| Alphabet City | 1       | 396   | 1990       | ... |
| Alphabet City | 1       | 396   | 1984       | ... |
| Alphabet City | 1       | 396   | 0          | ... |

```

What I want to do is take every row where the value in the "year built" column equals zero, and replace the "year built" value in those rows with the median of the "year built" values in the rows with the same neighborhood, borough, and block. There are cases where there are multiple rows within a {neighborhood, borough, block} set that have a zero in the "year built" column. This is shown in the example dataframe above.

To illustrate the problem I put these two rows in the example dataframe. 

```
| neighborhood  | borough | block | year built | ... |
------------------------------------------------------
| Alphabet City | 1       | 396   | 0          | ... |
| Alphabet City | 1       | 396   | 0          | ... |

```

To solve the problem I want to use the mean of the "year built" values from all the other rows that had the same neighborhood, borough, and block to fill the "year built" value in rows that had a zero in the "year built" column. For the example rows the neighborhood is Alphabet City, the borough is 1, and the block is 396 so I would use the following matching rows from the example dataframe to calculate the mean:

```
| neighborhood  | borough | block | year built | ... |
------------------------------------------------------
| Alphabet City | 1       | 396   | 1985       | ... |
| Alphabet City | 1       | 396   | 1986       | ... |
| Alphabet City | 1       | 396   | 1992       | ... |
| Alphabet City | 1       | 396   | 1990       | ... |
| Alphabet City | 1       | 396   | 1984       | ... |

```

I would take the mean of the "year built" column from those rows (which is 1987.4) and replace the zeros with the mean. The rows that originally had zeros would end up looking like this:

```
| neighborhood  | borough | block | year built | ... |
------------------------------------------------------
| Alphabet City | 1       | 396   | 1987.4     | ... |
| Alphabet City | 1       | 396   | 1987.4     | ... |

```

The code I have so far
======================

All I've managed to do so far is chop out rows with zeros in the "year built" column and find the mean year of every {neighborhood, borough, block} set. The original dataframe is stored in raw\_data and it looks like the example dataframe at the very top of this post. The code looks like this:

```
# create a copy of the data
temp_data = raw_data.copy()

# remove all rows with zero in the "year built" column
mean_year_by_location = temp_data[temp_data["YEAR BUILT"] > 0]

# group the rows into {neighborhood, borough, block} sets and take the mean of the "year built" column in those sets
mean_year_by_location = mean_year_by_location.groupby(["NEIGHBORHOOD","BOROUGH","BLOCK"], as_index = False)["YEAR BUILT"].mean()

```

and the output looks like this:

```
| neighborhood  | borough | block | year built | 
------------------------------------------------
| ....          | ...     | ...   | ...        |
| Alphabet City | 1       | 390   | 1985.342   | 
| Alphabet City | 1       | 391   | 1986.76    | 
| Alphabet City | 1       | 392   | 1992.8473  | 
| Alphabet City | 1       | 393   | 1990.096   | 
| Alphabet City | 1       | 394   | 1984.45    | 

```

So how can I take those average "year built" values from the mean\_year\_by\_location dataframe and replace the zeros in the original raw\_data dataframe?

I apologize for the long post. I just wanted to be really clear.