You are doing an amazing job. Bro

Well explained, Thank you 🙏

from pyspark.sql.functions import * df1 = df.select('name',substring('phone',1,3).alias('stdnum'),substring('phone',5,8).alias('phonenum')) # when length is not fixed use below method #df1 = df.select('name',substring('phone', 1, 3).alias('stdnum'),substring('phone', 5, len(df.select('phone').first()[0]) - 4).alias('phonenum')) df1.display()

data=[('Goa', ' ', 'AP'),('', 'AP', None), (None, ' ', 'Bglr')] columns=["city1", "city2", "city3"] df=spark.createDataFrame(data, columns) display(df)

easy to understand👍

input dataframe: from pyspark.sql.types import * data =[('IT','M') ,('IT','F') ,('IT','M') ,('IT','M') ,('HR','F') ,('HR','F') ,('HR','F') ,('HR','F') ,('HR','F') ,('Sales','M') ,('Sales','M') ,('Sales','F') ,('Sales','M') ,('Sales','M') ,('Sales','F')] schema =['DeptName','Gender'] df1=spark.createDataFrame(data,schema) display(df1)

the idea of giving code in description is the best keep it up

CREATE TABLE DistanceTable ( FromLocation VARCHAR(50), ToLocation VARCHAR(50), Distance INT ); INSERT INTO DistanceTable (FromLocation, ToLocation, Distance) VALUES ('Kanpur', 'Unnao', 18), ('Unnao', 'Kanpur', 18), ('Unnao', 'Lucknow', 60), ('Unnao', 'Noida', 450), ('Kanpur', 'Delhi', 460), ('Lucknow', 'Ayodhya', 130), ('Ayodhya', 'Lucknow', 130), ('Agra', 'Delhi', 220), ('Mathura', 'Delhi', 140), ('Delhi', 'Agra', 220);

thank u for explaining,,,Can some please explain below Table1 Table2 inner join left join right full join cross join Sample1 52 Sample1 52 1 5 7 9 10 49 Sample2 53 Sample21 53 0 Sample1 48 Sample11 48 1 Sample3 48 Sample22 70 1 Sample4 52 Sample3 48 0 Sample5 54 Sample41 52 1 Sample5 20 Sample5 54 1 SQL query for full join is showing 11 records and I see NULL values.. NOT sure why Name age Sample1 52 Sample2 53 Sample1 48 Sample3 48 Sample4 52 Sample5 54 Sample5 20 NULL NULL NULL NULL NULL NULL NULL NULL

Wow

God bless you for this amazing playlist. Thank you very much.

What if i want to define only 2 column not all the columns then what i can do?

this is vert static code.. we can use the dynamic code like... Order MonthNoNew = IF(Orders[Order MonthNumber] > 6, Orders[Order MonthNumber] - 6, Orders[Order MonthNumber] + 6)

Thank you sir, this is very useful. I did not quite understand the purpose of joining a dataframe with itself, this part: df1.alias('a').join(df1.alias('b') Could someone explain?

Thank you

Awsm approach to teach

Hi bro good playlist kindly upload more challenging and solvable sql questions in this playlist. if you couldnot find time, plz upload it in a single video thanks for your efforts

This should be Part-29

dbutils.widgets.text("nth_salary", "1") windowSpec = Window.partitionBy(F.col("Deptname")).orderBy(F.col("Salary").desc()) result_df = df.withColumn("salary_rnk", F.dense_rank().over(windowSpec)) nth_val= int(dbutils.widgets.get("nth_salary")) highest_rnk= result_df.agg(F.max("salary_rnk").alias("max_val")).collect()[0][0] if nth_val <= highest_rnk: result_df.filter(F.col("salary_rnk") == int(dbutils.widgets.get("nth_salary"))).drop("salary_rnk").display() else: print(f"This value is out of Bounds. We are expecting rank values less than or equal to {highest_rnk}.")

very helpful. thanks a lot!

can you teach how to do the page 2 of this dataset, i mean the dashboard at page 2 on your power bi

We can do it without alter row right? Without alter row ,It will give the same output

Can we use web activity instead of webhook? Also, do we need to use same account for teams and ADF in this scenario?

bhai @oggysworls5205 ne jo queery puchi hai na wo batao mera bhi same issue hai

Thank you for this video. Very helpful.

Thank you.

Can't we use append variable activity to get the name of the fine in for loop rather using another get metadata activity?

Amazing playlist for Databricks beginners.

df1 = df.withColumn("City1", when(col("City1").isin("","null"), None).otherwise(col("City1"))) \ .withColumn("City2", when(col("City2").isin("","null"), None).otherwise(col("City2"))) \ .withColumn("City3", when(col("City3").isin("","null"), None).otherwise(col("City3"))) df1= df1.withColumn("NewColumn",coalesce(col("City1"),col("City2"),col("City3"))) df1.show()

First of all, nice video Second, should the timestamp be hard coded everytime? How to make the time stamp dynamic??

Why r we calling testing folder as archive folder?

with cte as (Select * From Adm_Education union Select * From Adm_Sports union Select * From Adm_behaviour), cte_2 as (select Student_id,education_marks,case when education_marks <=70 then 'Fail' when education_marks >70 and education_marks<=80 then 'Pass' when education_marks >80 then 'Merit' end as marks_status from cte), cte_3 as (select Student_id,marks_status,count(2) as cnt from cte_2 group by Student_id,marks_status ), cte_4 as (select *,row_number() over(partition by student_id order by cnt desc) rn from cte_3) select Student_id,marks_status from cte_4 where rn =1

Dataset for above video: ItemId,VartId,ItemName,VartName,WhNo,Zone,Bin 1,1,Bajaj 20CT100 20Mudguard,Black,Wh001,Z001,B001 1,2,Bajaj 20CT100 20Mudguard,Red,Wh001,Z001,B002 1,3,Bajaj 20CT100 20Mudguard,Silver,Wh001,Z001,B003 2,1,Hero 20Super 20SPLD 20Mudguard,Black,Wh001,Z002,B001 2,2,Hero 20Super 20SPLD 20Mudguard,Red,Wh001,Z002,B002 2,3,Hero 20Super 20SPLD 20Mudguard,Blue,Wh001,Z002,B003 3,2,Hero 20HF 20DLX 20Mudguard,Red,Wh001,Z003,B002 3,1,Hero 20HF 20DLX 20Mudguard,Black,Wh001,Z003,B003 4,1,Hero 20HF 20DLX 20Mudguard,Black,Wh001,Z004,B001

Complwtly confused

Great video and nice explanation

Hi Bro, Is the playlist completed?

not works for even row numbers. it should take the average of the middle ones for even row numbers.

Can we get GPU A100 from gcp on free account

We have supplier's in legend and count of late pos in values. 90% late suppliers need to show with their name and remaining 10% should come in others category. How to do this.pls help.

Alternate approach is to use regexpreplace. replaces space more than one with ','. then apply split function. then iterate over array to get column values. split(regexReplace(Column_1,`[ ]{2,}`,','),',')[1], split(regexReplace(Column_1,`[ ]{2,}`,','),',')[2], so on...

We can do this very easily with logic app

I am working in Persistent Systems since very long time. They are not showing any leniency to even very old employees. I was on bench for two months and I received a call from HR for termination, but I got saved luckily as I got assigned to the project on the same day. In past Persistent was known as very employee friendly company but now as they are growing they are becoming worse.

hey, when i try debit card , its getting rejected

I think Q 5 should be wrong. UPDATE @TBL SET ID=NAME,NAME=ID Once you set the ID column to have value of the NAME then the value of ID itself should be lost. Which means both columns now should have the NAME value. Could you please help me (and other) on that. Thank you.

can you please help me on 'error message the requested operation requires elevation'

Sir, make video of pyspark Architecture please?

Its a good project and Nice idea.

----> 1 df1=df.select(spark_partition_id().alias('partid')).groupBy('partid').count() NameError: name 'spark_partition_id' is not defined Sir I am getting above error

hi, thanks for the video presented. I just want to know how we can reduce the PIE size (Circle). because when we make that Pie Visual some label gets hidden.

Can you please share Excel file?