Operations on Rows and Columns in DataFrames

Once a DataFrame exists, we are not stuck with it as-is. We can perform some basic operations on its rows and columns — selection, deletion, addition, and renaming — and this section works through each of them in turn.

Adding a new column to a DataFrame is as simple as assigning a list of values to a new column label. Consider the DataFrame ResultDF defined earlier (5 students × 3 subjects). To add a column for another student, 'Preeti', we write:

>>> ResultDF['Preeti'] = [89, 78, 76]
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     92        89    81       94      89
Science     91     81        91    71       95      78
Hindi       97     96        88    67       99      76

The rule is: assigning values to a column label that does not exist creates a new column at the end of the DataFrame.

Updating an existing column. If the column label already exists, the very same assignment statement updates the values of that column instead of creating a new one:

>>> ResultDF['Ramit'] = [99, 98, 78]
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     99        89    81       94      89
Science     91     98        91    71       95      78
Hindi       97     78        88    67       99      76

Ramit's marks have been replaced by the new list; everything else is untouched.

Setting an entire column to one value. We can also change the data of a whole column to a single particular value. The following statement sets marks = 90 in all subjects for the column 'Arnab':

>>> ResultDF['Arnab'] = 90
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     99        89    81       94      89 …

A new row is added to a DataFrame using the DataFrame.loc[] method. Consider ResultDF, which has three rows for the three subjects — Maths, Science and Hindi:

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     92        89    81       94      89
Science     91     81        91    71       95      78
Hindi       97     96        88    67       99      76

Suppose we need to add the marks for the English subject. We assign a list of values to the new row label through loc:

>>> ResultDF.loc['English'] = [95, 86, 95, 80, 95, 99]
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     92        89    81       94      89
Science     91     81        91    71       95      78
Hindi       97     96        88    67       99      76
English     95     86        95    80       95      99

Duplicate row labels update, they do not append. We cannot use this method to add a row with an already existing (duplicate) index label. If the label already exists, that row is updated instead — for example, assigning to 'English' again simply replaces the English row's values:

>>> ResultDF.loc['English'] = [85, 86, 83, 80, 90, 89]
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths       90     92        89    81       94      89
Science     91     81        91    71       95      78
Hindi       97     96        88    67       99      76
English     85     86        83    80       90      89

Setting an entire row to one value. DataFrame.loc[] can also change all the data values of a row to a single particular value. The following statement sets the marks in 'Maths' to 0 for all columns:

>>> ResultDF.loc['Maths'] = 0
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika  Preeti
Maths        0      0         0     0        0       0
Science     91     81        91    71       95      78
Hindi       97     96        88    67       99      76
English     95     86        95    80       95      99

Mismatched lengths raise a ValueError. Two error cases to remember:

• If we try to add a row with fewer values than the number of columns in the DataFrame, it results in a ValueError with the message: ValueError: Cannot set a row with mismatched columns.
• Similarly, if we try to add a column with fewer values than the number of rows, it results in: ValueError: Length of values does not match length of index.

Setting every value at once. Further, we can set all values of a DataFrame to one particular value in a single statement:

>>> ResultDF[:] = 0   # Set all values in ResultDF to 0
>>> ResultDF

Rows and columns are deleted from a DataFrame with the DataFrame.drop() method. We need to tell it two things: the names of the labels to be dropped, and the axis from which they are to be dropped — axis=0 deletes a row, axis=1 deletes a column.

Consider the following DataFrame:

         Arnab  Ramit  Samridhi  Riya  Mallika
Maths       90     92        89    81       94
Science     91     81        91    71       95
Hindi       97     96        88    67       99
English     95     86        95    80       95

Deleting a row (axis=0). The following example deletes the row with label 'Science'. Note that the result of drop() is assigned back to ResultDF:

>>> ResultDF = ResultDF.drop('Science', axis=0)
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika
Maths       90     92        89    81       94
Hindi       97     96        88    67       99
English     95     86        95    80       95

Deleting columns (axis=1). Several columns can be dropped in one call by passing a list of labels. The following deletes the columns 'Samridhi', 'Ramit' and 'Riya':

>>> ResultDF = ResultDF.drop(['Samridhi', 'Ramit', 'Riya'], axis=1)
>>> ResultDF

         Arnab  Mallika
Maths       90       94
Hindi       97       99
English     95       95

Duplicate labels are all dropped. If the DataFrame has more than one row with the same label, DataFrame.drop() deletes all the matching rows. Consider a DataFrame that has two rows labelled 'Hindi':

         Arnab  Ramit  Samridhi  Riya  Mallika
Maths       90     92        89    81       94
Science     91     81        91    71       95
Hindi       97     96        88    67       99
Hindi       97     89        78    60       45

To remove the duplicate rows labelled 'Hindi', we write:

>>> ResultDF = ResultDF.drop('Hindi', axis=0)
>>> ResultDF

         Arnab  Ramit  Samridhi  Riya  Mallika …

Once a DataFrame has been created, we are not stuck with the row labels it was given — Pandas lets us change the labels of rows (and columns) using the DataFrame.rename() method. To rename row labels, we pass a dictionary that maps each existing row label to the new label we want, together with the parameter axis='index'. This parameter is what tells rename() that it is the row labels that are to be changed.

The textbook demonstrates this on the familiar ResultDF DataFrame of students' marks. Suppose we want to rename the row indices Maths to Sub1, Science to Sub2, English to Sub3 and Hindi to Sub4:

>>> ResultDF
         Arnab  Ramit  Samridhi  Riya  Mallika
Maths       90     92        89    81       94
Science     91     81        91    71       95
English     97     96        88    67       99
Hindi       97     89        78    60       45

>>> ResultDF = ResultDF.rename({'Maths':'Sub1', 'Science':'Sub2',
...                             'English':'Sub3', 'Hindi':'Sub4'},
...                            axis='index')
>>> print(ResultDF)

       Arnab  Ramit  Samridhi  Riya  Mallika
Sub1      90     92        89    81       94
Sub2      91     81        91    71       95
Sub3      97     96        88    67       99
Sub4      97     89        78    60       45

Every row label named in the dictionary has been replaced by its new label; the data values themselves are untouched.

An important detail: renaming is selective, not all-or-nothing. If an existing row label is not given a new label in the dictionary, that row simply keeps its old label. For example, if we rename only Maths, Science and Hindi (leaving English out of the dictionary):

>>> ResultDF = ResultDF.rename({'Maths':'Sub1', 'Science':'Sub2',
...                             'Hindi':'Sub4'}, axis='index')
>>> print(ResultDF)

         Arnab  Ramit  Samridhi  Riya  Mallika
Sub1        90     92        89    81       94
Sub2        91     81        91    71       95 …

The same rename() method that renames row labels also renames column labels — the only change is the axis. Passing the parameter axis='columns' tells rename() that the dictionary we supply maps old column names to new ones.

Continuing with ResultDF, suppose we want to replace the student names with generic labels — Arnab becomes Student1, Ramit becomes Student2, Samridhi becomes Student3 and Mallika becomes Student4:

>>> ResultDF = ResultDF.rename({'Arnab':'Student1', 'Ramit':'Student2',
...                             'Samridhi':'Student3', 'Mallika':'Student4'},
...                            axis='columns')
>>> print(ResultDF)

         Student1  Student2  Student3  Riya  Student4
Maths          90        92        89    81        94
Science        91        81        91    71        95
English        97        96        88    67        99
Hindi          97        89        78    60        45

Note that the column Riya remains unchanged, because we did not pass any new label for it in the dictionary — exactly the same selective behaviour we saw when renaming row labels: whatever is not mentioned in the mapping keeps its existing name. …