Creating JSON style API call dict from Pandas DF data

Question

Scenario: I have a dataframe which contains one row of data. Each column is an year and it has the relevant value. I am trying to use the data from this df to create a json style structure to pass to an API requests.post.

Sample DF:

+-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+
|       | 2020  | 2021  | 2022  | 2023  | 2024  |    2025     |    2026     |    2027     |    2028     |    2029     |    2030     |
+-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+
| Total | 23648 | 20062 | 20555 | 22037 | 26208 | 28224.88801 | 29975.87934 | 31049.01582 | 32170.68853 | 33190.35298 | 34031.93951 |
+-------+-------+-------+-------+-------+-------+-------------+-------------+-------------+-------------+-------------+-------------+

Sample JSON style structure:

parameters = {
    "first_Id":first_id,
    "version":2,
    "overrideData":[
      {
          "period":2024,          
          "TOTAL":101.64,          
      },
      {
          "period":2025,
          "TOTAL":104.20,
      }
    ]
}

Question: What would be the best approach to use the data from the Df to fill and expand the JSON style object? I tried the following, but this only separates two lines, one for total and one for period, which does not result in the pairings needs:

        parameters = {}
        parameters['first_Id'] = first_id
        parameters['version'] = 2
        parameters['overrideData'] = {
            }
        parameters['overrideData']['total'] = test_input.iloc[0].tolist()         
        parameters['overrideData']['period'] = list(test_input.columns)

This results in:

{
  "companyId": 11475,
  "version": 2,
  "overrideData": {
    "TOTAL": [
      23647.999999999996,
      20061.999999999996,
      20555,
      22036.999999999996,
      26207.999999999993,
      28224.88800768,
      29975.879336500002,
      31049.015816740008,
      32170.68852577,
      33190.3529754,
      34031.93951397
    ],
    "period": [
      2020,
      2021,
      2022,
      2023,
      2024,
      2025,
      2026,
      2027,
      2028,
      2029,
      2030
    ]
  }
}

Answer 1

You could transpose, rename_axis, reset_index, convert to_dict as records:

test_input.T.rename_axis('period').reset_index().to_dict('records')

In your case:

parameters['overrideData'] = (test_input.T.rename_axis('period')
                                        .reset_index().to_dict('records')
                              )

Output:

[{'period': '2020', 'Total': 23648.0},
 {'period': '2021', 'Total': 20062.0},
 {'period': '2022', 'Total': 20555.0},
 {'period': '2023', 'Total': 22037.0},
 {'period': '2024', 'Total': 26208.0},
 {'period': '2025', 'Total': 28224.88801},
 {'period': '2026', 'Total': 29975.87934},
 {'period': '2027', 'Total': 31049.01582},
 {'period': '2028', 'Total': 32170.68853},
 {'period': '2029', 'Total': 33190.35298},
 {'period': '2030', 'Total': 34031.93951}]