Python Pandas add rows based on missing sequential values in a timeseries

Question

I'm new to python and struggling to manipulate data in pandas library. I have a pandas database like this:

    Year  Value
0    91     1
1    93     4
2    94     7
3    95    10
4    98    13

And want to complete the missing years creating rows with empty values, like this:

    Year  Value
0    91     1
1    92     0
2    93     4
3    94     7
4    95    10
5    96     0
6    97     0
7    98    13

How do i do that in Python? (I wanna do that so I can plot Values without skipping years)

maxymoo · Accepted Answer · 2015-06-01 02:23:18Z

I would create a new dataframe that has Year as an Index and includes the entire date range that you need to cover. Then you can simply set the values across the two dataframes, and the index will make sure that they correct rows are matched (I've had to use fillna to set the missing years to zero, by default they will be set to NaN):

df = pd.DataFrame({'Year':[91,93,94,95,98],'Value':[1,4,7,10,13]})
df.index = df.Year
df2 = pd.DataFrame({'Year':range(91,99), 'Value':0})
df2.index = df2.Year

df2.Value = df.Value
df2= df2.fillna(0)
df2
      Value  Year
Year             
91        1    91
92        0    92
93        4    93
94        7    94
95       10    95
96        0    96
97        0    97
98       13    98

Finally you can use reset_index if you don't want Year as your index:

df2.drop('Year',1).reset_index()

   Year  Value
0    91      1
1    92      0
2    93      4
3    94      7
4    95     10
5    96      0
6    97      0
7    98     13

Collectives™ on Stack Overflow

Python Pandas add rows based on missing sequential values in a timeseries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related