Creating a column in a dataframe based on inputs of other columns

user_10643412 0
Viewed 92

I would like to create a new column on the below df, which returns 1 once C>55 day high. Each subsequent row would also show 1, even if C<55 day high until C<20 day low. How would I do this? Thanks in advance

df = pd.DataFrame([
    ['2020-01-01 01:01:00', 7147.69, 7163.32, 7147.69],
    ['2020-01-01 01:02:00', 7158.31, 7163.32, 7147.69],
    ['2020-01-01 01:03:00', 7157.08, 7163.32, 7147.69],
    ['2020-01-01 01:04:00', 7157.01, 7163.32, 7147.69],
    ['2020-01-01 01:05:00', 7159.85, 7163.32, 7147.69],
    ['2020-01-01 01:06:00', 7161.29, 7163.32, 7147.69],
    ['2020-01-01 01:07:00', 7161.29, 7163.32, 7147.69],
    ['2020-01-01 01:08:00', 7161.28, 7162.03, 7147.69],
    ['2020-01-01 01:09:00', 7161.29, 7162.03, 7147.69],
], columns=['date', 'C', '55 day high', '20 day low'])

DataFrame:

                               C  55 day high  20 day low
date                                                 
2020-01-01 01:01:00  7147.69      7163.32     7147.69
2020-01-01 01:02:00  7158.31      7163.32     7147.69
2020-01-01 01:03:00  7157.08      7163.32     7147.69
2020-01-01 01:04:00  7157.01      7163.32     7147.69
2020-01-01 01:05:00  7159.85      7163.32     7147.69
2020-01-01 01:06:00  7161.29      7163.32     7147.69
2020-01-01 01:07:00  7161.29      7163.32     7147.69
2020-01-01 01:08:00  7161.28      7162.03     7147.69
2020-01-01 01:09:00  7161.29      7162.03     7147.69
dataframe function python
2 Answers

I can't think of a way to do this apply(), but you can use iterrows() to loop through the rows with conditionals, output a list of results, and then make that into a new dataframe column. Note that I've changed the course data to simulate the start and stop conditions you describe:

import pandas as pd 
df = pd.DataFrame([
    ['2020-01-01 01:01:00', 7147.69, 7163.32, 7147.69],
    ['2020-01-01 01:02:00', 7158.31, 7163.32, 7147.69],
    ['2020-01-01 01:03:00', 7164.08, 7163.32, 7147.69],
    ['2020-01-01 01:04:00', 7157.01, 7163.32, 7147.69],
    ['2020-01-01 01:05:00', 7159.85, 7163.32, 7147.69],
    ['2020-01-01 01:06:00', 7161.29, 7163.32, 7147.69],
    ['2020-01-01 01:07:00', 7161.29, 7163.32, 7147.69],
    ['2020-01-01 01:08:00', 7145.28, 7162.03, 7147.69],
    ['2020-01-01 01:09:00', 7161.29, 7162.03, 7147.69],
], columns=['date', 'C', '55 day high', '20 day low'])

new_col = []
state = 0
for row in df.iterrows():
    if row[1]['C'] > row[1]['55 day high']:
        state = 1
    if row[1]['C'] < row[1]['20 day low']:
        state = 0
    new_col.append(state)

df['result'] = new_col
df

date    C   55 day high 20 day low  result
0   2020-01-01 01:01:00 7147.69 7163.32 7147.69 0
1   2020-01-01 01:02:00 7158.31 7163.32 7147.69 0
2   2020-01-01 01:03:00 7164.08 7163.32 7147.69 1
3   2020-01-01 01:04:00 7157.01 7163.32 7147.69 1
4   2020-01-01 01:05:00 7159.85 7163.32 7147.69 1
5   2020-01-01 01:06:00 7161.29 7163.32 7147.69 1
6   2020-01-01 01:07:00 7161.29 7163.32 7147.69 1
7   2020-01-01 01:08:00 7145.28 7162.03 7147.69 0
8   2020-01-01 01:09:00 7161.29 7162.03 7147.69 0
user_45519450

This might help you solve the problem. Unsure about if my logic here but hopefully this gets you closer to solving your problem.

def logic(C,H,L):
    if (C > H and C < L):
        return(1)
    if (C < L):
        return(0)
    else:
        return(-1)
for C, H, L in df[['C', '55 day high', '20 day low']].itertuples(index=False):
    print(logic(C,H,L)
user_86920030
Related