CODEWITHSUNDEEP
pythonpandasdataframe
burhancigdem
burhancigdem

Reputation: 105

How to filter rows containing a similar pattern

i have this dataframe in pandas:

               Value    Date
ISIN        
TRT010213A15    10    2019-11-15
TRT010213T23    100   2019-11-15
TRT010213T23    70    2019-12-20
TRT080328T15    150   2018-10-12
TRT080420T12    175   2018-05-11
TRT150120T16    150   2019-08-29
TRT150120A05    15    2019-11-18
TRT150120T16    300   2019-10-15
TRT150120T16    275   2019-11-18

How to get the difference (subtraction) between "Values" columns if both the first 9 characters of the indixes ("ISIN") and the values ​​in the "Date" column are same. Subtraction will be held between indexes that have "T" on the 10th character and "A" on the 10th character.

e.g. first 9 characters of first and second indexes are "TR010213" and "Date" values are "2019-11-15". In this case new value of "Value" for second row (cause 10th char of index is "T") should be 90(100-10) and "Value" for first row (10th char of index is "A") will not be changed.

I wanna get this dataframe:

                Value   Date
ISIN        
TRT010213A15    10  2019-11-15
TRT010213T23    90  2019-11-15
TRT010213T23    70  2019-12-20
TRT080328T15    150 2018-10-12
TRT080420T12    175 2018-05-11
TRT150120T16    150 2019-08-29
TRT150120A05    15  2019-11-18
TRT150120T16    300 2019-10-15
TRT150120T16    260 2019-11-18

Upvotes: 4

Views: 73

Answers (2)

luigigi
luigigi

Reputation: 4215

You could use this:

df.update(df.groupby([df.index.str[:9],'Date']).transform('diff'))

              Value        Date
ISIN                           
TRT010213A15   10.0  2019-11-15
TRT010213T23   90.0  2019-11-15
TRT010213T23   70.0  2019-12-20
TRT080328T15  150.0  2018-10-12
TRT080420T12  175.0  2018-05-11
TRT150120T16  150.0  2019-08-29
TRT150120A05   15.0  2019-11-18
TRT150120T16  300.0  2019-10-15
TRT150120T16  260.0  2019-11-18

Upvotes: 2

Quang Hoang
Quang Hoang

Reputation: 150765

IIUC, you can use df.index.str[:9] to get the first 9 characters, and groupby().shift():

df['Value'] -= df.groupby([df.index.str[:9], df['Date']]).Value.shift().fillna(0)

Output:

              Value        Date
ISIN                           
TRT010213A15   10.0  2019-11-15
TRT010213T23   90.0  2019-11-15
TRT010213T23   70.0  2019-12-20
TRT080328T15  150.0  2018-10-12
TRT080420T12  175.0  2018-05-11
TRT150120T16  150.0  2019-08-29
TRT150120A05   15.0  2019-11-18
TRT150120T16  300.0  2019-10-15
TRT150120T16  260.0  2019-11-18

Note: for some reason, groupby().diff() doesn't work for me.

Upvotes: 1

Related Questions