Subtracting rows between data frames in pandas

Question

I have two dataframes,

df1
Name   | std
kumar  | 8
Ravi   | 10
Sri    | 2
Ram    | 4


df2,
Name   | std
Sri    | 2
Ram    | 4

I want to subtract df2 rows from df1 and I tried,

df1.subtract(df2,fill_value=None)

but I am getting error,

 TypeError: unsupported operand type(s) for -: 'str' and 'str'

My desired output,

df3
Name   | std
kumar  | 8
Ravi   | 10

It is not clear what the column names are in your 'dataframe'. The method `subtract` will align the dataframe on matching indices and columns (even the index names need to be identical). Try to paste the output of `df.iloc[:5, :5]` so that we can get a better idea what your data looks like. — Alexander, Aug 02 '17 at 07:26

score 9 · Answer 1 · answered Aug 02 '17 at 07:40

9

Use pd.Series.isin

df1[~df1.Name.isin(df2.Name)]

    Name  std
0  kumar    8
1   Ravi   10

answered Aug 02 '17 at 07:40

piRSquared

285,575
57
475
624

Any possible issues with this approach? – Lobstw Nov 04 '19 at 13:47
No. This is a solid approach. – piRSquared Nov 04 '19 at 14:46

jezrael · Accepted Answer · 2017-08-02T09:23:25.900

3

You an use merge with parameter indicator and outer join, query for filtering and then remove helper column with drop:

DataFrames are joined on all columns, so on parameter can be omit.

df1 = pd.DataFrame({'Name':['kumar','Ravi','Sri','Ram'],
                    'std':[8,10,2,4],
                    'col':list('abcd')})
print (df1)
    Name col  std
0  kumar   a    8
1   Ravi   b   10
2    Sri   c    2
3    Ram   d    4

df2 = pd.DataFrame({'Name':['Sri','Ram'],
                    'std':[2,4],
                    'col':list('cd')})
print (df2)
  Name col  std
0  Sri   c    2
1  Ram   d    4

df3 = pd.merge(df1, df2, how='outer', indicator=True)
        .query('_merge=="left_only"')
        .drop('_merge',1)
print (df3)
    Name col  std
0  kumar   a    8
1   Ravi   b   10

edited Aug 02 '17 at 09:23

answered Aug 02 '17 at 07:20

jezrael

822,522
95
1,334
1,252

there is more columns with strings? – jezrael Aug 02 '17 at 07:24
your edited version gives, only the names column, I want all the columns. I have 6 string columns in my actual dataframe – Pyd Aug 02 '17 at 07:32
I edited my question, can you pls check my desired output – Pyd Aug 02 '17 at 07:35
What is output if in second `df2` is `Ram | 3` ? – jezrael Aug 02 '17 at 07:43
same as my desired output – Pyd Aug 02 '17 at 09:02
I am trying to remove the rows which are present in both df1 and df2 – Pyd Aug 02 '17 at 09:03
only by column `Name` ? Or by all columns? – jezrael Aug 02 '17 at 09:09
I completely rewrite answer, please check it. – jezrael Aug 02 '17 at 09:23
@pyd - There is difference between my and piRSquared answer - if need check only column `Name`, beter is `pir` answer, but if need check all columns, need my answer. – jezrael Aug 02 '17 at 11:28
https://stackoverflow.com/questions/45522478/how-to-remove-entire-column-if-a-particular-row-has-duplicate-values-in-a-datafr – Pyd Aug 05 '17 at 13:30

DYZ · Answer 3 · 2017-08-02T07:26:52.230

0

You "numbers" are not numbers but strings. Make them numbers first. If only some columns are numeric, select them before conversion:

numeric = ["c1", "c2", "c3", ...] # All numeric columns
df1.set_index('Name')[numeric].astype(int) \
- df2.set_index('Name')[numeric].astype(int)

edited Aug 02 '17 at 07:26

answered Aug 02 '17 at 07:25

DYZ

55,249
10
64
93

we can change, but I have some more string columns – Pyd Aug 02 '17 at 07:25
Then you have to select numeric columns before subtraction. See updated answer. – DYZ Aug 02 '17 at 07:26

Subtracting rows between data frames in pandas

3 Answers3