SQL Problem ,Recursive CTE

 

WITH  integer_sequence(n) AS (

  SELECT 2019 -- starting value

  UNION ALL

  SELECT n+1 FROM integer_sequence WHERE n < 2021 -- ending value

)

Select customer_id,customer_name,AVG(Amount) from (

Select A.n as bill_Year,A.customer_id ,A.customer_name,ISNULL(Amount,0) AS Amount from (

SELECT * FROM integer_sequence A  cross join  (Select distinct customer_id,customer_name from Test_SQL)B

) A

left outer join  Test_SQL B on  A.n=DATEPART(YEAR,B.ddate) and A.customer_id=B.customer_id

 

) B

group by customer_id,customer_name

Useful git command

 git remote  -v  (find he git origin link)

git branch branch-name    (create new branch)

git branch -a (list all branch)

git switch "branch-name"  (switch to another branch)

git merge "branch-name"  (merging branch )

git push --set-upstream origin "branch-name"  update new branch in remote

git log --oneline --all --graph  (list changes in files)

Box Plot for All Column

 plt.figure(figsize=(10,10))

sns.boxplot(data=df)
plt.show()

List to Dictionary to DataFrame

#List

height = [151, 174, 138, 186, 128, 136, 179, 163, 152, 131]

weight = [63, 81, 56, 91, 47, 57, 76, 72, 62, 48]

##List to Dictionary

baby_dic = {'height':height,'weight':weight} 

#dictionary to DataFrame

baby_data = pd.DataFrame(baby_dic)

Outlier Detecting in Pandas

 Q1 = df_boston['CRIM'].quantile(0.25)

Q3 = df_boston['CRIM'].quantile(0.75)

IQR = Q3 - Q1

Outlier_min = Q1 - 1.5 * IQR 

Outlier_max = Q3 + 1.5 * IQR

print(IQR, Outlier_min, Outlier_max)

###outlier dealing

df_boston['CRIM'] = np.where(df_boston['CRIM']>= Outlier_max, Outlier_max,df_boston['CRIM'])

df_boston['CRIM'] = np.where(df_boston['CRIM']<= Outlier_min, Outlier_min,df_boston['CRIM'])

Pandas Series and Dataframe

 # Series from list

a = [4, 100, 45, 32, 7, 2]

example1 = pd.Series(a)

print(example1)

# Series with index

example2 = pd.Series(a, index = ["a", "b", "c","d","e","f"])

# Series from Data Dictionary

datadict = {"data1": 420, "data2": 380, "data3": 390}

example3 = pd.Series(datadict)

# Dataframe from list

data = [['Alex',10],['Bob',12],['Clarke',13]]

df1 = pd.DataFrame(data,columns=['Name','Age'])

df1

# Add new Column in Dataframe

df2['Address'] = pd.Series(['Mumbai','Pune','Delhi'])

# Add new rows

data = [['Ehsan',10,'Mumbai'],['Rahman',12,'Goa'],['Himlu',13,'Milan']]

df3 = pd.DataFrame(data,columns=['Name','Age','Address'])

df = df2.append(df3)

df

Usefull Pandas Statical Formula

 #data.sex.describe()

Describinf sing Column

#data.std()

For Standard Deviation

#Skewness and Kurtosis

print(skew(data.pollution_exp, axis=0, bias=True))

print(kurtosis(data.pollution_exp, axis=0, bias=True))