Pandas DataFrame Methods: Arithmetic, Labels and Functions

Practise DataFrame arithmetic, labels, aggregation, alignment, Boolean checks, concatenation and function application with current Pandas.

Show Table of Contents ↓

Absolute values with abs() 🔝

Retain the signed list, single MATH column and two-column examples. Apply abs() to numeric columns; retain the original signed values separately.

import pandas as pd 
my_dict=([-1,-1,2,-3])
my_data = pd.DataFrame(data=my_dict)
print(my_data.abs())

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
#print(my_data['MATH'].abs())
print(abs(my_data['MATH'])) # MATH column

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(abs(my_data[['MATH','ENGLISH']]))

Expected output

   0
0  1
1  1
2  2
3  3
0    30
1    40
2    50
Name: MATH, dtype: int64
   MATH  ENGLISH
0    30       20
1    40       30
2    50       40

Add to numeric values 🔝

Add a scalar to a numeric table or selected numeric columns. Arithmetic between tables aligns labels; it is different from combining rows.

import pandas as pd 
my_dict=([-1,-1,2,-3])
my_data = pd.DataFrame(data=my_dict)
print(my_data.add(2))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
#print(my_data[['MATH']].add(2)) # applying to single column
print(my_data[['MATH','ENGLISH']].add(2)) # applying to two columns

Expected output

   0
0  1
1  1
2  4
3 -1
   MATH  ENGLISH
0   -28       22
1    42      -28
2   -48       42

Prefix Series and DataFrame labels 🔝

Series prefixes change row labels; DataFrame prefixes change column labels by default. The original four-item Series output incorrectly omitted its fourth item. See add_prefix().

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.Series(data=my_dict)
print(my_data.add_prefix('row_no_'))

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.DataFrame(data=my_dict)
print(my_data.add_prefix('col_no_'))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
my_label=pd.DataFrame()
print(my_data[['NAME']].add_prefix('f_name '))

Expected output

row_no_0    1
row_no_1    2
row_no_2    3
row_no_3    4
dtype: int64
   col_no_0
0         1
1         2
2         3
3         4
  f_name NAME
0        Ravi
1        Raju
2        Alex

Suffix Series and DataFrame labels 🔝

Suffixes change labels rather than cell contents. Retain the single-column and full-table cases. See add_suffix().

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.Series(data=my_dict)
print(my_data.add_suffix('_no_'))

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.DataFrame(data=my_dict)
print(my_data.add_suffix(' col_no_'))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
my_label=pd.DataFrame()
print(my_data[['NAME']].add_suffix(' (f_name)'))

Expected output

0_no_    1
1_no_    2
2_no_    3
3_no_    4
dtype: int64
   0 col_no_
0          1
1          2
2          3
3          4
  NAME (f_name)
0          Ravi
1          Raju
2          Alex

Aggregate numeric columns and groups 🔝

Use sum, min, max and mean on numeric columns explicitly. A text NAME column has no numeric mean; it must not be included in the mixed aggregation. The sales GroupBy example now has embedded synthetic product/store/quantity/price data. See GroupBy.

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.DataFrame(data=my_dict)
print(my_data.agg(['sum']))

import pandas as pd 
my_dict=([1,2,3,4])
my_data = pd.DataFrame(data=my_dict)
print(my_data.agg(['sum','min','max','mean']))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[30,40,50],
		 'ENGLISH':[20,30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.select_dtypes(include='number').agg(['sum','min','max','mean'])) # numeric columns

print(my_data['MATH'].agg(['sum','min','max','mean'])) # on one  columns

print(my_data[['MATH','ENGLISH']].agg(['sum','min','max','mean'])) # two columns

sales = pd.DataFrame({"product": ["Pen", "Pen", "Book"], "store": ["A", "A", "B"],
    "qty": [2, 3, 1], "price": [10.0, 12.0, 25.0]})
my_sale=sales.groupby(['product','store'],as_index=False).agg({'qty':'sum','price':'mean'})
print(my_sale)
assert my_sale.loc[my_sale["product"].eq("Pen"), "qty"].iloc[0] == 5

Expected output

      0
sum  10
         0
sum   10.0
min    1.0
max    4.0
mean   2.5
       ID   MATH  ENGLISH
sum   6.0  120.0     90.0
min   1.0   30.0     20.0
max   3.0   50.0     40.0
mean  2.0   40.0     30.0
sum     120.0
min      30.0
max      50.0
mean     40.0
Name: MATH, dtype: float64
       MATH  ENGLISH
sum   120.0     90.0
min    30.0     20.0
max    50.0     40.0
mean   40.0     30.0
  product store  qty  price
0    Book     B    1   25.0
1     Pen     A    5   11.0

Align column labels 🔝

axis=1 aligns columns only, leaving each original row index intact. outer takes the union; left takes the first table column labels. align() does not match people by name or ID; use merge() for key matching.

import pandas as pd 
my_dict1={'NAME':['Ravi','Alex'],
         'MATH':[30,40]}
my_dict2={'NAME':['Ravi','Raju','Rone'],
         'ENGLISH':[20,50,60]}
my_data1 = pd.DataFrame(data=my_dict1)
my_data2 = pd.DataFrame(data=my_dict2)
a1, a2 = my_data1.align(my_data2, join='outer', axis=1)
print(a1)
print(a2)

import pandas as pd 
my_dict1={'NAME':['Ravi','Alex'],
          'ID':[1,2],
         'MATH':[30,40]}
my_dict2={'NAME':['Ravi','Raju','Rone'],
          'ID':[1,3,4],
         'ENGLISH':[20,50,60]}
my_data1 = pd.DataFrame(data=my_dict1)
my_data2 = pd.DataFrame(data=my_dict2)
a1, a2 = my_data1.align(my_data2, join='left', axis=1)
print(a1)
print(a2)

Expected output

   ENGLISH  MATH  NAME
0      NaN    30  Ravi
1      NaN    40  Alex
   ENGLISH  MATH  NAME
0       20   NaN  Ravi
1       50   NaN  Raju
2       60   NaN  Rone
   NAME  ID  MATH
0  Ravi   1    30
1  Alex   2    40
   NAME  ID  MATH
0  Ravi   1   NaN
1  Raju   3   NaN
2  Rone   4   NaN

Check whether all values are truthy 🔝

For these examples, map(bool) explicitly converts strings and mixed numeric/Boolean cells to truth values before all(). Zero, False and the empty string are false; nonempty names and nonzero numbers are true. axis=0 reduces columns and axis=1 reduces rows. For real validation, use explicit comparisons and missing-value checks, not general truthiness.

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,False,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).all())

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,False,-50],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).all(axis=1))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[-30,40,-50],
		 'ENGLISH':[20,'',40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).all(axis=1))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,40,-50],
		 'ENGLISH':[20,30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).all(axis=1))

Expected output

NAME        True
ID          True
MATH       False
ENGLISH     True
dtype: bool
0     True
1    False
2     True
dtype: bool
0     True
1    False
2     True
dtype: bool
0    False
1     True
2     True
dtype: bool

Check whether any values are truthy 🔝

Use explicit Boolean conversion for the original text/mixed examples. any() returns true if at least one value is true along the chosen axis. A row with a name can be true even if all its marks are zero. Missing-value rules and nullable Boolean semantics need an explicit validation policy.

import pandas as pd 
my_dict={'NAME':['']}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).any())

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex','']}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).any())

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).any())

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).any(axis=1))

import pandas as pd 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(bool).any(axis=0))

Expected output

NAME    False
dtype: bool
NAME    True
dtype: bool
NAME        True
ID          True
MATH       False
ENGLISH     True
dtype: bool
0    True
1    True
2    True
dtype: bool
NAME        True
ID          True
MATH       False
ENGLISH     True
dtype: bool

Combine rows with concat() instead of append() 🔝

DataFrame.append() was removed in Pandas 2.0. Use pd.concat() for the original same-column and differing-column cases. sort=True orders column labels; ignore_index=True generates fresh row labels. Default outer joining retains all columns, introducing missing values where absent. verify_integrity=True rejects duplicate labels on the concatenation axis. See adding rows with concat().

import pandas as pd 
my_dict1={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[10,20,30],
		 'ENGLISH':[20,30,40]}
my_dict2={'NAME':['Ravi2','Raju2','Alex2'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[40,50,60]}
my_data1 = pd.DataFrame(data=my_dict1)
my_data2 = pd.DataFrame(data=my_dict2)
my_data1 = pd.concat([my_data1, my_data2], sort=True) 
print(my_data1)

my_data1 = pd.concat([my_data1.iloc[:3], my_data2], sort=True, ignore_index=True)

import pandas as pd 
my_dict1={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[20,-30,40]}
my_dict2={'SCIENCE':[7,8,9]}
my_data1 = pd.DataFrame(data=my_dict1)
my_data2 = pd.DataFrame(data=my_dict2)
my_data1 = pd.concat([my_data1, my_data2], sort=True) 
print(my_data1)

import pandas as pd 
my_dict1={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[10,20,30],
		 'ENGLISH':[20,30,40]}
my_dict2={'NAME':['Ravi2','Raju2','Alex2'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[40,50,60]}
my_data1 = pd.DataFrame(data=my_dict1)
my_data2 = pd.DataFrame(data=my_dict2)
#my_data1 = pd.concat([my_data1, my_data2], sort=True) 
my_data1= pd.concat([my_data1,my_data2]) 
print(my_data1)

Expected output

   ENGLISH  ID  MATH   NAME
0       20   1    10   Ravi
1       30   2    20   Raju
2       40   3    30   Alex
0       40   1     0  Ravi2
1       50   2     0  Raju2
2       60   3     0  Alex2
   ENGLISH   ID  MATH  NAME  SCIENCE
0     20.0  1.0   0.0  Ravi      NaN
1    -30.0  2.0   0.0  Raju      NaN
2     40.0  3.0   0.0  Alex      NaN
0      NaN  NaN   NaN   NaN      7.0
1      NaN  NaN   NaN   NaN      8.0
2      NaN  NaN   NaN   NaN      9.0
    NAME  ID  MATH  ENGLISH
0   Ravi   1    10       20
1   Raju   2    20       30
2   Alex   3    30       40
0  Ravi2   1     0       40
1  Raju2   2     0       50
2  Alex2   3     0       60

Apply functions along an axis 🔝

apply() receives a Series for each column by default or each row with axis=1; its return type depends on the function. Retain sum/max and the square-root example. Negative real values have no real square root, so the NumPy example deliberately produces NaN under a local error-state context. See apply().

import pandas as pd 
my_dict={'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)

print(my_data.apply(sum)) # 30 
print(my_data.apply(max)) # 40

import pandas as pd 
my_dict={'ENGLISH':[20,-30,40],'MATH':[40,50,70]}
my_data = pd.DataFrame(data=my_dict)

print(my_data.apply(sum,axis=0))  
print(my_data.apply(sum,axis=1))

import pandas as pd 
import numpy as my_np 
my_dict={'NAME':['Ravi','Raju','Alex'],
         'ID':[1,2,3],'MATH':[0,0,0],
		 'ENGLISH':[20,-30,40]}

my_data = pd.DataFrame(data=my_dict)
with my_np.errstate(invalid='ignore'):
    roots = my_data['ENGLISH'].apply(my_np.sqrt)
print(roots)
assert roots.isna().sum() == 1

Expected output

ENGLISH    30
dtype: int64
ENGLISH    40
dtype: int64
ENGLISH     30
MATH       160
dtype: int64
0     60
1     20
2    110
dtype: int64
0    4.472136
1         NaN
2    6.324555
Name: ENGLISH, dtype: float64

Map a function to each element 🔝

Use DataFrame.map(), available since Pandas 2.1, for the elementwise operation formerly called applymap(). Retain adding 1 or 10 to the whole numeric table and the selected MATH column. For simple arithmetic, vectorized addition is usually clearer than a Python callback.

import pandas as pd 
my_dict={'ENGLISH':[20,-30,40]}
my_data = pd.DataFrame(data=my_dict)
print(my_data.map(lambda x:x+1))

import pandas as pd 
my_dict={'ENGLISH':[20,-30,40],'MATH':[40,50,70]}
my_data = pd.DataFrame(data=my_dict)

print(my_data.map(lambda x:x+10))

import pandas as pd 
my_dict={'ENGLISH':[20,-30,40],'MATH':[40,50,70]}
my_data = pd.DataFrame(data=my_dict)

print(my_data[['MATH']].map(lambda x:x+10))

Expected output

   ENGLISH
0       21
1      -29
2       41
   ENGLISH  MATH
0       30    50
1      -20    60
2       50    80
   MATH
0    50
1    60
2    80

Check duplicate row labels 🔝

Concatenating the same row labels with verify_integrity=True raises an error; this does not validate business IDs.

left = pd.DataFrame({"mark": [10]}, index=[0])
right = pd.DataFrame({"mark": [20]}, index=[0])
try:
    pd.concat([left, right], verify_integrity=True)
except ValueError:
    print("Duplicate row labels rejected")
else:
    raise AssertionError("Expected duplicate labels")

Expected output

Duplicate row labels rejected

Exercise: summarise marks 🔝

Using Ravi, Raju and Alex with MATH marks 30, 40, 50 and ENGLISH marks 20, 30, 40, calculate total marks per student and the mean for each subject.

Exercise solution 🔝

Use numeric reductions for this task rather than arbitrary callbacks.

marks = pd.DataFrame({"MATH": [30, 40, 50], "ENGLISH": [20, 30, 40]}, index=["Ravi", "Raju", "Alex"])
print(marks.sum(axis=1))
print(marks.mean(axis=0))
assert marks.sum(axis=1).tolist() == [50, 70, 90]
assert marks.mean(axis=0).tolist() == [40.0, 30.0]

Expected output

Ravi    50
Raju    70
Alex    90
dtype: int64
MATH       40.0
ENGLISH    30.0
dtype: float64

Practice in Google Colab 🔝

Open in Google Colab View on GitHub

Run examples in order, inspect method results and try the exercise. Save a copy to keep edits.

Reference: Pandas DataFrame Methods documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here