python - Convert JSON file to xls correctly

Question

I have a JSON file I change in a Python file via lists. I'd like to convert it to xls. It's working but not the way I want it. My JSON file:

[
{
    "populaires": {
        "perfo_indice_annual_value": 100,
        "perfo_percentage_annual_value": "0.96%",
        "perfo_percentage_monthly_value": "0.96%"
    },
    "classics": {
        "perfo_indice_annual_value": 100,
        "perfo_percentage_annual_value": "0.0%",
        "perfo_percentage_monthly_value": "0.0%"
    },
    "500k": {
        "perfo_indice_annual_value": 103,
        "perfo_percentage_annual_value": "3.62%",
        "perfo_percentage_monthly_value": "3.62%"
    },
    "youngtimers": {
        "perfo_indice_annual_value": 105,
        "perfo_percentage_annual_value": "4.97%",
        "perfo_percentage_monthly_value": "4.97%"
    }
}
]

The output I have:

The output I'd like:

My code:

# Just indices
for indices in data_indices:
    indice_pop_modele = indices["populaires"]
    indice_pop = indices["populaires"]["perfo_indice_annual_value"]
    indices["populaires"]["perfo_indice_annual_value"] = int(indices["populaires"]["perfo_indice_annual_value"])
    indices["populaires"]["perfo_percentage_annual_value"] = str(indices["populaires"]["perfo_percentage_annual_value"]) + "%"
    indices["populaires"]["perfo_percentage_monthly_value"] = str(indices["populaires"]["perfo_percentage_monthly_value"]) + "%"
    indice_pop_modele.pop('modeles', None)
    #print(indice_pop_modele)

    indice_classics_modele = indices["classics"]
    indices["classics"]["perfo_indice_annual_value"] = int(indices["classics"]["perfo_indice_annual_value"])
    indices["classics"]["perfo_percentage_annual_value"] = str(indices["classics"]["perfo_percentage_annual_value"]) + "%"
    indices["classics"]["perfo_percentage_monthly_value"] = str(indices["classics"]["perfo_percentage_monthly_value"]) + "%"
    indice_classics_modele.pop('modeles', None)
    #print(indice_classics_modele)

    ...

df_just_indices = pd.DataFrame(data_indices)
df_just_indices.to_excel('indices' + date_file + '.xls')

question from:https://stackoverflow.com/questions/65849240/convert-json-file-to-xls-correctly

Answer 1

You are creating the dataframe from a list of dict(s). So each dict is an entry, with the keys being the columns and the values the.... values. Since you have just one dict (which represents your whole data), you get one single entry.

First of all you need to extract the dict from the list:

pd.DataFrame(data_indices[0])

This means that now each key is a column, and each inner dict represents the rows.

So this will give:

                               populaires classics   500k youngtimers
perfo_indice_annual_value             100      100    103         105
perfo_percentage_annual_value       0.96%     0.0%  3.62%       4.97%
perfo_percentage_monthly_value      0.96%     0.0%  3.62%       4.97%

But you want the columns as rows in your excel, so simply transpose the dataframe:

pd.DataFrame(data_indices[0]).transpose()

Which will give:

            perfo_indice_annual_value  ... perfo_percentage_monthly_value
populaires                        100  ...                          0.96%
classics                          100  ...                           0.0%
500k                              103  ...                          3.62%
youngtimers                       105  ...                          4.97%