reindex sorted pandas dataframe

Question

I have a dataframe like so:

    Column A    Column B    Date       Value
1          A           1   2011-01-01   10
2          B           1   2011-01-01   10
3          A           2   2011-01-01   10
4          B           2   2011-01-01   10
5          A           1   2011-01-02   10
6          B           1   2011-01-02   10
7          A           2   2011-01-02   10
8          B           2   2011-01-02   10
9          A           1   2011-01-03   10
10         B           1   2011-01-03   10
11         B           2   2011-01-03   10

I want to find missing dates for every value of A and B (in this case, it would be A, date: 2011-01-03), and insert NaN there. I tried the reindex function:

df.sort_values(['Column A','Column B'],ascending = [True,True], inplace = True)
df.index = range(1,len(df)+1)
dates = pd.date_range('2011-01-01','2011-01-03')
df = df.reindex(dates, fill_value = None)
print df

But it gives me NaN in every column. Does anyone have any suggestions as to how I can flag these missing values?

piRSquared · Accepted Answer · 2016-06-15 23:58:12Z

3

Try this:

df.set_index(['Column A', 'Column B', 'Date']).unstack().stack(dropna=False).sort_index(level=[2, 0]).reset_index()

   Column A  Column B        Date  Value
0         A         1  2011-01-01   10.0
1         B         1  2011-01-01   10.0
2         A         2  2011-01-01   10.0
3         B         2  2011-01-01   10.0
4         A         1  2011-01-02   10.0
5         B         1  2011-01-02   10.0
6         A         2  2011-01-02   10.0
7         B         2  2011-01-02   10.0
8         A         1  2011-01-03   10.0
9         B         1  2011-01-03   10.0
10        A         2  2011-01-03    NaN
11        B         2  2011-01-03   10.0

Note: row 10 has the missing value NaN

edited Jun 15, 2016 at 23:58

answered Jun 15, 2016 at 23:50

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

megadarkfriend Over a year ago

This doesn't give me the missing value. I want a row for the missing value to be added whose Value will be NaN

piRSquared Over a year ago

I believe this takes care of that.

megadarkfriend Over a year ago

You are correct, the values had not persisted. Thanks for the solution

Collectives™ on Stack Overflow

reindex sorted pandas dataframe

1 Answer 1

Note: row 10 has the missing value NaN

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Note: row 10 has the missing value NaN

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related