Q:

How to fill empty values in a dataframe based on columns in another dataframe?

I have a dataframe called df1:
ID     Value       Name      Score
-1      10           A         -1
-1       5           B         -1
NaN     0.2       Track C     100
NaN     0.5       Track C     200
1        0           D        100
5        0           D        200

I want to fill the NaN in column ID with multiple rows of Score data from dataframe df2.
df2:
Score    ID
100      1
100      2
100      3
100      4
200      5
200      6
200      7

So that ultimately, my final dataframe looks like this:
df3:
ID     Value       Name      Score
-1      10           A         -1
-1       5           B         -1
1       0.2       Track C     100
2       0.2       Track C     100
3       0.2       Track C     100
4       0.2       Track C     100
5       0.5       Track C     200
6       0.5       Track C     200
7       0.5       Track C     200
1        0           D        100
5        0           D        200

How could I accomplish this?

A:

I have a solution, but it is not elegant, I plea experienced users to take a look at this.
to ease others, here are the code to setup the test case:
df1 = pd.DataFrame(
columns=\
'ID     Value       Name      Score'.split(),

data = [
re.split('\s{2,}', line)  for line in \
"""
-1      10           A         -1
-1       5           B         -1
NaN     0.2       Track C     100
NaN     0.5       Track C     200
1        0           D        100
5        0           D        200
""".strip().split('\n')  
],
)

df1 = df1.replace({'NaN':np.nan})

df2 = pd.DataFrame(

columns=\
'Score    ID'.split(),

data = [
re.split('\s{2,}', line)  for line in \
"""
100      1
100      2
100      3
100      4
200      5
200      6
200      7
""".strip().split('\n')  
],
)

and my solution is:
"""
the general first reaction is to pd.merge().
however the hurdle is, how to deal with the fillna of the column "ID".
mine works, but it is too hard coded.
"""

df = pd.merge(left=df1, right=df2, on='Score', how='left')

df['ID'] = df['ID_x'].fillna(df['ID_y'])

finalresult = df.drop(columns=['ID_x', 'ID_y']).drop_duplicates(subset=['ID','Name'])

OUTPUT:
   Value     Name Score  ID
0     10        A    -1  -1
1      5        B    -1  -1
2    0.2  Track C   100   1
3    0.2  Track C   100   2
4    0.2  Track C   100   3
5    0.2  Track C   100   4
6    0.5  Track C   200   5
7    0.5  Track C   200   6
8    0.5  Track C   200   7
9      0        D   100   1
13     0        D   200   5

A:

split your df, then using merge and concat back 
df1_1=df1.loc[df1.ID.isnull()].copy()
df1_2=df1.loc[df1.ID.notnull()].copy()
df1_1=df1_1.reset_index().drop('ID',1).merge(df2,on='Score',how='left').set_index('index')

yourdf=pd.concat([df1_1,df1_2],sort=False).sort_index()
yourdf
Out[645]: 
   Value    Name  Score   ID
0   10.0       A     -1 -1.0
1    5.0       B     -1 -1.0
2    0.2  TrackC    100  1.0
2    0.2  TrackC    100  2.0
2    0.2  TrackC    100  3.0
2    0.2  TrackC    100  4.0
3    0.5  TrackC    200  5.0
3    0.5  TrackC    200  6.0
3    0.5  TrackC    200  7.0
4    0.0       D    100  1.0
5    0.0       D    200  5.0

