2

I have a pandas dataFrame.After printing the pandas DataFrame the results looks like below

country     branch      no_of_employee     total_salary    count_DOB   count_email
  x            a            30                 2500000        20            25
  x            b            20                 350000         15            20
  y            c            30                 4500000        30            30
  z            d            40                 5500000        40            40
  z            e            10                 1000000        10            10
  z            f            15                 1500000        15            15

i would like to convert this into user defined user format like

    {
      "x": [
        {
          "Branch": "a",
          "no_employee": 30
        },
        {
          "Branch": "b",
          "no_employee": 20
        }

      ],
      "y": [
         {
          "Branch": "c",
          "no_employee": 30
        },
        {
          "Branch": "d",
          "no_employee": 40
        }

      ],
      "z": [
         {
          "Branch": "d",
          "no_employee": 40
        },
        {
          "Branch": "e",
          "no_employee": 10
        },
        {
          "Branch": "f",
          "no_employee": 15
        }

  ]

}

How can i convert this dataFrame to this format

Edwin Baby
  • 279
  • 1
  • 4
  • 11

1 Answers1

3

You can try groupby with apply to_dict and last to_json:

g = df.groupby('country')[["branch", "no_of_employee"]]
                                                .apply(lambda x: x.to_dict(orient='records'))
print g.to_json()

{
    "x": [{
        "no_of_employee": 30,
        "branch": "a"
    }, {
        "no_of_employee": 20,
        "branch": "b"
    }],
    "y": [{
        "no_of_employee": 30,
        "branch": "c"
    }],
    "z": [{
        "no_of_employee": 40,
        "branch": "d"
    }, {
        "no_of_employee": 10,
        "branch": "e"
    }, {
        "no_of_employee": 15,
        "branch": "f"
    }]
}
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • @ jezrael no no i got the exact answer . what i meant is i would like to learn how to write this function, for that purpose can i get any tutorials link or something – Edwin Baby Feb 29 '16 at 11:48
  • So I think you can check [tutorial1](https://pythonconquerstheuniverse.wordpress.com/2011/08/29/lambda_tutorial/), or [tutorial2](http://www.diveintopython.net/power_of_introspection/lambda_functions.html) or [question in SO](http://stackoverflow.com/questions/890128/why-are-python-lambdas-useful). But usually it makes writing code a bit easier, and the written code a bit cleaner. – jezrael Feb 29 '16 at 11:50
  • Glad can help you! Good luck! Nice day. :) – jezrael Feb 29 '16 at 11:52