From dataframe to dictionary for table in docxpl

March 16, 2022

I want to create a dictionary for a docxpl table that looks like this.

target=[{'NR1': 1, 'AREA1': 'A', 'RES1': 3.2, 'NR2': 4, 'AREA2': 'D', 'RES2': 5.1, 'NR3': 7, 'AREA3': 'G', 'RES3': 3.2},
 {'NR1': 2, 'AREA1': 'B', 'RES1': 3.6, 'NR2': 5, 'AREA2': 'E', 'RES2': 4.6, 'NR3': 8, 'AREA3': 'H', 'RES3': 2.9},
 {'NR1': 3, 'AREA1': 'C', 'RES1': 4.2, 'NR2': 6, 'AREA2': 'F', 'RES2': 3.8, 'NR3': 9, 'AREA3': 'I', 'RES3': 3.7},
 {'NR1': 10, 'AREA1': 'J', 'RES1': 5.2}]

I have a dataframe, df (below) and if I do df.to_dict(‘records’) I get the following result.

df=pd.DataFrame({'NR':range(1,11),
             'AREA': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'],
             'RES': [3.2, 3.6, 4.2, 5.1, 4.6, 3.8, 3.2, 2.9, 3.7, 5.2]})

dict_=df.to_dict('records')

print(dict)

[{'NR': 1, 'AREA': 'A', 'RES': 3.2},
 {'NR': 2, 'AREA': 'B', 'RES': 3.6},
 {'NR': 3, 'AREA': 'C', 'RES': 4.2},
 {'NR': 4, 'AREA': 'D', 'RES': 5.1},
 {'NR': 5, 'AREA': 'E', 'RES': 4.6},
 {'NR': 6, 'AREA': 'F', 'RES': 3.8},
 {'NR': 7, 'AREA': 'G', 'RES': 3.2},
 {'NR': 8, 'AREA': 'H', 'RES': 2.9},
 {'NR': 9, 'AREA': 'I', 'RES': 3.7},
 {'NR': 10, 'AREA': 'J', 'RES': 5.2}]

I need to find a way to get the fourth and seventh dictionary in dict_ to be in the same as the first. I also need a method to change the names of the keys in each dictionary, nr1, nr2, nr3 etc.

Any suggestion how to best solve this would be much appreciated.

>Solution :

Probably not the best way but it works:

out = (
  df.set_index([df.index // 3, 1 + df.index % 3]).melt(ignore_index=False).sort_index()
    .assign(variable=lambda x: x['variable'] + x.index.get_level_values(1).astype(str))
    .groupby(level=0).apply(lambda x: x.set_index('variable').squeeze().to_dict()).tolist()
)

Output:

>>> out
[{'NR1': 1,
  'AREA1': 'A',
  'RES1': 3.2,
  'NR2': 2,
  'AREA2': 'B',
  'RES2': 3.6,
  'NR3': 3,
  'AREA3': 'C',
  'RES3': 4.2},
 {'NR1': 4,
  'AREA1': 'D',
  'RES1': 5.1,
  'NR2': 5,
  'AREA2': 'E',
  'RES2': 4.6,
  'NR3': 6,
  'AREA3': 'F',
  'RES3': 3.8},
 {'NR1': 7,
  'AREA1': 'G',
  'RES1': 3.2,
  'NR2': 8,
  'AREA2': 'H',
  'RES2': 2.9,
  'NR3': 9,
  'AREA3': 'I',
  'RES3': 3.7},
 {'NR1': 10, 'AREA1': 'J', 'RES1': 5.2}]