pandas join two json columns and combine them but results in a key error

Question

I am attempting to join two json columns using Pandas.

import pandas as pd

students_df = pd.DataFrame([['Jay',16,'Soccer'],['Jay',26,'Soccer'],
                   ['Jack',19, 'FootBall'],
                   ['Dorsey',19, 'Dining'],
                   ['Mark',18, 'Swimming']],
                  columns = ['Name','Age', 'Sport'])
                  
courses_df = pd.DataFrame([['Jay','MS'],
                   ['Jay','Music'],
                   ['Dorsey','Music'],
                   ['Dorsey', 'Piano'],
                   ['Mark','MS']],
                  columns = ['Name','Course'])
                  
students_df_json = students_df.groupby(['Name']).apply(lambda x: x.drop(columns='Name').to_json(orient='records')).reset_index(name="students_json")          
courses_df_json = courses_df.groupby(['Name']).apply(lambda x: x.to_json(orient='records')).reset_index(name="courses_json")

I want to join the two dataframes and then concatenate the two json columns students_json and courses_json

pd.merge(students_df_json , courses_df_json, on="Name")

My try

pd.merge(students_df_json, courses_df_json, on="Name").apply(        
     lambda x: x["students_json"] + x["courses_json"]                 
 )

But this results in a key error

KeyError: 'students_json'

Also I have another question what is the variable x referring to in the lambda ?

`apply(..., axis=1)` to apply lambda for each row – Psidom Mar 04 '22 at 05:56 — Psidom, Mar 04 '22 at 05:56
Hi Psidom Thanks it works – jhon.smith Mar 04 '22 at 06:00 — jhon.smith, Mar 04 '22 at 06:00

score 1 · Answer 1 · answered Mar 04 '22 at 06:03

1

Usually the lambda x refers to the function input variable x. In your case when you apply the function on the dataframe it is by default on the axis=0 which is row wise values.

For your requirement I believe you need to apply through axis=1 for column wise values as mentioned below.

pd.merge(students_df_json, courses_df_json, on="Name").apply(        
     lambda x: x["students_json"] + x["courses_json"], axis=1
 )

answered Mar 04 '22 at 06:03

Abhi

1,080
1
7
21

Hi is there a way to annotate the lambda x: as a data frame so intellisense can help me?For example in python we can annotate a variable name:str – jhon.smith Mar 04 '22 at 06:04
In lambda x : x is not a dataframe type, it is pandas series instead and not sure how to annotate non native datatypes of Python in intellisense – Abhi Mar 04 '22 at 06:08

jezrael · Accepted Answer · 2022-03-04T06:07:31.620

1

For vectorized join dont use apply, only use + with Series:

df = pd.merge(students_df_json, courses_df_json, on="Name", how='left')
s = df["students_json"] + df["courses_json"]

edited Mar 04 '22 at 06:07

answered Mar 04 '22 at 06:05

jezrael

822,522
95
1,334
1,252

can we merge multiple data frames on a key and tell pandas that i need a left join ? – jhon.smith Mar 04 '22 at 06:07
@jhon.smith - How many DataFrames? It is list of DataFrames? – jezrael Mar 04 '22 at 06:07
@jhon.smith - something like [this](https://stackoverflow.com/a/38978237/2901002) ? – jezrael Mar 04 '22 at 06:08

pandas join two json columns and combine them but results in a key error

2 Answers2