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 πŸ™‚

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 πŸ™‚

Keyboard copy paste shortcuts in putty commandline

Everyone know how to copy and paste characters in putty terminal using mouse (in case you don’t know, selecting the characters using mouse automatically copies and right click paste it) but less people know that it is possible with keyboard as well. You can use the below command to paste the characters into putty terminal

SHIFT + INS 

Enjoy πŸ™‚

Compare two files and list common lines

You can use the awk command to compare two files and show the common ones. Compared to the Linux command “comm”, advantages of using “awk” is it will give output though the files are not sorted.
Syntax :

awk 'NR==FNR{a[$1]++;next} a[$1] ' file1 file2

Eg :

[root@test]# cat A
apple
orange
mango
grape

[root@test]# cat B
mango
banana
grape

[root@test]# awk 'NR==FNR{a[$1]++;next} a[$1] ' A B
mango
grape

Connect to serial console from Linux command line

The easiest way to connect to a serial console from Linux command line is using the screen command. In my example below, I will show you how to connect to a serial console of a server using the serial to usb cable.

[root@test ~]# screen /dev/ttyUSB0
******************************************
 Connected to *******
******************************************

Enjoy πŸ™‚

Delete files starting with hyphen (-)

Today I have gone through something interesting, Just to remove a file in Linux; but the file starts with a hyphen!

[root@test]# rm '-g.txt'
rm: invalid option -- 'g'
Try rm ./-g.txt' to remove the file `-g.txt'.
Try `rm --help' for more information.

Yes error message said it, just use the format

rm -rf ./filename_with_hiphen

Enjoy πŸ™‚

Device eth0 does not seem to be present, delaying initialization.

You might have come across this headline, especially if you working with Oracle Virtual box machines, machines those are cloned from previous setups etc.

device

To resolve the issue, just remove the file /etc/udev/rules.d/70-persistent-net.rules and reboot the machine. This file contains the MAC address details associated with the machine. If you don’t want to reboot the machine, run the below command :

udevadm trigger --subsystem-match=net

This command will trigger the rules again.

Enjoy πŸ™‚