# -*- coding: utf-8 -*-
"""
Created on Wed Mar 23 09:44:06 2022

@author: paraj
"""
import seaborn as sns
import statistics as stats
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
import numpy as np
import statsmodels.api as sm
import pandas as pd
import matplotlib as plt
import seaborn as sns
import statistics as stats
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
import numpy as np
import statsmodels.api as sm
import pandas as pd
import statsmodels.api as smf


# PROBLEM 1)
datap1 = pd.read_excel(r"C:\Users\paraj\OneDrive\Desktop\python\Problem 1.xlsx")

datap1["loc_temp"] = datap1.Location.str.split(",")
datap1["PrimaryLocation"] = datap1["loc_temp"].apply(lambda x: x[0].split("(")[0])
datap1["PrimaryLocation"] = datap1["PrimaryLocation"].str.strip()
datap1.drop(columns=["loc_temp"], inplace=True)
datap1.loc[datap1["PrimaryLocation"] == "Mumbai Suburbs", "PrimaryLocation"] = "Mumbai"
datap1.loc[
    (datap1["PrimaryLocation"] == "Bangalore")
    | (datap1["PrimaryLocation"] == "Bengaluru Bangalore"),
    "PrimaryLocation",
] = "Bengaluru"
datap1.loc[datap1["PrimaryLocation"] == "Doha", "PrimaryLocation"] = "Qatar"
datap1.loc[datap1["PrimaryLocation"] == "Dubai/ UAE", "PrimaryLocation"] = "Dubai"
datap1.reset_index(inplace=True, drop=True)

datap1["PrimaryLocation"].nunique()
location_data = datap1[
    datap1["PrimaryLocation"].isin(
        [
            "Delhi",
            "Bengaluru",
            "Gurgaon",
            "Noida",
            "Pune",
            "Ahmedabad",
            "Mumbai",
            "Chennai",
            "Hyderabad",
            "Dubai",
            "Kolkata",
            "Australia",
            "Canada",
            "Sinagapore",
            "Malaysia",
        ]
    )
]

location_data = location_data.groupby("PrimaryLocation").count()
p1 = sns.barplot(data=location_data, x=location_data.index, y="Experience")
p1.set_ylabel("Number of Jobs")
p1.set_xticklabels(labels=list(location_data.index), rotation=90)
p1.set(title="Number of Jobs per City")

location_data1 = datap1[
    datap1["PrimaryLocation"].isin(
        [
            "Delhi",
            "Bengaluru",
            "Gurgaon",
            "Noida",
            "Pune",
            "Ahmedabad",
            "Mumbai",
            "Chennai",
            "Hyderabad",
            "Dubai",
            "Kolkata",
            "Australia",
            "Canada",
            "Singapore",
            "Malaysia",
        ]
    )
]
location_data1 = location_data1.groupby("PrimaryLocation").nunique()
location_data1.columns = ["1", "2", "3", "4", "Number of Companies"]
p2 = sns.barplot(data=location_data1, x=location_data1.index, y="Number of Companies")
p2.set_xticklabels(labels=location_data1.index, rotation=90)
p2.set(title="Number of Companies per City")


# Problem 2:

# a)
work = datap1
work["temp1"] = work["Experience"].str.replace("yrs", "").str.strip()
work["temp2"] = work["temp1"].str.split("-")
work["Average Experience"] = work["temp2"].apply(
    lambda x: stats.mean([float(a) for a in x])
)
work.drop(columns=["temp1", "temp2"], inplace=True)

sns.boxplot(data=work, y="Average Experience")

work_sal = datap1
work_sal["temp1"] = work_sal["Salary"].str.split("to")
work_sal["Average Salary"] = work_sal["temp1"].apply(
    lambda x: stats.mean([float(a) for a in x])
)
final_df = work_sal.groupby("PrimaryLocation").mean()
finaldf = final_df.loc[
    [
        "Delhi",
        "Bengaluru",
        "Gurgaon",
        "Noida",
        "Pune",
        "Ahmedabad",
        "Mumbai",
        "Chennai",
        "Hyderabad",
        "Dubai",
        "Kolkata",
        "Australia",
        "Canada",
        "Singapore",
        "Malaysia",
    ],
    :,
]
p3 = sns.barplot(data=finaldf, x=finaldf.index, y="Average Salary")
p3.set_xticklabels(labels=finaldf.index, rotation=90)
p3.set(title="Average Salary by Location")


"""Problem 2"""
datap2 = pd.read_excel(r"C:\Users\paraj\OneDrive\Documents\Python Scripts\assignment 2.xlsx")

# Fitting regression models

X1 = np.array(datap2["X1"]).reshape(-1, 1)
X1 = sm.add_constant(X1)
X2 = np.array(datap2["X2"]).reshape(-1, 1)
X2 = sm.add_constant(X2)
X3 = np.array(datap2["X3"]).reshape(-1, 1)
X3 = sm.add_constant(X3)
X4 = np.array(datap2["X4"]).reshape(-1, 1)
X4 = sm.add_constant(X4)

Y1 = np.array(datap2["Y1"]).reshape(-1, 1)
Y2 = np.array(datap2["Y2"]).reshape(-1, 1)
Y3 = np.array(datap2["Y3"]).reshape(-1, 1)
Y4 = np.array(datap2["Y4"]).reshape(-1, 1)

model1 = smf.OLS(Y1, X1)
print(model1.fit().summary())

model2 = smf.OLS(Y2, X2)
print(model2.fit().summary())

model3 = smf.OLS(Y3, X3)
print(model3.fit().summary())

model4 = smf.OLS(Y4, X4)
print(model4.fit().summary())

"""Despite the fact that the data for each model differs,
 the R-squared and coefficient values appear to be similar. 
 The summaries that have been printed above show the detailed testing results and values. 
 Given that the data is different, all of the models will have a relatively similar line of best fit,
 which appears to be a discrepancy."""