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
.
.
.
(all about 6 million rows)
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]
print('Done adding : arief_anbiya_{}.csv'.format(digits))
read_csv
. One solution is to use alternative such asdask
package. \$\endgroup\$ – Arief Anbiya Jul 23 '18 at 18:13