Running head: DATA MANIPULATION OF MORRISANIA/LONGWOOD 1 Data Manipulation of MORRISANIA/LONGWOOD Real Estate Market Yuanfei Zang Boston University DATA MANIPULATION OF MORRISANIA/LONGWOOD 2 Table of Contents Executive summary.............................................................................................................3 Data Manipulation of MORRISANIA/LONGWOOD Real Estate Market........................4 Procedures and Findings......................................................................................................4 Figures.................................................................................................................................6 DATA MANIPULATION OF MORRISANIA/LONGWOOD 3 Executive summary This assignment aims to use RStudio to extract datasets for selected neighborhood among related given tables, to calculate average sale price per square foot for selected neighborhood each year, and to visualize calculated result. First of all, I joined all related and needed tables, did calculations for average price per square foot for selected neighborhood, and gained views of yearly sale information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018. Additionally, I found that the price consistently increasing overall from around $32.48 per square foot on average in 2003 up to around $148.85 on average in 2018. Then, I filtered datasets by removing fields with 0 for price or square feet, generated new filtered datasets accordingly, and did similar steps to calculate and visualize yearly sale information of filtered datasets. In addition, I observed that the average price per square foot increases from around $54.63 per square foot on average in 2003 up to around $260.0 on average in 2018. At last, I gathered filtered datasets for other two neighborhoods, MOTT HAVEN/PORT MORRIS (id 168), and MORRIS PARK/VAN NEST (id 166), in order to compare all yearly sale performances among three neighborhoods so that I can gain a wider view of overall real estate market. After comparing them, I found that average price per square foot kept increasing overall for all three neighborhoods. Overall, I completed this assignment, since I solved all stated questions, explored few more visualizations techniques, and at last but not the least, I gained technical skills using power RStudio, and better understanding of sales performance on the real estate market. DATA MANIPULATION OF MORRISANIA/LONGWOOD 4 DATA MANIPULATION OF MORRISANIA/LONGWOOD 5 Data Manipulation of MORRISANIA/LONGWOOD Real Estate Market Contents below are mainly detailed description of my assignment procedures, such as how I get my results, what are the results I got, what I learned or observed based on the results. Procedures and Findings Through data manipulations, I got the following findings. Unfiltered Yearly Sales For MORRISANIA/LONGWOOD First imported all needed datasets including NEIGHBORHOOD, BUILDING_CLASS, and NYC_HISTORICAL after connected to SQL server. Then, by using left join method, I put all three datasets together and filtered by Neighborhood ID, which selected as 167, referred to MORRISANIA/LONGWOOD. And got first finding, which can be seen in Figure 1. Specifically, the finding shows Yearly Sales information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018 (included fields with 0 for price or square feet). Importantly, the price consistently increasing overall from around $32.48 per square foot on average in 2003 up to around $148.85 on average in 2018. However, there had two significant price drops: more than $20 reduction per square foot on average in 2007, and more than $55 reduction per square foot on average in 2010. Plotted datasets in Figure 1, which can be seen in Figure 2. Figure 2 generally shows the same findings as Figure 1 indicates, since Figure 2 is visualization of Figure 1. Filtered Yearly Sales For MORRISANIA/LONGWOOD Next, filtered out datasets that have 0 sale price and gross square feet, then got new datasets which indicates filtered datasets, which can be seen in Figure 3. Based on Figure 3, though both total sale price and total square feet of residential units decreased, total square feet DATA MANIPULATION OF MORRISANIA/LONGWOOD 6 of residential units decreased dramatically, which led to higher average price per square foot than unfiltered datasets. Overall, the average price per square foot increases from around $54.63 per square foot on average in 2003 up to around $260.0 on average in 2018. Similarly, few price drops happen: more than $50 reduction per square foot on average in 2007, about $20 reduction per square foot on average in 2010, and consistently reduction from $149.93 on average in 2013 to $116.11 on average in 2015, which indicates more than $30 reduction from 2013 to 2015. Interesting thing was that after the significant price drops in 2015, average residential units’ price per square foot soon almost doubled in 2018. Plotted datasets in Figure 3, which can be seen in Figure 4. Figure 4 generally shows the same findings as Figure 3 indicates, since Figure 4 is visualization of Figure 2. Comparison of Filtered Average Price / Square Foot Each Year Similar to Filtered Yearly Sales For MORRISANIA/LONGWOOD, plotted filtered yearly sales for MORRISANIA/LONGWOOD (id 167), MOTT HAVEN/PORT MORRIS (id 168), and MORRIS PARK/VAN NEST (id 166) into Figure 7, where datasets of MORRIS PARK/VAN NEST can be seen in Figure 5, and datasets of MOTT HAVEN/PORT MORRIS can be seen in Figure 6. As Figure 7 shows, MORRIS PARK/VAN NEST has the highest average sale price per square foot from 2003 to 2017, while the other two holds the 2nd and 3rd positions back and forth. However, when it comes to 2018, MORRISANIA/LONGWOOD holds the highest average sale price per square foot, around $260.0, followed by MOTT HAVEN/PORT MORRIS, which achieved about $254.32 average sale price per square foot, and MORRIS PARK/VAN NEST had the least average sale price per square foot at this time. DATA MANIPULATION OF MORRISANIA/LONGWOOD 7 Figures Figure 1. Unfiltered Yearly Sales of Residential Real Estate in MORRISANIA/LONGWOOD. Yearly Sales information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018 (included fields with 0 for price or square feet). DATA MANIPULATION OF MORRISANIA/LONGWOOD 8 Figure 2. Unfiltered Average Price / Square Foot Each Year For MORRISANIA/LONGWOOD. Average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018 (included fields with 0 for price or square feet). ß DATA MANIPULATION OF MORRISANIA/LONGWOOD 9 Figure 3. Filtered Yearly Sales of Residential Real Estate in MORRISANIA/LONGWOOD. Yearly Sales information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018 (removed fields with 0 for price or square feet). DATA MANIPULATION OF MORRISANIA/LONGWOOD 10 Figure 4. Filtered Average Price / Square Foot Each Year For MORRISANIA/LONGWOOD. Average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167) from 2003 to 2018 (removed fields with 0 for price or square feet). DATA MANIPULATION OF MORRISANIA/LONGWOOD 11 Figure 5. Filtered Yearly Sales of Residential Real Estate in MORRIS PARK/VAN NEST. Yearly Sales information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MORRIS PARK/VAN NEST (id 166) from 2003 to 2018 (removed fields with 0 for price or square feet). DATA MANIPULATION OF MORRISANIA/LONGWOOD 12 Figure 6. Filtered Yearly Sales of Residential Real Estate in MOTT HAVEN/PORT MORRIS. Yearly Sales information including Year, total sale price, total square, and average price of 1 square foot of residential real estate in MOTT HAVEN/PORT MORRIS (id 168) from 2003 to 2018 (removed fields with 0 for price or square feet). DATA MANIPULATION OF MORRISANIA/LONGWOOD 13 Figure 7. Comparison of Filtered Average Price / Square Foot Each Year. Average price of 1 square foot of residential real estate in MORRISANIA/LONGWOOD (id 167), MOTT HAVEN/PORT MORRIS (id 168), and MORRIS PARK/VAN NEST (id 166) from 2003 to 2018 (removed fields with 0 for price or square feet).