Python - Pandas Indexing

Question

What is the best way to get the value of a cell in relation to another cell?

I have the below list of identifiers and also the below csv file (not dataframe):

list = ['ABC0123', 'DEF0123']

>  Column 1  Column 2   Column 3    Column 4
>   "Date"   20170101 "Identifier"   ABC0123
>"OpenPrice"   500     "Currency"      USD
>"ClosePrice"  550       "foo"         bar
>    ...
>    ...
>    ...
>  "Date"   20170101 "Identifier"   DEF0123
>"OpenPrice"   600     "Currency"      USD
>"ClosePrice"  650       "foo"         bar

I want to get the close prices for the identifiers, e.g 550 and 650, while parsing the file, looking for the identifiers and then move 2 down and 2 to the left.

"Close Price" repeats itself and I want to get the values for a list of identifiers. Also iloc and loc only gets the values in the same row, so what is the easiest way to get the values?

Do you have the ability to change the format? You'll have a much easier time if you have one column for each type of data: Date, Identifier, OpenPrice, ClosePrice, Currency, etc. Then each row will be a complete record. — Mr Fooz
– Mr Fooz, Commented Jun 16, 2017 at 22:14
first of all thank you for helping with the format of my question, second, unfortunately not, I know what you mean but I am working on a big file that is sent to me rather than being able to control the output. — bloo
– bloo, Commented Jun 16, 2017 at 22:16

Scott Boston · Accepted Answer · 2017-06-16 22:43:49Z

1

Let's try:

df.iloc[df[df['Column 4'].isin(list)].index + 2,1]

answered Jun 16, 2017 at 22:43

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bloo Over a year ago

took me some time to apply a for i loop, adjust and test on my dataset but..YES! Can you please provide some explanation as to how and why this works, or guide me to the documentation? not sure if this is the right one pandas.pydata.org/pandas-docs/stable/generated/…, but cannot say it helps much.

Scott Boston Over a year ago

Okay... Let's work inside out. df['Column 4'] is in the list. returns true for those two records. We then use boolean index selection to get the records and .index returns the index add 2 to this index to go down to rows. Now, use .iloc for index location of the dataframe and get the 2nd column over.

bloo Over a year ago

Thank you, I guess apart from the thought process of getting the index row, the essence of your solution is knowing how to move from there. I hadn't tried '+ 2, 1' before.

Scott Boston Over a year ago

+ 2 adds to the row index and the 1 after the comma is for the second column. Look at .iloc in docs.

Collectives™ on Stack Overflow

Python - Pandas Indexing

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related