CODEWITHSUNDEEP
python
Joan Leung
Joan Leung

Reputation: 3

Batch Renaming of Files in sub-directories based on a .xlsx

There is a directory in C:\Users\abcd\video

Inside the directory, there are a lot of .mp4 files.

How do I rename all .mp4 files based on an Excel sheet that contains following information using Python:

enter image description here

For example, the current filename is A.mp4. I would like to rename it to 1.mp4.

Upvotes: 0

Views: 111

Answers (2)

namhsuya
namhsuya

Reputation: 117

Note: If you want to use an Excel file, with the extension .xlsx After installing xlrd, i.e. a dependency for reading .xlsx files using the follwing command from the command line:

pip install xlrd

Code:

import os
import pandas as pd

# Read your renaming data
dst = pd.read_excel('C:\\Users\\abcd\\video\\map.xlsx', header=None)
# Create a dictionary for easier access
dictionary = dict(zip(list(dst[dst.columns[0]]), list(dst[dst.columns[1]])))

# Renaming if filename ends with .mp4
for filename in os.listdir("C:\\Users\\abcd\\video"):
    if (filename.endswith(".mp4")):
        dest = str(dictionary[filename[:-4]]) + ".mp4"
        src = "C:\\Users\\abcd\\video\\" + filename 
        dst = "C:\\Users\\abcd\\video\\" + dest
        os.rename(src, dest)

Edit2:

Can use python3.4+ Path/pathlib to iterate recursively throught any folder

import os
import pandas as pd
from pathlib import Path

root = "C:\\Users\\abcd\\video\\"

# Read your renaming data
dst = pd.read_excel('C:\\Users\\abcd\\video\\map.xlsx', header=None)
# Create a dictionary for easier access
dictionary = dict(zip(list(dst[dst.columns[0]]), list(dst[dst.columns[1]])))

# Recursively reading all .mp4 files
files = list(Path(root).glob('**/*.mp4'))


for filename in files:
src = str(filename)
if(src[:src.rindex('\\')]==root):
    dest = src[:src.rindex('\\')] + str(dictionary[str(filename)[str(filename).rindex('\\')+1:-4]]) + ".mp4"
else:
    dest = src[:src.rindex('\\')] + "\\" + str(dictionary[str(filename)[str(filename).rindex('\\')+1:-4]]) + ".mp4"
os.rename(src, dest)

Upvotes: 2

Eduard Ilyasov
Eduard Ilyasov

Reputation: 3308

To solve your problem you can use the following approach (string formatting for python 3.6+):

import glob
import os
import pandas as pd

def rename_files(parent_dir, files_extension, filenames_map):
    """
    Rename files with specified extension located in subfolders of parent directory
    :param parent dir: Path to subfolder's parent directory
    :param files_extension: Renaming file's extension
    :param filenames_map: Mapping from initial filenames to target filenames
    :return: None
    """
    files_template = os.path.join(parent_dir, f'**/*{files_extension}')
    files = glob.glob(pathname=files_template, recursive=True)
    for file_path in files:
        base_dir, filename_with_ext = os.path.split(file_path)
        filename, extension = os.path.splitext(filename_with_ext)
        try:
            new_filename = filenames_map[filename]
        except KeyError:
            raise Exception(f"There's no {filename} key in filenames mapping")
        new_file_path = os.path.join(base_dir, f'{new_filename}{extension}')
        os.rename(file_path, new_file_path)


filenames_map_file_path = r'C:\Users\abcd\video\filenames_map.xlsx'
parent_files_dir_path = r'C:\Users\abcd\video'
extension = '.mp4'

filenames_map = pd.read_excel(io=filenames_map_file_path, header=None) \
                  .set_index(0)[1] \
                  .to_dict()

rename_files(parent_dir=parent_files_dir_path,
             files_extension=extension,
             filenames_map=filenames_map)

Upvotes: 0

Related Questions