Python script to compare and merge excel files

Here I’m talking about a cool Python module using that you can compare two sheets in an excel sheet for a common value and merge the sheets.

First you need to install the module “pandas” for the solution. Python script is as below :

import pandas as pd

source1_df = pd.read_excel('a.xlsx', sheetname='Sheet1')
 source2_df = pd.read_excel('a.xlsx', sheetname='Sheet2')
 joined_df = pd.merge(source1_df,source2_df,on='Serial',how='outer')
 joined_df.to_excel('result.xlsx')

Note : replace the sheet1 and sheet2 with actual name of the pages and use the correct primary key name(Here “Serial”; case sensitive)

Eg :
sheet1 as below

Serial Hostname
123 abc
234 bcd
345 cde

 

sheet2 as below

Serial Model
234 Sparc
345 IBM
567 HP

 

After executing the python script, output comesΒ  as below :

Serial Hostname Model
0 123 abc
1 234 bcd Sparc
2 345 cde IBM
3 567 HP

 

ENjoy πŸ™‚

Advertisements

Install modules in virtualenv for different Python versions(using pip)

If you work with different python versions in virtual environment, you might have noticed that the module installed for a particular python version is not recognized in different python version.

For example, I have 2 python versions in my host.

(virtualenv) [root@machine1 ~]# python -V
Python 2.7.5
(virtualenv) [root@machine1 ~]# python3 -V
Python 3.5.0

I was trying to run the script hack.py and it given an error saying the module not found.

(virtualenv) [root@machine1 script_folder]# python3 hack.py
Traceback (most recent call last):
File "hack.py", line 3, in <module>
import paramiko, multiprocessing
ImportError: No module named 'paramiko'

 

I have installed the module by simply issuing the command pip install

(virtualenv) [root@machine1 script_folder]# pip install paramiko
Collecting paramiko
Using cached paramiko-2.2.1-py2.py3-none-any.whl
Collecting cryptography>=1.1 (from paramiko)
Using cached cryptography-2.0.3-cp27-cp27mu-manylinux1_x86_64.whl
Collecting bcrypt>=3.1.3 (from paramiko)
Using cached bcrypt-3.1.3-cp27-cp27mu-manylinux1_x86_64.whl
Collecting pyasn1>=0.1.7 (from paramiko)
Using cached pyasn1-0.3.2-py2.py3-none-any.whl
Collecting pynacl>=1.0.1 (from paramiko)
Using cached PyNaCl-1.1.2-cp27-cp27mu-manylinux1_x86_64.whl
Collecting idna>=2.1 (from cryptography>=1.1->paramiko)
Using cached idna-2.6-py2.py3-none-any.whl
Collecting six>=1.4.1 (from cryptography>=1.1->paramiko)
Using cached six-1.10.0-py2.py3-none-any.whl
Collecting ipaddress (from cryptography>=1.1->paramiko)
Using cached ipaddress-1.0.18-py2-none-any.whl
Collecting asn1crypto>=0.21.0 (from cryptography>=1.1->paramiko)
Using cached asn1crypto-0.22.0-py2.py3-none-any.whl
Collecting enum34 (from cryptography>=1.1->paramiko)
Using cached enum34-1.1.6-py2-none-any.whl
Collecting cffi>=1.7 (from cryptography>=1.1->paramiko)
Using cached cffi-1.10.0-cp27-cp27mu-manylinux1_x86_64.whl
Collecting pycparser (from cffi>=1.7->cryptography>=1.1->paramiko)
Installing collected packages: idna, six, ipaddress, asn1crypto, enum34, pycparser, cffi, cryptography, bcrypt, pyasn1, pynacl, paramiko
Successfully installed asn1crypto-0.22.0 bcrypt-3.1.3 cffi-1.10.0 cryptography-2.0.3 enum34-1.1.6 idna-2.6 ipaddress-1.0.18 paramiko-2.2.1 pyasn1-0.3.2 pycparser-2.18 pynacl-1.1.2 six-1.10.0

Notice that the module just installed is applicable only to the python version 2.7 and it never works on python3

(virtualenv) [root@machine1 script_folder]# python3 hack.py
Traceback (most recent call last):
File "hack.py", line 3, in <module>
import paramiko, multiprocessing
ImportError: No module named 'paramiko'

Hence, we need to re-install the module in python3 as well using the command below

/Python_location/python3 /pip_location/pip install Module_Name
(virtualenv) [root@machine1 script_folder]# /bin/python3 /bin/pip install paramiko
Collecting paramiko
Using cached paramiko-2.2.1-py2.py3-none-any.whl
Collecting pynacl>=1.0.1 (from paramiko)
Downloading PyNaCl-1.1.2-cp35-cp35m-manylinux1_x86_64.whl (536kB)
100% |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 542kB 76kB/s
Collecting cryptography>=1.1 (from paramiko)
Downloading cryptography-2.0.3-cp35-cp35m-manylinux1_x86_64.whl (2.2MB)
100% |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 2.2MB 56kB/s
Collecting bcrypt>=3.1.3 (from paramiko)
Downloading bcrypt-3.1.3-cp35-cp35m-manylinux1_x86_64.whl (54kB)
100% |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 61kB 140kB/s
Collecting pyasn1>=0.1.7 (from paramiko)
Using cached pyasn1-0.3.2-py2.py3-none-any.whl
Collecting cffi>=1.4.1 (from pynacl>=1.0.1->paramiko)
Downloading cffi-1.10.0-cp35-cp35m-manylinux1_x86_64.whl (406kB)
100% |β–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆβ–ˆ| 409kB 57kB/s
Collecting six (from pynacl>=1.0.1->paramiko)
Using cached six-1.10.0-py2.py3-none-any.whl
Collecting asn1crypto>=0.21.0 (from cryptography>=1.1->paramiko)
Using cached asn1crypto-0.22.0-py2.py3-none-any.whl
Collecting idna>=2.1 (from cryptography>=1.1->paramiko)
Using cached idna-2.6-py2.py3-none-any.whl
Collecting pycparser (from cffi>=1.4.1->pynacl>=1.0.1->paramiko)
Installing collected packages: pycparser, cffi, six, pynacl, asn1crypto, idna, cryptography, bcrypt, pyasn1, paramiko
Successfully installed asn1crypto-0.22.0 bcrypt-3.1.3 cffi-1.10.0 cryptography-2.0.3 idna-2.6 paramiko-2.2.1 pyasn1-0.3.2 pycparser-2.18 pynacl-1.1.2 six-1.10.0

Now, modules imported successfully

(virtualenv) [root@machine1 script_folder]# python3 hack.py
machine is under control now

Enjoy πŸ™‚