Question:

How to change the rows and columns in the pandas data frame?

Problem:

I have code like this which creates dataframe from the dictionary, but the output is differant from what is expected. Have given the code as follows with steps

options_pnl={'BTC': {'Pnl(since 6pm)': Decimal('7831.52228528'),

'Pnl(4 hr)': Decimal('2930.47450133'),

'Pnl(1 hr)': Decimal('1416.81306308'),

'Volume(since 6pm)': Decimal('24509290.62181862'),

'Volume(4 hr)': Decimal('4504202.83422724'),

'Volume(1 hr)': Decimal('1067850.01837278')},

'ETH': {'Pnl(since 6pm)': Decimal('387.87564823'),

'Pnl(4 hr)': Decimal('-349.14871930'),

'Pnl(1 hr)': Decimal('656.74824550'),

'Volume(since 6pm)': Decimal('10700784.53262117'),

'Volume(4 hr)': Decimal('1968872.36761706'),

'Volume(1 hr)': Decimal('778937.22275036')}}

options_pnl_df = pd.DataFrame.from_dict(options_pnl)

options_pnl_df = options_pnl_df.astype('int64')

options_pnl_df = options_pnl_df.transpose()

display(options_pnl_df)

and output is like below :

Pnl(since 6pm) Pnl(4 hr) Pnl(1 hr) Volume(since 6pm) Volume(4 hr) Volume(1 hr)

BTC 7831 2930 1416 24509290 4504202 1067850

ETH 387 -349 656 10700784 1968872 778937

I need to change the structure to this from the above ouput to the below output given

PNL Volume

BTC since 6pm 7831 24509290

BTC since last 4 hr 2930 4504202

BTC last 1 hr 1416 1067850

ETH since 6pm 387 10700784

ETH since last 4 hr -349 1968872

ETH last 1 hr 656 778937

tried with

data = []

for asset, values in options_pnl.items():

for interval, metrics in values.items():

metric_type, metric_interval = interval.split('(')

metric_interval = metric_interval.rstrip(')') # remove trailing ')'

pnl_value = metrics if metric_type == 'Pnl' else 0

volume_value = metrics if metric_type == 'Volume' else 0

data.append([asset, metric_interval, pnl_value, volume_value])

columns = ['Asset', 'Type', 'PNL', 'Volume']

options_pnl_df = pd.DataFrame(data, columns=columns)

print(options_pnl_df)

but not getting desired ouput, could someone make the desired output from the given dicts

Solution:

You can convert the current column index to a >MultiIndex and then >stack the result:

options_pnl_df.columns = pd.MultiIndex.from_arrays(options_pnl_df

.columns.str.extract(r'(\w+)\s*\(([\w\s]+)', expand=True)

.values

)

options_pnl_df = options_pnl_df.stack()

Output:

Pnl Volume

BTC 1 hr 1416 1067850

4 hr 2930 4504202

since 6pm 7831 24509290

ETH 1 hr 656 778937

4 hr -349 1968872

since 6pm 387 10700784

>How to do Yup validation in form with TypeScript?

>How can I merge two arrays of objects that can be undefined in TypeScript?

>Javascript Error Solved: Property 'id' does not exist on type 'T'

>How to make sticky div remain stuck in JavaScript?

>How to manipulate manipulating Array object in JavaScript?

>How to do light and dark mode in a website using HTML and JavaScript?

>How to fix mouseover event glitch in JavaScript?

Python

Nisha Patel

Your Answer

Submit

Nisha Patel

0 Answers

Top Questions

Nisha Patel

0 Answers

Top Questions

Related Questions