Skip to main content

The Data Crusher

This blog is for complex data analysis problem and trying to solve in good manner.

Search This Blog

Home

Showing posts with the label pyspark

Posts

How to get increasing and decreasing sale in dataframe find via pyspark

February 09, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to add new column of gender male as and female as 1

February 09, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to add prefix in dataframe column for all via pyspark

February 09, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to unpivot with two column via pyspark

February 09, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to pivot table in pyspark

February 08, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to connect mysql with pyspark and make some filters

February 08, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to explode the dataframe via pyspark

February 05, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to sum two column country with dept in below table in json via pyspark

February 05, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to find Prev and Next price from price column via pyspark

February 05, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

json array file explode like below output in pyspark

February 05, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to join two table and find high and low in AQILevel

February 05, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

Give input dataframe to find the desired output in pyspark

February 03, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to get marks compare to low,more or same as result via pyspark

February 03, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to find out desired output by pyspark

February 03, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

How to calculate cumulative amount in below table in pyspark

February 03, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

Solve within pyspark below questions

January 31, 2023

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

Page Views this month

Your can Ask me Question?

Name

Email *

Message *

Popular Post

Find Total working hours of each employee in a day through spark and scala

Find Total working hours of each employee in a day through spark and scala File name : epid.txt 1,"2020-12-30T09:00:00","IN" 1,"2020-12-30T13:00:00","OUT" 1,"2020-12-30T14:00:00","IN" 1,"2020-12-30T17:00:00","OUT" 1,"2020-12-30T17:30:00","IN" 1,"2020-12-30T20:00:00","OUT" 2,"2020-12-30T10:30:00","IN" 2,"2020-12-30T15:00:00","OUT" 2,"2020-12-30T16:30:00","IN" 2,"2020-12-30T21:30:00","IN" Solution : val schemax = new StructType() .add("eid",IntegerType,true) .add("ts",TimestampType,true) .add("event",StringType,true) val df = spark.read.format("csv").schema(schemax).load("file:///D:/epid.txt") df.show() df.printSchema() val unixtime = unix_timestamp(col("ts")) val pro = (unixtime-lag(unixtime,1).over(Window.partitionBy("eid&q

How to complex string data convert into dataframe via pyspark.

How to complex string data convert into dataframe via pyspark altimetric Input: Jack xxxx ["BL", "ND"] John yyyy ["CH", "MB"] Output: +----+----+---+ | _1| _2| _3| +----+----+---+ |Jack|xxxx| BL| |Jack|xxxx| ND| |John|yyyy| CH| |John|yyyy| MB| +----+----+---+ Solve: dfjack = sc.textFile("file:////home/cloudera/jack.txt") dfjack.foreach(print) John yyyy ["CH", "MB"] Jack xxxx ["BL", "ND"] task1 = dfjack.map(lambda x : x.replace('["','')).map(lambda x:x.replace('"]','')).map(lambda x:x.replace('", "','-')).map(lambda x : x.replace(" ",",")) task1.foreach(print) Jack,xxxx,BL-ND John,yyyy,CH-MB splitdata = task1.map(lambda x:x.split(",")) splitdata.foreach(print) ['Jack', 'xxxx', 'BL-ND'] ['John', 'yyyy', 'CH-MB'] df = splitdata.toDF() df.show() +----+---

How to make balance of bank statement

How to make balance of bank statement coforge Input: Output: Solution: create table bank (TransactionDate varchar(20),AccName varchar(20),Type varchar(20),Amount integer); insert into bank values ("2017-01-01","Anvesh","CR",60000.00),("2017-02-01","Anvesh","DB",8000.00),("2017-03-01","Anvesh","CR",8000.00),("2017-04-01","Anvesh","DB",5000.00),("2017-01-01","Nupur","CR",10000.00),("2017-02-02","Nupur","CR",8000.00),("2017-03-03","Nupur","DB",8000.00) WITH balances AS ( SELECT AccName, TransactionDate, Type, Amount, SUM(CASE WHEN Type = 'DB' THEN -Amount ELSE Amount END) OVER (PARTITION BY AccName ORDER BY TransactionDate) AS AccBalance FROM bank ) SELECT TransactionDate, AccName, Type, Amount, balance FROM balances ORDER BY AccName, TransactionDat

Find below expected output through spark and scala

Find below expected output through spark and scala File Name : dxr.txt 11111,"2021-01-15",10 11111,"2021-01-16",15 11111,"2021-01-17",30 11112,"2021-01-15",10 11112,"2021-01-16",20 11112,"2021-01-17",30 Output Required : Solution : val schemax = new StructType() .add("sensorid",IntegerType,true) .add("timestamp",TimestampType,true) .add("values",IntegerType,true) val df = spark.read.format("csv").schema(schemax).load("file:///D:/dxr.txt") df.show() df.printSchema() val dx = df.withColumn("tx",lead("values",1).over(Window.partitionBy("sensorid") orderBy("timestamp"))) dx.show() val dr = dx.withColumn("diff",col("tx")-col("values")) .filter(col("tx").isNotNull) .drop("values","tx") .withColumnRenamed("diff","values") dr.show()

Hive Assignment

Hive Assignment incedo Input: 1) Create a database “Olympic”, then within this database create a table “player” in following structure: pid: Int, pname: String, gender: String, age: Int, height: Int, eid: Int Note: create the partition on “gender” column. 2) Create a table “event” within this database in following structure: eid: Int, ename: String, year: Int, season: String, city: String Note: create the partition on “season” column and bucketing on “year” column. 3) Write a query to insert data into two table from two text files “player.txt” and “event.txt” respectively. 4) Do the query on the following scenarios: a. List the players details who not participated in Olympic event. b. List the number of players participated in each event c. List the number of Male and Female Players participated in event d. List down number of players participated in Beijing Olympic, e. List down player details who have highest age and joined in Olympic Event. f. Find out the play

Powered by Blogger

The Data Crusher