Pandas: updating cells with new value plus old value

Question

I have a folder with zip files that contain CSVs for each day. Each CSV contains rows with IPs and a company ID. I want to calculate a distribution of the number of firms accessed. Meaning that I want to know how many users accessed only 1 firm, 2 firms and so on.

The code asks for a specific date interval and then only selects those zips in the specified date interval. First I create a dataframe (final_df) that has 2 columns: n_firms and n_users by importing it from a CSV:

index n_firms n_users
0     1       0
1     2       0
...   ...     ...

Then, the first for loop opens the zip file and reads the CSV and returns the CSV contents to master_df. Then I group by IPs because I have to count how many unique firms one specific user has accessed (the reason why I drop duplicates in the second for loop).

Since the index is offset by 1 from the n_firms, I subtract 1 from the line of the unique firms the user accessed. Then it reads the previous value in the final_df corresponding to the number of firms accessed and adds 1 because now there is one extra user who accessed that number of firms.

From what I can tell, the second for loop is slow and I want to know if there is some way I can improve that.

My code is the following:

import pandas as pd
import zipfile
from tqdm import tqdm


def _load_log_file(inputFile):
    colsToKeep = ['ip', 'cik']
    # Open the raw compressed file from EDGAR
    with zipfile.ZipFile(inputFile, 'r') as zippedLogFile:
        # Archive contains a CSV plus a readme, get the CSV name
        for fn in zippedLogFile.namelist():
            if '.csv' in fn:
                break

        logFile = pd.read_csv(zippedLogFile.open(fn, 'r'), usecols=colsToKeep)

    return logFile


if __name__ == '__main__':
    print("Please enter start date: ")
    startdate = input()
    print("Please enter end date: ")
    enddate = input()

    final_df = pd.read_csv('D:/empty_df.csv')
    final_df.n_cik = final_df.n_cik.astype(int)
    print("length of the matrix: ", len(final_df.index))

    path_to_zips = 'D:/SBP/{year}/Qtr{qtr}/log{year}{mnth:02d}{day:02d}.zip'
    dates = [(date.year, date.quarter, date.month, date.day) for date in pd.date_range(startdate, enddate)]

    logFilesToProcess = [path_to_zips.format(year=y, qtr=q, mnth=m, day=d) for y, q, m, d in dates]

    counter = 0
    for each_zip_file in tqdm(logFilesToProcess):
        counter += 1
        name = each_zip_file.split(".")
        name = name[0]
        name = name.split("/")
        name_csv = name[-1]

        master_df = _load_log_file(each_zip_file)

        master_df = master_df.groupby("ip")
        for item, df_ip in master_df:
            df_ip = df_ip.drop_duplicates(subset="cik", keep="first")
            number_of_firms = len(df_ip.index)-1
            read_number = final_df.at[number_of_firms, 'n_users']
            final_df.at[number_of_firms, 'n_users'] = read_number + 1

    final_df.to_csv("D:/2004.csv", index=False)

Each CSV file looks like this (and I only keep the IP and the CIK= central index key. Each company has a unique CIK and you can use that number to search for the company's fillings on the securities and exchange comission website https://www.sec.gov/edgar/searchedgar/companysearch.html):

ip              date        time        zone  cik
199.43.32.edd   03/01/2004  00:00:00    500 78890
67.82.239.bhe   03/01/2004  00:00:00    500 746838
67.82.239.bhe   03/01/2004  00:00:00    500 1001082
67.82.239.bhe   03/01/2004  00:00:00    500 746838
67.82.239.bhe   03/01/2004  00:00:00    500 752642
67.82.239.bhe   03/01/2004  00:00:00    500 1001082
151.196.250.ahd 03/01/2004  00:00:01    500 825411
208.61.82.abc   03/01/2004  00:00:01    500 106926
67.82.239.bhe   03/01/2004  00:00:01    500 82020
67.82.239.bhe   03/01/2004  00:00:01    500 1001082
67.82.239.bhe   03/01/2004  00:00:01    500 101829
67.82.239.bhe   03/01/2004  00:00:01    500 1001082
151.196.250.ahd 03/01/2004  00:00:02    500 825411
207.168.174.jdd 03/01/2004  00:00:02    500 714756
66.108.151.fgg  03/01/2004  00:00:02    500 1000180

The first few lines from the 2003 CSV output is the following:

n_firms n_users
1   2392550
2   478414
3   205789
4   115967
5   73688
6   51690
7   37297
8   28025
9   21959
10  17480
11  14295
12  11983
13  9937
14  8513
15  7451
16  6611
17  5749
18  4991
19  4702
20  4001
21  3668
22  3330
23  2971
24  2638
25  2462
26  2338
27  2177
28  2006

There are 2006 users in the year 2003 that accessed 28 unique firms in a day or there are 2392550 users in the year 2003 that accessed only 1 unique firm in a day.

Answer 1

Getting the base name from a file is best done using either the os package or the pathlib package:

import os
from pathlib import Path

path = "some/directory/file_name.csv"
print(os.path.splitext(os.path.basename(path))[0])
# file_name
print(Path(path).stem)
# file_name

---

As for your inner loop, you can simplify getting the number of different companies visited by writing:

visits_distribution = master_df.groupby("ip").cik.unique().apply(len)

Which will give you the distribution per user for that day:

ip
151.196.250.ahd    1
199.43.32.edd      1
207.168.174.jdd    1
208.61.82.abc      1
66.108.151.fgg     1
67.82.239.bhe      5
Name: cik, dtype: int64

On this you can just call np.bincount, which can take as an optional argument what the maximum number is supposed to be:

n = len(final_df.index)
hist_on_day = np.bincount(visits_distribution, minlength=n)

Then you just need to sum all these arrays for every day, which eliminates the inner loop completely (by being done by numpy):

...
n = len(final_df.index)
final_hist = np.zeros(n)

for zip_file in tqdm(logFilesToProcess):
    name_csv = Path(zip_file).stem
    df = _load_log_file(each_zip_file)
    visits_distribution = df.groupby("ip").cik.unique().apply(len)
    hist_on_day = np.bincount(visits_distribution, minlength=n)
    final_hist += hist_on_day

final_df["n_users"] = final_hist
final_df.to_csv("D:/2004.csv", index=False)

Instead of minlength you could also use numpy.pad (test which one is faster):

hist_on_day = np.bincount(visits_distribution)
final_hist += np.pad(hist_on_day, (0, len(final_hist)), 'constant')

---

You should also separate this into multiple functions, the inner part of the for loop (without the last line) would be perfect for one:

def parse_file(file_name):
    df = _load_log_file(file_name)
    visits_distribution = df.groupby("ip").cik.unique().apply(len)
    return np.bincount(visits_distribution, minlength=n)

---

And one way to make it a bit faster is to not generate all dates at the beginning with a list comprehension, but instead use a generator comprehension which will produce them as needed:

dates = ((date.year, date.quarter, date.month, date.day)
         for date in pd.date_range(start_date, end_date))
log_files = (path_to_zips.format(year=y, qtr=q, mnth=m, day=d)
             for y, q, m, d in dates)

However, that does preclude you from using tqdm for the progress (since generators don't have a length), but there are ways around that:

n_days = (pd.to_datetime(end_date) - pd.to_datetime(start_date)).days
for zip_file in tqdm(log_files, total=n_days):
    ...

Note that I renamed some of your variables to conform to Python's official style-guide, PEP8.