CODEWITHSUNDEEP
pythonpandasdataframe
Kallol
Kallol

Reputation: 2189

Merge two or more tables with common column values in descending order and repeat values if not available

I have three data frames like this,

df1
col1    col2     fix_col
 p       q         1
 q       e         4
 x       y         7

df2
col3    col4     fix_col
 t        u       2
 v        w       6

df3
col5    col6     fix_col
 x        r        5

Assuming that for each data frame the fix_col column values are sorted in descending order.

Now I want to merge these three data frames in a way that, col3 values will be in descending order, irrespective of other columns. But the column value is not presents, it will repeat the above or below (if the above value is not present) value.

The desired data frame should look like,

df
 col1    col2    col3    col4    col5    col6    fix_col
  p        q       t      u        x       r         1
  p        q       t      u        x       r         2
  p        e       t      u        x       r         4
  p        e       t      u        x       r         5
  p        e       v      w        x       r         6
  x        y       v      w        x       r         7

I could do this using a loop, But this will take longer time to execute,

Looking for shortcut/pythonic way to do it

Upvotes: 2

Views: 47

Answers (1)

jezrael
jezrael

Reputation: 862751

Use concat with DataFrame.sort_values and last replace missing values forward and back filling:

df = pd.concat([df1, df2, df3], sort=True).sort_values('fix_col').ffill().bfill()
print (df)
  col1 col2 col3 col4 col5 col6  fix_col
0    p    q    t    u    x    r        1
0    p    q    t    u    x    r        2
1    q    e    t    u    x    r        4
0    q    e    t    u    x    r        5
1    q    e    v    w    x    r        6
2    x    y    v    w    x    r        7

Upvotes: 3

Related Questions