How to add null value rows into pandas dataframe for missing years in a multi-line chart plot

user_12417398 0
Viewed 99

I am building a chart from a dataframe with a series of yearly values for six countries. This table is created by an SQL query and then passed to pandas with read_sql command...

    country date    value
0     CA    2000    123
1     CA    2001    125
2     US        1999    223
3     US        2000    235
4     US        2001    344
5     US        2002    355
...

Unfortunately, not every year has a value in each country, nevertheless the chart tool requires each country to have the same number of years in the dataframe. Years that have no values need a Nan (null) row added.

In the end, I want the pandas dataframe to look as follows for all six countries....

    country date    value
0    CA     1999    Nan
1     CA    2000    123
2     CA    2001    125
3    CA     2002    Nan
4     US        1999    223
5     US        2000    235
6     US        2001    344
7     US        2002    355
8    DE     1999    Nan
9    DE     2000    Nan
10  DE     2001    423
11  DE     2002    326
...

Are there any tools or shortcuts for determining min-max dates and then ensuring a new nan row is created if needed?

chart.js pandas python
1 Answers

Use Series.unstack with DataFrame.stack trick:

df = df.set_index(['country','date']).unstack().stack(dropna=False).reset_index()
print (df)
  country  date  value
0      CA  1999    NaN
1      CA  2000  123.0
2      CA  2001  125.0
3      CA  2002    NaN
4      US  1999  223.0
5      US  2000  235.0
6      US  2001  344.0
7      US  2002  355.0

Another idea with DataFrame.reindex:

mux = pd.MultiIndex.from_product([df['country'].unique(), 
                                  range(df['date'].min(), df['date'].max() + 1)], 
                                 names=['country','date'])
df = df.set_index(['country','date']).reindex(mux).reset_index()
print (df)
  country  date  value
0      CA  1999    NaN
1      CA  2000  123.0
2      CA  2001  125.0
3      CA  2002    NaN
4      US  1999  223.0
5      US  2000  235.0
6      US  2001  344.0
7      US  2002  355.0
user_29010020
Related