Python - Dataframe Merge + JSON

Question

I am trying to merge 2 data frames by using the id and then trying to save the result in the JSON file.

| |id|a|b

|1| 5|1|4

|2|10|2|5

|3|15|3|6

     +

| |id|a |b

|1| 5|10|13

|2|10|11|14

|3|15|12|15

     =

| |id|  a  |  b

|1| 5|1, 10|4, 13

|2|10|2, 11|5, 14

|3|15|3, 12|6, 15

Answer 1

Try the below code:

df3 = pd.concat([df1, df2]).groupby('id', as_index=False).agg({'a': lambda x: ', '.join(map(str, x)), 'b': lambda x: ', '.join(map(str, x))})

print(df3)

Output:

   id      a      b

0   5  1, 10  4, 13

1  10  2, 11  5, 14

2  15  3, 12  6, 15

you can use the below code to convert to json

print(df3.to_json())

Output:

{

  "id": {

    "0": 5,

    "1": 10,

    "2": 15

  },

  "a": {

    "0": "1, 10",

    "1": "2, 11",

    "2": "3, 12"

  },

  "b": {

    "0": "4, 13",

    "1": "5, 14",

    "2": "6, 15"

  }

}

If you are a beginner and want to know more about Python the do check out the python for data science course