Converting python dataframe into dictionary and then into a string

Question

I have a dataframe called users_df that looks like this

id	first_name	last_name	signup_date
1	James	Smith	2021-01-01
2	Mark	Adams	2021-03-02
3	Mary	Rose	2021-05-03

users_df = pd.DataFrame([[1, 'James', 'Smith', '2021-01-01'], [2, 'Mark', 'Adams', '2021-03-02'],[3, 'Mary', 'Rose', '2021-05-03']], columns=['id', 'first_name','last_name', 'signup_date'])

Which I am trying to convert into a dictionary first that looks like this:

user_dict = {
    "users": [
        {"id": 1, "user_fields": {"first_name": "James", "last_name": "Smith", "signup_date": "2021-01-01"}}, 
        {"id": 2, "user_fields": {"first_name": "Mark", "last_name": "Adams", "signup_date": "2021-03-02"}}, 
        {"id": 3, "user_fields": {"first_name": "Mary", "last_name": "Rose", "signup_date": "2021-05-03"}}
    ]
}

And then finally convert that into a string:

user_dict_string = '{"users": [{"id": 1, "user_fields": {"first_name": "James", "last_name": "Smith", "signup_date": "2021-01-01"}}, {"id": 2, "user_fields": {"first_name": "Mark", "last_name": "Adams", "signup_date": "2021-03-02"}}, {"id": 3, "user_fields": {"first_name": "Mary", "last_name": "Rose", "signup_date": "2021-05-03"}}]}'

The part where I am stuck is converting the dataframe into the dictionary while keeping the "id" part (see the example below when I do to_dict('index')) and then adding the "users" list and the nested "user_fields" parts.

All I could find is using this:

user_dict = user_list.to_dict('index')

But the results look like this which is not what I am after

{1: {'first_name': 'James', 'last_name': 'Smith', 'signup_date': '2021-01-01'}, 2: {'first_name': 'Mark', 'last_name': 'Adams', 'signup_date': '2021-03-02'}, 3: {'first_name': 'Mary', 'last_name': 'Rose', 'signup_date': '2021-05-03'}}

Any help is appreciated

Shubham Sharma · Accepted Answer · 2021-08-03 10:07:47Z

1

We can use dict comprehension to create the records in the desired format

{'users': [{'id': d.pop('id'), 'user_fields': d} for d in users_df.to_dict('r')]}

{'users': [{'id': 1,
            'user_fields': {'first_name': 'James',
                            'last_name': 'Smith',
                            'signup_date': '2021-01-01'}},
           {'id': 2,
            'user_fields': {'first_name': 'Mark',
                            'last_name': 'Adams',
                            'signup_date': '2021-03-02'}},
           {'id': 3,
            'user_fields': {'first_name': 'Mary',
                            'last_name': 'Rose',
                            'signup_date': '2021-05-03'}}]}

answered Aug 3, 2021 at 10:07

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

1 Comment

Anurag Dabas Over a year ago

that's look pretty neat

Serge Ballesta · Accepted Answer · 2021-08-03 10:08:10Z

You could use a comprehension to build the expected struct from

users_df = pd.DataFrame([[1, 'James', 'Smith', '2021-01-01'],
                         [2, 'Mark', 'Adams', '2021-03-02'],
                         [3, 'Mary', 'Rose', '2021-05-03']],
                        columns=['id', 'first_name','last_name', 'signup_date'])

You have already find that:

users_df.set_index('id').to_dict('index')

gives:

{1: {'first_name': 'James', 'last_name': 'Smith', 'signup_date': '2021-01-01'},
 2: {'first_name': 'Mark', 'last_name': 'Adams', 'signup_date': '2021-03-02'},
 3: {'first_name': 'Mary', 'last_name': 'Rose', 'signup_date': '2021-05-03'}}

Just add one step:

user_dict = {'users': [{'id': k, 'user_fields': v}
               for k, v in users_df.set_index('id').to_dict('index')
               .items()]}

to get:

{'users': [{'id': 1,
            'user_fields': {'first_name': 'James',
                            'last_name': 'Smith',
                            'signup_date': '2021-01-01'}},
           {'id': 2,
            'user_fields': {'first_name': 'Mark',
                            'last_name': 'Adams',
                            'signup_date': '2021-03-02'}},
           {'id': 3,
            'user_fields': {'first_name': 'Mary',
                            'last_name': 'Rose',
                            'signup_date': '2021-05-03'}}]}

And a simple json.dumps will convert it to a string...

Collectives™ on Stack Overflow

Converting python dataframe into dictionary and then into a string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related