CODEWITHSUNDEEP
pythonpandasdataframe
daragh
daragh

Reputation: 537

Make Pandas Dataframe column equal to value in another Dataframe based on index

I have 3 dataframes as below

df1

id     first_name      surname     state
1                  
88     
190
2509  
....

df2

id     given_name      surname     state    street_num
17     John            Doe         NY       5
88     Tom             Murphy      CA       423
190    Dave            Casey       KY       250
....

df3

id     first_name      family_name state    car
1      John            Woods       NY       ford
74     Tom             Kite        FL       vw
2509   Mike            Johnson     KY       toyota

Some id's from df1 are in df2 and others are in df3. There are also id's in df2 and df3 that are not in df1.

EDIT: there are also some id's in df1 that re not in either df2 or df3.

I want to fill the columns in df1 with the values in the dataframe containing the id. However, I do not want all columns (so i think merge is not suitable). I have tried to use the isin function but that way I could not update records individually and got an error. This was my attempt using isin:

df1.loc[df1.index.isin(df2.index), 'first_name'] = df2.given_name

Is there an easy way to do this without iterating through the dataframes checking if index matches?

Upvotes: 1

Views: 3328

Answers (1)

jezrael
jezrael

Reputation: 862521

I think you first need to rename your columns to align the DataFrames in concat and then reindex to filter by df1.index and df1.columns:

df21 = df2.rename(columns={'given_name':'first_name'})
df31 = df3.rename(columns={'family_name':'surname'})
df = pd.concat([df21, df31]).reindex(index=df1.index, columns=df1.columns)
print (df)
     first_name  surname state
d                             
1          John    Woods    NY
88          Tom   Murphy    CA
190        Dave    Casey    KY
2509       Mike  Johnson    KY

EDIT: If need intersection of indices only: 

df4 = pd.concat([df21, df31])
df = df4.reindex(index=df1.index.intersection(df4.index), columns=df1.columns)

Upvotes: 3

Related Questions