Reading Files In Python

#importing the required libraries

 import pandas as pd

import shutil

from shutil import copyfile 

from datetime import date,datetime, timedelta 

import os

source_folder = r"D:\\SourceFiles\\"

dest_folder=r"D:\\DestinationFiles\\"

###Loop throught the source_folder getting the datepart from it filename and subtract 1 from it

###and then recombine the again with yesterday.

for file in os.listdir(source_folder):

    r=file.rfind("_")

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

    fname=file[:r][:]

    remainingpart=file[r:][9:]

    date_object = datetime.strptime(date_string, "%Y%m%d")

    date_object=date_object-timedelta(days=1)

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

    #print(fname+"_"+date_object+remainingpart)

    old_file=source_folder+file

    new_file=dest_folder+fname+"_"+date_object+remainingpart

    #print("oldfile_name",old_file, "Newfile name", new_file)

    os.rename(old_file, new_file)

#############Reading the files from the dest_folder one by one and rewrite to Final folder

for file in os.listdir(dest_folder):

    print(file)

    df = pd.read_csv("D:\\DestinationFiles\\"+file, dtype=str)

    print(df)

    df.to_csv("D:\\Final\\"+file, index=False)

#####Adding Columns of SysDATE and File_Name in existing DataFrame Using Lists

####Inserting BlankColumn in Beginning of DataFrame 

import pandas

from datetime import date,datetime, timedelta

df=pandas.read_csv("1.csv")

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

    date_string=df['Date'].iloc[i]

    do = datetime.strptime(date_string, '%d-%b-%y')

    SysDATE=do.strftime('%Y%m%d')

    datelist.append(SysDATE)

    filelist.append("filename_"+SysDATE)

    

df['SDATE']=datelist

df['File']=filelist

df.insert(0,'Unnamed 0',' ')

######Batch Programming Example### DATE Handling#########

@echo ON

rem set year=%date:~-4,4%

rem set month=%date:~-7,2%

rem set day=1%date:~-10,2%-100

rem set /A lday=%day%-1

set year=2024

set month=11

set day=1

set /A lday=%day%-1

IF %lday% LSS 10 (SET lday=0%lday%) else (SET lday=%lday%)

echo %year%%month%%lday%

IF %lday% LSS 1 IF %month% EQU 1 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 2 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 3 (

SET lday=28 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 4 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 5 (

SET lday=30 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 6 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 7 (

SET lday=30 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 8 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 9 (

SET lday=31  

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 10 (

SET lday=30   

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 11 (

SET lday=31 

SET /A month=%month%-1

)

IF %lday% LSS 1 IF %month% EQU 12 (

SET lday=30   

SET /A month=%month%-1

)

echo %year%%month%%lday%

IF %lday% LSS 1 IF %month% EQU 1 (

SET month=12 

SET /A year=%year%-1

)

echo %year%%month%%lday%

Comparing Two files and their Headers using Pandas and Lists

import pandas

import os

import re

old_col_list=[]

Source_Folder_OldFiles = "./data//Old_Columns_Files"

new_col_list=[]

Source_Folder_NewFiles = "./data//New_Columns_Files"

############################################### Loop through old files###############       

for file in os.listdir(Source_Folder_OldFiles):

    if(re.search("000000",file) and (

       file.startswith("abc_Re_")

       or file.startswith("def_Re_") 

       or file.startswith("ghi_Re_")

       or file.startswith("jkl_Re_")

       or file.startswith("mno_Re_")

       or file.startswith("pqr_Re_")

       or file.startswith("stu_Re_")

       or file.startswith("vwx_Re_")

       or file.startswith("yz_Re_") ) 

    ):

        old_file_df=pandas.read_csv(".//data//Old_Columns_Files//"+file+"")

        print(file)

        old_col_list.append(old_file_df.columns)

print(old_col_list[0])

print(len(old_col_list))

############################################### Loop through New or current day files###############       

for file in os.listdir(Source_Folder_NewFiles):

    if(re.search("000000",file) and (

       file.startswith("abc_Re_")

       or file.startswith("def_Re_") 

       or file.startswith("ghi_Re_")

       or file.startswith("jkl_Re_")

       or file.startswith("mno_Re_")

       or file.startswith("pqr_Re_")

       or file.startswith("stu_Re_")

       or file.startswith("vwx_Re_")

       or file.startswith("yz_Re_") ) 

    ):

        new_file_df=pandas.read_csv(".//data//New_Columns_Files//"+file+"")

        print(file)

        new_col_list.append(new_file_df.columns)

        

##########################  Loop the logic for all files###########################

for x in range(0, 2):

    print(new_col_list[x])

    print(len(new_col_list))

    ##comparing the elements of lists, that the cols of old files with the cols of new current file

    result = [a == b for a, b in zip(old_col_list[x], new_col_list[x])]

    #print(all(result),result[0],result[1:])        

    #False False [True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True]

    #find the matchig elements

    matches = [i for i in old_col_list[x] if i in new_col_list[x]]

    print(matches)

    #find the non matching elements and measure the len of list contain non match element

    no_matches = [j for j  in new_col_list[x] if j not in  old_col_list[x]]

    print(no_matches,len(no_matches))

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()

Selection under condition in data frame ,along with Group by clause in Pandas dataframe

import pandas

df=pandas.read_csv(".//abc.csv")

df['date_t']=df.ds.astype(dtype='datetime64[ms]')

df['month'] = df['date_t'].dt.month

df['year'] = df['date_t'].dt.year

df.info()

df_tmp = df.groupby(['date_t','month','year'])['yhat'].sum().reset_index().sort_values( 'yhat',ascending = False)

r=df_tmp[((df_tmp['month'] == 1)|(df_tmp['month'] == 2)) & (df_tmp['year'] == 2002)].groupby(['month','year'])['yhat'].apply(lambda grp: grp.nlargest(3).mean())

r.to_csv('./Average_of_t3.csv')

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()