CODEWITHSUNDEEP
pythonpandas
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

merging 2 data frames in pandas with user defined values in one column

we have 2 dataframes:

**df**

ASSET_KEY IS_PRESENT FEED_NAME
1
2
3
4
5

df1
ASSET_KEY FEED_NAME
1           XYZ 
2           PQR
4           ABC

Initially first data frame's IS_PRESENT and FEED_NAME is all null.

We need to populate IS_PRESENT-> YES/NO and FEED_NAME with df1's FEED_NAME if it is present.

Expected output:

df

ASSET_KEY IS_PRESENT FEED_NAME
1           YES     XYZ
2           YES     PQR
3           NO
4           YES     ABC
5           NO

I tried merging but not sure HOW to populate IS_PRESENT. what is the best way to achieve this in pandas?

Upvotes: 0

Views: 42

Answers (2)

Joonyoung Park
Joonyoung Park

Reputation: 484


import pandas as pd

### inputs ###############################################
# df to change
d1 = {"ASSET_KEY": [1, 2, 3, 4, 5],
      "IS_PRESENT": ["", "", "", "", ""],
      "FEED_NAME": ["", "", "", "", ""]}
df1 = pd.DataFrame(d1)

# df to ref
d2 = {"ASSET_KEY": [1, 2, 4],
      "FEED_NAME": ["XYZ", "PQR", "ABC"]}
df2 = pd.DataFrame(d2)

### solution ##############################################
# 1. merge on left (df)
df3 = pd.merge(df1[["ASSET_KEY", "IS_PRESENT"]], df2, on="ASSET_KEY", how="left")

# 2. fill "IS_PRESENT"
df3.loc[df3["FEED_NAME"].isnull(), "IS_PRESENT"] = "NO"
df3.loc[df3["FEED_NAME"].notnull(), "IS_PRESENT"] = "YES"

# 3. fill NaNs with ""
df3.fillna('', inplace=True)

print(df3)

Upvotes: 0

BENY
BENY

Reputation: 323346

Let us try

out = df.merge(df1.assign(IS_PRESENT='Yes')).fillna({'IS_PRESENT' : 'No'})

Upvotes: 2

Related Questions