How can I implement case when aggregating between python and columns


Noah

I've used SQL and SAS before, and now I've recently moved to Python. I ran into a lot of problems. I'm still working on them, but a new question is driving me crazy:

import pandas
import numpy as np
data = {'Period':['2016-02','2016-02','2016-02','2016-02','2016-03','2016-03','2016-04'],
'Name':['a','b','c','c','d','e','v'],
'amount':[2,3,41,1,8,43,20],
'Credit':[5,2,45,50,9,21,32]}

How to get the same result with SQL:

select *,case when Period = '2016-02' then (amount/Credit)*1.2
              when Period = '2016-03' then (amount/Credit)*1.1
         else (amount/Credit)*1.0 end as Rate from data

or like in SAS:

data data;
set data;
if Period = '2016-02' then rate=(amount/Credit)*1.2;
else if Period = '2016-03' then rate=(amount/Credit)*1.1;
else rate=(amount/Credit)*1.0;
run;

even more:

select Period,min(amount,credit) from data group by Period;
pirate

Option 1pandas
usemap

m = lambda x: 1.2 if x == '2016-02' else 1.1 if x == '2016-03' else 1.
df.amount / df.Credit * df.Period.map(m)

Option 2numpy
numpy.where

p = df.Period.values
multiplier = np.where(p == '2016-02', 1.2, np.where(p == '2016-03', 1.1, 1.))
df.amount / df.Credit * multiplier

All production

0    0.480000
1    1.800000
2    1.093333
3    0.024000
4    0.977778
5    2.252381
6    0.625000
dtype: float64

Related


How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this well, but maybe someone can still help me... Suppose I have some data: A B C D

How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this very well, but maybe someone can still help me... Suppose I have some data: A B

How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this well, but maybe someone can help me... Suppose I have some data: A B C D F1 6

How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this well, but maybe someone can help me... Suppose I have some data: A B C D F1 6

How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this well, but maybe someone can still help me... Suppose I have some data: A B C D

How to preserve columns when grouping/aggregating?

silver claw So, the problem with this question is that I can't post the actual code because I have to sign the agreement, and I'm new to R and may not be able to explain this well, but maybe someone can still help me... Suppose I have some data: A B C D

How can I implement IEnumerable in my specific case?

Rene Gijzemijter My question has been asked before, but even with all the previous articles, I can't figure it out. Obviously, I'm not understanding it correctly. I let Visual Studio first generate an ADO NET Entity Framework Model, code from the database. In

How can I implement these two loops in Python?

Malone my_list = ['ab', 'abc', 'abcd'] for i=0; i<len(my_list); i++ for j=i+1; j<len(my_list); j++: print(my_list[i], my_list[j]) I need more than two loops (in Java or C++) to compare elements with each other. How can I do this similarly in Py

How can I implement this symbolic summation in python?

Lana Mahedi I'm trying to implement this summary, any help? enter image description here Joazimas Considering x is a list of numbers, this can be written as follows: H = '+'.join([f'x{i}*x{i+1}' for i in range(1, 21)]) >>>print(H) 'x1*x2+x2*x3+x3*x4+x4*x5+x5*

How can I implement these two loops in Python?

Malone my_list = ['ab', 'abc', 'abcd'] for i=0; i<len(my_list); i++ for j=i+1; j<len(my_list); j++: print(my_list[i], my_list[j]) I need more than two loops (in Java or C++) to compare elements with each other. How can I do this similarly in Py

How can I convert rows to columns for my case?

scala boy I have the following pandas DataFrame df: SIGN TYPE TIME ADDITIONAL ABC5245 10 2017-01-01 01:52:25.000 2017-01-01 01:39:04.000 ABC5245 20 2017-01-01 01:53:22.000 2017-01-01 02:39:04.000 DE

How can I convert rows to columns for my case?

scala boy I have the following pandas DataFrame df: SIGN TYPE TIME ADDITIONAL ABC5245 10 2017-01-01 01:52:25.000 2017-01-01 01:39:04.000 ABC5245 20 2017-01-01 01:53:22.000 2017-01-01 02:39:04.000 DE