Merge two annotated results into one dictionary

Question

I have to annotated results:

cred_rec = Disponibilidad.objects.values('mac__mac', 'int_mes').annotate(tramites=Count('fuar'), recibidas=Count('fecha_disp_mac')
cred14   = Disponibilidad.objects.filter(int_disponible__lte=14).values('mac__mac', 'int_mes').annotate(en14=Count('fuar'))

Both have the same keys 'mac__mac' and 'int_mes', what I want is to create a new dictionary with the keys in cred_red, plus en14 from cred14.

I try some answers found here, but I missing something.

Thanks.

EDIT. After some tries and errors, I got this:

for linea in cred_rec:
  clave = (linea['mac__mac'], linea['int_mes'])
  for linea2 in cred14:
    clave2 = (linea2['mac__mac'], linea2['int_mes'])
    if clave2 == clave:
      linea['en14'] = linea2['en14']
      linea['disp'] = float(linea2['en14'])/linea['recibidas']*100

Now, I have to ask for a better solution. Thanks again.

======= EDIT This is how the input looks like:

fuar, mac_id, int_mes, int_disponible, int_exitoso, fecha_tramite, fecha_actualiza_pe, fecha_disp_mac
1229012106349,1,7,21,14,2012-07-02 08:33:54.0,2012-07-16 17:33:21.0,2012-07-23 08:01:22.0
1229012106350,1,7,25,17,2012-07-02 09:01:25.0,2012-07-19 17:45:57.0,2012-07-27 17:45:59.0
1229012106351,1,7,21,14,2012-07-02 09:15:12.0,2012-07-16 19:14:35.0,2012-07-23 08:01:22.0
1229012106352,1,7,24,16,2012-07-02 09:25:19.0,2012-07-18 07:52:18.0,2012-07-26 16:04:11.0
... a few  thousand lines dropped ...

The fuar is like an order_id; mac__mac is like the site_id, mes is month; int_disponible is the timedelta between fecha_tramite and fecha_disp_mac; int_exitoso is the timedelta between fecha_tramite and fecha_actualiza_pe.

The output is like this:

mac, mes, tramites, cred_rec, cred14,  % rec,  % en 14
1, 7, 2023, 2006, 1313, 99.1596638655, 65.4536390828
1, 8, 1748, 1182, 1150, 67.6201372998, 97.2927241963
2, 8, 731, 471, 441, 64.4322845417, 93.6305732484
3, 8, 1352, 840, 784, 62.1301775148, 93.3333333333

tramites is the sum of all orders (fuar) in a month
cred_rec cred is our product, in theory for each fuar there is a cred, cred_rec is the sum of all cred produced in a month
cred_14 is the sum of all cred made in 14 days
% rec the relationship between fuar received and cred produced, in %
% en 14 is the relationship between the cred produced and the cred produced in time

I will use this table in a Annotated Time Line chart or a Combo Chart from Google Charts to show the performance of our manufacturation process.

Thanks for your time.

What does your input look like, and what do you want your output to look like? — Ignacio Vazquez-Abrams, Sep 26 '12 at 02:12
The output talks about both "a month" and "14 days". How should we handle this? — Ignacio Vazquez-Abrams, Sep 26 '12 at 05:53
`14 days` is the number of products made in 14 days. We a have a certain number of order in a month and we have to fullfill them in 14 days or less, that's our quality objectve. We must know how many orders we complete in 14 days and what percentage represents. — toledano, Sep 26 '12 at 16:15

score 1 · Accepted Answer · answered Nov 01 '12 at 16:51

One immediate improvement to the current code you have would be to have the en14 and disp values precalculated and indexed by key. This will reduce the scans on the cred14 list, but will use memory to store the precalculated values.

def line_key(line):
      return (line['mac__mac'], line['int_mes'])

cred14_calcs = {}
for line in cred14:
  cred14_calcs[line_key(line)] = {
      'en14': line['en14'],
      'disp': float(line['en14'])/line['recibidas']*100
    }

for line in cred_rec:
  calc = cred14_calcs.get(line_key(line))
  if calc:
    line.update(calc)

Merge two annotated results into one dictionary

1 Answers1