Type specific output expected using pandas

Question

I calculated changbtwread column using this code below for multiple type.

for v in df['Type'].unique():
    df[f'Changebetweenreadings_{v}'] = df.loc[df['Type'].eq(v), 'Last'].diff()

Given

  Type     Last  changbtwread_ada  changbtwread_btc  changbtwread_eur
0  ada  3071.56               NaN               NaN               NaN
1  ada  3097.82             26.26               NaN               NaN
2  btc  1000.00               NaN               NaN               NaN
3  ada  2000.00          -1097.82               NaN               NaN
4  btc  3000.00               NaN            2000.0               NaN
5  eur  1000.00               NaN               NaN               NaN
6  eur  1500.00               NaN               NaN             500.0

Now that i need to calculate direction column based on these changebtw column.

My output should look like

Type    change_dir_ada    change_dir_btc   change_dir_eur   
ada       Nut
ada       Pos
btc                          Nut
ada       Neg
btc                          Nut
eur
eur                                               Pos

A quick fix i tried is using this code.

df.loc[df.Changebetweenreadings_btceur > 0, 'ChangeDirection_btceur'] = 'Pos' 
df.loc[df.Changebetweenreadings_btceur < 0, 'ChangeDirection_btceur'] = 'Neg' 
df.loc[df.Changebetweenreadings_btceur == 0, 'ChangeDirection_btceur'] = 'Nut'

df.loc[df.Changebetweenreadings_adabtc > 0, 'ChangeDirection_adabtc'] = 'Pos' 
df.loc[df.Changebetweenreadings_adabtc < 0, 'ChangeDirection_adabtc'] = 'Neg' 
df.loc[df.Changebetweenreadings_adabtc == 0, 'ChangeDirection_adabtc'] = 'Nut'

But i this is a lot of code and its not a dynamic way of doing i think. I expect something like this.

for v in df['Type'].unique():
   df[f'Changebetweenreadings_{v}'] #--> Do this calculation above.

It doesn't work for these values

change        type    dir_ada   dir_btc
-3637.31      ada      
-4E-08        ada       Neg
-3637.31      ada       Nut
3637.8        btc                  Nut

In place of Pos it gives random mapping.

it would help to know your data (are `Type` and `Changebetweenreadings` one dataframe? why is the length different?). it would also help to know what error you are facing or what exactly you want to achieve. (e.g. why how do you get 2 columns 6 rows from two series of length 5 and 8). We also don't know content of column `'Last'`. — flurble, Apr 28 '19 at 12:41

jezrael · Accepted Answer · 2019-04-28T15:01:31.087

0

I believe you need:

vals = ['Pos','Neg', 'Nut']
for v in df['Type'].unique():
    df[f'change_dir_{v}'] = df.loc[df['Type'].eq(v), 'Last'].diff()
    df[f'change_dir_{v}'] = np.select([df[f'change_dir_{v}'] > 0, 
                                       df[f'change_dir_{v}'] < 0, 
                                       df[f'change_dir_{v}']== 0], vals, '')


print (df)
  Type     Last change_dir_ada change_dir_btc change_dir_eur
0  ada  3071.56                                             
1  ada  3097.80            Pos                              
2  btc  1000.00                                             
3  ada  2000.00            Neg                              
4  btc  3000.00                           Pos               
5  eur  1000.00                                             
6  eur  1500.00                                          Pos

edited Apr 28 '19 at 15:01

answered Apr 28 '19 at 13:55

jezrael

822,522
95
1,334
1,252

please check the error i get after trying this code. It works for most cases and after a while it is giving random mapping. – KSp Apr 28 '19 at 15:00
@KSp - I try simplify solution, but not sure if understand `random mapping`. – jezrael Apr 28 '19 at 15:04
sorry, i mean to say it randomly assigns pos inplace of neg or nut. like it assigns randomly without any logic, i meant :) – KSp Apr 28 '19 at 15:06
@KSp - hmmm, there are negative too small numbers, so not easy check it. e.g. `n= -4E-08` and then `print(f'{n:.10f}')` – jezrael Apr 28 '19 at 15:11
But what about the values like -3643.909983 ? because my values start with two digits and end up having more values, even negatives. So, its still assigning it wrongly. I tried your new code. – KSp Apr 28 '19 at 15:17
@KSp - I think it seems some data problem :( – jezrael Apr 28 '19 at 15:18
I think data related problem. – jezrael Apr 28 '19 at 15:19
@KSp - I think I find it - problem is to small numbers are convertted to `0` [link](https://stackoverflow.com/q/3704570/2901002) in numpy, I think same problem you find in your real data. – jezrael Apr 28 '19 at 15:27
Thats fine but what about the values like -3643.90 why its not accepted as neg value? Any idea? @jezrael – KSp Apr 28 '19 at 15:41
@KSp - values are floats? If check `df['colname'].dtype` ? – jezrael Apr 28 '19 at 15:42
Are you asking about Last column or change direction column? – KSp Apr 28 '19 at 15:44
change_dir_btceur 118175 non-null object change_dir_adabtc 118175 non-null object Last 118175 non-null float64 – KSp Apr 28 '19 at 15:49
hmmm, not necessary answer. Because if not numeric, `diff` raise error. So only last possible solution - try restart your IDE, if still problem, not idea. Because for me it working nice, so hard to test your problem. – jezrael Apr 28 '19 at 15:49
@KSp - hmmmm, is possible convert `Last` to integer like `df['Last_new'] = df['Last'].mul(10000).astype(int)` and then test solution with `Last_new`? – jezrael Apr 28 '19 at 15:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192622/discussion-between-ksp-and-jezrael). – KSp Apr 30 '19 at 09:26

Type specific output expected using pandas

1 Answers1