-1

i working on data cleansing that fetched from mongo collection and exported as csv.

the data frame has only one column a huge nested json embbeded in each row iam trying to extract particular fields in that tried multiple ways nothing seems workout.

df    :    inputpayload

{
    "action": "val1",
    "action2": "val12",
    "date": "12012",
    "membership": [
        {
            "m1": "00",
            "m2": "001",
            "m3": "003",
            "m4": "005",
            "m5": "006",
            "group": [
                {
                    "g1": "a1",
                    "g2": "a2",
                    "g3": "a3",
                    "g4": "a4",
                    "g5": "a5",
                    "g6": "a6",
                    "g7": "a7",
                    "g9": "a10",
                    "data": [
                        {
                            "id": "xyz",
                            "code": "0012",
                            "fname": "abc",
                            "lname": "x",
                            "dob": "111280",
                            "sno": "234",
                            "bal": "2.3",
                            "cbal": "9.9"
                        }
                    ]
                }
            ]
        }
    ]
}

fields to extract = ["id","code","fname","dob","sno","bal",""cbal"]

desired output data frame

id   code   fname  dob     sno  bal     cbal
xyz  0012   abc    111280  234  2.3      9.9

i want to do this in more pythonic way any solution/input will be highly appreciated.

pascscha
  • 1,623
  • 10
  • 16
Ashok kumar
  • 49
  • 1
  • 7

1 Answers1

1

Set your payload to d and then

pd.DataFrame(d['membership'][0]['group'][0]['data'])


    id  code fname lname     dob  sno  bal cbal
0  xyz  0012   abc     x  111280  234  2.3  9.9
Jonathan Leon
  • 5,440
  • 2
  • 6
  • 14
  • thanks for the response but how to apply this logic to entire data frame – Ashok kumar Jan 23 '21 at 15:58
  • You didn’t post a data frame. It’s just one dictionary. If you can post a few rows of the data frame I can take a look. Unless you are saying there is a column Of these that need to be converted. – Jonathan Leon Jan 23 '21 at 20:12