-3

I have created a dataframe (df) which stores below information, HS1 is the index.

HS1 HS2 EffNotional_UnMG
EUR 3   -10082.91381
USD 2   -36253.84938
USD 3   78693.86806

Now I want to apply two ierations on this dataframe: outer by HS1 and the inner by HS2 so as to get two dictionaries (already declared with default values) as below: For EUR: {'2': 20000.00,'3':0} [Key 3 has 0 default value] For USD: {'2': -36253.84938, '3':78693.86806}

I used itertuple and get following output for the outer iteration:

for row in df.itertuples():
print (row)

Pandas(Index='EUR', HS2=3, EffNotional_UnMG=-10082.913813053281)

Pandas(Index='USD', HS2=2, EffNotional_UnMG=-36253.849384403635)

Pandas(Index='USD', HS2=3, EffNotional_UnMG=78693.868057473315)

Can one guide on proceed with the inner iteration using itertuples.

RKG
  • 3
  • 3

1 Answers1

0

I believe loop is not necessary, use pivot + fillna + to_dict:

d = df.pivot('HS2','HS1','EffNotional_UnMG').fillna(0).to_dict()

Alternative solution with set_index + unstack + to_dict:

d = df.set_index(['HS2','HS1'])['EffNotional_UnMG'].unstack(fill_value=0).to_dict()

print (d)
{'EUR': {'2': 0.0, '3': -10082.91381}, 'USD': {'2': -36253.84938, '3': 78693.86806000001}}
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • I dont understand, can you create data with expected output and explain, why it does not work? – jezrael Jan 29 '18 at 14:06
  • The itertuple solution would be still helpful. Shared solution is great. However the dictionary has a mandatory key as '1' which is required in later calculation along with other keys. How can we introduce the keys missed in pivot ? The addition of missing key (1 or 2 or 3) has to be dynamic. Many thanks. The formula used is Addon=sqrt(d['1']**2+d['2']**2+d['3']**2+1.4*d['1']*d['2']+1.4*d['2']*d['3']+0.6*d['1']*d['3']) – RKG Jan 29 '18 at 14:17
  • `pivot` is for adding `0` for all missing values. Without data is is really close give you nice answer - what you need ;) – jezrael Jan 29 '18 at 14:32
  • I managed the missing part as : d2.update((missing,0) for missing in set(lst).difference(d2)) where lst is a list containing 1,2 and 3 and d2 is the subdictionary for each key – RKG Jan 29 '18 at 14:39
  • 1
    Got the answer below for each currency for x,y in d.items(): d2=y print (x) d2.update((missing,0) for missing in set(lst).difference(d2)) print (d2) addon=sqrt(d2[1]**2+d2[2]**2+d2[3]**2+1.4*d2[1]*d2[2]+1.4*d2[2]*d2[3]+0.6*d2[1]*d2[3]) print (addon) EUR {2: 0.0, 3: -10082.913813053281, 1: 0} 10082.913813053281 USD {2: -36253.849384403635, 3: 78693.868057473315, 1: 0} 59269.9634637104 – RKG Jan 29 '18 at 14:42