How to fill continuous rows to panda dataframe?

user_12029343 0
Viewed 251

I have a dataframe which looks like:

    name  date         value
0    a    2020-01-01   1
1    a    2020-01-03   1
2    a    2020-01-05   1
3    b    2020-01-02   1
4    b    2020-01-03   1
5    b    2020-01-04   1
6    b    2020-01-05   1

where the values were calculate by value_df = df.groupby(['name', 'date'], as_index=False).value.sum()

how can I make it to following:

    name  date         value
0    a    2020-01-01   1
1    a    2020-01-02   1
2    a    2020-01-03   1
3    a    2020-01-04   1
4    a    2020-01-05   1
5    b    2020-01-01   1
6    b    2020-01-02   1
7    b    2020-01-03   1
8    b    2020-01-04   1
9    b    2020-01-05   1

I tried 

date_index = pd.date_range(start=min(df['date']), end=max(df['date']))
value_df['value'] = pd.Series(value_df['value'])
value_df.reindex(date_index)

Which has made no difference.

pandas time-series dataframe python
3 Answers

Try pivoting and then stack:

date_index = pd.date_range(start=df['date'].min(), end=df['date'].max())

(df.pivot_table('value','name','date',fill_value=1)
 .reindex(date_index,axis=1).reset_index().melt('name',var_name='date'))

Or:

(df.pivot_table('value','name','date',fill_value=1)
   .reindex(date_index,axis=1).stack().reset_index(name='value'))

  name        date  value
0    a  2020-01-01      1
1    a  2020-01-02      1
2    a  2020-01-03      1
3    a  2020-01-04      1
4    a  2020-01-05      1
5    b  2020-01-01      1
6    b  2020-01-02      1
7    b  2020-01-03      1
8    b  2020-01-04      1
9    b  2020-01-05      1
user_98406370

We can do pivot then stack 

s=df.pivot(*df.columns).ffill().bfill().stack().to_frame('value').reset_index()
Out[199]: 
  name        date  value
0    a  2020-01-01    1.0
1    a  2020-01-02    1.0
2    a  2020-01-03    1.0
3    a  2020-01-04    1.0
4    a  2020-01-05    1.0
5    b  2020-01-01    1.0
6    b  2020-01-02    1.0
7    b  2020-01-03    1.0
8    b  2020-01-04    1.0
9    b  2020-01-05    1.0
user_79645270

In case your data (value) is not all 1, you can do ffill,bfill:

(df.set_index(['date','name'])
   .unstack().reindex(date_index)
   .ffill().bfill()
   .stack()
   .reset_index()
)

Output:

        date name  value
0 2020-01-01    a    1.0
1 2020-01-01    b    1.0
2 2020-01-02    a    1.0
3 2020-01-02    b    1.0
4 2020-01-03    a    1.0
5 2020-01-03    b    1.0
6 2020-01-04    a    1.0
7 2020-01-04    b    1.0
8 2020-01-05    a    1.0
9 2020-01-05    b    1.0
user_42384080
Related