Pandas - Data Frame - examples

formatting_options_in_pandas.py

# Formatting
workbook = writer.book
total_fmt = workbook.add_format({'align': 'right', 'bold':  False, 'bottom': 0})
total_fmt1 = workbook.add_format({'align': 'left', 'bold':  False, 'bottom': 0})
key_worksheet = writer.sheets[key]
key_worksheet.set_zoom(80)
key_worksheet.set_column('A:A', 14, total_fmt)
key_worksheet.set_column('B:B', 40, total_fmt)
key_worksheet.set_column('C:C', 45, total_fmt)
key_worksheet.set_column('D:D', 35, total_fmt)
key_worksheet.set_column('E:E', 70, total_fmt1)
key_worksheet.set_column('F:F', 110, total_fmt1)
key_worksheet.set_column('G:G', 22, total_fmt)
key_worksheet.set_column('H:H', 15, total_fmt)
key_worksheet.set_column('I:I', 30, total_fmt)
key_worksheet.set_column('J:J', 25, total_fmt)
key_worksheet.set_column('K:K', 25, total_fmt)
# sheet 2 formating
role_details_worksheet = writer.sheets['Role Details']
# Zoom
role_details_worksheet.set_zoom(80)
# Apply formatting to the column
role_details_worksheet.set_column('A:A', 30, total_fmt)
role_details_worksheet.set_column('B:B', 40, total_fmt)
role_details_worksheet.set_column('C:C', 15, total_fmt)
role_details_worksheet.set_column('D:D', 35, total_fmt)
role_details_worksheet.set_column('E:E', 47, total_fmt)
role_details_worksheet.set_column('F:F', 27, total_fmt)
role_details_worksheet.set_column('G:G', 15, total_fmt)
writer.save()
writer.close()

Mssql_Pandas.py

import pymssql
import pandas as pd

# db configurations DEFAULT
sql_db_default = {
    "host_name": "192.168.1.100",
    "port": "1604",
    "user_name":  "Base_User",
    "password":  "HappyForYoU",
    "database_name":  "BaseDB",
    "timeout": 300,
    "login_timeout": 300
}

# Put any query you want with format options or not
sql_query  = 'use [BaseDB]; select * from tbl_name where name in (%s) ... Somme query (%s)'


class MsSqlDataPuller(object):
    def __init__(self, **db_credentials):
        if not db_credentials:
            db_credentials = sql_db_default
        self.db_cred = db_credentials
        self.connection = pymssql.connect(
                host=self.db_cred["host_name"],
                port=self.db_cred["port"],
                user=self.db_cred["user_name"],
                password=self.db_cred["password"],
                database=self.db_cred["database_name"],
                login_timeout=self.db_cred["login_timeout"],
                timeout=self.db_cred["timeout"]
        )
    
    def process(self, email_lists):
        # Execute the query and returns as panda data frame
        df = pd.read_sql_query(sql_query.format(email_lists, email_lists), self.connection)
        return df

pandas_excels_clubbing_rows_based_on_values_in_one_column.py

# pandas_excels_clubbing_rows_based_on_values_in_one_column

df = pandas.read_excel('sample.xlsx', sheetname='Sheet1')
# to get the unique values from particular column
practices = df['repeating_values_column'].unique()
new_values = []

# getting rows of particular values
for value in practices:
    found = df[df['repeating_values_column'] == value]
    # found is the row, we want 

Read Excel.py

# Read the excel file and converting all details into a data frames
xlsx = pd.ExcelFile('all_details.xlsx')
data = pd.read_excel(xlsx, sheetname='A_B', index_col=None, na_values=['NA'])
xlsx.close()

Read Write csv.py

import pandas as pd

file_name = 'NationalNames.csv'

# Read the excel file and converting all details into a data frames
df = pd.read_csv(file_name)

for i in df:
    print(i)
print(df.columns)

df[['Name','Gender', 'Count']].to_csv('National_names.txt', sep=',', header=0, index=False)

some_other_pandas_useful_snippets.py

for key in keys:
    # writer object
    writer = pd.ExcelWriter('{}.xlsx'.format(key), engine='xlsxwriter')
    found = data[data['Group'] == key]
    # Sort values in data frame
    found.sort_values(by=['Sender Email Address', 'Sender Name'], ascending=[False, True], inplace=True)
    # TO order/arrange the column in specific order to be written in excel file
    found = found[['Label', 'Sender Name', 'Sender Email Address', 'Received By Name',
                       'Subject','Message', 'Creation Time', 'Group',
                       'Contact', 'Email', 'Company Name']]
    
    found.to_excel(writer, sheet_name=key, index=False)
    
    # Rename Index values
    new_found.reindex(df.index.rename(['Index_1', 'Index_2']))
    # Rename Column values
    new_found.columns.values[0] = 'Column_1'
    new_found.columns.values[1] = 'Column_2'
    new_found.columns.values[2] = 'Column_3'
    new_found.columns.values[3] = 'Column_4'
    new_found.columns.values[4] = 'Column_5'
    new_found.columns.values[5] = 'Column_6'
    new_found.columns.values[6] = 'Column_7'
    new_found.columns.values[7] = 'Column_8'
    # delete column 
    new_found.drop(new_found.columns[[7]], axis=1, inplace=True)
    # sort values before writing in excel
    new_found.sort_values(by=['Column_4'], ascending=[True], inplace=True)
    new_found.to_excel(writer, sheet_name='Role Details', index=False)
    
   

Write Excel.py

import pandas as pd

all_details = [{'A': 1, 'B': 2}, {'A': 2, 'B': 3}]

# Create the data frame
df = pd.DataFrame(all_details)
# Create excel file using pandas ExcelWriter
writer = pd.ExcelWriter('all_details.xlsx', engine='xlsxwriter')
# write the data frame into a excel file
df.to_excel(writer, sheet_name='A_B', index=False)
writer.save()
writer.close()