Insert empty row after every Nth row in pandas dataframe-CodePudding

I have a dataframe:

pd.DataFrame(columns=['a','b'],data=[[3,4],
[5,5],[9,3],[1,2],[9,9],[6,5],[6,5],[6,5],[6,5],
[6,5],[6,5],[6,5],[6,5],[6,5],[6,5],[6,5],[6,5]])

I want to insert two empty rows after every third row so the resulting output looks like that:

    a   b
0   3.0 4.0
1   5.0 5.0
2   9.0 3.0
3   NaN NaN
4   NaN NaN
5   1.0 2.0
6   9.0 9.0
7   6.0 5.0
8   NaN NaN
9   NaN NaN
10  6.0 5.0
11  6.0 5.0
12  6.0 5.0
13  NaN NaN
14  NaN NaN
15  6.0 5.0
16  6.0 5.0
17  6.0 5.0
18  NaN NaN
19  NaN NaN
20  6.0 5.0
21  6.0 5.0
22  6.0 5.0
23  NaN NaN
24  NaN NaN
25  6.0 5.0
26  6.0 5.0

I tried a number of things but didn't get any closer to the desired output.

CodePudding user response：

The following should scale well with the size of the DataFrame since it doesn't iterate over the rows and doesn't create intermediate DataFrames.

import pandas as pd

df = pd.DataFrame(columns=['a','b'],data=[[3,4],
    [5,5],[9,3],[1,2],[9,9],[6,5],[6,5],[6,5],[6,5],
    [6,5],[6,5],[6,5],[6,5],[6,5],[6,5],[6,5],[6,5]])

def add_empty_rows(df, n_empty, period):
    """ adds 'n_empty' empty rows every 'period' rows  to 'df'. 
        Returns a new DataFrame. """
    
    # to make sure that the DataFrame index is a RangeIndex(start=0, stop=len(df)) 
    # and that the original df object is not mutated. 
    df = df.reset_index(drop=True)
    
    # length of the new DataFrame containing the NaN rows
    len_new_index = len(df)   n_empty*(len(df) // period)
    # index of the new DataFrame
    new_index = pd.RangeIndex(len_new_index)
    
    # add an offset (= number of NaN rows up to that row) 
    # to the current df.index to align with new_index. 
    df.index  = n_empty * (df.index
                             .to_series()
                             .groupby(df.index // period)
                             .ngroup())
    
    # reindex by aligning df.index with new_index. 
    # Values of new_index not present in df.index are filled with NaN.
    new_df = df.reindex(new_index)
    
    return new_df

Tests:

# original df
>>> df

    a  b
0   3  4
1   5  5
2   9  3
3   1  2
4   9  9
5   6  5
6   6  5
7   6  5
8   6  5
9   6  5
10  6  5
11  6  5
12  6  5
13  6  5
14  6  5
15  6  5
16  6  5

# add 2 empty rows every 3 rows
>>> add_empty_rows(df, 2, 3)

      a    b
0   3.0  4.0
1   5.0  5.0
2   9.0  3.0
3   NaN  NaN
4   NaN  NaN
5   1.0  2.0
6   9.0  9.0
7   6.0  5.0
8   NaN  NaN
9   NaN  NaN
10  6.0  5.0
11  6.0  5.0
12  6.0  5.0
13  NaN  NaN
14  NaN  NaN
15  6.0  5.0
16  6.0  5.0
17  6.0  5.0
18  NaN  NaN
19  NaN  NaN
20  6.0  5.0
21  6.0  5.0
22  6.0  5.0
23  NaN  NaN
24  NaN  NaN
25  6.0  5.0
26  6.0  5.0

# add 5 empty rows every 4 rows
>>> add_empty_rows(df, 5, 4)

      a    b
0   3.0  4.0
1   5.0  5.0
2   9.0  3.0
3   1.0  2.0
4   NaN  NaN
5   NaN  NaN
6   NaN  NaN
7   NaN  NaN
8   NaN  NaN
9   9.0  9.0
10  6.0  5.0
11  6.0  5.0
12  6.0  5.0
13  NaN  NaN
14  NaN  NaN
15  NaN  NaN
16  NaN  NaN
17  NaN  NaN
18  6.0  5.0
19  6.0  5.0
20  6.0  5.0
21  6.0  5.0
22  NaN  NaN
23  NaN  NaN
24  NaN  NaN
25  NaN  NaN
26  NaN  NaN
27  6.0  5.0
28  6.0  5.0
29  6.0  5.0
30  6.0  5.0
31  NaN  NaN
32  NaN  NaN
33  NaN  NaN
34  NaN  NaN
35  NaN  NaN
36  6.0  5.0

CodePudding user response：

Try this:

(pd.concat([df,pd.DataFrame([[np.NaN]*2],
index = [i for i in df.index if i%3 == 2] * 2,
columns = list('ab'))])
.sort_index()
.reset_index(drop=True))

Output:

    a   b
0   3.0 4.0
1   5.0 5.0
2   9.0 3.0
3   NaN NaN 
4   NaN NaN 
5   1.0 2.0
6   9.0 9.0
7   6.0 5.0
8   NaN NaN 
9   NaN NaN 
10  6.0 5.0
11  6.0 5.0
12  6.0 5.0
13  NaN NaN 
14  NaN NaN 
15  6.0 5.0
16  6.0 5.0
17  6.0 5.0
18  NaN NaN 
19  NaN NaN 
20  6.0 5.0
21  6.0 5.0
22  6.0 5.0
23  NaN NaN 
24  NaN NaN 
25  6.0 5.0
26  6.0 5.0

CodePudding user response：

You can iterate over rows and add two rows every third rows

data = [[row.tolist(), [pd.NA]*len(row), [pd.NA]*len(row)]
        if (idx 1) % 3 == 0 else [row.tolist()]
        for idx, row in df.iterrows()]

out = pd.DataFrame([i for lst in data for i in lst], columns=df.columns)

print(data)

[[[3, 4]],
 [[5, 5]],
 [[9, 3], [<NA>, <NA>], [<NA>, <NA>]],
 [[1, 2]],
 [[9, 9]],
 [[6, 5], [<NA>, <NA>], [<NA>, <NA>]],
 [[6, 5]],
 [[6, 5]],
 [[6, 5], [<NA>, <NA>], [<NA>, <NA>]],
 [[6, 5]],
 [[6, 5]],
 [[6, 5], [<NA>, <NA>], [<NA>, <NA>]],
 [[6, 5]],
 [[6, 5]],
 [[6, 5], [<NA>, <NA>], [<NA>, <NA>]],
 [[6, 5]],
 [[6, 5]]]

print(out)

       a     b
0      3     4
1      5     5
2      9     3
3   <NA>  <NA>
4   <NA>  <NA>
5      1     2
6      9     9
7      6     5
8   <NA>  <NA>
9   <NA>  <NA>
10     6     5
11     6     5
12     6     5
13  <NA>  <NA>
14  <NA>  <NA>
15     6     5
16     6     5
17     6     5
18  <NA>  <NA>
19  <NA>  <NA>
20     6     5
21     6     5
22     6     5
23  <NA>  <NA>
24  <NA>  <NA>
25     6     5
26     6     5