Mapping column dataframe with another dataframe-CodePudding

I have a pandas data frame (df1) with a column that has some 'NaN' values that I want to replace with the values that I have in another data frame (df2).

This is a part of the data frame (df1) with NaN that has to be mapped:

reporting_date_id   filing_date_id
    19910930          NaN
    19920930          NaN

This is the dataframe (df2) that I want to use to map, it's a bit tricky cause they have the same column name

reporting_date_id   filing_date_id
    19910930          19911118
    19920930          19921116
    19930930          19931122

I was trying to do it in this way but it doesn't seems to work

for n in range(len(df1)):
    if df1['filing_date_id'].isna().loc[n]==True:
        fix_date=df2[df2['reporting_date_id']==df1['reporting_date_id'].loc[n]]['filing_date_id']
        df1['filing_date_id'].loc[n]=fix_date

CodePudding user response：

You can mapping column reporting_date_id by another DataFrame by Series.map and then use it for replace missing values in Series.fillna:

s = df2.set_index('reporting_date_id')['filing_date_id']
df1['filing_date_id'] = df1['filing_date_id'].fillna(df1['reporting_date_id'].map(s))

CodePudding user response：

Align by index and use fillna. Then reset the index again.

idx = 'reporting_date_id'
result = df1.set_index(idx).fillna(df2.set_index(idx)).reset_index()

Demo:

>>> df1
   reporting_date_id  filing_date_id
0           19910930             NaN
1           19920930             NaN
>>> df2
   reporting_date_id  filing_date_id
0           19910930        19911118
1           19920930        19921116
2           19930930        19931122
>>> idx = 'reporting_date_id'
>>> result = df1.set_index(idx).fillna(df2.set_index(idx)).reset_index()
>>> result
   reporting_date_id  filing_date_id
0           19910930      19911118.0
1           19920930      19921116.0

CodePudding user response：

I personally prefer @jezrael answer, but if you are interested in using a for loop over the dataframe rows, you can use code below:

df1.set_index("reporting_date_id", inplace=True)
df2.set_index("reporting_date_id", inplace=True)
for index, row in df1.iterrows():
  if row["filing_date_id"] != row["filing_date_id"] or row["filing_date_id"] == None:
    df1.loc[index , "filing_date_id"] = df2.loc[index]["filing_date_id"]
df1

Output

reporting_date_id	filing_date_id
19910930	19911118
19920930	19911118

CodePudding user response：

import pandas as pd

df1 = pd.DataFrame(
    {
        "reporting_date_id": [19910930, 19920930],
        "filing_date_id": [None, None],
    }
)
#    repdateid filing_date_id
# 0   19910930           None
# 1   19920930           None

df2 = pd.DataFrame(
    {
        "reporting_date_id": [19910930, 19920930, 19930930],
        "filing_date_id": [19911118, 19921116, 19931122],
    }
)
# repdateid  filing_date_id
# 0   19910930        19911118
# 1   19920930        19921116
# 2   19930930        19931122

result = pd.merge(df1, df2, on=["reporting_date_id", "reporting_date_id"])

result.drop(['filing_date_id_x'], axis=1)

This will keep both of the columns in case both have different values for the same reporting_date_id. If not you can always drop the NaN column as I did above.

Output:

   repdateid filing_date_id_x  filing_date_id_y
0   19910930             None          19911118
1   19920930             None          19921116