# Pandas: updating cells with new value plus old value

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

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

return logFile

if __name__ == '__main__':
startdate = input()
enddate = input()

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 = 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
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.

• It is not quite clear what is contained in those csv files and what you want in the end. Wouldn't it just be enough to count for each user how many unique companies they visited? Can you share an example csv file (obviously not with real user date, just some random one). Jan 30 '19 at 16:48
• @Graipher I added an example of one of the CSV files. In the end, I want to know how many users have accessed 1 firm, 2 firms... 100 firms and so on. So I can make a distribution graph. I need to store the data into the final_df and then read from it and update the fields because the IPs are coded (the last 3 digits are replaced) and I have to do this for each day of the year. Jan 31 '19 at 8:29
• Please add the data as text, otherwise we cannot use it to test our or your code. I'm not going to transcribe a hundred fields. What is a CIK? Company Identification K? When you say the last IP block is encoded, that encoding is unique for each number, right? How big are all CSV files together (would a concated dataframe fit into memory)? Jan 31 '19 at 8:46
• @Graipher Thank you for taking the time to look into my issue. I added the date as a text format. About the IP encoding.. what I know is that for each day they scramble randomly the last three digits with numbers. Meaning there is no way to track 1 ip from 1 day to another day. All the CSV files together for one year add up to more than 60GB and even more for recent years where there is considerably more traffic..therefore making a dataframe that big is impossible (for my machine with 16GB of RAM) Jan 31 '19 at 8:56
• Yes. Sorry for not being clear enough. As you have said, I want to track the distribution of how many unique companies each user visited within a day. Jan 31 '19 at 9:02

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
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):
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.

• Wow. Thank you so much for explaining and for showing me how to improve my code. You've been a great help. My machine is a bit slow and this will help me save quite a lot of hours! Jan 31 '19 at 9:59