Manipulating DataFrame Columns

What are the main operations on a DataFrame’s columns?

Question: What are the main operations on a DataFrame’s columns?

Answer: Mainly, one may want to:

• Rename a column
• Drop a column
• Create a new column
• Amend an existing column’s values

How do I rename or drop a column in Pandas?

Question: How do I rename or drop a column in Pandas?

Answer: As shown in the code snippet below, Pandas has two methods, .rename() and .drop(), to rename and drop columns respectively.

Line 35 shows how to rename a column. A first argument is a dictionary mapping the new name onto the old name (the dictionary’s key). The second argument is a boolean flag that indicates whether to make the change effective (if “True”) or not (if “False”).

Lines 45 and 51 show how to drop a column. The first argument is an array with the name(s) of the column(s) to delete. The second argument is a boolean flag that indicates whether to make the change effective (if “True”) or not (if “False”).

# import pandas with the socially accepted alias 'pd'
>>> import pandas as pd

# the df
>>> df = pd.DataFrame(
    {
        "laptop": ["MacBook Pro 13inch", "Thinkpad T14", "Dell XPS 13"],
        "ram": ["16 GB", "48 GB", "8 GB"],
        "os": ["macOS Monterey", "Debian 11", "Windows 11"],
        "chip": ["M1", "Ryzen", "Intel Core i7"]
    }
    )

# data view
>>> df
               laptop    ram              os           chip
0  MacBook Pro 13inch  16 GB  macOS Monterey             M1
1        Thinkpad T14  48 GB       Debian 11          Ryzen
2         Dell XPS 13   8 GB      Windows 11  Intel Core i7

# info
>>> df.info()
RangeIndex: 3 entries, 0 to 2
Data columns (total 4 columns):
     Column  Non-Null Count  Dtype
---  ------  --------------  -----
 0   laptop  3 non-null      str
 1   ram     3 non-null      str
 2   os      3 non-null      str
 3   chip    3 non-null      str
dtypes: str(4)
memory usage: 228.0 bytes

# rename the column "ram" to "memory"
>>> df.rename(columns={"ram": "memory"}, inplace=True)
>>> df
               laptop memory              os           chip
0  MacBook Pro 13inch  16 GB  macOS Monterey             M1
1        Thinkpad T14  48 GB       Debian 11          Ryzen
2         Dell XPS 13   8 GB      Windows 11  Intel Core i7

# drop the column "chip"
# --+ when inplace is set to False, the outcome of .drop is sent to the
# interactive sessions, but the data in memory are not affected
>>> df.drop(columns=["chip"], inplace=False)
               laptop memory              os
0  MacBook Pro 13inch  16 GB  macOS Monterey
1        Thinkpad T14  48 GB       Debian 11
2         Dell XPS 13   8 GB      Windows 11
# --+ when inplace is set to True, the data in memory are affected
>>> df.drop(columns=["chip"], inplace=True)
>>> df
               laptop memory              os
0  MacBook Pro 13inch  16 GB  macOS Monterey
1        Thinkpad T14  48 GB       Debian 11
2         Dell XPS 13   8 GB      Windows 11

How do I create a new column or amend an existing Pandas column?

Question: How do I create a new column or amend an existing Pandas column?

Answer: The .loc method is the most appropriate way to create a new column or alter an existing column’s values. The code snippet below illustrates a couple of common tasks. In line 15, we assign a list of string objects to a new column called manufacturer for all cases included in df.

In lines 26/28, 46, and 63 we amend the value of an existing column. First, we manipulate the strings included under RAM to replace the substring " GB" with an empty string (lines 26/28). Second, we change the type of the column from string to integer using the Pandas’ .astype() method (line 46). Then, we take the log of ram and assign it to the new column log_ram (line 63).

In line 90, we use .loc to create a new variable for a subset of cases only. Specifically, we populate the column gpu provided the value of laptop is “Thinkpad T14.”

# import pandas with the socially accepted alias 'pd'
>>> import pandas as pd

# the df
>>> df = pd.DataFrame(
        {
            "laptop": ["MacBook Pro 13inch", "Thinkpad T14", "Dell XPS 13"],
            "ram": ["16 GB", "48 GB", "8 GB"],
            "os": ["macOS Monterey", "Debian 11", "Windows 11"],
            "chip": ["M1", "Ryzen", "Intel Core i7"],
        }
)

# create a new column, e.g., the name of the manufacturer
>>> df.loc[:, "manufacturer"] = ["Apple", "Lenovo", "Dell"]
>>> df
               laptop    ram              os           chip manufacturer
0  MacBook Pro 13inch  16 GB  macOS Monterey             M1        Apple
1        Thinkpad T14  48 GB       Debian 11          Ryzen       Lenovo
2         Dell XPS 13   8 GB      Windows 11  Intel Core i7         Dell

# transform the column "ram" from string to number type
# --+ step 1: get rid of non-number characters using a regular expression
#     NOTE two things here: plain [] assignment, not .loc (see below), and
#     regex=True, which since Pandas 2.0 you must ask for explicitly
>>> df["ram"] = df["ram"].str.replace(r"[^0-9]", "", regex=True)
# --+ alternative way to carry out step 1: using Pandas string methods
>>> df["ram"] = df["ram"].str.replace(r"\sGB", "", regex=True)
# --+ step 2: convert the string to number type
# ----+ check the type of "ram"
>>> df.info()
Data columns (total 5 columns):
     Column        Non-Null Count  Dtype
---  ------        --------------  -----
 0   laptop        3 non-null      str
 1   ram           3 non-null      str
 2   os            3 non-null      str
 3   chip          3 non-null      str
 4   manufacturer  3 non-null      str
dtypes: str(5)
# ----+ surprise-surprise: we have not a number type yet
# let us change dtype with Pandas astype method
#     again [] and not .loc --- .loc writes INTO the existing text column,
#     and on Pandas 3 that is a TypeError:
#     "Invalid value '[16 48  8]' for dtype 'str'"
>>> df["ram"] = df["ram"].astype(int)
# ----+ check the data type again
>>> df.info()
Data columns (total 5 columns):
     Column        Non-Null Count  Dtype
---  ------        --------------  -----
 0   laptop        3 non-null      str
 1   ram           3 non-null      int64
 2   os            3 non-null      str
 3   chip          3 non-null      str
 4   manufacturer  3 non-null      str
dtypes: int64(1), str(4)
memory usage: 252.0 bytes

# transform an existing column and assign the output to a new column
# --+ import numpy to access the log function
>>> import numpy as np
>>> df["log_ram"] = np.log(df["ram"])
# --+ preview
>>> df
               laptop  ram              os           chip manufacturer  \
0  MacBook Pro 13inch   16  macOS Monterey             M1        Apple
1        Thinkpad T14   48       Debian 11          Ryzen       Lenovo
2         Dell XPS 13    8      Windows 11  Intel Core i7         Dell

    log_ram
0  2.772589
1  3.871201
2  2.079442
# --+ info
>>> df.info()
Data columns (total 6 columns):
     Column        Non-Null Count  Dtype
---  ------        --------------  -----
 0   laptop        3 non-null      str
 1   ram           3 non-null      int64
 2   os            3 non-null      str
 3   chip          3 non-null      str
 4   manufacturer  3 non-null      str
 5   log_ram       3 non-null      float64
dtypes: float64(1), int64(1), str(4)
memory usage: 276.0 bytes

# create a new column conditional on another column's value
>>> df.loc[df["laptop"] == "Thinkpad T14", "gpu"] = True
# --+ info
>>> df.info()
Data columns (total 7 columns):
     Column        Non-Null Count  Dtype
---  ------        --------------  -----
 0   laptop        3 non-null      str
 1   ram           3 non-null      int64
 2   os            3 non-null      str
 3   chip          3 non-null      str
 4   manufacturer  3 non-null      str
 5   log_ram       3 non-null      float64
 6   gpu           1 non-null      object
dtypes: float64(1), int64(1), object(1), str(4)
memory usage: 300.0+ bytes
# --+ preview
>>> df
               laptop  ram              os           chip manufacturer  \
0  MacBook Pro 13inch   16  macOS Monterey             M1        Apple
1        Thinkpad T14   48       Debian 11          Ryzen       Lenovo
2         Dell XPS 13    8      Windows 11  Intel Core i7         Dell

    log_ram   gpu
0  2.772589   NaN
1  3.871201  True
2  2.079442   NaN

WarningTwo traps when you change an existing column

Both of these bite silently, and both changed with the Pandas version.

.loc writes into a column; [] replaces it. That distinction does not matter when you create a column, which is why line 15 above is fine. It matters a great deal when you change one, because writing into a column has to respect the dtype that column already has. Here is what

df.loc[:, "ram"] = df.loc[:, "ram"].astype(int)

does to that text column, by version:

Pandas	What happens
1.x	the column is replaced, the cast works
2.x	the cast is silently discarded — ram stays text
3.0	raises TypeError: Invalid value '[16 48 8]' for dtype 'str'

Pandas 3.0 turning this into an error is an improvement: the wrong answer 2.x gave you quietly is now loud. Use df["ram"] = df["ram"].astype(int), which is correct on all three.

.str.replace() no longer assumes a regular expression. Pandas 1.x defaulted to regex=True; since 2.0 the default is False. So df["ram"].str.replace(r"\sGB", "") does nothing at all — no error, no warning — and the .astype(int) that follows then fails with a confusing message about '16 GB'. Pass regex=True whenever the pattern is a regular expression.

What is a missing value?

Question: What is a missing value?

Answer: A missing value is a datapoint for which no information is available in the dataset. Missing values can arise for many reasons. The most popular reasons are:

• the case does not present a value for the variable. For example, it is not possible to record the stock market of a company before the IPO
• the case does present a value for the variable. However, the value was not recorded because of the limitations of the data gathering process
• the recorded value is not accurate/valid — hence, it was removed from the dataset

How does Pandas represent missing values?

Question: How does Pandas represent missing values?

Answer: Pandas denote missing values using NumPy’s floating representation numpy.nan. Note the spelling: NumPy 2.0 removed the old numpy.NaN alias, and lowercase numpy.nan is the only one that still works

How do I handle missing values in Pandas?

Question: How do I handle missing values in Pandas?

Answer: There are several approaches to cope with missing values:

• deleting all cases where a missing value is present
• replacing missing values with a fixed value
• replacing missing values with the mean of the column
• replacing missing values with the estimate of a statistical model

The latter approach is the most sophisticated one and falls beyond the remit of these notes. In the code snippet below, I show how to implement the first three approaches in Pandas. Line 34 uses the .dropna() method to delete all cases presenting at least one missing value. Line 41 draws on the .fillna() method to replace the missing values with a fixed value (e.g., a value that the analyst considers ‘plausible’ based on her/his contextual knowledge). Like line 41, line 50 relies the .fillna() method. However, it does not replace the missing values with a fixed value. Instead, it uses the mean value of price.

# import numpy with the socially accepted alias "np"
>>> import numpy as np
# import pandas with the socially acceptable alias "pd"
>>> import pandas as pd

# the dataframe
>>> df = pd.DataFrame(
    {
        "item": ["a", "b", "c", "d", "e"],
        "price": [16.32, 16.78, np.nan, np.nan, 16.41],
    }
)

# data view
>>> df
  item  price
0    a  16.32
1    b  16.78
2    c    NaN
3    d    NaN
4    e  16.41

# info
>>> df.info()
Data columns (total 2 columns):
     Column  Non-Null Count  Dtype
---  ------  --------------  -----
 0   item    5 non-null      str
 1   price   3 non-null      float64
dtypes: float64(1), str(1)
memory usage: 212.0 bytes

# approach 1 --- delete all cases where a missing value is present
>>> df.dropna()
  item  price
0    a  16.32
1    b  16.78
4    e  16.41

# approach 2 --- replace missing values with a fixed value
>>> df.fillna(value=16.99, inplace=False)
  item  price
0    a  16.32
1    b  16.78
2    c  16.99
3    d  16.99
4    e  16.41

# approach 3 --- replace missing values with the mean of the columns
>>> df.fillna(value=np.mean(df["price"]), inplace=False)
  item      price
0    a  16.320000
1    b  16.780000
2    c  16.503333
3    d  16.503333
4    e  16.410000