CODEWITHSUNDEEP
pythonjsonpandasdataframe
learner57
learner57

Reputation: 491

Output pandas dataframe to json in a particular format

My dataframe is

fname  lname  city    state    code
Alice  Lee    Athens  Alabama  PXY
Nor    Xi     Mesa    Arizona  ABC

The output of json should be

{

   "Employees":{

      "Alice Lee":{

         "code":"PXY",

         "Address":"Athens, Alabama"

      },

      "Nor Xi":{

         "code":"ABC",

         "Address":"Mesa, Arizona"

      }

   }

}

df.to_json() gives no hierarchy to the json. Can you please suggest what am I missing? Is there a way to combine columns and give them a 'keyname' while writing json in pandas?

Thank you.

Upvotes: 1

Views: 149

Answers (4)

Codographer
Codographer

Reputation: 36

json = json.loads(df.to_json(orient='records'))
employees = {}
employees['Employees'] = [{obj['fname']+' '+obj['lname']:{'code':obj['code'], 'Address':obj['city']+', '+obj['state']}} for obj in json]

This outputs -

{
'Employees': [
{
  'Alice Lee': {
    'code': 'PXY',
    'Address': 'Athens, Alabama'
  }
},
{
  'Nor Xi': {
    'code': 'ABC',
    'Address': 'Mesa, Arizona'
   }
  }
 ]
}

Upvotes: 1

Tu Khac Nguyen
Tu Khac Nguyen

Reputation: 16

you can solve this using df.iterrows()

employee_dict = {}
for row in df.iterrows():

    #  row[0] is the index number, row[1] is the data respective to that index
    row_data = row[1]
    employee_name = row_data.fname + ' ' + row_data.lname
    employee_dict[employee_name] = {'code': row_data.code, 'Address': 
                                     row_data.city + ', ' + row_data.state}

json_data = {'Employees': employee_dict}

Result:

{'Employees': {'Alice Lee': {'code': 'PXY', 'Address': 'Athens, Alabama'},
               'Nor Xi': {'code': 'ABC', 'Address': 'Mesa, Arizona'}}}

Upvotes: 0

Mustafa Aydın
Mustafa Aydın

Reputation: 18306

We can populate a new dataframe with columns being "code" and "Address", and index being "full_name" where the latter two are generated from the dataframe's columns with string addition:

new_df = pd.DataFrame({"code": df["code"],
                       "Address": df["city"] + ", " + df["state"]})
new_df.index = df["fname"] + " " + df["lname"]

which gives

>>> new_df
          code          Address
Alice Lee  PXY  Athens, Alabama
Nor Xi     ABC    Mesa, Arizona

We can now call to_dict with orient="index":

>>> d = new_df.to_dict(orient="index")
>>> d

{"Alice Lee": {"code": "PXY", "Address": "Athens, Alabama"},
 "Nor Xi": {"code": "ABC", "Address": "Mesa, Arizona"}}

To match your output, we wrap d with a dictionary:

>>> {"Employee": d}

{
   "Employee":{
      "Alice Lee":{
         "code":"PXY",
         "Address":"Athens, Alabama"
      },
      "Nor Xi":{
         "code":"ABC",
         "Address":"Mesa, Arizona"
      }
   }
}

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195438

Try:

names = df[["fname", "lname"]].apply(" ".join, axis=1)
addresses = df[["city", "state"]].apply(", ".join, axis=1)
codes = df["code"]

out = {"Employees": {}}
for n, a, c in zip(names, addresses, codes):
    out["Employees"][n] = {"code": c, "Address": a}

print(out)

Prints:

{
    "Employees": {
        "Alice Lee": {"code": "PXY", "Address": "Athens, Alabama"},
        "Nor Xi": {"code": "ABC", "Address": "Mesa, Arizona"},
    }
}

Upvotes: 1

Related Questions