From dataframe to dictionary for table in docxpl-CodePudding

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.

CodePudding user response：

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}]