CODEWITHSUNDEEP
jsonpython-3.xpandas
MAC
MAC

Reputation: 1515

Json to pandas dataframe with slight modification

I have a json data as below:

{
"X": "abc",
"Y": 1,
"Z": 4174,

"t_0": 
  {
     "M": "bm",
     "T": "sp",
     "CUD": 4,
"t_1": '
   {
     "CUD": "1",
     "BBC": "09",
     "CPR": -127
  },

  "EVV": "10.7000",
  "BBC": -127,
  "CMIX": "25088"
 },
 "EYR": "sp"
}

The problem is converting to python data-frame creates two columns of same name CUD. One is under t_0 and another is under t_1. But both are different events. How can I append json tag name to column names so that I can differentiate two columns of same name. Something like t_0_CUD , t_1_CUD.

My code is below:

df = pd.io.json.json_normalize(json_data)
df.columns = df.columns.map(lambda x: x.split(".")[-1])

Upvotes: 1

Views: 30

Answers (1)

jezrael
jezrael

Reputation: 862691

If use only first part of solution it return what you need, only instead _ are used .:

df = pd.io.json.json_normalize(json_data)
print (df)
     X  Y     Z EYR t_0.M t_0.T  t_0.CUD t_0.t_1.CUD t_0.t_1.BBC  t_0.t_1.CPR  \
0  abc  1  4174  sp    bm    sp        4           1          09         -127   

   t_0.EVV  t_0.BBC t_0.CMIX  
0  10.7000     -127    25088

If need _:

df.columns = df.columns.str.replace('\.','_')
print (df)
     X  Y     Z EYR t_0_M t_0_T  t_0_CUD t_0_t_1_CUD t_0_t_1_BBC  t_0_t_1_CPR  \
0  abc  1  4174  sp    bm    sp        4           1          09         -127   

   t_0_EVV  t_0_BBC t_0_CMIX  
0  10.7000     -127    25088

Upvotes: 3

Related Questions