1
$\begingroup$

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.

$\endgroup$

Browse other questions tagged or ask your own question.