6.1 Reading and Modifying Data

DataFrame Basics

In Pandas, we can read and modify data at different levels of granularity. We can read entire columns, entire rows, individual cells, or the indexes. We can also modify the data in these locations. To begin, let’s start by creating a new DataFrame from a dictionary (if we remember, using dictionaries creates a DataFrame with the keys as column names and the values as the data). We will also use a list generator to create the data for the DataFrame.

import pandas as pd
import random

user_type = ["admin", "user", "guest"]
data = {
    "id": [i for i in range(10)],
    "name": [f"Name {i}" for i in range(10)],
    "value": [random.randint(1, 100) for _ in range(10)],
    "birthday": [
        f"{random.randint(2000,2023)}-{random.randint(1,12)}-{random.randint(1,28)}"
        for _ in range(10)
    ],
    "user_type": [random.choice(user_type) for _ in range(10)],
}
df = pd.DataFrame(data)
df

	id	name	value	birthday	user_type
0	0	Name 0	18	2014-2-24	user
1	1	Name 1	32	2007-12-27	admin
2	2	Name 2	13	2010-11-16	user
3	3	Name 3	51	2022-4-21	guest
4	4	Name 4	17	2019-5-19	user
5	5	Name 5	19	2007-3-28	guest
6	6	Name 6	59	2016-9-27	user
7	7	Name 7	75	2010-2-19	guest
8	8	Name 8	26	2004-10-17	guest
9	9	Name 9	80	2004-8-28	guest

The data dictionary contains four keys: id, name, value, and birthday, each associated with a list of values. For the id key, the range() function generates integers from 0 to 9 using the values generated from range(). The name key uses an f-string to create labels such as “Name 0”, “Name 1”, and so on. The value key produces random integers between 1 and 100 by calling the randint() function from Python’s random library. This function returns a random integer that is greater than or equal to the first argument and less than or equal to the second argument (inclusive). For the birthday key, random dates in the “YYYY-MM-DD” format are generated by combining three separate calls to random.randint() for the year, month, and day. In both the value and birthday lists, the range() function is used to repeat the process 10 times, and the underscore (_) serves as a placeholder variable since the actual value from range() is not needed.

Now we can create a DataFrame from this data:

df = pd.DataFrame(data)
df

	id	name	value	birthday	user_type
0	0	Name 0	18	2014-2-24	user
1	1	Name 1	32	2007-12-27	admin
2	2	Name 2	13	2010-11-16	user
3	3	Name 3	51	2022-4-21	guest
4	4	Name 4	17	2019-5-19	user
5	5	Name 5	19	2007-3-28	guest
6	6	Name 6	59	2016-9-27	user
7	7	Name 7	75	2010-2-19	guest
8	8	Name 8	26	2004-10-17	guest
9	9	Name 9	80	2004-8-28	guest

Columns

Now that we have a DataFrame, we can access and modify its columns. Columns are very simple to access. We can use the column name as an attribute of the DataFrame, or we can use the column name as a key in a dictionary-like syntax. For example, to access the name column, we can do:

df['name']

0    Name 0
1    Name 1
2    Name 2
3    Name 3
4    Name 4
5    Name 5
6    Name 6
7    Name 7
8    Name 8
9    Name 9
Name: name, dtype: object

df.name

0    Name 0
1    Name 1
2    Name 2
3    Name 3
4    Name 4
5    Name 5
6    Name 6
7    Name 7
8    Name 8
9    Name 9
Name: name, dtype: object

Both of these will return the same result, which is a Pandas Series containing the values from the name column. If we want to modify a column, we can assign a new value to it. For example, if we want to change all names to “Updated Name”, we can do:

df['name'] = "Updated Name"
df.head()

	id	name	value	birthday	user_type
0	0	Updated Name	18	2014-2-24	user
1	1	Updated Name	32	2007-12-27	admin
2	2	Updated Name	13	2010-11-16	user
3	3	Updated Name	51	2022-4-21	guest
4	4	Updated Name	17	2019-5-19	user

This will update the entire name column to have the value “Updated Name” for all rows. It is unlikely that we want all names to equal “Updated Name”, so let’s change it back to the original values with the list comprehension we used to create the DataFrame:

df['name'] = [f"Name {i}" for i in range(10)]
df.head()

	id	name	value	birthday	user_type
0	0	Name 0	18	2014-2-24	user
1	1	Name 1	32	2007-12-27	admin
2	2	Name 2	13	2010-11-16	user
3	3	Name 3	51	2022-4-21	guest
4	4	Name 4	17	2019-5-19	user

Notice that we can assign a list of values to a column, and Pandas will automatically align the values with the rows based on the index. If the length of the list does not match the number of rows in the DataFrame, Pandas will raise an error.

If want to add an entirely new column to the DataFrame, we can simply assign a new list to a new column name by writing it into existence. For example, if we want to add a new column called age with random values, we can do:

df['age'] = [random.randint(18, 65) for _ in range(10)]
df.head()

	id	name	value	birthday	user_type	age
0	0	Name 0	18	2014-2-24	user	55
1	1	Name 1	32	2007-12-27	admin	56
2	2	Name 2	13	2010-11-16	user	45
3	3	Name 3	51	2022-4-21	guest	25
4	4	Name 4	17	2019-5-19	user	28

This will create a new column called age and fill it with random integers between 18 and 65. If we want to add a column that is derived from existing columns, we can do that as well. For example, if we want to create a new column called age_in_days that is the age in days (assuming the age column is in years), we can do:

df['age_in_days'] = df['age'] * 365
df.head()

	id	name	value	birthday	user_type	age	age_in_days
0	0	Name 0	18	2014-2-24	user	55	20075
1	1	Name 1	32	2007-12-27	admin	56	20440
2	2	Name 2	13	2010-11-16	user	45	16425
3	3	Name 3	51	2022-4-21	guest	25	9125
4	4	Name 4	17	2019-5-19	user	28	10220

Here, we are multiplying every value in the age column by 365 to convert the age from years to days and setting it to a new column called age_in_days. This will create a new column with the same number of rows as the DataFrame, and each value will be the result of the calculation.

Renaming Columns

We can also rename columns using the rename() method. This method takes a dictionary as an argument, where the keys are the current column names and the values are the new column names. For example, if we want to rename the value column to score, we can do:

df.rename(columns={'value': 'score'}, inplace=True)
df.head()

	id	name	score	birthday	user_type	age	age_in_days
0	0	Name 0	18	2014-2-24	user	55	20075
1	1	Name 1	32	2007-12-27	admin	56	20440
2	2	Name 2	13	2010-11-16	user	45	16425
3	3	Name 3	51	2022-4-21	guest	25	9125
4	4	Name 4	17	2019-5-19	user	28	10220

This will rename the value column to score. The inplace=True argument tells Pandas to modify the DataFrame in place, rather than returning a new DataFrame with the changes. If we want to rename multiple columns at once, we can do that as well by adding more key-value pairs to the dictionary:

df.rename(columns={'birthday': 'dob', 'user_type': 'role'}, inplace=True)
df.head()

	id	name	score	dob	role	age	age_in_days
0	0	Name 0	18	2014-2-24	user	55	20075
1	1	Name 1	32	2007-12-27	admin	56	20440
2	2	Name 2	13	2010-11-16	user	45	16425
3	3	Name 3	51	2022-4-21	guest	25	9125
4	4	Name 4	17	2019-5-19	user	28	10220

This will rename the birthday column to dob and the user_type column to role.

Let’s rename the columns back to their original names for consistency in the rest of the chapter:

df.rename(columns={'score': 'value', 'dob': 'birthday', 'role': 'user_type'}, inplace=True)
df.head()

	id	name	value	birthday	user_type	age	age_in_days
0	0	Name 0	18	2014-2-24	user	55	20075
1	1	Name 1	32	2007-12-27	admin	56	20440
2	2	Name 2	13	2010-11-16	user	45	16425
3	3	Name 3	51	2022-4-21	guest	25	9125
4	4	Name 4	17	2019-5-19	user	28	10220

Rows

To access rows, we can use the .loc[] and .iloc[] indexers. The .loc[] indexer is used for label-based indexing (the name of the row first and the name of the column second), while the .iloc[] indexer is used for positional indexing (the integer position of the row first and the integer position of the column second).

With `.loc[]`

With .loc[], we access rows by their row label. If you did not specific a row label when creating the DataFrame, Pandas will automatically assign integer labels starting at 0. For example, to access the first row of the DataFrame, we can do:

df.loc[0]

id                     0
name              Name 0
value                 18
birthday       2014-2-24
user_type           user
age                   55
age_in_days        20075
Name: 0, dtype: object

This will return a Pandas Series containing the values from the first row of the DataFrame. In order to indicate the difference between .loc[] and iloc[], let’s give our DataFrame a new index. We can do this by assigning a new list of labels to the index attribute of the DataFrame:

df.index = [f"row_{i}" for i in range(10)]
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	0	Name 0	18	2014-2-24	user	55	20075
row_1	1	Name 1	32	2007-12-27	admin	56	20440
row_2	2	Name 2	13	2010-11-16	user	45	16425
row_3	3	Name 3	51	2022-4-21	guest	25	9125
row_4	4	Name 4	17	2019-5-19	user	28	10220

Now, we need to access the first row again, but this time we will use the new row label:

df.loc['row_0'] # notice the label name is now in a string.

id                     0
name              Name 0
value                 18
birthday       2014-2-24
user_type           user
age                   55
age_in_days        20075
Name: row_0, dtype: object

We can also select a range of rows using .loc[]. For example, to select the first three rows, we can use a colon (:) to specify the range:

df.loc['row_0':'row_2']

	id	name	value	birthday	user_type	age	age_in_days
row_0	0	Name 0	18	2014-2-24	user	55	20075
row_1	1	Name 1	32	2007-12-27	admin	56	20440
row_2	2	Name 2	13	2010-11-16	user	45	16425

Note

With .loc[], both the start and the stop labels are included in the result. This means that if you specify a range of rows, both the starting and ending rows will be included in the result.

Similarly, we could pass a list of row labels into the first index of the .loc[] indexer to select multiple rows at once:

df.loc[['row_0', 'row_1', 'row_2']]

	id	name	value	birthday	user_type	age	age_in_days
row_0	0	Name 0	18	2014-2-24	user	55	20075
row_1	1	Name 1	32	2007-12-27	admin	56	20440
row_2	2	Name 2	13	2010-11-16	user	45	16425

We can also select specific columns along with the rows. For example, to select the first three rows and only the name and value columns, we can add a second index to the .loc[] indexer separated by a comma:

df.loc['row_0':'row_2', ['name', 'value']]

	name	value
row_0	Name 0	18
row_1	Name 1	32
row_2	Name 2	13

Or we could just select a single column:

df.loc['row_0':'row_2', 'name']

row_0    Name 0
row_1    Name 1
row_2    Name 2
Name: name, dtype: object

Or we could select a range of columns using a colon (:):

df.loc['row_0':'row_2', 'name':'birthday']

	name	value	birthday
row_0	Name 0	18	2014-2-24
row_1	Name 1	32	2007-12-27
row_2	Name 2	13	2010-11-16

Finally, we can also select all rows and specific columns by using a colon (:) for the row index:

df.loc[:, ['name', 'value']]

	name	value
row_0	Name 0	18
row_1	Name 1	32
row_2	Name 2	13
row_3	Name 3	51
row_4	Name 4	17
row_5	Name 5	19
row_6	Name 6	59
row_7	Name 7	75
row_8	Name 8	26
row_9	Name 9	80

Modifying Rows with `.loc[]`

We can also modify rows using .loc[]. All you have to do is use the .loc[] indexer to select the row and column you want to modify. For example, if we want to change the name of the first row to “Updated Name”, we can do:

df.loc['row_0', 'name'] = "Updated Name"
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	0	Updated Name	18	2014-2-24	user	55	20075
row_1	1	Name 1	32	2007-12-27	admin	56	20440
row_2	2	Name 2	13	2010-11-16	user	45	16425
row_3	3	Name 3	51	2022-4-21	guest	25	9125
row_4	4	Name 4	17	2019-5-19	user	28	10220

We can also modify multiple rows at once. For example, if we want to change the value of the first three rows to 100, we can do:

df.loc['row_0':'row_2', 'value'] = 100
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	0	Updated Name	100	2014-2-24	user	55	20075
row_1	1	Name 1	100	2007-12-27	admin	56	20440
row_2	2	Name 2	100	2010-11-16	user	45	16425
row_3	3	Name 3	51	2022-4-21	guest	25	9125
row_4	4	Name 4	17	2019-5-19	user	28	10220

With `.iloc[]`

With .iloc[], we access rows by their integer position. The first row is at position 0, the second row is at position 1, and so on. For example, to access the first row of the DataFrame, we can do:

df.iloc[:, 0] = [x for x in range(5, df.shape[0] + 5)]
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	5	Updated Name	100	2014-2-24	user	55	20075
row_1	6	Name 1	100	2007-12-27	admin	56	20440
row_2	7	Name 2	100	2010-11-16	user	45	16425
row_3	8	Name 3	51	2022-4-21	guest	25	9125
row_4	9	Name 4	17	2019-5-19	user	28	10220

This will replace the Pandas Series containing the values from the first column of the DataFrame. The : in the first location indicates all rows, and the 0 in the second location indicates the first column.

This will return a Pandas Series containing the values from the first row of the DataFrame. We can also select a range of rows using .iloc[]. For example, to select the first three rows, we can use a colon (:) to specify the range:

df.iloc[0, 0:3] = [777, "Name 1", 29]
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	777	Name 1	29	2014-2-24	user	55	20075
row_1	6	Name 1	100	2007-12-27	admin	56	20440
row_2	7	Name 2	100	2010-11-16	user	45	16425
row_3	8	Name 3	51	2022-4-21	guest	25	9125
row_4	9	Name 4	17	2019-5-19	user	28	10220

Cells wtih `.at[]` and `.iat[]`

We can also access and modify individual cells using the .at[] and .iat[] indexers. The .at[] indexer is used for label-based indexing, while the .iat[] indexer is used for positional indexing. These indexers are the fastest way to access a single value in a DataFrame because they are optimized to only access a single value.

with `.at[]`

To access a single cell with .at[], we can specify the row label and the column name. For example, to access the cell in the first row and the name column, we can do:

df.at['row_0', 'name']

'Name 1'

This will return the value in the cell located at the intersection of the first row and the name column. We can also modify a single cell using .at[]. For example, if we want to change the name of the first row to “Single Cell Update”, we can do:

df.at['row_0', 'name'] = "Single Cell Update"
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	777	Single Cell Update	29	2014-2-24	user	55	20075
row_1	6	Name 1	100	2007-12-27	admin	56	20440
row_2	7	Name 2	100	2010-11-16	user	45	16425
row_3	8	Name 3	51	2022-4-21	guest	25	9125
row_4	9	Name 4	17	2019-5-19	user	28	10220

with `.iat[]`

To access a single cell with .iat[], we can specify the row and column positions as integers. For example, to access the cell in the first row and the first column, we can do:

df.iat[0, 0]

np.int64(777)

This will return the value in the cell located at the intersection of the first row and the first column. We can also modify a single cell using .iat[]. For example, if we want to change the value in the first row and the first column to 999, we can do:

df.iat[0, 0] = 999
df.head()

	id	name	value	birthday	user_type	age	age_in_days
row_0	999	Single Cell Update	29	2014-2-24	user	55	20075
row_1	6	Name 1	100	2007-12-27	admin	56	20440
row_2	7	Name 2	100	2010-11-16	user	45	16425
row_3	8	Name 3	51	2022-4-21	guest	25	9125
row_4	9	Name 4	17	2019-5-19	user	28	10220

Indexes

We can also access and modify the index of a DataFrame. The index is a special column that is used to label the rows of the DataFrame. We can access the index using the index attribute of the DataFrame. For example, to access the index of the DataFrame, we can do:

df.index

Index(['row_0', 'row_1', 'row_2', 'row_3', 'row_4', 'row_5', 'row_6', 'row_7',
       'row_8', 'row_9'],
      dtype='object')

This will return a Pandas Index object containing the labels of the rows. We can also modify the index by assigning a new list of labels to the index attribute of the DataFrame. For example, if we want to change the index to be “A”, “B”, “C”, and so on, we can do:

df.index = [chr(i) for i in range(65, 75)]  # ASCII values for A-J
df.head()

	id	name	value	birthday	user_type	age	age_in_days
A	999	Single Cell Update	29	2014-2-24	user	55	20075
B	6	Name 1	100	2007-12-27	admin	56	20440
C	7	Name 2	100	2010-11-16	user	45	16425
D	8	Name 3	51	2022-4-21	guest	25	9125
E	9	Name 4	17	2019-5-19	user	28	10220

This will change the index of the DataFrame to be the letters “A” through “J”. We can also reset the index to the default integer index by using the reset_index() method. For example, to reset the index, we can do:

df.reset_index(drop=True, inplace=True)
df.head()

	id	name	value	birthday	user_type	age	age_in_days
0	999	Single Cell Update	29	2014-2-24	user	55	20075
1	6	Name 1	100	2007-12-27	admin	56	20440
2	7	Name 2	100	2010-11-16	user	45	16425
3	8	Name 3	51	2022-4-21	guest	25	9125
4	9	Name 4	17	2019-5-19	user	28	10220

Columns

Renaming Columns

Rows

With .loc[]

Modifying Rows with .loc[]

With .iloc[]

Cells wtih .at[] and .iat[]

with .at[]

with .iat[]

Indexes

With `.loc[]`

Modifying Rows with `.loc[]`

With `.iloc[]`

Cells wtih `.at[]` and `.iat[]`

with `.at[]`

with `.iat[]`