Python - reindex monthly values

Question

I have some Dataframes, of monthly averages as follow:

Month,Value1,Value2
02,1,1
03,2,2
04,3,3
06,4,4
07,5,5
08,6,6
09,7,7
10,8,8
12,9,9

My problem is that those Dataframes are missing some months, in the enclosed examples month 1, 5 and 11 are missing.

Therefore I would like to re-index the dataframe and fill the missing Values by NaN as follow:

Month,Value1,Value2
01,NaN,NaN
02,1,1
03,2,2
04,3,3
05,NaN,NaN
06,4,4
07,5,5
08,6,6
09,7,7
10,8,8
11,NaN,NaN
12,9,9

I did this small code:

data = pd.read_csv("test.csv", index_col=[0])
new_index = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']
data = data.reindex(new_index)

Unfortunately, the output is far from the desired one and all the values are now replaced by NaNs:

Month,Value1,Value2
01,NaN,NaN
02,NaN,NaN
03,NaN,NaN
04,NaN,NaN
05,NaN,NaN
06,NaN,NaN
07,NaN,NaN
08,NaN,NaN
09,NaN,NaN
10,NaN,NaN
11,NaN,NaN
12,NaN,NaN

Does anyone know why? and maybe how to fix that?

Answer 1

When you read the csv, the index is of type int64, you can check with following:

data = pd.read_csv("test3.csv", index_col=[0])
print(data.index.dtype)

Result:

int64

Now, when using reindex as below:

new_index = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']
data = data.reindex(new_index)

In above, if new_index is all of type str, it does not match to the existing index and new object will be produced: According to documentation:

Docstring:

Conform DataFrame to new index with optional filling logic, placing NA/NaN in locations having no value in the previous index. A new object is produced unless the new index is equivalent to the current one and copy=False

Hence, you may want to try using new index with type of int instead of str:

new_index = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]   
data = data.reindex(new_index)