python - 使用列值python对excel文件进​​行排序

Question

我有 n 个 excel 文件，我需要根据列的值对它们进行排序。实际上，我需要在创建子文件夹时组织放置在特定文件夹下的 excel 文件，并且每个子文件夹都包含相同的 excel 文件DEPTNAME，知道这DEPTNAME是一个列名，每个 excel 文件都有 m 个工作表，但所有工作表都有相同的DEPTNAME.

示例：一个包含 4 个 excel 文件的文件夹：

df1= pd.DataFrame({'Last Name':[‘Stark’, ‘Stark’, ‘ Stark’, ‘Stark’],
 'FirstName':['Arya', ,'Arya','Arya','Arya',],
 'DEPTNAME':['Sécu','Sécu','Sécu','Sécu']})

df2= pd.DataFrame({'Last Name':[‘Lannister’, ‘Lannister’, ‘ Lannister’, ‘Lannister’],
 'FirstName':['Cersei', ,'Cersei','Cersei','Cersei',],
 'DEPTNAME':['Auto','Auto','Auto','Auto']})

df3= pd.DataFrame({'Last Name':[‘Snow’, ‘Snow’, ‘ Snow’, ‘Snow’, ‘ Snow’, ‘Snow’],
         'FirstName':['Jon', 'Jon','Jon','Jon','Jon','Jon'],
         'DEPTNAME':['Aero','Aero','Aero','Aero','Aero','Aero']})

df4= pd.DataFrame({'Last Name':[‘Lannister’, ‘Lannister’, ‘ Lannister’, ‘Lannister’],
         'FirstName':['Tyrion', 'Tyrion','Tyrion','Tyrion',],
         'DEPTNAME':['Aero','Aero','Aero','Aero']})

现在我需要自动创建 3 个文件夹Sécu：Aero和Auto.

Sécu将包含一个 excel 文件

Aero将包含两个excel文件

Auto将包含一个 excel 文件

知道我的初始文件夹包含 n 个带有多张工作表的 excel 文件是否可行？

标签： pythonpython-3.xpandassorting

Answer 1

here is one way which combines all files in a folder and all sheets in each file and then groups on DEPTNAME and the filename + sorts the files in the folder(Note: if same DEPTNAME are in 2 different excel fies, they are saves as 2 different files in the same folder <- as requested):

def myf(folder,files_to_be_created_in_folder):
    """ folder is the path to input files and files_to_be_created_in_folder
         is the path where the directories are to be created"""
    folder = folder
    list_of_files=os.listdir(folder)
    combined_sheets={i[:-5]:pd.concat(pd.read_excel(os.path.join(folder,i),sheet_name=None)
        .values(),sort=False)for i in list_of_files}
    combined_all_files=pd.concat(combined_sheets.values(),keys=combined_sheets.keys())
    d={i:g for i,g in combined_all_files.groupby(['DEPTNAME'
             ,combined_all_files.index.get_level_values(0)])}
    to_create_folder=files_to_be_created_in_folder
    for k,v in d.items():
        newpath=os.path.join(to_create_folder,k[0])
        if not os.path.exists(newpath):
            os.makedirs(newpath)
        v.to_excel(os.path.join(newpath,f"{k[1]}.xlsx"),index=False)

---

myf(r'C:\path_to_files\test_folder',r'C:\path_to_write\New folder') #replace paths carefully

---

For testing I have tried t print a folder tree based on this solution which depicts a folder tree:

ptree(r'C:\path_to_files\test_folder')

test_folder/
|-- test_1.xlsx
|-- test_2.xlsx
|-- test_3.xlsx
|-- test_4.xlsx

ptree(r'C:\path_to_write\New folder') #this also has the test folder

New folder/
|-- Aero/
|   |-- test_3.xlsx
|   |-- test_4.xlsx
|-- Auto/
|   |-- test_2.xlsx
|-- Sécu/
|   |-- test_1.xlsx
|-- test_folder/
|   |-- test_1.xlsx
|   |-- test_2.xlsx
|   |-- test_3.xlsx
|   |-- test_4.xlsx