Removing rows from one DataFrame based on rows from another DataFrame

Question

I have two different dataframes with two different lengths of rows. I want df1 to match df2 but I don't want to create a new dataframe in the process (no merge).

df1
    0             Alameda
    1              Alpine
    2              Amador
    3               Butte
    4           Calaveras
    5              Colusa
    6        Contra Costa
    7           Del Norte
    8           El Dorado
    9              Fresno
    10              Glenn
    11           Humboldt
    12           Imperial
    13               Inyo
    14               Kern
    15              Kings
    16               Lake
    17             Lassen
    18        Los Angeles
    19             Madera
    20              Marin
    21           Mariposa
    22          Mendocino
    23             Merced
    24              Modoc
    25               Mono
    26           Monterey
    27               Napa
    28             Nevada
    29             Orange
    30             Placer
    31             Plumas
    32          Riverside
    33         Sacramento
    34         San Benito
    35     San Bernardino
    36          San Diego
    37      San Francisco
    38        San Joaquin
    39    San Luis Obispo
    40          San Mateo
    41      Santa Barbara
    42        Santa Clara
    43         Santa Cruz
    44             Shasta
    45             Sierra
    46           Siskiyou
    47             Solano
    48             Sonoma
    49         Stanislaus
    50             Sutter
    51             Tehama
    52            Trinity
    53             Tulare
    54           Tuolumne
    55            Ventura
    56               Yolo
    57               Yuba

df2
    0             Alameda
    1              Amador
    2               Butte
    3           Calaveras
    4              Colusa
    5        Contra Costa
    6           Del Norte
    7           El Dorado
    8              Fresno
    9               Glenn
    10           Humboldt
    11           Imperial
    12               Inyo
    13               Kern
    14              Kings
    15               Lake
    16             Lassen
    17        Los Angeles
    18             Madera
    19              Marin
    20           Mariposa
    21          Mendocino
    22             Merced
    23               Mono
    24           Monterey
    25               Napa
    26             Nevada
    27             Orange
    28             Placer
    29             Plumas
    30          Riverside
    31         Sacramento
    32         San Benito
    33     San Bernardino
    34          San Diego
    35      San Francisco
    36        San Joaquin
    37    San Luis Obispo
    38          San Mateo
    39      Santa Barbara
    40        Santa Clara
    41         Santa Cruz
    42             Shasta
    43           Siskiyou
    44             Solano
    45             Sonoma
    46         Stanislaus
    47             Sutter
    48             Tehama
    49             Tulare
    50            Ventura
    51               Yolo
    52               Yuba

Is there a way to modify a column's rows in a dataframe using a column's rows from a different dataframe? Again I want to keep the dataframes separate, but the goal is to get the dataframes to have the same number of rows containing the same values.

So, you're trying to avoid a merge because it produces a new dataframe, but in the process you call df.drop which creates a new copy per each iteration... maybe you should focus on correctness first. — cs95
– cs95, Commented May 1, 2018 at 20:37
So I'm clear on what you want, you're trying to drop rows in such a way that df1.equals(df2) returns True, correct? There are certainly more performant ways than others to do the same thing, however, nothing that modifies your data without creating a copy in memory — cs95
– cs95, Commented May 1, 2018 at 20:43

cs95 · Accepted Answer · 2018-05-01 20:44:39Z

2

Since you just want common rows, you can compute them quickly using np.intersect1d:

i = df1.values.squeeze()
j = df2.values.squeeze()
df1 = pd.DataFrame(np.intersect1d(i, j))

And have df2 just become a copy of df1:

df2 = df1.copy(deep=True)

answered May 1, 2018 at 20:44

cs95

406k106 gold badges744 silver badges797 bronze badges

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

Comments

BENY · Accepted Answer · 2018-05-01 20:49:53Z

0

Using duplicated

s=pd.concat([df1,df2],keys=[1,2])
df1,df2=s[s.duplicated(keep=False)].loc[1],s[s.duplicated(keep=False)].loc[1]

answered May 1, 2018 at 20:49

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Removing rows from one DataFrame based on rows from 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