CODEWITHSUNDEEP
pythonpandasdataframe
PV8
PV8

Reputation: 6270

Merging with filling up values (missing entries / indexes)

I have a leading data table, which looks like:

df
number index columnA
A       000    10
A       001    20
A       002    10
A       003    10
A       004    20
B       000     1
B       001     0
B       002     3

which have always all index entries for all numbers. My second dataframe missing some values:

df2
number index columnC
A       000   1
A       003   2
B       000   0

How can I merge the dataframe, that I duplicate the entries in the df2 so that it is mapped like this:

dfmerge
number  index columnA columnC
A       000    10      1
A       001    20      1
A       002    10      1
A       003    10      2
A       004    20      2
B       000     1      0
B       001     0      0
B       002     3      0

so that the mapping from df2 takes always the entrie where the number in df2 is smaller then the number in df.

Upvotes: 2

Views: 38

Answers (1)

jezrael
jezrael

Reputation: 863331

I believe you need left join with forward filling missing values:

df = df1.merge(df2, how='left').ffill()
print (df)
  number index columnA columnC
0      A   000      10       1
1      A   001      20       1
2      A   002      10       1
3      A   003      10       2
4      A   004      20       2
5      B   000       1       0
6      B   001       0       0
7      B   002       3       0

Upvotes: 2

Related Questions