This article is the next in the series about the topic of Pandas and SQL similarity which we discussed earlier
Pandas and SQL similarity (2)
Check the dtypes
To check data types of the data frame, type ‘df.dtypes’ command
type sp_help table_name in the query editor. You may be wondering what is ‘nvarchar’
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
You can visit for more information on varchar and nvarchar.
Check the data
Once you load the data in to the pandas you can view the data by running the ‘df.head()’ command. This command will give you output of first 5 rows and all columns by default. This output is not in the order.
You can specify the number of rows you want to see e.g. 10
To view last rows in the data frame, type ‘df.tail()’ command
We can sort the data frame by column name in the ascending order
by descending order
In SQL we have to use ‘ORDER BY’ Column to view the data.
Check the NULL Value
Every data set will have some kind of NULL or missing values either in one column or multiple column. Command to find out NULL Value is
The ‘Product Base Margin’ column has 72 missing values.
In SQL to get NULL Value we have to run below query for each column.
SELECT * from table where columnname IS NULL
Select the columns
What if we want to see the specific columns, how do we select them?
Note – Pandas will not display all rows in the output by default, to see all rows please run the command –> ‘pd.set_option(‘display.max_rows’, None)’
select [Row ID],[Order Priority] from table name.
Filter the data
Every data scientist or data analyst have to filter the data to get some insight. How do we do that in both pandas and SQL?
Lets say, we need details of all the orders shipped through ‘Regular Air’ only
and sent to California state.
SELECT * from table where [Ship Mode] = ‘Regular Air’
Hope you like the article about pandas and SQL command similarity part 2, Stay tuned for next update.
Pandas and SQL are the integral part of Data Science. It is used for data manipulation and analysis while SQL or Structured Query Language is usually used to fetch data, update the contents of the table, or operate on the structure of the database or tables, using any type of database tools, which will have a user interface to apply the operations on the database.
I believe Pandas and SQL are both similar in terms of analyzing the datasets, freshers like me find it difficult to remember the command syntax of both tool. A small effort from my end to show the command similarity.
Pandas Installation –
To work on both the tool, first we need to install them. I am using Anaconda distribution on my laptop which comes with all the required library pre-installed. To download Anaconda, please go to their site here
Launch the Jupyter Notebook
Load the pandas module,
Note – If you are not using Anaconda, then pandas installation procedure can be found here
SQL Installation –
I am using Microsoft SQL Express edition for the demo purpose, which is available here for the download and installation procedure here.
Note – The only expectation here is that you should know pandas syntax.
Importing the data –
I am using ‘read_excel’ because my source file is Excel and added sheet_name parameter because i want to import only first sheet from the excel file.
You can import entire excel in to the SQL database with the help of SQL Import and Export wizard.
You have to choose source data source as ‘Microsoft Excel’ as shown in below screenshot
Choose the Excel file which you want to import and the correct Excel version
Select ‘SQL Server Native Client 11.0’ in the ‘Choose a Destination’ screen
Pick correct server name, authentication type and the database
You can select first option to copy entire data from the excel sheet or you can write custom SQL query to import only required data. I will go ahead and select first option.
Here please select the sheet you to import under first column, new table will be created in the database with the sheet name (second column).
It will import data and will create new table in to the database which we provided.
Lets work on the data now –
How to view entire data
‘df’ is nothing but a variable to store the data. Pandas can store or read different type of data which is called as DataFrame.
You can type ‘print(df)’ or ‘df’ to view the entire data.
Connect to the SQL Express using the SQL Management Studio
Click on the ‘New Query’
type below commands in the query field
‘select * from the dbo.Orders
Hope you enjoyed the article on pandas and SQL, please visit for next set of commands.
I have added replication step at the end of script.
Please do due diligence before running this RP4VM consistency group creation API script in the production environment. I am working on it to improve it, if you have any suggestions feel free to drop me a message or send me an email.
This article is about a script for OVA deployment from ESXi local data store. This idea came to my mind after working on one project where i had to deploy OVAs on around 250 ESXi clusters.
Here i used VMware OVFTool and Selenium webdriver. You can Download both software from the links mentioned below. Import-Vapp command from powercli doesn’t support HTTPS source for OVA deployment from ESXi local data store. You can check its help section for more information.
I am starting new blog series on VxRail (Hyper Converged Product). To start with this, i thought about writing VxRail – VSAN Software component which will help in better understanding of VxRail and VSAN.
VSAN Software Component
Cluster Level Object Manager (CLOM)
•An instance of CLOM runs on all the vSAN nodes •/etc/init.d/clomd [start | stop | restart | status] (Need to check on VxRail Appliance) •Checks if object can be created based on available resources •Ensures policy compliance of an object •Is responsible for initial placement, migration and replacement of components •Distributes the load equally across all the hosts
Distributed Object Manager (DOM)
•An instance of DOM runs on all the vSAN nodes •Creates local components of an object once CLOM agrees to create an object •Instructs DOM on other nodes to create the remaining components •Resyncs the objects during recovery •Defines three roles: Owner, Client and Component Manager
•Provides distributed access path to the Object
•There is one owner assigned to each DOM Object
•Owner also determines who is allowed to send I/Os to the object
•DOM Client performs I/Osto the object on behalf of a VM •It runs on every host that runs a VM •Typically resides with DOM Owner
Local Log-Structured Object Manager (LSOM)
•Performs SSD log recovery when the host boots •Interacts with the MD and SSD directly •Provides persistence of storage for the components •Provides read and write buffering •Reports unhealthy drives •Performs I/O retries to the failing drives
•Reliable Datagram Transport (RDT)
•Network protocol for the transmission of vSAN traffic
•Cluster Membership, Monitoring and Directory Services (CMMDS)
•DOM, CLOM may use CMMDS database to learn about the topology and object configuration •Requires multicast network to be able to form a cluster (pre vSAN 6.6) •CMMDS is used to elect owners for objects •It maintains inventory of items such as hosts, devices and networks •Stores object metadata info such as policies, distributed RAID etc. in an in-memory database •CMMDS discovers, maintains and establishes a cluster of networked node members •Defines four cluster roles: Master, Backup, Agent and witness host(forstretched clusters) •The roles have nothing to do with managing objects in the cluster or doingI/Os to the objects •The backup speeds up the process of convergence if master fails •The roles are decided at the time of cluster discovery •Updates are exchanged with the help of RDT •Master to other hosts – > 126.96.36.199:12345 (Every second) •Other hosts to Master -> 188.8.131.52:23451 (Updates)
For more information about VSAN, please refer VMware VSAN
PTE Introduction – Introduction.This section will help you with PTE Speaking and writing information.
To assess speaking your skills will be tested by your ability to produce spoken English in an academic environment and writing section will require you to write response in academic English using correct Grammar and spelling.
Speaking 1.1 – Personal Introduction
The personal introduction is an opportunity for you to give some information about yourself. You will get 25 second to read the prompt and prepare your response, and 30 second to give response. This section will not be scored. You can give introduction in below format
Your name, where are you working currently
Your hobbies, what is importance of English in your life
Why did you choose PTE
Speaking 1.2 – Read a loud
You have 30-40 seconds to prepare, depending on the length of the passage. You hear a short tone. After the tone, start speaking immediately. Do not start speaking before the microphone opens because your voice will not be recorded. You should speak clearly. There is no need to rush.
Finish speaking before the progress bar reaches the end. The word “Recording” changes to “Completed”.
Tip -You can record only once and if you pause for 3 second then recording will be stopped.
Scoring – You will be scored based on Content, Pronunciation and oral fluency
Content is not about what you do, it is about what you should not do.
You should not –
1. Misread a word - you say a different word then mentioned in paragraph / text. e.g. When someone ---- and you say when somebody
2. Skip a word - means you leave / don't mention a word e.g. when someone offers afree sample. here you missed "a"
3. Add means - you add a word that does not belong their. e.g. when "a" someone offer. You added "a"
Be careful with
Articles – a / an / the
Preposition – in / on / at / to / for / with
Plural – Nouns –> Its very IMP to correctly pronounce plural forms, don’t skip “s”
5 Native like - Means native English speaker can easily understand you 4. Advanced - There were mistakes but i (native speaker) get you 3. Good - Interference with first language 2. Intermediate - What is that, i (native speaker) could not understand. 1. Low - Sorry ? 0. Non-English - What?
Top score “5” means – all “44” sounds of English are made correctly. Accent is fine but PTE focus on if you are capable of making all 44 sounds.
Understand what you are reading, if you understand what you are reading then you will read it naturally like pause at right place, correct pronunciation.
Read a loud means – Express the meaning as much as you can, as if you are giving speech or conveying a message.
In this section,you need to repeat the sentence you hear.The audio begins to plays automatically. When the audio finishes, the microphone opens and the recording status box shows “Recording”. Speak into the microphone immediately (there is no short tone) and repeat exactly what you heard. You should speak clearly. There is no need to rush.
Finish speaking before the progress bar reaches the end. The word “Recording” changes to “Completed”. You are not able to replay the audio and only able to record your response once.
You will get 3 sec to prepare to listen, please take a deep breath. Question will have single sentence and repeated one time only so you must listen carefully. There are chances you might get 10-12 repeat a sentence question.
Content – It has max 3 points per sentence. If you repeat every word in correct sequence, you will get 3 points. You get 2 points if you can repeat more than 50% of words in the correct sequence and 1 point if you repeat less than 50 % word in correct sequence.You should repeat the sentence with correct grammar.
Pronunciation and oral fluency – You get 5 points for native like pronunciation and oral fluency.
Try to copy the rhythm and speed from the audio.
Sentence will generally have 10-15 words.
Take care of pronouncing “s” at correct place.
Sentence will last for 3-4 sec, you should be able to finish in same time with no errors.
Speaking 1.4 : Describe image
You will get one graph / diagram or image in this question. 25 seconds will be provided to prepare and 40 seconds for answer.
Types of images –
Double Bar chart
Two bar chart
Double pie chart
Diagram or process
You might get 5-6 images on test day. You will be judges or marked based on below 3 main points
Content – You should describe the image and not label it.
Pronunciation – How clearly describe image
Oral Fluency – How smoothly you describe image
Use below method to describe the image –
a. Introductory sentence ( in 5 Sec ) –
You should start like ” This —– represents “the title” + x-axis.”
lets take above bar chart example –
“This bar chart represents the top oil reserves in various countries like Venezuela, Saudi, Canda,Iran, Russia, UAE, US and China in Million barrels.“
We have to arrange the title, so it makes grammatical sense. First we have to identify the type of graph, then looks at the title and X and Y axis.
b. Image Description (in 25 Sec)-
You should describe image in such a way that it cover all aspects of image. We can describe above image in following way
“Venezuela has the most amount of oil at close to 300,000. This is followed closely bySaudi Arabia, Canada and then Iran. The US and China have the least amount ofoil – well below 1 million barrels each”
c. Conclusion (in 10 sec) –
Actually you will get 10 Sec, but i would suggest to do it in 5 sec. You can start like “Overall we can say that ……..” to show it as we are concluding the image description
We can conclude above image like “Overall, this graph shows a steep and gradual decline in the amount of oil that each country has and Venezuela has far more oil than the US and China.”
This time i have decided to write a non-technical stuff. Recently i cleared my PTE and thought of writing what is PTE and how to prepare for it.
What is PTE?
PTE Academic is a computer-based academic English language test aimed at non-native English speakers wanting to study abroad and for immigration purpose. It tests reading, writing, Speaking and Listening.
Questions often test 2 skills together, such as listening and reading or reading and speaking. The whole test is done in a single session, lasting 3 hours and is taken sitting at a computer in a secure test environment. The speaking part of the exam is done at the computer. Your voice is recorded and sent for marking. One advantage of this test is that you get your results very quickly; in about 5 days.
There are twenty different question formats, ranging from multiple choice through to essay writing and interpreting information.PTE Academic assesses real-life, academic English, so you will hear excerpts from lectures and view graphs and charts. You will hear a range of accents in the test, from British and American to non-native speakers, so you will be exposed to the type of accents you will encounter in everyday life.
PTE Academic assesses real-life, academic English, so you will hear excerpts from lectures and view graphs and charts. You will hear a range of accents in the test, from British and American to non-native speakers, so you will be exposed to the type of accents you will encounter in everyday life.
Recently i had to run some commands on 250 linux servers in one of my project. So i decided to use plink.exe from windows machine. This section will help you how to use plink to run linux command from windows on multiple server
syntax – -m – create filename called “script.txt” or the name you want and place it under folder where plink.exe is located -pw – password of remote host -l – user id to login to remote host ip address of host -t – it will open pseudo shell
lets say script.txt include, hostname pwd ls /tmp
Above command will show output on your local windows machines cmd.exe or powershell
if the host key was not cached in the registry yet, you would get an interactive prompt saying “Store key in cache? (y/n)” which would not be conducive to a batch script. And once again, the way around this is to pipe a “y” to stdin, which makes the command: cmd.exe /c echo y | plink.exe -ssh -t -pw myP4ss -l myuser@trusty1 -m script.txt <IP Address of host?
plink and sudo with password prompt
In the above example we dealt with simple commands that required no input and no privilege escalation with sudo (hostname, pwd). But many times the commands we want to run in batch processes require sudo, which throws up an interactive prompt for a password.
Sudo password echoed and embedded inside plink script
Make sure you call plink with “-t” so that a tty is setup. Then sudo to a shell and echo the password into stdin before running the command you want with sudo.
Recently i came across a situation where i had to create and assign IP to vmkernel portgroup (for vmotion) for 3300 ESX host. So i wrote one basic powercli script to assign IP on each host in ESX Cluster.
Privacy & Cookies Policy
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.