Styjun

Why can't I share a one use code with anyone else?

How could it be that 80% of townspeople were farmers during the Edo period in Japan?

Would life always name the light from their sun "white"

Which creature is depicted in this Xanathar's Guide illustration of a war mage?

How do I know which cipher suites can be disabled?

Can anyone give me examples of the relative-determinative 'which'?

tikz drawing rectangle discretized with triangle lattices and its centroids

What do the "optional" resistor and capacitor do in this circuit?

Wiring a 4 channel relay - is this possible?

How to describe a building set which is like LEGO without using the "LEGO" word?

Formal Definition of Dot Product

To whom did Varys write those letters in Game of Thrones S8E5?

Will consteval functions allow template parameters dependent on function arguments?

Why were the bells ignored in S8E5?

Do people who work at research institutes consider themselves "academics"?

Polynomial division: Is this trick obvious?

Was the dragon prowess intentionally downplayed in S08E04?

Could a space colony 1g from the sun work?

Holding rent money for my friend which amounts to over $10k?

Is random forest for regression a 'true' regression?

​Cuban​ ​Primes

Is there any deeper thematic meaning to the white horse that Arya finds in The Bells (S08E05)?

What do you call the hair or body hair you trim off your body?

Why doesn't Iron Man's action affect this person in Endgame?

Calculate pandas dataframe index difference based on the value of another column

Selecting multiple columns in a pandas dataframeRenaming columns in pandasAdding new column to existing DataFrame in Python pandasDelete column from pandas DataFrame by column nameHow to drop rows of Pandas DataFrame whose value in certain columns is NaN“Large data” work flows using pandasHow to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueGet list from pandas DataFrame column headers

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I'm trying to figure out how to calculate the difference in index of the current row with the row WHERE a certain column has a certain value.

i.e.

I have a dataframe:

import pandas as pd

# pandas settings
pd.set_option('display.max_columns', 320)
pd.set_option('display.max_rows', 1320)
pd.set_option('display.width', 320)

df = pd.read_csv('https://www.dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')

So I would like to calculate how many indexes behind is the row with candle = candle-20

So for instance, if current row is 583185 and the candle value is 119, then the candle we are interested in is 99. We need to figure out current_index - index(where candle=99 1st occurrence)

I hope I made myself clear, cheers =)

EDIT:
Ok, I did pretty bad explaination above..

I believe I'm actually quite close to solving this myself. Have a look:

x = df.index[df.candle == df.candle - 20][0]
df['test'] = df.bid.rolling(int(x)).mean()

So the 'test' column should be the mean() value of the df.bid last X rows, where X is how many rows between current df.candle and the one that is 20 candles back (first iteration so [0] (there are many rows with same candle value))

But the code above gives an error:

IndexError: index 0 is out of bounds for axis 0 with size 0

python pandas

share|improve this question

edited Mar 25 at 17:57

Andrey Kurnikovs

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  can you add a data sample and an expected output? Thanks
  
  – anky_91
  Mar 23 at 15:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  df = pd.read_csv('dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')
  
  – Andrey Kurnikovs
  Mar 25 at 11:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have a formula: b = 0.015 * TP.rolling(X).std() I need to calculate rolling.(X) - depending on a value of another column. So X should be the index difference between current row and row where value of candle is -20 from current (first iteration)
  
  – Andrey Kurnikovs
  Mar 25 at 11:11
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is TP? Also, that CSV is more than 78 MB in size, which is very large for slow internet connections. If you can trim it down to a subset of the data required to demonstrate your problem, that would be helpful.
  
  – Nathaniel
  Mar 25 at 16:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've added an EDIT
  
  – Andrey Kurnikovs
  Mar 25 at 17:57
  

  
  
  
  

add a comment | 

0

I'm trying to figure out how to calculate the difference in index of the current row with the row WHERE a certain column has a certain value.

i.e.

I have a dataframe:

import pandas as pd

# pandas settings
pd.set_option('display.max_columns', 320)
pd.set_option('display.max_rows', 1320)
pd.set_option('display.width', 320)

df = pd.read_csv('https://www.dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')

So I would like to calculate how many indexes behind is the row with candle = candle-20

So for instance, if current row is 583185 and the candle value is 119, then the candle we are interested in is 99. We need to figure out current_index - index(where candle=99 1st occurrence)

I hope I made myself clear, cheers =)

EDIT:
Ok, I did pretty bad explaination above..

I believe I'm actually quite close to solving this myself. Have a look:

x = df.index[df.candle == df.candle - 20][0]
df['test'] = df.bid.rolling(int(x)).mean()

So the 'test' column should be the mean() value of the df.bid last X rows, where X is how many rows between current df.candle and the one that is 20 candles back (first iteration so [0] (there are many rows with same candle value))

But the code above gives an error:

IndexError: index 0 is out of bounds for axis 0 with size 0

python pandas

share|improve this question

edited Mar 25 at 17:57

Andrey Kurnikovs

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  can you add a data sample and an expected output? Thanks
  
  – anky_91
  Mar 23 at 15:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  df = pd.read_csv('dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')
  
  – Andrey Kurnikovs
  Mar 25 at 11:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have a formula: b = 0.015 * TP.rolling(X).std() I need to calculate rolling.(X) - depending on a value of another column. So X should be the index difference between current row and row where value of candle is -20 from current (first iteration)
  
  – Andrey Kurnikovs
  Mar 25 at 11:11
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is TP? Also, that CSV is more than 78 MB in size, which is very large for slow internet connections. If you can trim it down to a subset of the data required to demonstrate your problem, that would be helpful.
  
  – Nathaniel
  Mar 25 at 16:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've added an EDIT
  
  – Andrey Kurnikovs
  Mar 25 at 17:57
  

  
  
  
  

add a comment | 

I'm trying to figure out how to calculate the difference in index of the current row with the row WHERE a certain column has a certain value.

i.e.

I have a dataframe:

import pandas as pd

# pandas settings
pd.set_option('display.max_columns', 320)
pd.set_option('display.max_rows', 1320)
pd.set_option('display.width', 320)

df = pd.read_csv('https://www.dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')

So I would like to calculate how many indexes behind is the row with candle = candle-20

So for instance, if current row is 583185 and the candle value is 119, then the candle we are interested in is 99. We need to figure out current_index - index(where candle=99 1st occurrence)

I hope I made myself clear, cheers =)

EDIT:
Ok, I did pretty bad explaination above..

I believe I'm actually quite close to solving this myself. Have a look:

x = df.index[df.candle == df.candle - 20][0]
df['test'] = df.bid.rolling(int(x)).mean()

So the 'test' column should be the mean() value of the df.bid last X rows, where X is how many rows between current df.candle and the one that is 20 candles back (first iteration so [0] (there are many rows with same candle value))

But the code above gives an error:

IndexError: index 0 is out of bounds for axis 0 with size 0

python pandas

share|improve this question

edited Mar 25 at 17:57

Andrey Kurnikovs

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

import pandas as pd

# pandas settings
pd.set_option('display.max_columns', 320)
pd.set_option('display.max_rows', 1320)
pd.set_option('display.width', 320)

df = pd.read_csv('https://www.dropbox.com/s/hy94jp4d7qwmv04/eurusd_df1.csv?dl=1')

x = df.index[df.candle == df.candle - 20][0]
df['test'] = df.bid.rolling(int(x)).mean()

share|improve this question

edited Mar 25 at 17:57

Andrey Kurnikovs

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

share|improve this question

edited Mar 25 at 17:57

Andrey Kurnikovs

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

asked Mar 23 at 15:21

Andrey KurnikovsAndrey Kurnikovs

3218

1 Answer
1

active

oldest

votes

0

Here is a method to accomplish this:

# Generate example data
np.random.seed(0)
df = pd.Series(np.round(np.random.rand(1000000)*1000), dtype=int, name='candle').to_frame()

# Compute row index where df.candle is 20 less than candle_value at current_index
current_index = 583185
candle_value = df.loc[current_index, 'candle'] # = 119 in your df
index = df.index[df.candle == candle_value - 20][0]
print(index)

835

Edit: To compute the difference in indexes, just subtract them:

X = current_index - index
print(X)

582350

Then you can compute your formula:

b = 0.015 * TP.rolling(X).std()

share|improve this answer

edited Mar 25 at 16:28

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You got it a bit wrong actually. I have a formula: ``` b = 0.015 * TP.rolling(X).std()``` I need to calculate rolling.(X) - depending on a value of another column. So X should be the index difference between current row and row where value of candle is -20 from current (first iteration)
  
  – Andrey Kurnikovs
  Mar 25 at 10:58
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55315269%2fcalculate-pandas-dataframe-index-difference-based-on-the-value-of-another-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Here is a method to accomplish this:

# Generate example data
np.random.seed(0)
df = pd.Series(np.round(np.random.rand(1000000)*1000), dtype=int, name='candle').to_frame()

# Compute row index where df.candle is 20 less than candle_value at current_index
current_index = 583185
candle_value = df.loc[current_index, 'candle'] # = 119 in your df
index = df.index[df.candle == candle_value - 20][0]
print(index)

835

Edit: To compute the difference in indexes, just subtract them:

X = current_index - index
print(X)

582350

Then you can compute your formula:

b = 0.015 * TP.rolling(X).std()

share|improve this answer

edited Mar 25 at 16:28

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You got it a bit wrong actually. I have a formula: ``` b = 0.015 * TP.rolling(X).std()``` I need to calculate rolling.(X) - depending on a value of another column. So X should be the index difference between current row and row where value of candle is -20 from current (first iteration)
  
  – Andrey Kurnikovs
  Mar 25 at 10:58
  
  

  
  
  
  

add a comment | 

0

Here is a method to accomplish this:

# Generate example data
np.random.seed(0)
df = pd.Series(np.round(np.random.rand(1000000)*1000), dtype=int, name='candle').to_frame()

# Compute row index where df.candle is 20 less than candle_value at current_index
current_index = 583185
candle_value = df.loc[current_index, 'candle'] # = 119 in your df
index = df.index[df.candle == candle_value - 20][0]
print(index)

835

Edit: To compute the difference in indexes, just subtract them:

X = current_index - index
print(X)

582350

Then you can compute your formula:

b = 0.015 * TP.rolling(X).std()

share|improve this answer

edited Mar 25 at 16:28

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You got it a bit wrong actually. I have a formula: ``` b = 0.015 * TP.rolling(X).std()``` I need to calculate rolling.(X) - depending on a value of another column. So X should be the index difference between current row and row where value of candle is -20 from current (first iteration)
  
  – Andrey Kurnikovs
  Mar 25 at 10:58
  
  

  
  
  
  

add a comment | 

Here is a method to accomplish this:

# Generate example data
np.random.seed(0)
df = pd.Series(np.round(np.random.rand(1000000)*1000), dtype=int, name='candle').to_frame()

# Compute row index where df.candle is 20 less than candle_value at current_index
current_index = 583185
candle_value = df.loc[current_index, 'candle'] # = 119 in your df
index = df.index[df.candle == candle_value - 20][0]
print(index)

835

Edit: To compute the difference in indexes, just subtract them:

X = current_index - index
print(X)

582350

Then you can compute your formula:

b = 0.015 * TP.rolling(X).std()

share|improve this answer

edited Mar 25 at 16:28

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

# Generate example data
np.random.seed(0)
df = pd.Series(np.round(np.random.rand(1000000)*1000), dtype=int, name='candle').to_frame()

# Compute row index where df.candle is 20 less than candle_value at current_index
current_index = 583185
candle_value = df.loc[current_index, 'candle'] # = 119 in your df
index = df.index[df.candle == candle_value - 20][0]
print(index)

835

X = current_index - index
print(X)

582350

b = 0.015 * TP.rolling(X).std()

share|improve this answer

edited Mar 25 at 16:28

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

answered Mar 23 at 15:33

NathanielNathaniel

2,210214

answered Mar 23 at 15:33

NathanielNathaniel

2,210214