# Grouped by First Two Digits of Account_ID of ~240 million rows of Pandas DF

I have 40 CSV files, each containing ~6 million rows, and 2 columns. The columns are account_id and account_balance.

The objective is getting the sum of all account_balance grouped by first two digits/chars of account_id.

For example:

account_id = 00as3dff01234 is in group 00, and the account id is paired with an account_balance value.

For illustration:

Each CSV file is of the form:

account_id, account_balance
0012228hgs, 123000
01223jj4d, 34000
fb1233999, 1245999
.
.
.


The output by using all 40 CSV files would be of the form:

00 : 123000444220
01 : 500000
0a : 30444555120
.
.
.
ff : 45002221222
(all 276 groups)


The right-hand side is the sum of money of all accounts that have account_id with the left-hand side as first two digits/characters.

My code gives the correct result, BUT not efficient enough. On my laptop it consumes around 11-12 minutes. How can I improve the performance such that the time is around 5-6 minutes? Sorry, I can't provide the data, but I think it can be replicated by simulating only 1 replicated CSV file.

import pandas

for i in range(4):

for j in range(10):

digits = str(i)+str(j)
all_data = pandas.read_csv('/home/asus/data/arief_anbiya_{}.csv'.format(digits), chunksize = 100000, iterator = True) #Transform each csv data to DF.
all_data = pandas.concat(all_data, ignore_index= True)
all_data['account_group'] = [acc_id[0:2] for acc_id in all_data.account_id] #Adding a now column 'first_two' of each account_id (from current csv).

dummy = all_data.groupby(by = 'account_group').sum() #Sum of 'account_balance' of current DF grouped by 'first_two' (from current csv).

if i == 0 and j == 0: #Create the Series for the total 'account_balance' (grouped by 'first_two').

sum_of_groupby = dummy.account_balance

else:   #Update the Series of total 'account_balance'.

for ft in set(all_data['account_group']): # unique_ft is all unique 'first_two' (from current csv).

try:
sum_of_groupby[ft] += dummy.account_balance[ft]
except:
sum_of_groupby[ft] = dummy.account_balance[ft]


• @juvian yes I already realized that the problem is read_csv. One solution is to use alternative such as dask package. – Arief Anbiya Jul 23 '18 at 18:13