1

I have two dataframes like df1, df2 below. I would like to:

  • filter df1: that is, remove rows and columns, so that it has the same index elements and columns as df2. The elements within the table for the columns and rows kept should be not be modified.
  • In addition, I would like to organize the rows and columns of this 'filtered' dataframe so that it has rows and columns in the same order as df2.

Dataframe df1 is:

index x_3 x_1 x_2
10 110 126 112
11 131 140 143
12 130 128 116
13 118 150 125
14 102 117 110
15 103 105 148
16 116 114 114
17 120 132 110

..and a second dataframe (df2) like:

index x_1 x_2 x_3
10 1 1 5
11 4 1 2
14 2 2 4
15 1 2 1
16 2 4 1

The final result would be df3, that is:

index x_1 x_2 x_3
10 126 112 110
11 140 143 131
14 117 110 102
15 105 148 103
16 114 114 116

Any insights?

smci
  • 32,567
  • 20
  • 113
  • 146
StatsScared
  • 517
  • 6
  • 20
  • Given that your index is unique, (i.e. doesn't have duplicates); since duplicates will break some approaches. Also when you say *'organize the columns of df3 like df1'* that's called *'df3 should have same column order as df1'*. – smci Feb 13 '21 at 07:12
  • Possible duplicate: [Pandas: replace values of one data frame with values of another data frame based on index and column](https://stackoverflow.com/questions/56133410/pandas-replace-values-of-one-data-frame-with-values-of-another-data-frame-based) – smci Feb 13 '21 at 07:26
  • Related: [answer on `.reindex_like()`](https://stackoverflow.com/a/47961242/202229) also [this](https://stackoverflow.com/questions/41260764/python-pandas-how-to-copy-column-values-regarding-an-index-column) – smci Feb 13 '21 at 07:29

2 Answers2

4

You can use .reindex_like to conform the index and columns of df1 according to index and columns of df2:

df3 = df1.reindex_like(df2)

>>> df3

       x_1  x_2  x_3
index               
10     126  112  110
11     140  143  131
14     117  110  102
15     105  148  103
16     114  114  116
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53
1
df1.loc[df2.index, df2.columns]

Shubham's answer is quite pythonic. Using loc is also a simple way to go from first principles.

supercooler8
  • 503
  • 2
  • 7