I have a DataFrame with more than 2 million rows, that looks like this:
+-------------------+--------------+--------+---------------------------+-------------+
| PartitionKey | RowKey | Type | Path | Name |
+-------------------+--------------+--------+---------------------------+-------------+
| / | /People | Folder | /People | People |
| /People | /index1.xlsx | File | /People/index1.xlsx | index1.xlsx |
| /People | /index2.xlsx | File | /People/index2.xlsx | index2.xlsx |
| /People | /index3.xlsx | File | /People/index3.xlsx | index3.xlsx |
| /People | /Employees | Folder | /People/Employees | Employees |
| /People/Employees | /cv1.pdf | File | /People/Employees/cv1.pdf | cv1.pdf |
| /People/Employees | /cv2.pdf | File | /People/Employees/cv2.pdf | cv2.pdf |
| /People/Employees | /cv3.pdf | File | /People/Employees/cv3.pdf | cv3.pdf |
+-------------------+--------------+--------+---------------------------+-------------+
I want to add two new columns: DirectFileCount and RecursiveFileCount.
Those should indicate the number of files within the folder itself (folder identified as Type == Folder), and the number of files within itself and all sub folders recursively, as per the Path --> PartitionKey relationship from the folders to files.
It should make the DataFrame look like this:
+-------------------+--------------+--------+---------------------------+-------------+-----------------+--------------------+
| PartitionKey | RowKey | Type | Path | Name | DirectFileCount | RecursiveFileCount |
+-------------------+--------------+--------+---------------------------+-------------+-----------------+--------------------+
| / | /People | Folder | /People | People | 3 | 6 |
| /People | /index1.xlsx | File | /People/index1.xlsx | index1.xlsx | 0 | 0 |
| /People | /index2.xlsx | File | /People/index2.xlsx | index2.xlsx | 0 | 0 |
| /People | /index3.xlsx | File | /People/index3.xlsx | index3.xlsx | 0 | 0 |
| /People | /Employees | Folder | /People/Employees | Employees | 3 | 3 |
| /People/Employees | /cv1.pdf | File | /People/Employees/cv1.pdf | cv1.pdf | 0 | 0 |
| /People/Employees | /cv2.pdf | File | /People/Employees/cv2.pdf | cv2.pdf | 0 | 0 |
| /People/Employees | /cv3.pdf | File | /People/Employees/cv3.pdf | cv3.pdf | 0 | 0 |
+-------------------+--------------+--------+---------------------------+-------------+-----------------+--------------------+
I have something for direct count that works:
df_count = df.groupby(['.tag', 'PartitionKey']).size().reset_index(name='counts')
df_file_count = df_count[df_count['.tag'] == 'file'].set_index('PartitionKey')
def direct_count(row):
if row['.tag'] == 'folder':
try:
return df_file_count.loc[row['path_lower']].counts
except:
pass
return 0
df['DirectFileCount'] = df.apply(lambda row: direct_count(row), axis=1)
The above code takes care of DirectFileCount and completes in less than 2 minutes.
RecursiveFileCount I cannot get done in a way that doesn't take ages. It performs insanely bad, and since there's two million rows, if it just takes a second for each, it practically never completes.
