Skip to content Skip to sidebar Skip to footer

Create Id Column In A Pandas Dataframe

I have a dataframe containing a trading log. My problem is that I do not have any ID to match buy and sell of a stock. The stock could be traded many times and I would like to have

Solution 1:

Try this:

m = df1['deal'] == 'buy'
df1['ID'] = m.cumsum().where(m)
df1['ID'] = df1.groupby('stock')['ID'].ffill()

df1

Output:

  stock  deal   ID
0A   buy  1.01B   buy  2.02     C   buy  3.03A  sell  1.04     C  sell  3.05A   buy  4.06A  sell  4.0

Details:

  • Create a boolean series, True where deal equals 'buy'
  • Cumsum and assign to 'ID' to buy records
  • Use groupby and ffill to assign 'ID' to next 'sell' record buy 'stock'

Solution 2:

Try This:

import pandas as pd
df1 = pd.DataFrame({'stock': ['A', 'B', 'C', 'A','C', 'A', 'A'],
                'deal': ['buy', 'buy', 'buy', 'sell','sell', 'buy', 'sell']})

defsequential_buy_sell_id_generator(df1):

    column_length = len(df1["stock"])
    found = [0]*column_length
    id = [0]*column_length

    counter = 0for row_pointer_head inrange(column_length):
        if df1["deal"][row_pointer_head]=="buy":
            id[row_pointer_head]= counter
            counter+=1
            found[row_pointer_head] = 1id[row_pointer_head]= counter

            for row_pointer_tail inrange(row_pointer_head+1, column_length):

                if df1["stock"][row_pointer_head]== df1["stock"][row_pointer_tail] and df1["deal"][row_pointer_tail] =="sell"and found[row_pointer_tail] == 0:
                    found[row_pointer_tail] = 1id[row_pointer_tail]= counter
                    break

    df1 = df1.assign(id = id) 
    return df1


print(sequential_buy_sell_id_generator(df1))

Output:

enter code here
    stock  deal  id
0A   buy   11B   buy   22     C   buy   33A  sell   14     C  sell   35A   buy   46A  sell   4

Another Example:

Fordf1= pd.DataFrame({'stock': ['A', 'B', 'C', 'A','C', 'A', 'A'],
                'deal': ['buy', 'buy', 'buy', 'buy','sell', 'sell', 'sell']})
  stock deal    ID
0   A   buy     11   B   buy     22   C   buy     33   A   buy     44   C   sell    35   A   sell    16   A   sell    4

Post a Comment for "Create Id Column In A Pandas Dataframe"