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.
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 −
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.
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
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
Sometimes, you may need to reindex one DataFrame to align it with another. The reindex_like() method allows you to do this seamlessly.
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.
The reindex() method provides an optional parameter method for filling missing values. The available methods include −
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.
The limit argument provides additional control over filling while reindexing. The limit specifies the maximum count of consecutive matches.
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.