1
\$\begingroup\$

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))
\$\endgroup\$
  • \$\begingroup\$ Learn to meassure times, which part is slowing the process the most? Its meaningless to try to improve code performance when 95% of that time is from reading/writing file \$\endgroup\$ – juvian Jul 23 '18 at 17:01
  • \$\begingroup\$ @juvian yes I already realized that the problem is read_csv. One solution is to use alternative such as dask package. \$\endgroup\$ – Arief Anbiya Jul 23 '18 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.