Groupby and get values from another column

user_12957999 0
Viewed 815

I have a huge dataset arranged like this

Serial     Val1      Val2      Val3     
1          21.10                         
1          43.06                         
1          32.12                         
2                    11.20               
2                    22.20               
3          45.10                         
3          14.16                         
4                              34.90     
4                              12.12     
4                              18.09

I would like to groupby each unique serial and consolidate its corresponding values (from Val1 to Val3) to one column ['All'] and also place a ['Source'] column.

Serial     Val1      Val2      Val3      All       Source
1          21.10                         21.10     Val1
1          43.06                         43.06
1          32.12                         32.12
2                    11.20               11.20     Val2
2                    22.20               22.20
3          45.10                         45.10     Val1
3          14.16                         14.16
4                              34.90     34.90     Val3
4                              12.12     12.12
4                              18.09     18.09

I tried doing something like this, df['All'] = df['Serial'].map(df.groupby('Serial').apply(lambda x: x['Val2'] if pd.isnull(x['Val1']) else x['Val3'])

pandas python-3.x python
3 Answers

If there is only one value filled non missing values first select columns for processing, e.g. here omit first by indexing in DataFrame.iloc, then get mean with compare non mising values by DataFrame.notna with DataFrame.idxmax for column name by first Trues, last add Series.mask for only first values per groups by Series.duplicated:

df1 = df.iloc[:, 1:]
df = df.assign(All = df1.mean(axis=1),
               Source = df1.notna().idxmax(axis=1).mask(df['Serial'].duplicated()))
print (df)
   Serial   Val1  Val2   Val3    All Source
0       1  21.10   NaN    NaN  21.10   Val1
1       1  43.06   NaN    NaN  43.06    NaN
2       1  32.12   NaN    NaN  32.12    NaN
3       2    NaN  11.2    NaN  11.20   Val2
4       2    NaN  22.2    NaN  22.20    NaN
5       3  45.10   NaN    NaN  45.10   Val1
6       3  14.16   NaN    NaN  14.16    NaN
7       4    NaN   NaN  34.90  34.90   Val3
8       4    NaN   NaN  12.12  12.12    NaN
9       4    NaN   NaN  18.09  18.09    NaN

If possible only missing values per some row is necessary add another mask for prevent matching first NaN column:

df1 = df.iloc[:, 1:]

mask = df1.isna().all(axis=1)

df = df.assign(All = df1.mean(axis=1),
               Source = df1.notna().idxmax(axis=1).mask(df['Serial'].duplicated() | mask))
print (df)
   Serial   Val1  Val2   Val3    All Source
0       1  21.10   NaN    NaN  21.10   Val1
1       1  43.06   NaN    NaN  43.06    NaN
2       1  32.12   NaN    NaN  32.12    NaN
3       2    NaN  11.2    NaN  11.20   Val2
4       2    NaN  22.2    NaN  22.20    NaN
5       3  45.10   NaN    NaN  45.10   Val1
6       3  14.16   NaN    NaN  14.16    NaN
7       4    NaN   NaN  34.90  34.90   Val3
8       4    NaN   NaN  12.12  12.12    NaN
9       5    NaN   NaN    NaN    NaN    NaN

Performance in small DataFrame - solution above is 329 times faster with sample data:

#10k rows
df = pd.concat([df] * 1000, ignore_index=True)


In [109]: %%timeit
     ...: def lastVal(row):
     ...:     lvi = row.last_valid_index()
     ...:     return pd.Series({'All': row.loc[lvi], 'Source': lvi})
     ...: 
     ...: result = df.join(df.apply(lastVal, axis=1))
     ...: result.Source = np.where(result.Source != result.Source.shift(), result.Source, '')
     ...: 
4.97 s ± 254 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [110]: %%timeit
     ...: df1 = df.iloc[:, 1:]
     ...: df.assign(All = df1.mean(axis=1), Source = df1.notna().idxmax(axis=1).mask(df['Serial'].duplicated()))
     ...: 
15.1 ms ± 549 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
user_29010020

To get values of both new columns, for each row:

  • retrieve the last valid index,
  • retrieve the value indicated by it,
  • return the value and index, as a Series, with proper column names.

To to it, define the following function:

def lastVal(row):
    lvi = row.last_valid_index()
    return pd.Series({'All': row.loc[lvi], 'Source': lvi})

Then apply it and join the result with the original DataFrame:

result = df.join(df.apply(lastVal, axis=1))

The result is:

   Serial   Val1  Val2   Val3    All Source
0       1  21.10   NaN    NaN  21.10   Val1
1       1  43.06   NaN    NaN  43.06   Val1
2       1  32.12   NaN    NaN  32.12   Val1
3       2    NaN  11.2    NaN  11.20   Val2
4       2    NaN  22.2    NaN  22.20   Val2
5       3  45.10   NaN    NaN  45.10   Val1
6       3  14.16   NaN    NaN  14.16   Val1
7       4    NaN   NaN  34.90  34.90   Val3
8       4    NaN   NaN  12.12  12.12   Val3
9       4    NaN   NaN  18.09  18.09   Val3

If you are unhappy about "repeating" values in Source column, run:

result.Source.mask(result.Source == result.Source.shift(), '', inplace=True)
user_73884770

The best way to tackle such problem is to "Un-pivot Columns".Un-pivoting is useful for problem like yours which will make data visibility and analysis harder as the size of columns grows over time.

The way to un-pivot columns in pandas is by Using melt function.

Solution:

# return new dataframe after unpivoting columns 
df_unpivoted=df.melt(id_vars=['Series'],var_name='Source',value_name='All')
#remove null for dataframe as you have them for all value columns
df_unpivoted.dropna(inplace=True)
#try
df_unpivoted.head()

Additional you can now add the values columns from the old data-frame to this new one !!

user_89624000
Related