🔒 pandas dataframe manipulation | Python | Page 1

short bronze Aug 2, 2024, 5:08 AM

#

need help

errant dustBOT Aug 2, 2024, 5:08 AM

#

short bronze need help

#

@short bronze

Python help channel opened

Remember to:

Ask your Python question, not if you can ask or if there's an expert who can help.
Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

short bronze Aug 2, 2024, 5:08 AM

#


                price side            size  num_orders exchange  depth_level
0         25.30000000  bid    475.34900000          16   KuCoin          210
1         25.20000000  bid   5118.40670000          53   KuCoin          209
...
205        0.30000000  bid     10.00000000           1   KuCoin            5
206        0.20000000  bid     28.95730000           2   KuCoin            4
207        0.10000000  bid    272.25350000           7   KuCoin            3
208        0.00000000  bid 278964.05160000          14   KuCoin            2
209       25.30000000  ask   5503.47890000          46   KuCoin            1
210       25.40000000  ask   2269.94370000          29   KuCoin            2

vale heath Aug 2, 2024, 5:09 AM

#

short bronze ```py price side size num_orders exchange depth_le...

whats the issue

short bronze Aug 2, 2024, 5:11 AM

#

i have a dataframe in a variable in that format, its a little hard to see, but right now index 0 it is showing highest bid price and then descending to the midpoint. but i want midpoint where depth level is 1 to have that first number 25.3, and depth 2 to be subsequently descending, but right now its doing that starting from index 0 until the midpoint, rather than from midpoint to index 0 for the bids

#

to be clear its not that i want index to change, index should just be

#

1

#

2 3 4 5, and so on

vale heath Aug 2, 2024, 5:13 AM

#

are you trying to sort it by index or am i not understanding?

short bronze Aug 2, 2024, 5:14 AM

#

Screenshot_2024-08-01_at_10.14.02_PM.png

#

right now looks like that

vale heath Aug 2, 2024, 5:14 AM

#

this is the index?

short bronze Aug 2, 2024, 5:14 AM

#

0.000 should be 25.2

#

thats the depth

#

206 207 208 is the index

#

index no change

vale heath Aug 2, 2024, 5:14 AM

#

ah i see

#

sorry but i havent used pandas in a long while so im not certain how you would do it

short bronze Aug 2, 2024, 5:17 AM

#

first two rows, in case helpful, to also show headers. nw, in case anyone out there knows

Screenshot_2024-08-01_at_10.16.52_PM.png

flat widget Aug 2, 2024, 5:23 AM

#

Hey @short bronze So from what I gather, you are want to reverse the order of this but keep the indices the same?

short bronze Aug 2, 2024, 5:24 AM

#

but only reverse the order for when side = 'bid'

flat widget Aug 2, 2024, 5:24 AM

#

ahh ok

flat widget Aug 2, 2024, 5:35 AM

#

short bronze but only reverse the order for when side = 'bid'

This should do the trick for you:

bid_df = df[df['side'] == 'bid'].iloc[::-1]
ask_df = df[df['side'] == 'ask']
fixed_df = pd.concat([bid_df, ask_df]).sort_index()
fixed_df.reset_index(drop=True, inplace=True)

short bronze Aug 2, 2024, 5:43 AM

#

still showing same result

Screenshot_2024-08-01_at_10.43.03_PM.png

#

i added that in the last step

flat widget Aug 2, 2024, 5:43 AM

#

Are you displaying the new df?

short bronze Aug 2, 2024, 5:43 AM

#

yea

#

Screenshot_2024-08-01_at_10.43.37_PM.png

#

i turned it into a json so you can basically load it

📎 dataframekucoin.json

flat widget Aug 2, 2024, 5:46 AM

#

Thanks! Let me see what I did wrong here.

#

@short bronze try removing the .sort_index()

#

That re-sorted it back into the wrong order again haha 😅

short bronze Aug 2, 2024, 5:53 AM

#

yea

#

tougher problem than i thought

flat widget Aug 2, 2024, 5:53 AM

#

What exactly are you trying to achieve?

short bronze Aug 2, 2024, 5:53 AM

#

i've been restructuring this dataframe for the past hour

flat widget Aug 2, 2024, 5:53 AM

#

Maybe I'm misunderstanding

short bronze Aug 2, 2024, 5:53 AM

#

starting from midpoint, a market orderbook in the shape of a dataframe that goes outward on both sides

#

depth will be 1 for first bid, 1 for first ask at the very middle, and then 2 and 2, 3 and 3

#

until its assymetric, where lets say i have more bids than asks

#

then the depth will go on

#

and in terms of price, bids and asks will be closest at the midpoint and farther and farther away

#

exchange won't change, and num_orders is just the number of trades at that price point

#

which is mostly gonna be one, but i want to make sure that that's also correctly captured, because later on i might aggregate price into bundles of every $0.01 instead of having it fully spread out like current dataframe

flat widget Aug 2, 2024, 6:06 AM

#

short bronze which is mostly gonna be one, but i want to make sure that that's also correctly...

Ahh, I think I get it:

bids = df[df['side'] == 'bid'].sort_values('depth_level')
asks = df[df['side'] == 'ask'].sort_values('depth_level')
fixed_df = pd.concat([bids, asks])
fixed_df.reset_index(drop=True, inplace=True)

#

grab all the bids, sort by depth
grab all the asks, do the same
put the two together
reindex

short bronze Aug 2, 2024, 6:10 AM

#

the depth level is 206

Screenshot_2024-08-01_at_11.10.21_PM.png

#

when it should be 1 2 3 4 etc.

flat widget Aug 2, 2024, 6:11 AM

#

oh, you want it to start at 1s in the middle

#

easy fix

#

bids = df[df['side'] == 'bid'].sort_values('depth_level', ascending=False)
asks = df[df['side'] == 'ask'].sort_values('depth_level')
fixed_df = pd.concat([bids, asks])
fixed_df.reset_index(drop=True, inplace=True)

short bronze Aug 2, 2024, 6:13 AM

#

this reverts price back to counting from 0/lowest bid though though, when we want it to start from highest bid, closest to the lowest ask

Screenshot_2024-08-01_at_11.12.55_PM.png

flat widget Aug 2, 2024, 6:14 AM

#

I thought we were sorting by depth level, not price. Were you wanting to change the depth values?

short bronze Aug 2, 2024, 6:19 AM

#

no, maintain everything except flip the price

#

starting from the midpoint for bids only

#

rn it starts from 0

#

for bids

flat widget Aug 2, 2024, 6:20 AM

#

A bit more complex but maybe this one:

bids = df[df['side'] == 'bid'].sort_values('price', ascending=False)
asks = df[df['side'] == 'ask'].sort_values('price')

bids['depth_level'] = range(1, len(bids) + 1)
asks['depth_level'] = range(1, len(asks) + 1)

bids = bids.sort_values('depth_level', ascending=False)

fixed_df = pd.concat([bids, asks])
fixed_df.reset_index(drop=True, inplace=True)

short bronze Aug 2, 2024, 6:22 AM

#

ahh, that did it! would i still need
midpoint_index = len(df[df['side'] == 'bid'])
df['depth_level'] = df.apply(lambda x: abs(midpoint_index - x.name) + 1, axis=1)

flat widget Aug 2, 2024, 6:23 AM

#

no, I don't think so

#

I already recreated the depth_level column after sorting and don't need the midpoint

short bronze Aug 2, 2024, 6:29 AM

#

could you explain it a bit

flat widget Aug 2, 2024, 6:32 AM

#

short bronze could you explain it a bit

# sort the bids by price in descending order
bids = df[df['side'] == 'bid'].sort_values('price', ascending=False)
# sort the asks by price in ascending order
asks = df[df['side'] == 'ask'].sort_values('price')

# recreate the depth_level values for bids and asks from 1 - length
bids['depth_level'] = range(1, len(bids) + 1)
asks['depth_level'] = range(1, len(asks) + 1)

# reverse the bids so they are descending by depth
bids = bids.sort_values('depth_level', ascending=False)

# smash them together
fixed_df = pd.concat([bids, asks])
# reindex
fixed_df.reset_index(drop=True, inplace=True)

short bronze Aug 2, 2024, 6:35 AM

#

thats amazing, thanks so much

#

really appreciate this

flat widget Aug 2, 2024, 6:35 AM

#

Any time! Cheers!

errant dustBOT Aug 2, 2024, 7:35 AM

#

Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.

#🔒 pandas dataframe manipulation