Computing aggregate by creating nested dictionary on the fly
<p>I'm new to python and I could really use your help and guidance at the moment. I am trying to read a csv file with three cols and do some computation based on the first and second column i.e.</p>

<pre><code>A   spent   100     A   spent   2040
A   earned  60
B   earned  48
B   earned  180
A   spent   40
.
.
.
</code></pre>

<p>Where A spent 2040 would be the addition of all 'A' and 'spent' amounts. This does not give me an error but it's not logically correct:</p>

<pre><code>for row in rows:
    cols = row.split(",")
    truck = cols[0]
    if (truck != 'A' and truck != 'B'):
        continue
    record = cols[1]
    if(record != "earned" and record != "spent"):
        continue
    amount = int(cols[2])
    #print(truck+" "+record+" "+str(amount))

    if truck in entries:
        #entriesA[truck].update(record)
        if record in records:
            records[record].append(amount)
        else:
            records[record] = [amount]
    else:
        entries[truck] = records
        if record in records:
            records[record].append(amount)
        else:
            entries[truck][record] = [amount]
print(entries)
</code></pre>

<p>I am aware that this part is incorrect because I would be adding the same inner dictionary list to the outer dictionary but I'm not sure how to go from there:</p>

<pre><code>entries[truck] = records
if record in records:
    records[record].append(amount)
</code></pre>

<p>However, Im not sure of the syntax to create a new dictionary on the fly that would not be 'records'</p>

<p>I am getting:</p>

<pre><code>{'B': {'earned': [60, 48], 'spent': [100]}, 'A': {'earned': [60, 48], 'spent': [100]}}
</code></pre>

<p>But hoping to get:</p>

<pre><code>{'B': {'earned': [48]}, 'A': {'earned': [60], 'spent': [100]}}
</code></pre>

<p>Thanks.</p>

<p>For the kind of calculation you are doing here, I highly recommend <a href="http://pandas.pydata.org/" rel="nofollow">Pandas</a>.</p>

<p>Assuming <code>in.csv</code> looks like this:</p>

<pre><code>truck,type,amount
A,spent,100
A,earned,60
B,earned,48
B,earned,180
A,spent,40
</code></pre>

<p>You can do the totalling with three lines of code:</p>

<pre><code>import pandas
df = pandas.read_csv('in.csv')
totals = df.groupby(['truck', 'type']).sum()
</code></pre>

<p><code>totals</code> now looks like this:</p>

<pre><code>              amount
truck type          
A     earned      60
      spent      140
B     earned     228
</code></pre>

<p>You will find that Pandas allows you to think on a much higher level and avoid fiddling with lower level data structures in cases like this.</p>

python pandas dictionary group-by aggregate