I have a dataframe which contains some negative and positive values
I’ve used following code to get pct_change on row values
df_gp1 = df_gp1.pct_change(periods=4, axis=1) * 100
and here I want to assign some specific number, depending on how the values change from negative to positive or vice versa
for example, if the value turns from positive to negative, return -100
negative to positive, return 100
negative to negative, return -100,
positive to positive, ordinary pct_change
for example my current dataframe could look like the following
| DATA | D-4 | D-3 | D-2 | D-1 | D-0 |
|---|---|---|---|---|---|
| A | -20 | -15 | -13 | -10 | -5 |
| B | -30 | -15 | -10 | 10 | 25 |
| C | 40 | 25 | 30 | 41 | 30 |
| D | 25 | 25 | 10 | 15 | -10 |
I want a new output(dataframe) that gives me following return
| DATA | D-0 |
|---|---|
| A | -100 |
| B | 100 |
| C | -25 |
| D | -100 |
as you can see, the 4th period must provide pct_change (i.e D-0 / D-4), but if it stays negative, return -100 if it turns from positive to negative, still return -100 if it turns from negative to positive, return 100, if it’s a change from positive value to another positive value, then apply pct_chg
and my original dataframe is like 4000 rows and 300 columns big. Thus my desired output will have 4000 rows and 296 columns(since the it eliminates data D-4, D-3, D-2, D-1
I tried to make conditional list, and choice list, and use np.select method, but I just don’t know how to apply it across whole dataframe and create new one that returns percentage changes.
Any help is deeply appreciated.
Advertisement
Answer
Use:
#convert column DATA to index if necessary
df = df.set_index('DATA')
#compare for less like 0
m1 = df.lt(0)
#comapre shifted 4 columns less like 0
m2 = df.shift(4, axis=1).lt(0)
#pass to np.select
arr = np.select([m1, ~m1 & m2, ~m1 & ~m2],
[-100, 100, df.pct_change(periods=4, axis=1) * 100])
#create DataFrame, remove first 4 columns
df = pd.DataFrame(arr, index=df.index, columns=df.columns).iloc[:, 4:].reset_index()
print (df)
DATA D-0
0 A -100.0
1 B 100.0
2 C -25.0
3 D -100.0