Python Pandas - Reindexing

Reindexing is a powerful and fundamental operation in Pandas that allows you to align your data with a new set of labels. Whether you're working with rows or columns, reindexing gives you control over how your data aligns with the labels you specify.

This operation is especially useful when working with time series data, aligning datasets from different sources, or simply reorganizing data to match a particular structure.

What is Reindexing?

Reindexing in Pandas refers to the process of conforming your data to match a new set of labels along a specified axis (rows or columns). This process can accomplish several tasks −

• Reordering: Reorder the existing data to match a new set of labels.

• Inserting Missing Values: If a label in the new set does not exist in the original data, Pandas will insert a missing value (NaN) for that label.

• Filling Missing Data: You can specify how to fill in missing values that result from reindexing, using various filling methods.

The reindex() method is the primary tool for performing reindexing in Pandas. It allows you to modify the row and column labels of Pandas data structures.

Key Methods Used in Reindexing

• reindex(): This method is used to align an existing data structure with a new index (or columns). It can reorder and/or insert missing labels.

• reindex_like(): This method allows you to reindex one DataFrame or Series to match another. It's useful when you want to ensure two data structures are aligned similarly.

• Filling Methods: When reindexing introduces NaN values, you can fill them using methods like ffill, bfill, and nearest.

Example: Reindexing a Pandas Series

The following example demonstrates reindexing a Pandas Series object using the reindex() method. In this case, the "f" label was not present in the original Series, so it appears as NaN in the output reindexed Series.

 import pandas as pd import numpy as np s = pd.Series(np.random.randn(5), index=["a", "b", "c", "d", "e"]) print("Original Series:\n",s) s_reindexed = s.reindex(["e", "b", "f", "d"]) print('\nOutput Reindexed Series:\n',s_reindexed) 

On executing the above code you will get the following output −

 Original Series: a 0.148874 b 0.592275 c -0.903546 d 1.031230 e -0.254599 dtype: float64 Output Reindexed Series: e -0.254599 b 0.592275 f NaN d 1.031230 dtype: float64 

Example: Reindexing a DataFrame

Consider the following example of reindexing a DataFrame using the reindex() method. With a DataFrame, you can reindex both the rows (index) and columns.

 import pandas as pd import numpy as np N=5 df = pd.DataFrame({ 'A': pd.date_range(start='2016-01-01',periods=N,freq='D'), 'x': np.linspace(0,stop=N-1,num=N), 'y': np.random.rand(N), 'C': np.random.choice(['Low','Medium','High'],N).tolist(), 'D': np.random.normal(100, 10, size=(N)).tolist() }) print("Original DataFrame:\n", df) #reindex the DataFrame df_reindexed = df.reindex(index=[0,2,5], columns=['A', 'C', 'B']) print("\nOutput Reindexed DataFrame:\n",df_reindexed) 

Its output is as follows −

 Original DataFrame: A x y C D 0 2016-01-01 0.0 0.513990 Medium 118.143385 1 2016-01-02 1.0 0.751248 Low 91.041201 2 2016-01-03 2.0 0.332970 Medium 100.644345 3 2016-01-04 3.0 0.723816 High 108.810386 4 2016-01-05 4.0 0.376326 High 101.346443 Output Reindexed DataFrame: A C B 0 2016-01-01 Medium NaN 2 2016-01-03 Medium NaN 5 NaT NaN NaN 

Reindex to Align with Other Objects

Sometimes, you may need to reindex one DataFrame to align it with another. The reindex_like() method allows you to do this seamlessly.

Example

The following example demonstrates how to reindex a DataFrame (df1) to match another DataFrame (df2) using the reindex_like() method.

 import pandas as pd import numpy as np df1 = pd.DataFrame(np.random.randn(10,3),columns=['col1','col2','col3']) df2 = pd.DataFrame(np.random.randn(7,3),columns=['col1','col2','col3']) df1 = df1.reindex_like(df2) print(df1) 

Its output is as follows −

 col1 col2 col3 0 -2.467652 -1.211687 -0.391761 1 -0.287396 0.522350 0.562512 2 -0.255409 -0.483250 1.866258 3 -1.150467 -0.646493 -0.222462 4 0.152768 -2.056643 1.877233 5 -1.155997 1.528719 -1.343719 6 -1.015606 -1.245936 -0.295275 

Note: Here, the df1 DataFrame is altered and reindexed like df2. The column names should be matched or else NAN will be added for the entire column label.

Filling While ReIndexing

The reindex() method provides an optional parameter method for filling missing values. The available methods include −

• pad/ffill: Fill values forward.

• bfill/backfill: Fill values backward.

• nearest: Fill from the nearest index values.

Example

The following example demonstrates the working of the ffill method.

 import pandas as pd import numpy as np df1 = pd.DataFrame(np.random.randn(6, 3), columns=['col1', 'col2', 'col3']) df2 = pd.DataFrame(np.random.randn(2, 3), columns=['col1', 'col2', 'col3']) # Padding NaNs print(df2.reindex_like(df1)) # Now fill the NaNs with preceding values print("Data Frame with Forward Fill:") print(df2.reindex_like(df1, method='ffill')) 

Its output is as follows −

 col1 col2 col3 0 1.311620 -0.707176 0.599863 1 -0.423455 -0.700265 1.133371 2 NaN NaN NaN 3 NaN NaN NaN 4 NaN NaN NaN 5 NaN NaN NaN Data Frame with Forward Fill: col1 col2 col3 0 1.311620 -0.707176 0.599863 1 -0.423455 -0.700265 1.133371 2 -0.423455 -0.700265 1.133371 3 -0.423455 -0.700265 1.133371 4 -0.423455 -0.700265 1.133371 5 -0.423455 -0.700265 1.133371 

Note: The last four rows are padded.

Limits on Filling While Reindexing

The limit argument provides additional control over filling while reindexing. The limit specifies the maximum count of consecutive matches.

Example

Let us consider the following example to understand specifying limits on filling −

 import pandas as pd import numpy as np df1 = pd.DataFrame(np.random.randn(6,3),columns=['col1','col2','col3']) df2 = pd.DataFrame(np.random.randn(2,3),columns=['col1','col2','col3']) # Padding NaNs print(df2.reindex_like(df1)) # Now fill the NaNs with preceding values print("Data Frame with Forward Fill limiting to 1:") print(df2.reindex_like(df1, method='ffill', limit=1)) 

Its output is as follows −

 col1 col2 col3 0 0.247784 2.128727 0.702576 1 -0.055713 -0.021732 -0.174577 2 NaN NaN NaN 3 NaN NaN NaN 4 NaN NaN NaN 5 NaN NaN NaN Data Frame with Forward Fill limiting to 1: col1 col2 col3 0 0.247784 2.128727 0.702576 1 -0.055713 -0.021732 -0.174577 2 -0.055713 -0.021732 -0.174577 3 NaN NaN NaN 4 NaN NaN NaN 5 NaN NaN NaN 

Note: The forward fill (ffill) is limited to only one row.