Python - Dataframe Merge + JSON

Question

| |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

I am trying to merge two dataframes by id and save the result in json file.

Have you tried something? Post your code here.

Siva Shanmugam
– Siva Shanmugam

2020-08-27 05:43:12 +00:00
Commented Aug 27, 2020 at 5:43 — Siva Shanmugam
– Siva Shanmugam, Commented Aug 27, 2020 at 5:43

deadshot · Accepted Answer · 2020-08-27 05:56:15Z

2

Using pd.concat and df.groupby and df.agg

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 df.to_json() 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"
  }
}

edited Aug 27, 2020 at 5:56

answered Aug 27, 2020 at 5:43

deadshot

9,0774 gold badges23 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Python - Dataframe Merge + JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related