3

I have two data frames. I want to remove rows where the indexes do not occur in both data frames.

Here is an example of the data frames:

import pandas as pd
data = {'Correlation': [1.000000, 0.607340, 0.348844]}

df = pd.DataFrame(data, columns=['Correlation'])
df = df.rename(index={0: 'GINI'})
df = df.rename(index={1: 'Central government debt, total (% of GDP)'})
df = df.rename(index={2: 'Grants and other revenue (% of revenue)'})

data_2 =  {'Correlation': [1.000000, 0.607340, 0.348844, 0.309390, -0.661046]}

df_2 = pd.DataFrame(data_2, columns=['Correlation'])
df_2 = df_2.rename(index={0: 'GINI'})
df_2 = df_2.rename(index={1: 'Central government debt, total (% of GDP)'})
df_2 = df_2.rename(index={2: 'Grants and other revenue (% of revenue)'})
df_2 = df_2.rename(index={3: 'Compensation of employees (% of expense)'})
df_2 = df_2.rename(index={4: 'Central government debt, total (current LCU)'})

I have found this question: How to remove rows in a Pandas dataframe if the same row exists in another dataframe? but was unable to use it as I am trying to remove if the index name is the same.

I also saw this question: pandas get rows which are NOT in other dataframe but removes rows which are equal in both data frames but I also did not find this useful.

What I have thought to do is to transpose then concat the data frames and remove duplicate columns:

df = df.T

df_2 = df_2.T

df3 = pd.concat([df,df_2],axis = 1)

df3.iloc[: , ~df3.columns.duplicated()]

The problem with this is that it only removes one of the columns that is duplicated but I want it to remove both these columns.

Any help doing this would be much appreciated, cheers.

Alexander Caskie
  • 357
  • 3
  • 13

3 Answers3

1

You can just compare the indexes and use .loc to pull the relevant rows:

In [19]: df1 = pd.DataFrame(list(range(50)), index=range(0, 100, 2))

In [20]: df2 = pd.DataFrame(list(range(34)), index=range(0, 100, 3))

In [21]: df2.loc[df2.index.difference(df1.index)]
Out[21]:
     0
3    1
9    3
15   5
21   7
27   9
33  11
39  13
45  15
51  17
57  19
63  21
69  23
75  25
81  27
87  29
93  31
99  33
Randy
  • 14,349
  • 2
  • 36
  • 42
0

you can simply do this for indices in df2 but not in df1

df_2[~df_2.index.isin(df.index)]

                                              Correlation
Compensation of employees (% of expense)         0.309390
Central government debt, total (current LCU)    -0.661046
It_is_Chris
  • 13,504
  • 2
  • 23
  • 41
0

I have managed to work this out by adapting the answers already submitted:

df_2[df_2.index.isin(df.index)]
Alexander Caskie
  • 357
  • 3
  • 13