0

My code -

for file in os.listdir("C:/Users/hhh/Desktop/autotranscribe/python/Matching"):
    if file.startswith("Master"):
        dfs = pd.read_excel(file, sheet_name=None)
        output = dict()
        for ws, df in dfs.items():
            if ws in ["Added"]:
               continue
            if ws in ["All Members", "Terms"]:
               temp = df

>temp
Company State Address Zip    City
ABC      CA   1 st    86284  LA

I want another column that denotes the month the file was created/dropped in the directory minus 1(File Date minus 1 month). So basically, if the file was created on 5/5/22. I want the below

Company State Address Zip    City  File Month
ABC      CA   1 st    86284  LA    04-2022

My attempt-

import datetime
path = "C:/Users/hhh/Desktop/autotranscribe/python/Matching"
# file creation timestamp in float
c_time = os.path.getctime(path)
# convert creation timestamp into DateTime object
dt_c = datetime.datetime.fromtimestamp(c_time)
temp['File Month'] = (dt_c.replace(day=1) - datetime.timedelta(days=1)).strftime("%m-%Y")

But this only works if i set a path and not in my case because my path as you can see above is based on filename that startwith 'Master'. I only want it to be based on the filename and not on os path because os path has multiple files created in it on a daily basis. How do I do this?

Joe Tha
  • 163
  • 9
  • You'll need a full path to determine ctime. Just append (e.g. os.path.join) the string `file` to whatever your source directory is (`C:/Users/hhh/Desk..."`). Besides, I'd suggest to have a look at [pathlib](https://docs.python.org/3/library/pathlib.html) to simplify some things. – FObersteiner May 05 '22 at 18:06
  • Thanks for your response @FObersteiner. so you mean there is no solution to my question except for fully defining path? – Joe Tha May 05 '22 at 18:13

1 Answers1

1

Move your code for changing the time into your for loop. Also, you don't need datetime. pandas has all the operations you need.

path = "C:/Users/hhh/Desktop/autotranscribe/python/Matching"
for file in os.listdir(path):
    if file.startswith("Master"):
        dfs = pd.read_excel(file, sheet_name=None)
        output = dict()
        for ws, df in dfs.items():
            if ws in ["Added"]:
               continue
            if ws in ["All Members", "Terms"]:
               temp = df
               dt = pd.to_datetime(os.path.getctime(os.path.join(path,file)),unit="s").replace(nanosecond=0)
               temp['File Month'] = (dt.replace(day=1)-pd.DateOffset(days=1)).strftime("%m-%Y")
not_speshal
  • 20,086
  • 2
  • 13
  • 28
  • Hi notpeshal. thank you for your answer. do you mind posting output df please – Joe Tha May 05 '22 at 19:07
  • I can't. I have no access to your path or files. Why don't you test the answer? – not_speshal May 05 '22 at 19:07
  • 1
    Happy to help! And also, I'm a girl :p – not_speshal May 05 '22 at 19:16
  • Oh im such a foo. Sorry about that Mam. ill keep it in mind :) . Have to say pandas is the holygrail of libraries. panacea for all problems – Joe Tha May 05 '22 at 19:16
  • hey not_speshal ive asked a question on shutil for this code here https://stackoverflow.com/questions/72149034/how-do-i-shutil-move-my-file-post-processing-in-python-based-on-filename no one has given a canonical answer yet. please do chime in if you would like to – Joe Tha May 07 '22 at 05:31