Json to pandas dataframe with slight modification

Question

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])

kindly add an expected output dataframe. not pics. data

sammywemmy
– sammywemmy

2020-02-18 12:50:47 +00:00
Commented Feb 18, 2020 at 12:50 — sammywemmy
– sammywemmy, Commented Feb 18, 2020 at 12:50

jezrael · Accepted Answer · 2020-02-18 12:52:31Z

3

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

answered Feb 18, 2020 at 12:52

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Json to pandas dataframe with slight modification

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related