Split Datasets

Image
 The Objective of this article is to transform data set from row to column using explode() method. The scope of this article is to understand how to  unnest or explode a data set using parallel processing framework Pyspark and Python native library- Pandas . Dataset looks like as below: dept,name 10,vivek#ruby#aniket 20,rahul#john#amy 30,shankar#jagdish 40, 50,yug#alex#alexa Pandas explode() import pandas as pd pan_df=pd.read_csv(r'explode.csv') df_exp=pan_df.assign(name=pan_df['name'].str.split('#')).explode('name') df_exp Output: Dataset is transformed successfully and we are able to create new rows from nested dataset. Pandas way of explode is simple, crisp and straight forward unless the dataset is complex. In next section of this article we will cover PySpark way of exploding or unnesting dataset. PySpark explode() Import libraries and Connect to Spark from pyspark import SparkContext,SparkConf import pyspark from pyspark.sql import SparkSes
Post a Comment

Ingest Excel Data

Data can be in any format, recently I have got chance to work on excel data sets. However we have pandas as very efficient library to work with different types of datasets but, pandas degrades performance whenever data size goes beyond MBs to GBs. For efficient processing of GBs datasets parallel computing was designed and Spark shines here. And we have a library named com.crealytics:spark-excel_xxx,this package allows querying Excel spreadsheets as Spark Data Frames and leverage the parallel computing infrastructure.


The Objective of this article is to understand the usage of spark-excel library with python version of spark or Pyspark.

Connect to spark (standalone cluster)
import pyspark
from pyspark.sql import SparkSession
spark=SparkSession.builder \
	.appName('Spark_DB') 
    .config("spark.jars.packages", "com.crealytics:spark-excel_2.11:0.12.2") \
    .getOrCreate()
com.crealytics:spark-excel_2.11:0.12.2 is the creaytics spark-excel package used for handling excel data sets in python version of spark.

Import Data sets
emp_df=spark.read \
    .format("com.crealytics.spark.excel") \
    .option("dataAddress", "'Employees'!A1") \
    .option("useHeader", "true") \
    .load("D:\python_coding\pyspark_tutorial\demo_excel.xlsx")

emp_df.show(10)

dept_df=spark.read \
	.format("com.crealytics.spark.excel") \
    .option("dataAddress", "'Departments'!A1") \
    .option("useHeader", "true") \
    .load("D:\python_coding\pyspark_tutorial\demo_excel.xlsx")
dept_df.show(10)

region_df=spark.read \
    .format("com.crealytics.spark.excel") \
    .option("dataAddress", "'Regions'!F5:G9") \
    .option("useHeader", "true") \
    .load("D:\python_coding\pyspark_tutorial\demo_excel.xlsx")

region_df.show(10)
Write Data Frame output to Excel file
 #join emp and dept datasets
emp_dept_df= emp_df.join(dept_df,emp_df.DEPARTMENT_ID==dept_df.DEPARTMENT_ID,how='inner')
emp_dept_df.show(15)


#write emp_dept_df to excel using write method with specified format
emp_dept_df.write.format("com.crealytics.spark.excel") \
    .option("dataAddress", "emp_dept [#All]") \
    .option("useHeader", "true") \
    .mode("append") \
    .save("D:\python_coding\pyspark_tutorial\output_demo.xlsx")


region_df.write.format("com.crealytics.spark.excel") \
    .option("dataAddress", "region_details [#All]") \
    .option("useHeader", "true") \
    .mode("append") \
    .save("D:\python_coding\pyspark_tutorial\output_demo.xlsx")

Github link for Datasets and Notebook:  https://github.com/viv07/python_ETL/tree/master/spark-excel

To summarize, topics covered in blog as listed below:

- use of crealytics spark-excel library 
- read/write excel data sets

That's all with how to process Excel datasets with python version spark- Pyspark.

Thanks for reading my blog and supporting the content. 
Please subscribe and follow me on blogspot for upcoming contents. Word of appreciation always helps to keep up the spirit and a healthy knowledge network helps us grow.
Share the Knowledge.
I am a Data Engineer and I love to study and teach, extreme love for writing, programming, travel, cook, and fitness enthusiast. Believe in sharing Knowledge.

Comments

Post a Comment

Popular posts from this blog

Split Datasets

Image
 The Objective of this article is to transform data set from row to column using explode() method. The scope of this article is to understand how to  unnest or explode a data set using parallel processing framework Pyspark and Python native library- Pandas . Dataset looks like as below: dept,name 10,vivek#ruby#aniket 20,rahul#john#amy 30,shankar#jagdish 40, 50,yug#alex#alexa Pandas explode() import pandas as pd pan_df=pd.read_csv(r'explode.csv') df_exp=pan_df.assign(name=pan_df['name'].str.split('#')).explode('name') df_exp Output: Dataset is transformed successfully and we are able to create new rows from nested dataset. Pandas way of explode is simple, crisp and straight forward unless the dataset is complex. In next section of this article we will cover PySpark way of exploding or unnesting dataset. PySpark explode() Import libraries and Connect to Spark from pyspark import SparkContext,SparkConf import pyspark from pyspark.sql import SparkSes
Read more

pySQL

Image
 The Objective of this article is to understand how to perform data manipulation on pandas DataFrames using SQL with pandasql library.  What is pandasql? As per documentation pandasql allows us to query pandas DataFrames using SQL syntax. Installation of pandasql Library can be installed using below two methods, both of them uses PIP installation: Using Terminal pip install -U pandasql Using Jupyter Notebooks !pip install -U pandasql Use Case The main function used in pandasql is sqldf. sqldf accepts two arguments:  SQL query  Session environment variables ( globals() and locals() ) session environment variables is optional and handled by python itself, even if we do not provide the session variables, we still can achieve the goal of converting pandas DataFrame to perform SQL querying. Import Necessary Libraries from pandasql import sqldf import pandas as pd Import Data Sets emp_df=pd.read_csv(r'D:\python_coding\pandas_practice\emp.csv') emp_df.head(10) dept_df=pd.read_csv(r&
Read more

Can Julia compete PySpark? A Data Comparison

Image
Creators of Julia language claims Julia to be very fast, performance wise as it does not follow the two language theory like Python, it is a compiled language where as Python is an amalgamation of both compilation and interpretation. It would be interesting to dig deep to understand about how both of these languages behave behind the scenes but the objective of this blog is not to get into the theoretical details of the differences.  As a Data Engineer my innate behavior is to understand how Julia behaves when it is bombed with GBs or TBs of data sets. As I am talking about GBs or TBs of data sets so obviously I can not straight away compare Python with Julia or even the rich Pandas library, as all of us know the processing will never complete as python is quiet slow. So the scope of this blog is to draw parallels between Julia and PySpark, I know for some this is unfair but pardon me. Inspiration behind the blog is the Twitter podcast on Julia that took place in Jan 2022.  #Note: I ha
Read more