Pandas: How to pad value for every row that missing years

Question

I have a table contains keyword and its occurrence on each year, but if it doesn't occur in some years, those years are missing.

But I need to pad those years with zero now, how can I do it with Pandas dataframe?

My data is like the table below, each keyword should be padded zero up to 13 years from 2003 to 2015.

+---------+------+-------+
| keyword | year | count |
+---------+------+-------+
| a       | 2003 |     1 |
| a       | 2004 |     2 |
| b       | 2003 |     1 |
| b       | 2005 |     2 |
+---------+------+-------+

Desired result:

+---------+------+-------+
| keyword | year | count |
+---------+------+-------+
| a       | 2003 | 1     |
| a       | 2004 | 2     |
| a       | 2005 | 0     |
| a       | 2006 | 0     |
| a       | 2007 | 0     |
| a       | 2008 | 0     |
| a       | 2009 | 0     |
| a       | 2010 | 0     |
| a       | 2011 | 0     |
| a       | 2012 | 0     |
| a       | 2013 | 0     |
| a       | 2014 | 0     |
| a       | 2015 | 0     |
| b       | 2003 | 1     |
| b       | 2004 | 0     |
| b       | 2005 | 2     |
| b       | 2006 | 0     |
| ...     | ...  | ...   |
+---------+------+-------+

How can I do this? I have searched StackOverflow and only find the answers on non-repeating date, but here my years are repeating.

jezrael jezrael · Accepted Answer · 2019-03-26T11:05:40

You can create new MultiIndex by MultiIndex.from_product, then convert columns to MultiIndex by DataFrame.set_index and DataFrame.reindex:

mux = pd.MultiIndex.from_product([df['keyword'].unique(), 
                                  np.arange(2003, 2016)], names=['keyword','year'])
df = df.set_index(['keyword','year']).reindex(mux, fill_value=0).reset_index()

print (df)
   keyword  year  count
0        a  2003      1
1        a  2004      2
2        a  2005      0
3        a  2006      0
4        a  2007      0
5        a  2008      0
6        a  2009      0
7        a  2010      0
8        a  2011      0
9        a  2012      0
10       a  2013      0
11       a  2014      0
12       a  2015      0
13       b  2003      1
14       b  2004      0
15       b  2005      2
16       b  2006      0
17       b  2007      0
18       b  2008      0
19       b  2009      0
20       b  2010      0
21       b  2011      0
22       b  2012      0
23       b  2013      0
24       b  2014      0
25       b  2015      0

Another solution is create new DataFrame by itertools.product and DataFrame.merge with left join, last repalce missing values by DataFrame.fillna:

from  itertools import product
df1 = pd.DataFrame(list(product(df['keyword'].unique(), 
           np.arange(2003, 2016))), columns=['keyword','year'])
df = df1.merge(df, how='left').fillna({'count':0}, downcast='int')

Pandas: How to pad value for every row that missing years

1 Answers