Subsetting DataFrame based on column names of another DataFrame

Question

I have two DataFrames and I want to subset df2 based on the column names that intersect with the column names of df1. In R this is easy.

R code:

df1 <- data.frame(a=rnorm(5), b=rnorm(5))
df2 <- data.frame(a=rnorm(5), b=rnorm(5), c=rnorm(5))

df2[names(df2) %in% names(df1)]
           a          b
1 -0.8173361  0.6450052
2 -0.8046676  0.6441492
3 -0.3545996 -1.6545289
4  1.3364769 -0.4340254
5 -0.6013046  1.6118360

However, I'm not sure how to do this in pandas.

pandas attempt:

df1 = pd.DataFrame({'a': np.random.standard_normal((5,)), 'b': np.random.standard_normal((5,))})
df2 = pd.DataFrame({'a': np.random.standard_normal((5,)), 'b': np.random.standard_normal((5,)), 'c': np.random.standard_normal((5,))})

df2[df2.columns in df1.columns]

This results in TypeError: unhashable type: 'Index'. What's the right way to do this?

miradulo · Accepted Answer · 2017-04-10 19:35:53Z

2

If you need a true intersection, since .columns yields an Index object which supports basic set operations, you can use &, e.g.

df2[df1.columns & df2.columns]

or equivalently with Index.intersection

df2[df1.columns.intersection(df2.columns)]

However if you are guaranteed that df1 is just a column subset of df2 you can directly use

df2[df1.columns]

or if assigning,

df2.loc[:, df1.columns]

Demo

>>> df2[df1.columns & df2.columns]
          a         b
0  1.952230 -0.641574
1  0.804606 -1.509773
2 -0.360106  0.939992
3  0.471858 -0.025248
4 -0.663493  2.031343

>>> df2.loc[:, df1.columns]
          a         b
0  1.952230 -0.641574
1  0.804606 -1.509773
2 -0.360106  0.939992
3  0.471858 -0.025248
4 -0.663493  2.031343

edited Apr 10, 2017 at 19:35

answered Apr 10, 2017 at 19:22

miradulo

29.8k7 gold badges86 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2285236 · Accepted Answer · 2017-04-10 19:26:24Z

1

The equivalent would be:

df2[df1.columns.intersection(df2.columns)]
Out: 
          a         b
0 -0.019703  0.379820
1  0.040658  0.243309
2  1.103032  0.066454
3 -0.921378  1.016017
4  0.188666 -0.626612

With this, you will not get a KeyError if a column in df1 does not exist in df2.

answered Apr 10, 2017 at 19:26

user2285236

Collectives™ on Stack Overflow

Subsetting DataFrame based on column names of another DataFrame

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related