CODEWITHSUNDEEP
pythonpandasdataframe
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

How to convert rows in columns in pandas python and perform operation

I have a pandas dataframe like this.

Date    Strike_price        value           
1/1/18      10000           55
1/1/18      10100           40
1/1/18      10200           37
1/1/18      10300           25  
2/1/18      10000           52
2/1/18      10100           38
2/1/18      10200           33
2/1/18      10300           21
3/1/18      10000           58
3/1/18      10100           43
3/1/18      10200           39
3/1/18      10300           26

I want to create a new dataframe from that like this:

1/1/18      27 # 27 == 55 - 40 + 37 - 25
2/1/18      26
3/1/18      28

I tried to use df.pivot_table('value', ['Date'], 'Strike_Price'), which converts strike prices into columns but then i am not able to delete or filter out any specific columns from DF.

Can anyone help me on this please?

Upvotes: 1

Views: 85

Answers (2)

n3utrino
n3utrino

Reputation: 1210

One solution is to multiply odd rows of your "value" by -1:

df.value.iloc[1::2] *= -1

Then do a groupby and sum:

df.groupby('Date').sum()

This worked for me. If you don't want to modify your original "value" column, you could make a new column, operate on it, then optionally remove that column at the end.

Upvotes: -1

timgeb
timgeb

Reputation: 78690

Given

>>> df
      Date  Strike_price  value
0   1/1/18         10000     55
1   1/1/18         10100     40
2   1/1/18         10200     37
3   1/1/18         10300     25
4   2/1/18         10000     52
5   2/1/18         10100     38
6   2/1/18         10200     33
7   2/1/18         10300     21
8   3/1/18         10000     58
9   3/1/18         10100     43
10  3/1/18         10200     39
11  3/1/18         10300     26

you can issue

>>> df.groupby('Date')['value'].agg(lambda s: s[::2].sum() - s[1::2].sum())
Date
1/1/18    27
2/1/18    26
3/1/18    28
Name: value, dtype: int64

Alternatively, you might prefer

>>> df.groupby('Date', as_index=False)['value'].agg(lambda s: s[::2].sum() - s[1::2].sum())
     Date  value
0  1/1/18     27
1  2/1/18     26
2  3/1/18     28

Upvotes: 2

Related Questions