Reputation: 673
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')
Upvotes: 0
Views: 47
Reputation: 19440
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%
Upvotes: 1