Question 174725 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Pandas how to merge all the rows json columns value into one row with single column as JSON?

I have a task to upload MySQL table JSON data to the rest API. I am loading the data in the Pandas dataframe and transforming it and trying to make a single JSON value to post to the API endpoint.

The task is to collect all rows of MySQL table json column data and embeds it into a single JSON data to post all rows of JSON data in a single post request of Python.

Mysql table rows data example value as below –

id source action object_pk json_data
1 A Update 701 [{"keys": {"PlayerID": "701"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]
2 A Update 702 [{"keys": {"PlayerID": "702"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]

After loading, this data into the pandas dataframe would like to merge all rows of JSON data into a single valid JSON data.

Trying for expected output as below in Pandas df –

json_data
[{"keys": {"PlayerID": "701"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}},{"keys": {"PlayerID": "702"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]

My goal is to load mysql table data every 10 minutes interval and send all rows of JSON data in a single post to the rest API endpoint.

r = requests.post("https://abc/rowset", json=json_data,headers=headers)

Not sure how to accomplish this in Pandas. Any pointers or code samples will be highly appreciated! Thanks in advance

Tags:

2

Answers


  1. 0

    According to your last comment, the content of the json_data column is a (json) string representation of a list containing one single element which is a dict. IFAIK, there is no way to directly convert that column to a single json.

    A possible way is to:

    1. convert each element to a valid list
    2. extract the first (and single) element of that list
    3. make a list from all of those elements
    4. convert the list to a json string

    It could be implemented as:

    import json

...
js = json.dumps([json.loads(elt)[0] for elt in df['json_data']])

    with your shown data, I got:

    '[{"keys": {"PlayerID": "701"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}, {"keys": {"PlayerID": "702"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]'
    Login or Signup to reply.
  2. 0

    Not sure if I understood you correctly, but you can try something like this:

    df['json_data'].to_dict()

    This should return a dict which is technically a JSON-looking file, where each row index will be the key & whole df['json_data'] column as value, i.e.:

    {
0: '[{"keys": {"PlayerID": "701"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]'
1: '[{"keys": {"PlayerID": "702"}, "values": {"EMail": "[email protected]", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]'
}

    etc.
    Not sure why each json_data row is in [] brackets, maybe you would want to remove them from with the help of substr or any similar method.

    more about to_dict() method

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search