DataFrame Summary with Functions

 DataFrame Summary with Functions

import pandas as pd

pd.set_option('display.max_columns', None)

pd.set_option('display.max_rows', None)

df = pd.read_excel("D:\\1.xlsx", "SheetName", index_col='Index_Column_rowlabel', usecols='B:AG',skiprows=2)  

df.loc['No_of_Cats':'No_of_Dogs',:]

df = df.drop('Animals', axis=1)

df=df.reset_index()

df.loc[(df['Index_Column_rowlabel']=='No_of_Cats') | (df['Index_Column_rowlabel']=='No_of_Dogs')]

df=df.rename(columns={"Mobile Voice":"Date_T"})

df=df.transpose() 

df.columns = df.iloc[0]

df

####################

import pandas as pd

pd.set_option('display.max_columns', None)

pd.set_option('display.max_rows', None)

df = pd.read_excel("D:\\1.xlsx", "sheetname",index_col='Movekp' usecols='B:AGG',skiprows=2)  

#get the/quering the specific information from the sheet using loc function of the pandas

newdf=df.loc[(df['Movekp']=='Sub in Ind') | (df['Movekp']=='Sub in Pk')]

newdf

#droping the column

newdf=newdf.drop('Venture', axis=1)

#rename the columns

newdf=newdf.rename(columns={'Move`':'Date'})

#take transpose of the dataframe

newdf=newdf.transpose()

newdf=newdf.reset_index()

#assigning the value of first row to columns

newdf.columns = newdf.iloc[0]

# remove first row

newdf=newdf.tail(-1)

#save to disk

newdf.to_csv("D:\\1_data.csv")

# Information about df

newdf.info()

#importing datetime library.

from datetime import date,datetime

# converting Date column to datetime type

newdf["Date"]=pd.to_datetime(newdf["Date"])

# set the date column as index of dataframe df

newdf=newdf.set_index('Date')

# plot the graph of the dataframe df that is line

newdf.plot()

Using tkinter Class in python To plot a graph.

from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg

from matplotlib.figure import Figure 

import tkinter

# Create the default window 

win = tkinter.Tk() 

win.title("Welcome") 

win.geometry('800x600') 

  

# Create the list of options 

options_list=Flist.tolist()

# Variable to keep track of the option 

# selected in OptionMenu 

value_inside = tkinter.StringVar(win) 

  

# Set the default value of the variable 

value_inside.set("Select an Option") 

  

. 

my_menu = tkinter.OptionMenu(win, value_inside, *options_list) 

my_menu.pack() 

  

 

def print_answers(): 

    print("Selected Option: '{}' ".format(value_inside.get())) 

    

    data_view = data3.query("`ABC name`=='{}' ".format(value_inside.get())).groupby(['ABC name','Hour']).agg({'Tra': 'sum'})

    print(data3)

    fig1=data_view.plot(kind="bar",title='Graph').get_figure();

    global plot1 

    plot1=FigureCanvasTkAgg(fig1,root)

    

    plot1.draw() 

  

    # placing the canvas on the Tkinter window 

    plot1.get_tk_widget().pack() 

    return None

  

def print_clear(): 

    print("clear the figure")

    plot1.get_tk_widget().forget()

    return None

    

# Submit button 

submit_button = tkinter.Button(root, text='Submit', command=print_answers) 

submit_button.pack() 

clear_button = tkinter.Button(root, text='Clear', command=print_clear) 

clear_button.pack() 

Adding Colums to CSV and Write to Disk , Combine it, Then Query and Plot the Result in Python.

 import os

import pandas as pd

source_folder = r"D:\\original data"

dest_folder = r"D:\\Data_With_Time\\"

data2=pd.DataFrame()

for file in os.listdir(source_folder):

    if file.startswith("abc_file")  and file.endswith(".csv"):

        r=file.rfind("_")

        h=file[r:][9:13]

        dt=file[r:][1:9]

        fn=file

        data= pd.read_csv("D:\\original data\\"+file+" ")

        data['SHour']= h

        data['SDate']= dt

        data['filename']=fn

        data['DATA_TRA']=data['DATA_TRA']/3000

        data2=data2.append(data)

        data.to_csv("D:\\updated_"+file)

data2.to_csv("D:\\combine.csv")

data3=pd.read_csv("D:\\combine.csv")

data3=data3[['Short name','Date','Hour','Tra']]

data3 = data3.query("`name of Equipment`=='PIPE'").groupby(['name','Hour']).agg({'Tra': 'sum'})

print(data3)

data3.plot(kind="bar",title='Graph')

 #Comparing the columns of two CSVs files

import os

import pandas

mylist=[]

source_folder = "./data"

for file in os.listdir(source_folder):

    if file.startswith("abc_")  and file.endswith(".csv"):

        #print(file)

        df=pandas.read_csv(".//data//"+file+"")

        mylist.append(df.columns)

#print(mylist[1])

res = [c == d for c, d in zip(mylist[0], mylist[1])]

print(all(res))

Adding new column in dataframe after geting the info from filename in python

 import os

import pandas as pd

source_folder = r"D:\\DATA"

dest_folder = r"D:\\pdata"

for file in os.listdir(source_folder):

    if file.startswith("ABC")  and file.endswith(".csv"):

        r=file.rfind("_")

        h=file[r:][9:15]

        data= pd.read_csv("D:\\DATA\\"+file+" ")

        data['Time']= h

        data.to_csv("D:\\pdata\\updated_"+file)

Including the Images with Attachement using Python

 import win32com.client 

import datetime

import pandas as pd

import matplotlib.pyplot as plt

from datetime import date, timedelta,datetime 

def get_week_Last_day(year, week):

    year_start = date(year, 1, 1)

    # the following line assumes your this_week_int starts at 0

    # if this_week_int starts at 1, change to week-2

    week_start = year_start + timedelta(days=-year_start.isoweekday(), weeks=week-1)

    week_end = week_start + timedelta(days=6)

     

    week_start=week_start.strftime("%d/%m/%Y")

    week_end=week_end.strftime("%Y%m%d")

    lastweek="From "+week_start+ " to "+week_end

    return week_end

now = datetime.now()

curday=now

week_num=curday.strftime("%U")

year_num=curday.strftime("%Y")

week_num=int(week_num)

year_num=int(year_num)

LDOW=get_week_Last_day(year_num,week_num)

lday=LDOW

#######################################################

df=pd.read_csv("E:\\MyWork\\Python\\Data\\Tables_"+lday+".csv")

df=df[['DATE','TableName','Records']]

df= df.query("TableName=='Apple.AppleTable'").groupby(['DATE','TableName']).agg({'Records': 'sum'})

print(df)

df.plot(kind="bar",title='AppleTables')

plt.savefig("E:\\MyWork\\Python\\Data\image1.jpg",bbox_inches='tight')

import win32com.client 

import datetime

import pandas as pd

import matplotlib.pyplot as plt

from datetime import date, timedelta,datetime 

import time

from PIL import ImageGrab

import os

#import exchangelib as ex

from exchangelib import Message,FileAttachment,HTMLBody

from exchangelib import Credentials, Account

credentials = Credentials('Your@hotmail', 'password123')

account = Account('tahirkhalid@hotmail', credentials=credentials, autodiscover=True)

img_name=['image1','image2']

filetoattach = "E:\\TEST_"+lday+".csv"

with open(filetoattach, 'rb') as f:

    dmartcsv=FileAttachment(name=dmart_filename,content=f.read(),is_inline=False,content_id=dmart_filename)

 

 

logo1 = "E:\\MyWork\\Python\\Data\\image1.jpg"

logo2 = "E:\\MyWork\\Python\Data\image2.jpg"

with open(dmartcopperlogo, 'rb') as fa,open(dmartgponlogo, 'rb') as fb, \

     logoImage1 =FileAttachment(name=logo1, content=fa.read(), is_inline=True,content_id=logo1)

     logoImage2 =FileAttachment(name=logo2, content=fb.read(), is_inline=True,content_id=logo2)

     html1= '<html><body>ABC '+wk+'<br>'+globals()["df"+str(img_name[0])].to_html()+'<br>

    <img src="cid:%s"><br></body></html>' % (logo1,)

     html2 ='<html><body>$$$$<br>'+globals()["df"+str(img_name[1])].to_html()+'<br>

     <img src="cid:%s"><br></body></html>' % (logo2,)

     body =HTMLBody(html1+html2)

     m = Message(account=account,subject="ABC",body=body,to_recipients=['your@hotmail.com'])

     m.attach(logoImage1)

     m.attach(logoImage2)

     m.attach(csv)

     m.send()

Read CSVs files Get the No of cols,rows & column Name of Each CSV files using List , Pandas and OS Libraries of Python

 #!/usr/bin/env python

import pandas as pd

import os

####Read the files  the one by one from the folder and store the names of file in the list

source_folder=r"D:\\Rawdata\\Pythod_Code_RawData\\"

File_name_list=[]

for file in os.listdir(source_folder):

    if  file.endswith(".csv"):

        File_name_list.append(file)

        

print("Total File in the List",len(File_name_list))        

    

####Read the files names from the list and make df of them and get no of the columns and rows of the df and column name of df

## and store them in the list name mylist.

mylist=[]

try:

    for i in range(0,len(File_name_list)):

           dest_file=source_folder+File_name_list[i]

           df1=pd.read_csv(dest_file,on_bad_lines='skip')

           dfcols=len(df1.axes[1])

           dfrows=len(df1.axes[0])

           mylist.append(File_name_list[i]) 

           mylist.append(dfcols)

           mylist.append(dfrows)

           mylist.append(list(df1.columns))

                   

except pd.errors.EmptyDataError:

       print('Empty csv file!')

#print the mylist list and convert the mylist in dataframe by passing  the name of the list in DataFrame object and write df to csv

for i in range(0,len(mylist)):

    print(mylist[i])

new_df = pd.DataFrame(mylist)

new_df.to_csv(".//Result//2.csv", index=False, encoding='utf-8-sig')

Querying the Database using Python having List of Users and List of SQL Quries

import pandas as pd

import os

import cx_Oracle

df=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\SQL_List.csv')

df2=pd.DataFrame()

dfusers=pd.read_csv('D:\Oracle_Account\DATA\Python_Work\SQL_STMT_Read\Rquired_Users.csv',dtype=str)

Userlist = dfusers.OurUsers.values.tolist()

Userlist

res=str(Userlist)[1:-1]

print(res)

type(res)

dsn_tns = cx_Oracle.makedsn('localhost', '1531', 'XE')

conn=cx_Oracle.connect(user='abc', password='def', dsn=dsn_tns)

for i in range(0,3):

    query=df.loc[i,'sql_quries']

    query=query.format(res)

    print(query)

    df2= pd.read_sql(query, conn)

    df2.to_csv("D:\\Oracle_Account\\DATA\Python_Work\\SQL_STMT_Read\\"+str(i)+".csv", index = True, header=True) 

conn.close()