# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine
import sqlite3

df=pd.read_excel('cleaned_data.xlsx')
df.head()

df.info()

df.describe()

df.rename(columns={'Area(Sqft)':'Area in sqft'},inplace=True)

df

df.rename(columns={'Price($)':'Price in $'},inplace=True)

df

engine=create_engine('postgresql://postgres:1234@localhost:9000')
df.to_sql('real_state',engine)

df.Location.value_counts()
#select "Location", count(*) from real_state group by "Location" 

plt.hist(df.Location,bins=20,color='orange')
plt.xticks(rotation="vertical")
plt.title("Number of rows according to location")
plt.show()

pd.set_option('display.float_format',lambda x:"%.2f" % x)

df["Price in $"].describe()
#sql select max("Price in $") from real_state
#select "Location", max("Price in $"),min("Price in $") from real_state group by "Location"

df.groupby('Location')['Price in $'].min()

df.groupby('Location')['Price in $'].max()

df[df['Price in $'] == 1895]

df[df['Price in $'] == 169000000]

df.groupby('Location')['price/sqft'].mean().sort_values(ascending=False)
#select "Location",avg("price/sqft") from real_state group by "Location"

df.groupby('Location')['price/sqft'].mean().sort_values(ascending=False).plot(style=".-")

df[df['Location']=='San Francisco']['price/sqft'].max()

df[df['price/sqft']==3903.51]

# +
#sql
#select "Location" , max("price/sqft") from real_state
#where "Location"  = 'San Francisco'
#group by "Location"
# -

df[df['Location']=='Miami']['price/sqft'].min()
#sql
#select "Location" , min("price/sqft") from real_state
#where "Location"  = 'Miami'
#group by "Location"

df.groupby('Location')[['Price in $','price/sqft']].mean()
#sql
#select "Location" ,avg("Price in $"),avg("price/sqft") from real_state
#group by "Location"

df.groupby('Location')['Price in $'].mean().sort_values(ascending=False).plot(kind='bar')
plt.ticklabel_format(useOffset=False,axis='y',style='plain')

df[df['Location']=='Las Vegas']['Price in $'].idxmax()

df.iloc[1850]

df.loc[1850]

df[df['Location']=='Las Vegas']['Price in $'].max()
#select "Location" ,max("Price in $") from real_state
#where "Location" ='Las Vegas'
#group by "Location"

df[df['Location']=='San Diego']['Price in $'].idxmin()

df.loc[4723]

df[['Price in $','price/sqft']].corr()

sns.heatmap(data=df[['Price in $','price/sqft']].corr())

p1=df.groupby('Location')['Price in $'].mean()

p2=df.groupby('Location')['price/sqft'].mean()

# +
fig,ax1=plt.subplots()
ax2=ax1.twinx()
ax1.plot(p1,color='red')
ax2.plot(p2,color='green')
ax1.set_xlabel('Location')
ax1.set_ylabel('Mean Price in $',color='red')
ax2.set_ylabel('Price per sqft',color='green')

fig.autofmt_xdate(rotation=45)
plt.grid(True)
fig.show()
# -

pd.pivot_table(df,index=['Location','Parking'],values='price/sqft')

check=df[(df['Location']=='Washington') & (df['Parking']=='no')]

check.Parking.value_counts()

check.Location.value_counts()

check['price/sqft'].mean()



StarcoderdataJupyterScriptsDedupFiltered