Given a data frame structured as follows:

df = pd.DataFrame({
    'DATE' : [1,2,3,4,5],
    'Q24' : [23.28, 28.81, 29.32, 29.8, 30.25],
    'J24' : [24.22, 24.89, 25.54, 26.15, 26.73],
    'F24' : [22.34, 32.73, 33.1, 33.45, 33.77]
})

I would like to create a dictionary in which all keys are tuples containing the products of values in df['DATES'] and elements in df.columns[1:]. I would then like to assign the relevant values from the data frame to those keys.

So far, I have achieved this by creating an empty dictionary of the requisite keys:

import itertools
keys = list(itertools.product(df['DATE'],df.columns[1:]))
dict1 = dict.fromkeys(keys)

Then creating a list containing a dictionary for each relevant column:

dict2 = df.iloc[:,1:].to_dict('records')

I’ve then used a for loop to assign values to keys in dict1:

for x in df['DATE']:
    for y in df.columns[1:]:
        dict1[x,y] = dict2[x-1][y]

Which correctly produces the desired output:

{(1, 'Q24'): 23.28,(1, 'J24'): 24.22,(1, 'F24'): 22.34,
 (2, 'Q24'): 28.81,(2, 'J24'): 24.89,(2, 'F24'): 32.73,
 (3, 'Q24'): 29.32,(3, 'J24'): 25.54,(3, 'F24'): 33.1,
 (4, 'Q24'): 29.8,(4, 'J24'): 26.15,(4, 'F24'): 33.45,
 (5, 'Q24'): 30.25,(5, 'J24'): 26.73,(5, 'F24'): 33.77}

However, this feels like something of an ugly monstrosity of code and I wondered if there was a more elegant means to achieve the same output?

Help and guidance much appreciated!

>Solution :

Set DATE as the index and stack

df.set_index('DATE').stack().to_dict()

{(1, 'Q24'): 23.28,
 (1, 'J24'): 24.22,
 (1, 'F24'): 22.34,
 (2, 'Q24'): 28.81,
 (2, 'J24'): 24.89,
 (2, 'F24'): 32.73,
 (3, 'Q24'): 29.32,
 (3, 'J24'): 25.54,
 (3, 'F24'): 33.1,
 (4, 'Q24'): 29.8,
 (4, 'J24'): 26.15,
 (4, 'F24'): 33.45,
 (5, 'Q24'): 30.25,
 (5, 'J24'): 26.73,
 (5, 'F24'): 33.77}