Python add missing values to index

Question

Having the following DF :

Index   Date
1D      9/13/2022
1W      9/19/2022
2W      9/26/2022
3W      10/3/2022
1M      10/12/2022
2M      11/14/2022
3M      12/12/2022
4M      1/12/2023
5M      2/13/2023
6M      3/13/2023
7M      4/12/2023
8M      5/12/2023
9M      6/12/2023
10M     7/12/2023
11M     8/14/2023
12M     9/12/2023
18M     3/12/2024
2Y      9/12/2024
3Y      9/12/2025
4Y      9/14/2026
5Y      9/13/2027
6Y      9/12/2028
7Y      9/12/2029
8Y      9/12/2030
9Y      9/12/2031
10Y     9/13/2032
12Y     9/12/2034
15Y     9/14/2037
20Y     9/12/2042

The idea would be to do a loop, and to do :

if DF.index[i][-1] == 'Y':
   if int(self.dfcurve.index[i+1][:-1])-int(self.dfcurve.index[i][:-1])!= 1:
      ###Add Missing Index:
      index_val = int(self.dfcurve.index[i][:-1]) +1
      index_val = str(index_val)+'Y'
      ### Example of missing index : 
      ## 11Y
      ## 13Y
      ## 14Y
      ## 16Y
      ## 17Y
      ## 18Y
      ## 19Y

But I don't know how to add the index in list at the right place. The final DF would be :

Index   Date
1D      9/13/2022
1W      9/19/2022
2W      9/26/2022
3W      10/3/2022
1M      10/12/2022
2M      11/14/2022
3M      12/12/2022
4M      1/12/2023
5M      2/13/2023
6M      3/13/2023
7M      4/12/2023
8M      5/12/2023
9M      6/12/2023
10M     7/12/2023
11M     8/14/2023
12M     9/12/2023
18M     3/12/2024
2Y      9/12/2024
3Y      9/12/2025
4Y      9/14/2026
5Y      9/13/2027
6Y      9/12/2028
7Y      9/12/2029
8Y      9/12/2030
9Y      9/12/2031
10Y     9/13/2032
11Y     NA
12Y     9/12/2034
13Y     NA
14Y     NA
15Y     9/14/2037
16Y     NA
17Y     NA
18Y     NA
19Y     NA
20Y     9/12/2042

If your list of indices is fixed and known in advance, you could define it in a dictionary, and then fill the values with what's in the dataframe? You would end up having a resulting object that has always the same keys, and values would differ depending on the dataframe. — anteverse
– anteverse, Commented Sep 8, 2022 at 9:40
How do you detect missing index entries? Which criterion is applied here? Do you need a dynamic approach which detects missing entries or can the insertion be hard-coded/static? — albert
– albert, Commented Sep 8, 2022 at 9:41
The idea would be to do if int(self.dfcurve.index[i+1][:-1])-int(self.dfcurve.index[i][:-1])!= 1: Note that int(self.dfcurve.index[i][:-1]) delete the last char so we have only the number, and then we have to check if for example 3-2 = 1 ect.. if it is not add values. — TourEiffel
– TourEiffel, Commented Sep 8, 2022 at 9:51

jezrael · Accepted Answer · 2022-09-08 10:05:32Z

Use:

#filter Y index values
m = df.index.str.endswith('Y')
#processing only years
df1 = df[m].copy()

#extract numbers to index
df1.index = df1.index.str.extract(r'(\d+)', expand=False).astype(int)

#reindex by range for append missing rows
df1 = df1.reindex(range(df1.index.min(), df1.index.max()+1)).rename(index=str)
#added Y substring
df1.index += 'Y'

print (df1)
            Date
Index           
2Y     9/12/2024
3Y     9/12/2025
4Y     9/14/2026
5Y     9/13/2027
6Y     9/12/2028
7Y     9/12/2029
8Y     9/12/2030
9Y     9/12/2031
10Y    9/13/2032
11Y          NaN
12Y    9/12/2034
13Y          NaN
14Y          NaN
15Y    9/14/2037
16Y          NaN
17Y          NaN
18Y          NaN
19Y          NaN
20Y    9/12/2042

#remove Y original rows from Dataframe and append new Y rows
df = pd.concat([df[~m], df1])
print (df)

First solution add to all categories missing values:

df.index = pd.MultiIndex.from_frame(df.index.str.extract(r'(\d+)(\D+)', expand=True))

f = lambda x: x.reindex(range(x.index.min(), x.index.max()+1))
df = df.reset_index(1).rename(index=int).groupby(1).apply(f).drop(1, axis=1)
df.index = df.index.map(lambda x: f'{x[0]}{x[1]}')

print (df)
           Date
D1    9/13/2022
M1   10/12/2022
M2   11/14/2022
M3   12/12/2022

Collectives™ on Stack Overflow

Python add missing values to index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related