CODEWITHSUNDEEP
pythonpandasnumpydataframe
CodeGeek123
CodeGeek123

Reputation: 4501

Splitting a Column on Positive and Negative values

How do you split a column into two different columns based on a criteria, but maintain one key? For example

      col1  col2   time       value
0      A     sdf  16:00:00     100
1      B     sdh  17:00:00     -40
2      A     sf   18:00:45     300 
3      D     sfd  20:04:33     -89

I want a new dataframe like this

     time       main_val    sub_val
0   16:00:00     100         NaN
1   17:00:00     NaN         -40
2   18:00:45     300         NaN
3   20:04:33     NaN         -89

Upvotes: 7

Views: 2962

Answers (6)

b2002
b2002

Reputation: 914

use numpy where when creating new columns to pick from nans or column values (slightly faster than df.where, inspired by the excellent answer from Kamaraju Kusumanchi)

vals = df.value.values
nans = np.full(len(df), np.nan)
df2 = df[['time']].copy()
df2['main_val'] = np.where(vals < 0, nans, vals)
df2['sub_val'] = np.where(vals >= 0, nans, vals)

print(df2)

       time  main_val  sub_val
0  16:00:00     100.0      NaN
1  17:00:00       NaN    -40.0
2  18:00:45     300.0      NaN
3  20:04:33       NaN    -89.0

Upvotes: 0

Pyd
Pyd

Reputation: 6159

you need df.assign and np.where

mask=df['value'] < 0
df=df.assign(max_value=(np.where(mask,df['value'],np.nan)),min_value=(np.where(~mask,df['value'],np.nan)))

df=df[['time','max_value','min_value']]

Upvotes: 0

Kamaraju Kusumanchi
Kamaraju Kusumanchi

Reputation: 1964

Use DataFrame.where

import pandas as pd
df = pd.DataFrame({'col1':['A', 'B', 'A', 'D'],
                   'col2':['sdf', 'sdh', 'sf', 'sfd'],
                   'time':['16:00:00', '17:00:00', '18:00:45', '20:04:33'],
                   'value':[100, -40, 300, -89]})
print(df)

  col1 col2      time  value
0    A  sdf  16:00:00    100
1    B  sdh  17:00:00    -40
2    A   sf  18:00:45    300
3    D  sfd  20:04:33    -89

.

new = df[['time']].copy()
new['main_val'] = df['value'].where(df['value'] > 0)
new['sub_val'] = df['value'].where(df['value'] < 0)
print(new)

       time  main_val  sub_val
0  16:00:00     100.0      NaN
1  17:00:00       NaN    -40.0
2  18:00:45     300.0      NaN
3  20:04:33       NaN    -89.0

Upvotes: 1

Shivpe_R
Shivpe_R

Reputation: 1080

This Can even be Done By Pivot Table

df['Val1'] = np.where(df.value >=0,'main_val','sub_val' )

df = pd.pivot_table(df,index='time', values='value',
                columns=['Val1'], aggfunc=np.sum).reset_index()

df = pd.DataFrame(df.values)
df.columns = ['time','main_val','sub_val']

Upvotes: 1

piRSquared
piRSquared

Reputation: 294338

I use pd.get_dummies, mask, and mul

n = {True: 'main_val', False: 'sub_val'}
m = pd.get_dummies(df.value > 0).rename(columns=n)
df.drop('value', 1).join(m.mask(m == 0).mul(df.value, 0))

  col1 col2      time  sub_val  main_val
0    A  sdf  16:00:00      NaN     100.0
1    B  sdh  17:00:00    -40.0       NaN
2    A   sf  18:00:45      NaN     300.0
3    D  sfd  20:04:33    -89.0       NaN

If you look at m.mask(m == 0), it becomes more clear how this works.

   sub_val  main_val
0      NaN       1.0
1      1.0       NaN
2      NaN       1.0
3      1.0       NaN

pd.get_dummies gives us out zeros and ones. Then I make all the zeros into np.nan. When I multiply with mul, the df.value column gets broadcast across both of these columns and we have our result. I use join to attach it back to the dataframe.

We can improve the speed with numpy

v = df.value.values[:, None]
m = v > 0
n = np.where(np.hstack([m, ~m]), v, np.nan)
c = ['main_val', 'sub_val']
df.drop('value', 1).join(pd.DataFrame(n, df.index, c))

   sub_val  main_val
0      NaN       1.0
1      1.0       NaN
2      NaN       1.0
3      1.0       NaN

Upvotes: 4

jezrael
jezrael

Reputation: 862791

You can use mask:

mask = df['value'] < 0
df['main_val'] = df['value'].mask(mask)
df['sub_val'] = df['value'].mask(~mask)
df = df.drop(['col1','col2', 'value'], axis=1)
print (df)
       time  main_val  sub_val
0  16:00:00     100.0      NaN
1  17:00:00       NaN    -40.0
2  18:00:45     300.0      NaN
3  20:04:33       NaN    -89.0

Upvotes: 9

Related Questions