Python Pandas Pivot Table

This blog is going to be mostly helpful to folks who work with excel and databases. But, if you work with Excel and find yourself needing to deal with automation of pivot tables, you might also appreciate the technique.

When working with MySQL database, it was not an easy way for me to fetch the data and reshape it to a Pivot Table. I did duckduckgo, found ample of examples but most of them were hard to understand or not yielding the required output.

Python’s Pandas Pivot Table was the savior!! I followed below steps to fetch data from MySQL DB and generate a simple Pivot Table. Later this pivot table exported as an excel file and shared with stakeholders using slack.

  • Create connection string to MySQL DB using PyMySQL
  • Create a SQLAlchemy Engine (ORM)
  • Write a Query statement to fetch data from DB
  • Create a Pandas Dataframe directly from the query using SQLAlchemy
  • Create a Pandas Pivot Table with required Index, Columns and Values
  • Export data to excel or csv file using to_excel and to_csv respectively
  • Share it to stakeholders via slack

Please find below Python code screenshots

I hope this blog triggered a thought to automate the mundane task of generating pivot tables from spreadsheets or databases. Please reach out to me if you want to check out the full code.

Thank you for stopping by this blog and please share your suggestions below under the Comment section

Up Next – Manage Alerts of VMAX3 array using REST API

Image Courtesy:

Matt Whitt
https://ttorial.com/Images/python-automation-automate-mandane-task-python.jpg

Advertisements

TweePy – Twitter of Python

I had an opportunity to attend the NASSCOM Technology and Leadership Forum in Mumbai from 20th to 22nd Feb 2019. It was an amazing experience to listen, gain knowledge and insights from the industry’s best of the bests. CxO’s keynote speeches were focused on technologies like AI, ML, Blockchains et al

My personal favorite out of all the keynote was from Vala Afshar, Chief Digital Evangelist @ Salesforce. It was a privilege to be there and watch him explaining about AI, ML, and the importance of data. Truly inspiring and mesmerized by his depth of knowledge in the IT industry.

As mentioned by Vala Afshar, “Data is the oil of 21st century but oil is just useless thick goop until you refine it into fuel. AI is your refinery“. In those 3 days, a lot of critical information was shared and scattered across all the social media. The reason to write this blog is to share my idea to save those GEM of information which I can keep munching time and again to get inspired and motivated.

Twitter, the most popular social media platform is one of my favorites and wanted to save all those tweets which had hashtag #NASSCOM_TLF (official hashtag of the event). After quick research using ‘DuckDuckGo’, I had decided to use TweePy Twitter for Python module which is developed to use Twitter API to connect, read, write, retweet and send direct messages right from Python.

Tweepy requires twitter app to be created to use Twitter’s API to exchange information between Twitter and Python. I followed this link to set up and run my twitter app.

Here is the code which I wrote to download all the tweets having hashtag #NASSCOM_TLF and save it to an excel file!

# Download tweepy using pip install tweepy
import tweepy
# Pandas dataframe used to get tweets in tabular format and export it excel
import pandas as pd

# Replace consumer key, consumer secret
consumer_key = 'REPLACE'
consumer_secret = 'REPALCE'

# Replace access token key and secret
access_token = 'REPLACE-REPLACE'
access_token_secret = 'REPLACE'

# Authenticate to Twitter API
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth)

# Create dataframe with columns names 
df = pd.DataFrame(columns=['text','timeline', 'username', 'user_id'])
# Initialize lists to store messages
msgs = []
msg =[]

# search twitter with hashtag #NASSCOM_TLF and exclude retweets
for tweet in tweepy.Cursor(api.search, q='#NASSCOM_TLF -filter:retweets',tweet_mode='extended', rpp=100).items():
    msg = [tweet.full_text, tweet.created_at, tweet.user.name, tweet.user.screen_name] 
    msg = tuple(msg)                    
    msgs.append(msg)

# Append tweets stored in lists to dataframe
df = pd.DataFrame(msgs)
# Column header columns having full tweet messages, time, user name and ID
df.columns = ['Tweet Text', 'Tweet Date Time (GMT)', 'Username', 'User ID']
# Check the first 5 tweets to see any errors
print(df.head())
# Create a file 
output = "tweets_ntlf.xlsx"
# Export tweets from dataframe to excel
try:
    df.to_excel(output, index=False)
except Exception as Error:
    print("Unable to get NASSCOM Tweets", Error)
Screenshot of the excel file

NOTE: I’ve excluded retweets to avoid duplication of information.

Please continue to stop by this blog and share your comments below.

Up Next – Manage Alerts of VMAX array using REST API

Docker’ize’ Python

What is Docker?

Docker is a computer program that performs operating-system-level virtualization, also known as “containerization”. It was first released in 2013 and is developed by Docker, Inc. source: Wikipedia

How Docker works?

Docker containers wrap up software and its dependencies into a standardized unit for software development that includes everything it needs to run: code, runtime, system tools, and libraries. This guarantees that your application will always run the same and makes collaboration as simple as sharing a container image.
source: www.docker.com

Why Docker?

Docker unlocks the potential of your organization by giving developers and IT the freedom to build, manage and secure business-critical applications without the fear of technology or infrastructure lock-in. source: www.docker.com

Pycodestyle @ Sublime Text

Is your script/code looks dull and not in order? If yes, then its time to use pycodestyle!!

Pycodestyle (formerly known as PEP8) is a syntax and style checker tool for Python language. In this blog, I’ve documented step by step procedure to install this with Sublime Text.

Before we start, let us briefly understand more about linter. Lint or linter is a tool that analyzes source code to flag programming errors, bugs, stylistic errors. To know more please check out the wiki link

Pycodestyle is one of the linter plugins available for Sublime Text. To install this plugin, first, we need to install Sublime Linter plugin.

Prerequisites:

  1. Python3.x
  2. Sublime Text
  3. PIP

***This step by step procedure to apply to Debian and Debian based Linux Operation Systems.***

Steps to install pycodestyle:

  1. Install system package using pip or apt
$pip3 install pycodestyle
OR
$sudo apt install python3-pycodestyle
$which pycodestyle # shows the location where pycode style is

2. Install pycodestyle plugin in sublime text

a. Open package control (CTRL+SHIFT+P), type 'install package' and hit 'ENTER' # It takes some time to load repositories. 
b. Type 'sublimelinter-pycodestyle' and Click on the first option as shown below. After installation, a new tab window will open showing installed plugin information
C. To configure sublime-linter settings, goto Preferences > Package Settings > SublimeLinter > Settings
d. In the Sublime Linter settings, make sure @disable: false is set s shown in below screenshot. 
e. To test pycodestyle, create a new file and save it as test.py.
Start writing the code, Pycodestyle will automatically start showing the errors while you are typing in the console message.

That’s it!! Your code looks more disciplined of course after fixing the errors 🙂

In addition to pycodestyle, try pylint, pyflakes and pydocstyle plugins as shown above. These plugins improve Code Quality by following Python Coding Standards. Following best practices will make you a good scripter/developer and remember Quality Matters!!

References:

  1. https://pypi.org/project/pycodestyle/
  2. https://github.com/ergdev/SublimeLinter-pycodestyle
  3. http://pycodestyle.pycqa.org/en/latest/
  4. http://www.sublimelinter.com/en/stable/settings.html

So go ahead and use pycodestyle. Use the above references to deep dive and learn more about this plugin which is a ‘Must Have’ for the Sublime Text. Happy Pythonic way of coding!!

Python’s sh Library

Wish you all Happy, Prosperous & Pythonic New Year 2019!!

I thank everyone for stopping by my blog for 15K times in 2018 which is the highest so far! 

“Looking back at my life’s voyage, I can only say that it has been a good trip.” ~ Ginger Rogers

Similar to the above quote, I’d started my scripting voyage by writing shell, batch and Perl scripts in good old days. I miss some of the awesome, builtin & yet simple to use features available in shell. I felt using those features or builtins would make my life easier while writing scripts in Python…

Besides that, one main reason to look back at the past was while imparting the Python Workshop at my office. One of the participants raised concerns over Python’s builtin modules like OS and Subprocess. They mentioned we can accomplish with very simple steps in ‘Shell’ instead of using OS or Subprocess module. I had to accept the fact that sometimes Python is not so easy as we think!

After googling for a while I finally found this… Say hola to ‘sh‘ Module!

The ‘sh’ library provides simple and intuitive alternative to OS/System/Subprocess modules.

‘sh’ module simplifies Python’s ability to interact with native OS by calling shell commands. This would greatly helps in automation of routine tasks and running sequence of commands, parsing output as per requirement.

Python is a powerful language powered by those great developers who are continuously contributing to enable new features every day in day out. I would like to thank Andrew Moffat for the ‘sh’ library. This library is one of my favourites.

To install this library using PIP, run below command

pip3 install sh 

Please find sample scripts output. 

>>> import sh

>>> sh.uname()

Darwin

>>> sh.uptime()

 7:13  up 11 days, 23:29, 4 users, load averages: 1.51 1.65 1.88

>>> 

>>> sh.echo(‘Hello, This is shell\’s echo running from Python’)

Hello, This is shell's echo running from Python

>>> sh.who()

vinay    console  Dec 21 07:45 

vinay    ttys000  Dec 21 07:46 

vinay    ttys001  Dec 21 07:46 

vinay    ttys003  Jan  2 07:02 

>>>sh.df()

Filesystem    512-blocks     Used Available Capacity iused               ifree %iused  Mounted on

/dev/disk1s1   236568496 96124200 131754736    43% 1042911 9223372036853732896    0%   /

devfs                376      376         0   100%     653                   0  100%   /dev

/dev/disk1s4   236568496  6291496 131754736     5%       3 9223372036854775804    0%   /private/var/vm

map -hosts             0        0         0   100%       0                   0  100%   /net

map auto_home          0        0         0   100%       0                   0  100%   /home

>>> sh.ifconfig()

lo0: flags=8049<UP,LOOPBACK,RUNNING,MULTICAST> mtu 16384

options=1203<RXCSUM,TXCSUM,TXSTATUS,SW_TIMESTAMP>

inet 127.0.0.1 netmask 0xff000000 

inet6 ::1 prefixlen 128 

inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1 

nd6 options=201<PERFORMNUD,DAD>

gif0: flags=8010<POINTOPOINT,MULTICAST> mtu 1280

stf0: flags=0<> mtu 1280

XHC20: flags=0<> mtu 0

en0: flags=8863<UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST> mtu 1500

ether b8:e8:56:36:0b:58 

inet6 fe80::149c:1b88:578c:73f8%en0 prefixlen 64 secured scopeid 0x5 

inet 192.168.1.9 netmask 0xffffff00 broadcast 192.168.1.255

nd6 options=201<PERFORMNUD,DAD>

media: autoselect

status: active

p2p0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 2304

ether 0a:e8:56:36:0b:58 

media: autoselect

status: inactive

awdl0: flags=8943<UP,BROADCAST,RUNNING,PROMISC,SIMPLEX,MULTICAST> mtu 1484

ether 8a:0c:61:9e:64:9b 

inet6 fe80::880c:61ff:fe9e:649b%awdl0 prefixlen 64 scopeid 0x7 

nd6 options=201<PERFORMNUD,DAD>

media: autoselect

status: active

en1: flags=8963<UP,BROADCAST,SMART,RUNNING,PROMISC,SIMPLEX,MULTICAST> mtu 1500

options=60<TSO4,TSO6>

ether 72:00:00:20:b1:90 

media: autoselect <full-duplex>

status: inactive

en2: flags=8963<UP,BROADCAST,SMART,RUNNING,PROMISC,SIMPLEX,MULTICAST> mtu 1500

options=60<TSO4,TSO6>

ether 72:00:00:20:b1:91 

media: autoselect <full-duplex>

status: inactive

bridge0: flags=8863<UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST> mtu 1500

options=63<RXCSUM,TXCSUM,TSO4,TSO6>

ether 72:00:00:20:b1:90 

Configuration:

id 0:0:0:0:0:0 priority 0 hellotime 0 fwddelay 0

maxage 0 holdcnt 0 proto stp maxaddr 100 timeout 1200

root id 0:0:0:0:0:0 priority 0 ifcost 0 port 0

ipfilter disabled flags 0x2

member: en1 flags=3<LEARNING,DISCOVER>

        ifmaxaddr 0 port 8 priority 0 path cost 0

member: en2 flags=3<LEARNING,DISCOVER>

        ifmaxaddr 0 port 9 priority 0 path cost 0

nd6 options=201<PERFORMNUD,DAD>

media: <unknown type>

status: inactive

utun0: flags=8051<UP,POINTOPOINT,RUNNING,MULTICAST> mtu 2000

inet6 fe80::bd0:9dea:48de:5645%utun0 prefixlen 64 scopeid 0xb 

nd6 options=201<PERFORMNUD,DAD>

>>> 

Thanks for stopping by, please share your comments and ideas to improve this blog. Keep watching for more Python libraries used for automation.

Automate Cisco switches config using NETMIKO

Netmiko is a simplified module to manage Network devices via SSH. This module is developed by Kirk Byers. I would recommend all network engineers to learn and equip this module to automate day to day configuration tasks.

netmiko_show_arp

I’ve written a demo script to disable SNMP Server’s “globalenforcepriv” on both Cisco IOS and Nexus switches. Using this script we could make the necessary changes on 100’s of switches in few minutes saving time and energies of network engineers.

Al Sweigart wrote a book called ‘Automate boring stuff using python’. This is one of the scripts which could help network engineers to automate monotonous tasks using Python and Netmiko module.

Telnet (NetCat) and Ping Script

Nowadays most of the Linux distros do not include Telnet. If needed we can install it using the usual methods.

Let’s embrace Netcat which is a best alternative to Telnet. Earlier I had written a Perl script which did Telnet and Ping checks across multiple hosts.

This script requires two input files ips.csv and ports.csv. As the name suggest all target host IP’s to be placed in ips.csv file and the ports to which to netcat is placed in ports.csv. Output is in HTML format and user receives an email with colour coded results.

Replace ips.csv & ports.csv file contents and change SMTP IP and email address.

Here is the Python script which replaces Telnet with Netcat.