Pandas Tutorial: DataFrames in Python

• You also know that .iloc[] will go and look at the positions in the index. When you pass 2, you will get back:

48    7
49    8
50    9

• Since the index doesn’t only contain integers, .ix[] will have the same behavior as iloc and look at the positions in the index. You will get back the same result as .iloc[].

Now that the difference between .iloc[], .loc[] and .ix[] is clear, you are ready to give adding rows to your DataFrame a go!

Tip: as a consequence of what you have just read, you understand now also that the general recommendation is that you use .loc to insert rows in your DataFrame. That is because if you would use df.ix[], you might try to reference a numerically valued index with the index value and accidentally overwrite an existing row of your DataFrame. You should avoid this!

Check out the difference once more in the DataFrame below:

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), index= [2.5, 12.6, 4.8], columns=[48, 49, 50])

# This will make an index labeled `2` and add the new values
df.loc[2] = [11, 12, 13]
print(df)

     48  49  50

2.5    1   2   3

12.6   4   5   6

4.8    7   8   9

2.0   11  12  13

You can see why all of this can be confusing, right?

Adding a Column to your DataFrame

In some cases, you want to make your index part of your DataFrame. You can easily do this by taking a column from your DataFrame or by referring to a column that you haven’t made yet and assigning it to the .index property, just like this:

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['A', 'B', 'C'])
​
# Use `.index`
df['D'] = df.index
​
# Print `df`
print(df)

  A  B  C  D

0  1  2  3  0

1  4  5  6  1

2  7  8  9  2

In other words, you tell your DataFrame that it should take column A as its index.

However, if you want to append columns to your DataFrame, you could also follow the same approach as when you would add an index to your DataFrame: you use .loc[] or .iloc[]. In this case, you add a Series to an existing DataFrame with the help of .loc[]:

# Study the DataFrame `df`
print(df)

# Append a column to `df`
df.loc[:, 4] = pd.Series(['5', '6', '7'], index=df.index)

# Print out `df` again to see the changes
print(df)

  A  B  C  D

0  1  2  3  0

1  4  5  6  1

2  7  8  9  2

   A  B  C  D  4

0  1  2  3  0  5

1  4  5  6  1  6

2  7  8  9  2  7

Remember a Series object is much like a column of a DataFrame. That explains why you can easily add a Series to an existing DataFrame. Note also that the observation that was made earlier about .loc[] still stays valid, even when you’re adding columns to your DataFrame!

Resetting the Index of your DataFrame

When your index doesn’t look entirely the way you want it to, you can opt to reset it. You can easily do this with .reset_index(). However, you should still watch out, as you can pass several arguments that can make or break the success of your reset:

# Check out the weird index of your dataframe
print(df)

# Use `reset_index()` to reset the values. 
df_reset = df.reset_index(level=0, drop=True)

# Print `df_reset`
print(df_reset)

  A  B  C  D  4

0  1  2  3  0  5

1  4  5  6  1  6

2  7  8  9  2  7

   A  B  C  D  4

0  1  2  3  0  5

1  4  5  6  1  6

2  7  8  9  2  7

You can try replacing the drop argument by inplace in the example above and see what happens!

Note how you use the drop argument to indicate that you want to get rid of the index that was there. If you would have used inplace, the original index with floats is added as an extra column to your DataFrame.

Deleting an Index from your DataFrame

If you want to remove the index from your DataFrame, you should reconsider because DataFrames and Series always have an index.

However, what you *can* do is, for example:

• resetting the index of your DataFrame (go back to the previous section to see how it is done) or
• remove the index name, if there is any, by executing del df.index.name,
• remove duplicate index values by resetting the index, dropping the duplicates of the index column that has been added to your DataFrame and reinstating that duplicateless column again as the index
• and lastly, remove an index, and with it a row. This is elaborated further on in this tutorial.

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9], [40, 50, 60], [23, 35, 37]]), 
                  index= [2.5, 12.6, 4.8, 4.8, 2.5], 
                  columns=[48, 49, 50])
                  
df.reset_index().drop_duplicates(subset='index', keep='last').set_index('index')

Now that you know how to remove an index from your DataFrame, you can go on to removing columns and rows!

Deleting a Column from your DataFrame

To get rid of (a selection of) columns from your DataFrame, you can use the drop() method:

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['A', 'B', 'C'])

# Check out the DataFrame `df`
print(df)

# Drop the column with label 'A'                  
df.drop('A', axis=1, inplace=True)

# Drop the column at position 1
df.drop(df.columns[[1]], axis=1)

  A  B  C

0  1  2  3

1  4  5  6

2  7  8  9

You might think now: well, this is not so straightforward; There are some extra arguments that are passed to the drop() method!

• The axis argument is either 0 when it indicates rows and 1 when it is used to drop columns.
• You can set inplace to True to delete the column without having to reassign the DataFrame.

Removing a Row from your DataFrame

You can remove duplicate rows from your DataFrame by executing df.drop_duplicates(). You can also remove rows from your DataFrame, taking into account only the duplicate values that exist in one column.

Check out this example:

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9], [40, 50, 60], [23, 35, 37]]), 
                  index= [2.5, 12.6, 4.8, 4.8, 2.5], 
                  columns=[48, 49, 50])

# Check out your DataFrame `df`
print(df)

# Drop the duplicates in `df`
df.drop_duplicates([48], keep='last')

      48  49  50

2.5    1   2   3

12.6   4   5   6

4.8    7   8   9

4.8   40  50  60

2.5   23  35  37

If there is no uniqueness criterion to the deletion that you want to perform, you can use the drop() method, where you use the index property to specify the index of which rows you want to remove from your DataFrame:

# Check out the DataFrame `df`
print(df)

# Drop the index at position 1
df.drop(df.index[1])

     48  49  50

2.5    1   2   3

12.6   4   5   6

4.8    7   8   9

4.8   40  50  60

2.5   23  35  37

After this command, you might want to reset the index again.

Tip: try resetting the index of the resulting DataFrame for yourself! Don’t forget to use the drop argument if you deem it necessary.

Replacing all Occurrences of a String in a DataFrame

To replace certain strings in your DataFrame, you can easily use replace(): pass the values that you would like to change, followed by the values you want to replace them by.

Just like this:

df = pd.DataFrame({"Student1":['OK','Awful','Acceptable'], 
                   "Student2":['Perfect','Awful','OK'], 
                   "Student3":['Acceptable','Perfect','Poor']})

# Study the DataFrame `df` first
print(df)

# Replace the strings by numerical values (0-4)
df.replace(['Awful', 'Poor', 'OK', 'Acceptable', 'Perfect'], [0, 1, 2, 3, 4]) 

     Student1 Student2    Student3

0          OK  Perfect  Acceptable

1       Awful    Awful     Perfect

2  Acceptable       OK        Poor

Note that there is also a regex argument that can help you out tremendously when you’re faced with strange string combinations:

df =  pd.DataFrame([["1\n", 2, "3\n"], [4, 5, "6\n"] ,[7, "8\n", 9]])

# Check out your DataFrame `df`
print(df)

# Replace strings by others with `regex`
df.replace({'\n': ''}, regex=True)

    0    1    2

0  1\n    2  3\n

1    4    5  6\n

2    7  8\n    9

In short, replace() is mostly what you need to deal with when you want to replace values or strings in your DataFrame by others!

Removing Parts from Strings in the Cells of your DataFrame

Removing unwanted parts of strings is cumbersome work. Luckily, there is an easy solution to this problem!

df =  pd.DataFrame([["+-1aAbBcC", "2", "+-3aAbBcC"], ["4", "5", "+-6aAbBcC"] ,["7", "+-8aAbBcC", "9"]])

# Check out your DataFrame
print(df)

# Delete unwanted parts from the strings in the first column
df[0] = df[0].map(lambda x: x.lstrip('+-').rstrip('aAbBcC'))

# Check out the result again
df

          0          1          2

0  +-1aAbBcC          2  +-3aAbBcC

1          4          5  +-6aAbBcC

2          7  +-8aAbBcC          9

You use map() on the column result to apply the lambda function over each element or element-wise of the column. The function in itself takes the string value and strips the + or - that’s located on the left, and also strips away any of the six aAbBcC on the right.

Splitting Text in a Column into Multiple Rows in a DataFrame

This is somewhat a more difficult formatting task. However, the next code chunk will walk you through the steps:

df = pd.DataFrame({"Age": [34, 22, 19], 
                   "PlusOne":[0,0,1], 
                   "Ticket":["23:44:55", "66:77:88", "43:68:05 56:34:12"]})

# Inspect your DataFrame `df`
print(df)

# Split out the two values in the third row
# Make it a Series
# Stack the values
ticket_series = df['Ticket'].str.split(' ').apply(pd.Series, 1).stack()

# Get rid of the stack:
# Drop the level to line up with the DataFrame
ticket_series.index = ticket_series.index.droplevel(-1)

print(ticket_series)

  Age  PlusOne             Ticket

0   34        0           23:44:55

1   22        0           66:77:88

2   19        1  43:68:05 56:34:12

0    23:44:55

1    66:77:88

2    43:68:05

2    56:34:12

dtype: object

          0

0  23:44:55

1  66:77:88

2  43:68:05

2  56:34:12

In short, what you do is:

• First, you inspect the DataFrame at hand. You see that the values in the last row and in the last column are a bit too long. It appears there are two tickets because a guest has taken a plus-one to the concert.
• You take the Ticket column from the DataFrame df and strings on a space. This will make sure that the two tickets will end up in two separate rows in the end. Next, you take these four values (the four ticket numbers) and put them into a Series object:

      0         1
0  23:44:55       NaN
1  66:77:88       NaN
2  43:68:05  56:34:12

• That still doesn’t seem quite right. You have NaN values in there! You have to stack the Series to make sure you don’t have any NaN values in the resulting Series.
• Next, you see that your Series is stacked.

0  0    23:44:55
1  0    66:77:88
2  0    43:68:05
   1    56:34:12

• That is not ideal either. That is why you drop the level to line up with the DataFrame:

0    23:44:55
1    66:77:88
2    43:68:05
2    56:34:12
dtype: object

• That is what you’re looking for.
• Transform your Series to a DataFrame to make sure you can join it back to your initial DataFrame. However, to avoid having any duplicates in your DataFrame, you can delete the original Ticket column.

Applying a Function to your Pandas DataFrame’s Columns or Rows

You might want to adjust the data in your DataFrame by applying a function to it. Let’s begin answering this question by making your own lambda function:

doubler = lambda x: x*2

Tip: if you want to know more about functions in Python, consider taking this Python functions tutorial.

df = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['A', 'B', 'C'])

# Study the `df` DataFrame
print(df)

# Apply the `doubler` function to the `A` DataFrame column
df['A'].apply(doubler)
 A  B  C

0  1  2  3

1  4  5  6

2  7  8  9

0     2

1     8

2    14

Name: A, dtype: int64

Note that you can also select the row of your DataFrame and apply the doubler lambda function to it. Remember that you can easily select a row from your DataFrame by using .loc[] or .iloc[].

Then, you would execute something like this, depending on whether you want to select your index based on its position or based on its label:

df.loc[0].apply(doubler)

Note that the apply() function only applies the doubler function along the axis of your DataFrame. That means that you target either the index or the columns. Or, in other words, either a row or a column.

However, if you want to apply it to each element or element-wise, you can make use of the map() function. You can just replace the apply() function in the code chunk above with map(). Don’t forget to still pass the doubler function to it to make sure you multiply the values by 2.

Let’s say you want to apply this doubling function not only to the A column of your DataFrame but to the whole of it. In this case, you can use applymap() to apply the doubler function to every single element in the entire DataFrame:

doubled_df = df.applymap(doubler)
print(doubled_df)

   A   B   C

0   2   4   6

1   8  10  12

2  14  16  18

Note that in these cases, we have been working with lambda functions or anonymous functions that get created at runtime. However, you can also write your own function. For example:

def doubler(x):
    if x % 2 == 0:
        return x
    else:
        return x * 2

# Use `applymap()` to apply `doubler()` to your DataFrame
doubled_df = df.applymap(doubler)

# Check the DataFrame
print(doubled_df)

   A   B   C

0   2   2   6

1   4  10   6

2  14   8  18

If you want more information on the flow of control in Python, you can always check out our other resources.

Pivotting your DataFrame

You can use the pivot() function to create a new derived table out of your original one. When you use the function, you can pass three arguments:

1. values: this argument allows you to specify which values of your original DataFrame you want to see in your pivot table.
2. columns: whatever you pass to this argument will become a column in your resulting table.
3. index: whatever you pass to this argument will become an index in your resulting table.

# Import pandas
import pandas as pd

# Create your DataFrame
products = pd.DataFrame({'category': ['Cleaning', 'Cleaning', 'Entertainment', 'Entertainment', 'Tech', 'Tech'],
        'store': ['Walmart', 'Dia', 'Walmart', 'Fnac', 'Dia','Walmart'],
        'price':[11.42, 23.50, 19.99, 15.95, 55.75, 111.55],
        'testscore': [4, 3, 5, 7, 5, 8]})

# Use `pivot()` to pivot the DataFrame
pivot_products = products.pivot(index='category', columns='store', values='price')

# Check out the result
print(pivot_products)

store            Dia   Fnac  Walmart

category                            

Cleaning       23.50    NaN    11.42

Entertainment    NaN  15.95    19.99

Tech           55.75    NaN   111.55

When you don’t specifically fill in what values you expect to be present in your resulting table, you will pivot by multiple columns:

# Import the Pandas library
import pandas as pd
​
# Construct the DataFrame
products = pd.DataFrame({'category': ['Cleaning', 'Cleaning', 'Entertainment', 'Entertainment', 'Tech', 'Tech'],
                       'store': ['Walmart', 'Dia', 'Walmart', 'Fnac', 'Dia','Walmart'],
                       'price':[11.42, 23.50, 19.99, 15.95, 55.75, 111.55],
                       'testscore': [4, 3, 5, 7, 5, 8]})
​
# Use `pivot()` to pivot your DataFrame
pivot_products = products.pivot(index='category', columns='store')
​
# Check out the results
print(pivot_products)

             price                testscore             

store            Dia   Fnac Walmart       Dia Fnac Walmart

category                                                  

Cleaning       23.50    NaN   11.42       3.0  NaN     4.0

Entertainment    NaN  15.95   19.99       NaN  7.0     5.0

Tech           55.75    NaN  111.55       5.0  NaN     8.0

Note that your data can not have rows with duplicate values for the columns that you specify. If this is not the case, you will get an error message. If you can’t ensure the uniqueness of your data, you will want to use the pivot_table method instead:

# Import the Pandas library
import pandas as pd

# Your DataFrame
products = pd.DataFrame({'category': ['Cleaning', 'Cleaning', 'Entertainment', 'Entertainment', 'Tech', 'Tech'],
                        'store': ['Walmart', 'Dia', 'Walmart', 'Fnac', 'Dia','Walmart'],
                        'price':[11.42, 23.50, 19.99, 15.95, 19.99, 111.55],
                        'testscore': [4, 3, 5, 7, 5, 8]})

# Pivot your `products` DataFrame with `pivot_table()`
pivot_products = products.pivot_table(index='category', columns='store', values='price', aggfunc='mean')

# Check out the results
print(pivot_products)

store            Dia   Fnac  Walmart

category                            

Cleaning       23.50    NaN    11.42

Entertainment    NaN  15.95    19.99

Tech           19.99    NaN   111.55

Note the additional argument aggfunc that gets passed to the pivot_table method. This argument indicates that you use an aggregation function used to combine multiple values. In this example, you can clearly see that the mean function is used.

Using stack() and unstack() to Reshape your Pandas DataFrame

You have already seen an example of stacking in section 5. In essence, you might still remember that when you stack a DataFrame, you make it taller. You move the innermost column index to become the innermost row index. You return a DataFrame with an index with a new inner-most level of row labels.

The inverse of stacking is called unstacking. Much like stack(), you use unstack() to move the innermost row index to become the innermost column index.

For an explanation of pands pivoting, stacking and unstacking, check out our Reshaping Data with pandas course.

Reshape your DataFrame With melt()

Melting is considered useful in cases where you have data that has one or more columns that are identifier variables, while all other columns are considered measured variables.

These measured variables are all “unpivoted” to the row axis. That is, while the measured variables that were spread out over the width of the DataFrame, the melt will make sure that they will be placed in the height of it. Or, yet in other words, your DataFrame will now become longer instead of wider.

As a result, you have two non-identifier columns, namely, ‘variable’ and ‘value’.

Let’s illustrate this with an example:

# The `people` DataFrame
people = pd.DataFrame({'FirstName' : ['John', 'Jane'],
                       'LastName' : ['Doe', 'Austen'],
                       'BloodType' : ['A-', 'B+'],
                       'Weight' : [90, 64]})

# Use `melt()` on the `people` DataFrame
print(pd.melt(people, id_vars=['FirstName', 'LastName'], var_name='measurements'))

 FirstName LastName measurements value

0      John      Doe    BloodType    A-

1      Jane   Austen    BloodType    B+

2      John      Doe       Weight    90

3      Jane   Austen       Weight    64

If you’re looking for more ways to reshape your data, check out the documentation.

Output a DataFrame to CSV

To write a DataFrame as a CSV file, you can use to_csv():

import pandas as pd
df.to_csv('myDataFrame.csv')

That piece of code seems quite simple, but this is just where the difficulties begin for most people because you will have specific requirements for the output of your data. Maybe you don’t want a comma as a delimiter, or you want to specify a specific encoding.

Don’t worry! You can pass some additional arguments to to_csv() to make sure that your data is outputted the way you want it to be!

• To delimit by a tab, use the sep argument:

import pandas as pd
df.to_csv('myDataFrame.csv', sep='\t')

• To use a specific character encoding, you can use the encoding argument:

import pandas as pd
df.to_csv('myDataFrame.csv', sep='\t', encoding='utf-8')

• Furthermore, you can specify how you want your NaN or missing values to be represented, whether or not you want to output the header, whether or not you want to write out the row names, whether you want compression, you can read up on the options.

Writing a DataFrame to Excel

Similarly to what you did to output your DataFrame to CSV, you can use to_excel() to write your table to Excel. However, it is a bit more complicated:

import pandas as pd
writer = pd.ExcelWriter('myDataFrame.xlsx')
df.to_excel(writer, 'DataFrame')
writer.save()

Note, however, that, just like with to_csv(), you have a lot of extra arguments such as startcol, startrow, and so on, to make sure you output your data correctly. You can learn more about how to import data from and export data to CSV files using pandas in our tutorial. 

If, however, you want more information on IO tools in Pandas, you check out the pandas DataFrames to excel documentation.