1. Get Started

Connect to MSSQL

import pandas as pd
import pyodbc

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=<<Instance Name>>;DATABASE=<<Database Name>>;UID=<<UserName>>;PWD=<<Password>>;')

df= pd.io.sql.read_sql('select 1 as col1, 2 as col2', conn)

Result

To select only require column in pandas data frame, take note that the variable should be in list

df[['col1','col2']]

Result

To filter only require row in pandas data frame, "col1" is the column name defined and "0" is the index of the rows

df.col1[0]

Result

To change column data type

df[['col1']] = df[['col1']].astype(str)

Result

To add a new column

df['newcolumn']='new' + df['col1']

To remove a column

del df[df.columns[2]]   aka del df['newcolumn']

Result

Result

To add a new row

df = df.append([{'col1':3,'col2':4,'newcolumn':'new add manually'}])

Result

To modify a column value, example replace first character found in string with new value

df['newcolumn'].map(lambda x: x.replace(x[0:1], '<<Replaced>>'))

Result

To join 2 data frame into a single

left= pd.io.sql.read_sql('select 1 as col1, \'left1\' as col2 union all select 2, \'left2\'', conn)
right= pd.io.sql.read_sql('select 2 as col1, \'right2\' as col2 union all select 3, \'right2\'', conn)

combined=pd.merge(left, right, on=('col1'),how='left')

Data for left data frame

Data for right data frame

Combined both data frame, take note that one of the joining column is removed.

To remove na from dataframe, regardless column

combined.dropna()

Result

To pivot

dataset.pivot(index=<<Key Column>>, columns=<<Cell value to become column name>>, values=<<Cell value to be placed>>)

Result

To combine multiple dataframe

both=[left,right]

vertical=pd.concat(both, axis=0)

horizontal=pd.concat(both, axis=1) <-- only work on same amount of rows

Result (both=[left,right])

Result (vertical=pd.concat(both, axis=0)

horizontal=pd.concat(both, axis=1)

To remove duplicate

duplicate=pd.concat([left,left,right])
duplicate.drop_duplicates(subset=('<<column to be prioritize>>'))

Result