python pandas dataframe merge two rows

Question

I'm trying to make an option T-table. I have a pandas dataframe like this:

             name             contract   optionName      call_or_put     IV
0             Crude Oil         CL2009     CL2009C42         call       51.25%
1             Crude Oil         CL2009     CL2009P42         put        52.13%
2             Gold              GC2009     GC2009C1900       call       20%
3             Gold              GC2009     GC2009P1900       put        22%

And I would like to change it to like this:

      name          contract       call          IV_call       put         IV_put
0    Crude Oil       CL2009       CL2009C42       51.25%     CL2009P42      52.13%
1    Gold            GC2009       GC2009C1900      20%       GC2009P1900     22%

I'm thinking to seperate the df to two dataframes, and use combine_first. But I haven't been able to make it work. Any ideas? Thanks in advance.

so, if the call_or_put column has put, you want to keep that row? — sammywemmy
– sammywemmy, Commented Jul 25, 2020 at 4:51

Sumit · Accepted Answer · 2020-07-25 06:12:14Z

One way to do this is to simply divide dataframe into two different dataframe.

df = pd.DataFrame({'name': ['crude oil', 'crude oil', 'gold', 'gold'],
                     'contract': ['CL2009', 'CL2009', 'GC2009', 'GC2009'],
                     'optionName': ['CL2009C42', 'CL2009P42', 'GC2009C1900', 'GC2009P1900'],
                     'call_or_put': ['call', 'put', 'call', 'put'],
                     'IV': ['51.25%', '52.13%', '20%', '22%']},
                    index=[0, 1, 2, 3])

#dropping the duplicate i.e creating a new frame.
new1 = df.drop_duplicates(subset ="name", keep = 'last')

#creating new frame by removing the first frame from the original frame.
new2 = df.drop(new1['optionName'].index)
# final frame
final_df = pd.merge(new2 , new1, on=['name' , 'contract'])
final_df.drop(columns = ['call_or_put_x','call_or_put_y'] , inplace=True)
final_df
# you can rename column name to get your desired result.

sushanth · Accepted Answer · 2020-07-25 05:08:07Z

0

try this, using pandas.concat

import pandas as pd

print(
    pd.concat([
        df[df.call_or_put.eq("call")]
            .rename(columns={"IV": "IV_call", "optionName": "call"}).set_index("name"),
        df.loc[df.call_or_put.eq("put"), ['name', 'IV', 'optionName']]
            .rename(columns={"IV": "IV_put", "optionName": "put"}).set_index("name")
    ], axis=1)
        .drop(columns=['call_or_put']).reset_index()  # drop un-wanted column's
)

        name contract         call IV_call  IV_put          put
0  Crude Oil   CL2009    CL2009C42  51.25%  52.13%    CL2009P42
1       Gold   GC2009  GC2009C1900     20%     22%  GC2009P1900

edited Jul 25, 2020 at 5:08

answered Jul 25, 2020 at 5:01

sushanth

8,2923 gold badges20 silver badges31 bronze badges

Comments

jsmart · Accepted Answer · 2020-07-25 05:01:51Z

Here is a function to extract puts (only) or calls (only) from the original data frame. The 'call data frame' and the 'put data frame' are combined with pd.merge()

def df_to_option(df, option_type):
    return (df.query('call_or_put == @option_type')
              .rename(columns = {'IV': f'IV_{option_type}',
                               'optionName': option_type})
              .drop(columns='call_or_put')
         )

pd.merge(left = df_to_option(df, 'call'),
         right = df_to_option(df, 'put'),
         how = 'inner',
         on = ['name', 'contract'],
        )

Here is the result of the pd.merge() command:

   name        contract   call         IV_call    put          IV_put
0  Crude Oil   CL2009     CL2009C42    51.25%     CL2009P42    52.13%
1  Gold        GC2009     GC2009C1900  20%        GC2009P1900  22%

Collectives™ on Stack Overflow

python pandas dataframe merge two rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related