How to create a pandas dataframe from csv where one column contains nested dictionary?

user_14348631 0
Viewed 68

I have a CSV file and in one column there is a nested dictionary with the values of classification report, in a format like this one:

{'A': {'precision': 0.0, 'recall': 0.0, 'f1-score': 0.0, 'support': 60},

'B': {'precision': 0.42, 'recall': 0.09, 'f1-score': 0.14, 'support': 150},

'micro avg': {'precision': 0.31, 'recall': 0.31, 'f1-score': 0.31, 'support': 1710},

'macro avg': {'precision': 0.13, 'recall': 0.08, 'f1-score': 0.071, 'support': 1710},

'weighted avg': {'precision': 0.29, 'recall': 0.31, 'f1-score': 0.26, 'support': 1710}}

I would like to get key_value1_level as a column in a data frame. So, is it possible to get the following result?

A_precision A_recall ...weighted_avg_precision   weighted_avg_recall   weighted_avg_f1-score   weighted avg_support
0.0         0.0         0.29                     0.31                  0.26                                   1710

Thank you

pandas dataframe dictionary
1 Answers

You can use pd.json_normalize on that dictionary:

dct = {
    "A": {"precision": 0.0, "recall": 0.0, "f1-score": 0.0, "support": 60},
    "B": {"precision": 0.42, "recall": 0.09, "f1-score": 0.14, "support": 150},
    "micro avg": {
        "precision": 0.31,
        "recall": 0.31,
        "f1-score": 0.31,
        "support": 1710,
    },
    "macro avg": {
        "precision": 0.13,
        "recall": 0.08,
        "f1-score": 0.071,
        "support": 1710,
    },
    "weighted avg": {
        "precision": 0.29,
        "recall": 0.31,
        "f1-score": 0.26,
        "support": 1710,
    },
}

df = pd.json_normalize(dct, sep="_")
print(df)

Prints:

   A_precision  A_recall  A_f1-score  A_support  B_precision  B_recall  B_f1-score  B_support  micro avg_precision  micro avg_recall  micro avg_f1-score  micro avg_support  macro avg_precision  macro avg_recall  macro avg_f1-score  macro avg_support  weighted avg_precision  weighted avg_recall  weighted avg_f1-score  weighted avg_support
0          0.0       0.0         0.0         60         0.42      0.09        0.14        150                 0.31              0.31                0.31               1710                 0.13              0.08               0.071               1710                    0.29                 0.31                   0.26                  1710
user_100359850
Related