CODEWITHSUNDEEP
pythonpandas
asimo
asimo

Reputation: 2500

subtracting a dataframe(or its only column) from a series

I am trying to subtract a dataframe(which only has one column) from a series.

df['newcol'] = df['Col A'] - df.filter(regex="INT : Q1")

However, I get the following error: 

 Exception: Data must be 1-dimensional

df['Col A'] becomes a series.

df.filter(regex="INT : Q1") becomes a dataframe

Can you please advise a work around here.

df['Col A']
Out[126]: 
0       0.000000e+00
1       0.000000e+00
2       0.000000e+00
3       0.000000e+00
4       0.000000e+00
5       0.000000e+00
6       1.046203e+05
7      -8.081900e+02
.............
 .......


df.filter(regex="INT : Q1")
Out[127]: 
Quarter_incomeGroup_Final  INT : Q1_2018
  0                           0.000000e+00
  1                           4.997991e+05
  2                           7.915359e+04
  3                           4.837797e+04 
       .............
      ..............

Upvotes: 1

Views: 42

Answers (1)

jezrael
jezrael

Reputation: 863301

Use Series.rsub for subtract from right, but output is not new column but DataFrame:

df = df.filter(regex="INT : Q1").rsub(df['Col A'], axis=0)

Sample:

df = pd.DataFrame({'INT : Q1 1':[4,5,4,5,5,4],
                   'INT : Q1 2':[7,8,9,4,2,3],
                   'INT : Q1 3':[1,3,5,7,1,0],
                   'Col A':[5,3,6,9,2,4]})

print (df)
   INT : Q1 1  INT : Q1 2  INT : Q1 3  Col A
0           4           7           1      5
1           5           8           3      3
2           4           9           5      6
3           5           4           7      9
4           5           2           1      2
5           4           3           0      4

df = df.filter(regex="INT : Q1").rsub(df['Col A'], axis=0)
print (df)
   INT : Q1 1  INT : Q1 2  INT : Q1 3
0           1          -2           4
1          -2          -5           0
2           2          -3           1
3           4           5           2
4          -3           0           1
5           0           1           4

If want create new column - filter can return one or more columns, so possible solution is select first column by iloc:

df['newcol'] = df.filter(regex="INT : Q1").rsub(df['Col A'], axis=0).iloc[:, 0]

Upvotes: 1

Related Questions