How to fill values based on data present in column and an array? Pandas

Question

Lets say I have dataframe with nans in each group like

df = pd.DataFrame({'data':[0,1,2,0,np.nan,2,np.nan,0,1],'group':[1,1,1,2,2,2,3,3,3]})

and a numpy array like

x = np.array([0,1,2])

Now based on groups how to fill the missing values that are in the numpy array I have i.e

df = pd.DataFrame({'data':[0,1,2,0,1,2,2,0,1],'group':[1,1,1,2,2,2,3,3,3]})

      data   group
0     0      1
1     1      1
2     2      1
3     0      2
4     1      2
5     2      2
6     2      3
7     0      3
8     1      3

Let me explain a bit of how the data should be filled. Consider the group 2. The values of data are 0,np.nan,2 . The np.nan is the missing value from the array [0,1,2]. So the data to be filled inplace of nan is 1.

For multiple nan values, take a group for example that has data [np.nan,0,np.nan] now the values to be filled in place of nan are 1 and 2. resulting in [1,0,2].

Fill how? By mean of group or something? Or is like every group has exactly three numbers - 0,1,2? — Divakar, Oct 25 '17 at 16:25
Yes, I will have maximum three rows. So the data should be filled with missing data in the `data` and present in numpy array. If there are 2 values missing then the they should be filled be randomly. — Bharath M Shetty, Oct 25 '17 at 16:27
@ Divakar no 3 elems in a group is compulsary I mean are always present — Bharath M Shetty, Oct 25 '17 at 16:29
You come from the https://stackoverflow.com/questions/46937010/filling-missing-time-values-in-a-multi-indexed-dataframe , right ? I posted an answer there — BENY, Oct 25 '17 at 16:32
What happens if you have more than 3 nans in a row? Another question - reasonably how many NaNs can I expect per group? — cs95, Oct 26 '17 at 13:32
As I said earlier in the comments there will be only 3 rows in each group and three rows are always present. There can be maximum of three nans. — Bharath M Shetty, Oct 26 '17 at 13:33

jezrael · Accepted Answer · 2017-10-26T13:50:34.690

4

First find value which miss and then add it to fillna:

def f(y):
    a = list(set(x)-set(y))
    a = 1 if len(a) == 0 else a[0]
    y = y.fillna(a)
    return (y)

df['data'] = df.groupby('group')['data'].apply(f).astype(int)
print (df)
   data  group
0     0      1
1     1      1
2     2      1
3     0      2
4     1      2
5     2      2
6     2      3
7     0      3
8     1      3

EDIT:

df = pd.DataFrame({'data':[0,1,2,0,np.nan,2,np.nan,np.nan,1, np.nan, np.nan, np.nan],
                   'group':[1,1,1,2,2,2,3,3,3,4,4,4]})
x = np.array([0,1,2])
print (df)
    data  group
0    0.0      1
1    1.0      1
2    2.0      1
3    0.0      2
4    NaN      2
5    2.0      2
6    NaN      3
7    NaN      3
8    1.0      3
9    NaN      4
10   NaN      4
11   NaN      4

def f(y):
    a = list(set(x)-set(y))
    if len(a) == 1:
        return y.fillna(a[0])
    elif len(a) == 2:
        return y.fillna(a[0], limit=1).fillna(a[1])
    elif len(a) == 3:
        y = pd.Series(x, index=y.index)
        return y
    else:
        return y

df['data'] = df.groupby('group')['data'].apply(f).astype(int)
print (df)
    data  group
0      0      1
1      1      1
2      2      1
3      0      2
4      1      2
5      2      2
6      0      3
7      2      3
8      1      3
9      0      4
10     1      4
11     2      4

edited Oct 26 '17 at 13:50

answered Oct 26 '17 at 13:15

jezrael

822,522
95
1,334
1,252

Sir do try this for multiple nans in one group na. – Bharath M Shetty Oct 26 '17 at 13:23
I am doing on it – jezrael Oct 26 '17 at 13:23
@Bharathshetty It seems like jez understood your question better than I did. I think I'll leave it to him to answer your Q. – cs95 Oct 26 '17 at 13:27
Well @cᴏʟᴅsᴘᴇᴇᴅ I updated my question. Hope you find that information usefull. – Bharath M Shetty Oct 26 '17 at 13:27
Really hard, please check my solution. – jezrael Oct 26 '17 at 13:35
Sir really nice solution. My bad I had changed the array x to some other values. – Bharath M Shetty Oct 26 '17 at 13:38
@cᴏʟᴅsᴘᴇᴇᴅ - Thanks – jezrael Oct 26 '17 at 14:13

How to fill values based on data present in column and an array? Pandas

1 Answers1