Add empty rows to python dataframe

Question

I have a pd.dataframe df that looks like this:

key_value    a      date      b
value_01     1   03/17/2018   10
value_01     2   03/18/2018   12
value_01     3   03/19/2018   13
value_02     1   04/01/2018   24
value_02     2   04/02/2018   27
value_02     3   04/03/2018   33
value_03     1   03/17/2018   13
value_03     2   03/18/2018   17
value_03     3   03/19/2018   22

So now based on the key_value, I want to add say n(3 for now) amount of Empty Rows at the end of each key segment, so it should look something like this:

key_value    a      date      b
value_01     1   03/17/2018   10
value_01     2   03/18/2018   12
value_01     3   03/19/2018   13
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN
value_02     1   04/01/2018   24
value_02     2   04/02/2018   27
value_02     3   04/03/2018   33
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN
value_03     1   03/17/2018   13
value_03     2   03/18/2018   17
value_03     3   03/19/2018   22
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN
NaN         NaN     NaN       NaN

I have tried thinking of a way using grouby based on lease, but couldn't figure out how to do it. Thank you for all your help in advance.

Joe · Accepted Answer · 2018-08-08 13:53:50Z

This should work: First find the index of the rows where the values change, and then from the bottom insert theNaN.

l = df.index[(df.key_value != df.key_value.shift(-1)) == True].tolist()
a = [np.nan, np.nan, np.nan, np.nan]
for i in reversed([x+1 for x in l]):
    for j in range(3):
        df = pd.DataFrame(np.insert(df.values, i, values=a, axis=0))
df.columns = ['key_value','a', 'date', 'b']

Output:

   key_value    a      date      b
0   value_01    1  03/17/2018   10
1   value_01    2  03/18/2018   12
2   value_01    3  03/19/2018   13
3        NaN  NaN         NaN  NaN
4        NaN  NaN         NaN  NaN
5        NaN  NaN         NaN  NaN
6   value_02    1  04/01/2018   24
7   value_02    2  04/02/2018   27
8   value_02    3  04/03/2018   33
9        NaN  NaN         NaN  NaN
10       NaN  NaN         NaN  NaN
11       NaN  NaN         NaN  NaN
12  value_03    1  03/17/2018   13
13  value_03    2  03/18/2018   17
14  value_03    3  03/19/2018   22
15       NaN  NaN         NaN  NaN
16       NaN  NaN         NaN  NaN
17       NaN  NaN         NaN  NaN

It works. Is there any way I could also ffill the key_value in those 3 empty rows.
That worked, just one last question if it's possible is there a way to increment the date's for those 3 columns too, I figured out a way to do columna
Right now i cannot try on pc, but i guess you should try something like ffill with df['date']) + timedelta(days=1))

Collectives™ on Stack Overflow

Add empty rows to python dataframe

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related