pandas, csv, xlrd, which one is best for csv and xls files?

 

1, .csv vs .xls:

csv hold plain text as a series of values (cells) separated by commas (,) in a series of lines (rows). However, when I tested in Mac, it seems that values are separated by (;), not (,) as default.  While xls is an MS Excel workbook binary file.

2, python modules: csv & pandas & xlrd:

Both csv and pandas can be used to read .csv file.csv can be used to do simple work with the data that stores in .csv file, while pandas enable you to perform analysis or intense data manipulations.

Both pandas and xlrd can be used to read .xls files. Actually, pandas has used module xlrd when reading or writing .xls files in lower level. So it is xlrd that gave the possibility for reading .xls. However pandas provide calculation and analysis capabities on top of that. The evidence is that if you install pandas but without installing xlrd module, it will shows error when running pandas.read_excel function.

In summary, pandas is a good tool to use for reading both .csv or xls file

3, example codes using pandas to read a .xls file:

Below is a simple code example which can be used to read a .xls files and read the index value or column value accordingly

import sys
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile

filename = sys.argv[1]
sheet_name = sys.argv[2]
df = pd.read_excel(filename, sheet_name = sheet_name)

print(“Column headings:”)
print(df.columns) # print all index value, the first row in excel
print(df[‘$IPv4 Network’]) #print column value whose index is “$IPv4 Network”

4, code example to use csv module to read a csv file and save the data into dictionary:

import csv
import sys
#coding=utf-8

filename = sys.argv[1]

with open(filename,’rU’) as csvfile:
reader = csv.DictReader(csvfile,delimiter= ‘;’)
for line in reader:
print(line)

with .csv file example:

A B C
qer ewr tr
ewe dfe cbv

The output of the above .csv are:

$ python3 Documents/python/readcsvfile.py Documents/test.csv

OrderedDict([(‘\ufeffA’, ‘qer’), (‘B’, ‘ewr’), (‘C’, ‘tr’)])

OrderedDict([(‘\ufeffA’, ‘ewe’), (‘B’, ‘dfe’), (‘C’, ‘cbv’)])

$ python Documents/python/readcsvfile.py Documents/test.csv

{‘\xef\xbb\xbfA’: ‘qer’, ‘B’: ‘ewr’, ‘C’: ‘tr’}

{‘\xef\xbb\xbfA’: ‘ewe’, ‘B’: ‘dfe’, ‘C’: ‘cbv’}

Especially \xef\xbb\xbf  is byte order mark (BOM) of utf-8 unicode, because my test.csv is using utf-8. Normally .csv is encoded with utf-8 or ASCII. And in my example, delimiter = ‘;’ (default is ‘ , ‘) in order to get the correct result.

The result shows that in dictionary there are 3 keywords: A, B and C, each row shows values for the key word, that is ‘qer’, ‘ewe’ for A, ‘ewe’ and ‘die’ for B, etc.

5, Unicode

It is quite often to meet encoding/decoding problem when you are reading a file using unknown unicode. The best way is to find out what unicode used in the original file, then covert it according to the needs. defaultcodevalue in python 2 is ASCII, when reading a utf-8 file, it may generate some error, but not always, we need keep in mind of this.

Some people use the following script to solve above problem, but this is not recommended. More discussion can be found here:

import sys
reload(sys)
sys.setdefaultencoding("utf-8")
Advertisements

Catalyst 2960S bootloop/keep reloading during upgrading

2960 switch went to a booting loop due to bug CSCvf46629 when doing an upgrade from 15.2(2)E7 from 15.0(2)SE7.  That is when VTP mode set as client on the switch, switch will go into reloadloop when trying to upgrade switch to 12.2.2E7.

To recover the switch we need to move the switch to old IOS then boot switch up, then change the vtp mode to transparent.

If you have old IOS image in the flash, then just need to boot switch during pressing “mode” button until switch prompt shows up, then doing flash_init and boot from the old image.

In my case the old IOS image was removed during new image installation, so I have to download the old IOS image to the switch via console port.

Below is the steps to follow:

In rommon

-flash_init

-Set BAUD 115200

-copy xmodem: flash:OLD_IOS_image

Transfer the IOS file from computer to the switch with Serial tool in Mac or Hyperterminal in window. Once old IOS is copied:

-Boot flash:OLD_IOS_image

Once switch boots up in old IOS customer needs to boot up the switch and change the vtp mode, Then do the upgrade from 15.2(2)E7 from 15.0(2)SE7Then change the vtp mode back.

To set Baud as 115200 is because using the default Baud rate 9600 for file transfering will take 3-4 hours to download the ios image to the switch. Once switched to rate 11520 I can download image within half hour. Some other people also tried a different rate, like 57600, which can work too with a little bit longer download time.

One another alternative workaround might be to rename config.text to config.backup in rommon, then tried to boot up the switch with new image. By removing config.text the switch will not have vtp mode client configured, it should be able to bypass VTP client mode bug and boot the switch without need to go back to old image.  After switch is booted with new image, we can just to the following to recover the configuration:

-rename config.backup config.text

-copy config.text running-config

The process is the same as the process of password recovery. This alternative work-around haven’t been verified but is worth testing.

upload file to ftp server via mac os x

mac os has inbuild ftp client function and tftp server function. Click here to check how to activate and connect tftp server in mac.

Below is the step to upload file to ftp server via mac os x

1, open terminal

2, #ftp serverurl

input username and password

3, use dir to browse folder and file list in ftp server, use cd to enter into the folder where you want to put you file in

4, change local directory path

!list local directory

#lcd

!change local directory to /private/

#lcd /private/

5, upload the file

#put filenamn.name

 

two vtp server in a same domain

When vtp is configured, the configured vlans are stored in vlan.dat file, not in running-configure or startup-configure file.

It is common that two vtp server in a same domain is running, this is for vlans database redudant. When there is a new change generated in one VTP server, the update will be sent out inside the vtp domain, the other vtp server in the same domain will update its vlan.dat also.

Be careful to setup the same vtp version on both servers, otherwise the server with higher version will become master.

When replacing a switch that is acting as VTP server in the network, the best way is to change the new switch into client mode first, in order to get vlans updated from other vtp servers, then change the new switch back to server mode.

Configure OS x as tftp server and download from vrf enabled 6800 chassis

Below is the steps that I used to download files from vrf enabled 6800 chassis to my mac laptop:

1, Enable tftp server in OS x:

sudo launchctl load -F /System/Library/LaunchDaemons/tftp.plist
sudo launchctl start com.apple.tftpd

By default use private/tftpboot/ filefolder for tftp download and upload:

sudo chmod 777 /private/tftpboot
sudo chmod 777 /private/tftpboot/*

2, setup tftp route in 6800

I want to download/upload from/to mgmt port, mgmt port belongs to a separated management vrf. In order to make tftp server IP routed correctly, we need add the following configuration into the chassis:

cat6k#ip tftp source-interface mgmt0

3, Now it is ready for tftp download/upload to OS x from/to vrf enabled Chassis.

cat6k#copy running-config tftp://172.27.x.x

Address or name of remote host [172.27.x.x]?

Destination filename [xxx-confg]? running-config

!!

85918 bytes copied in 1.084 secs (79260 bytes/sec)

Install ansible in mac os

Ansible is mainly used for automize Linux/windows servers provisioning and operation, however from version 2.1 there is support module for network related devices.

In order to test it I have first install ansible in my mac:

There are several ways to install ansible, but the mostly common used on mac is homebrew an pip. Here is the comparision of both installation ways:

"pip is a packager for the python world – you should only ever be able to install python-things with it; homebrew is a package manager targetted at OSX; it doesn’t impose any restrictions onto what software you can install with it – since python is a subset of software.

installing things with brew will install them into /usr/local/;

installing things with pip will fetch packages from the Python Package Index, and it will install them in a place where your python interpreter will find them: either into your home directory (e.g. ~/.local/lib/python2.7/site-packages/) or in some global search-path of your python interpreter (e.g. /usr/local/lib/python2.7/dist-packages/)”

We will just explore the way to install ansible with homebrew:

1, install Xcode (C compiler) in order to use python
xcode-select –install

2, Install python using homebrew

brew install python

or

brew install python3

Actually, step 1 and 2 can be skipped because all new Mac OS X has python 2.7 installed already.

3, brew install ansible

After the installation we can find ansible is installed under /usr/local/bin/

mac-c02t6npagtfj:bin grayin$ ls ansible*

ansible ansible-doc ansible-pull

ansible-config ansible-galaxy ansible-vault

ansible-connection ansible-inventory

ansible-console ansible-playbook

notes: do “brew update” first before the installation to avoid any unexpected errors

How to influence EIGRP metrics to affect route selection

EIGRP updates contain five metrics: minimum bandwidth, delay, load, reliability, and maximum transmission unit (MTU). Of these five metrics, by default, only minimum bandwidth and delay are used to compute best path. Unlike most metrics, minimum bandwidth is set to the minimum bandwidth of the entire path, and it does not reflect how many hops or low bandwidth links are in the path. Delay is a cumulative value which increases by the delay value of each segment in the path.

Therefore we can change delay on interface to affect route selection, but this method can only be used when need to influence route selection learned via EIGRP neighbor on that interface.

Another more sophisticated way is to use offset-list, the metric of the route on the router can be modified using an offset-list on the neighbor router. Offset-list will insert the value to affect RD and FD advertised towards the peer router.