CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
user9098929
user9098929

Reputation:

Python Pandas: How to Insert one Missing Row?

For the following dataframe: every group of c should have three values of b. The second value of a should be the average of the first and third value of a.

What is the easiest way to insert the "missing" row with a=48, b=42, c=4 between index = 0 and index = 1?

df_x = pd.DataFrame({"a": [47, 49, 55, 54, 53, 24, 27, 30], "b": [41, 43, 51, 52, 53, 41, 42, 43], "c": [4, 4, 5, 5, 5, 4, 4, 4]})
df_x
Out[14]: 
    a   b  c
0  47  41  4
1  49  43  4
2  55  51  5
3  54  52  5
4  53  53  5
5  24  41  4
6  27  42  4
7  30  43  4

If I use groupby('c').transform(my_func) or groupby('c').apply(my_func), I face the situation that the first call to my function my_func is done twice.

Upvotes: 1

Views: 186

Answers (2)

user9098929
user9098929

Reputation:

a semipathetic solution would be the following.

Does anybody have ideas how to make the for loop more efficient / respectively avoid it at all?

import pandas as pd
df_x = pd.DataFrame({"a": [47, 49, 55, 54, 53, 24, 27, 30], "b": [41, 43, 51, 52, 53, 41, 42, 43], "c": [4, 4, 5, 5, 5, 4, 4, 4]})
df_x
Out[11]: 
    a   b  c
0  47  41  4
1  49  43  4
2  55  51  5
3  54  52  5
4  53  53  5
5  24  41  4
6  27  42  4
7  30  43  4
# make new column that allows group by
df_x['cumsum']=(df_x.c != df_x.c.shift()).cumsum()
df_x
Out[14]: 
    a   b  c  cumsum
0  47  41  4       1
1  49  43  4       1
2  55  51  5       2
3  54  52  5       2
4  53  53  5       2
5  24  41  4       3
6  27  42  4       3
7  30  43  4       3
# introduce index spreaded by 10
df_x['index10'] = df_x.index * 10
print(df_x)
    a   b  c  cumsum  index10
0  47  41  4       1        0
1  49  43  4       1       10
2  55  51  5       2       20
3  54  52  5       2       30
4  53  53  5       2       40
5  24  41  4       3       50
6  27  42  4       3       60
7  30  43  4       3       70
groupby = df_x.groupby("cumsum")
# initialize dataframe for new row
df_x_append = pd.DataFrame()
for key, item in groupby:
    # sub dataframe is too small
    if item.shape[0]!=3:
        # new entry will be in the middle of existing values
        my_index = item.index10[0]+5
        # create temporary dataframe
        df_x_single = pd.DataFrame({"index10":[my_index], "a": [(item.a[0]+item.a[1])/2], "b": [(item.b[0]+item.b[1])/2],"c":[item.c[0]]})
        # append this dataframe
        df_x_append = df_x_append.append(df_x_single)
df_x=df_x.append(df_x_append)
# sort by spreaded index
df_x=df_x.sort_values(by='index10', ascending=True, na_position='first')
print(df_x)
      a     b  c  cumsum  index10
0  47.0  41.0  4     1.0        0
0  48.0  42.0  4     NaN        5
1  49.0  43.0  4     1.0       10
2  55.0  51.0  5     2.0       20
3  54.0  52.0  5     2.0       30
4  53.0  53.0  5     2.0       40
5  24.0  41.0  4     3.0       50
6  27.0  42.0  4     3.0       60
7  30.0  43.0  4     3.0       70
# set spreaded index and remove it
df_x=df_x.set_index('index10')
df_x = df_x.reset_index().drop(["index10"], axis=1)
print(df_x)
      a     b  c  cumsum
0  47.0  41.0  4     1.0
1  48.0  42.0  4     NaN
2  49.0  43.0  4     1.0
3  55.0  51.0  5     2.0
4  54.0  52.0  5     2.0
5  53.0  53.0  5     2.0
6  24.0  41.0  4     3.0
7  27.0  42.0  4     3.0
8  30.0  43.0  4     3.0

Upvotes: 1

Tai
Tai

Reputation: 7994

pandas's insert method only works for columns. We can use numpy.insert. Cons: this will create a new dataset. This should serve as an alternative to pd.concat or pd.append or pd.merge. 

df_x = pd.DataFrame({"a": [47, 49, 55, 54, 53, 24, 27, 30], "b": [41, 43, 51, 52, 53, 41, 42, 43], "c": [4, 4, 5, 5, 5, 4, 4, 4]})

pd.DataFrame(np.insert(df_x.values, 1, values=[48, 42, 4], axis=0))


    0   1   2
0   47  41  4
1   48  42  4
2   49  43  4
3   55  51  5
4   54  52  5
5   53  53  5
6   24  41  4
7   27  42  4
8   30  43  4

In np.insert(df_x.values, 1, values=[48, 42, 4], axis=0), 1 tells the function the place/index you want to place the new values.

Upvotes: 1

Related Questions