python Dataframe using pandas data insert into excel file-CodePudding

I have received a data frame using pandas, data have one column and multiple rows in that column and each row has multiple data like ({buy_quantity:0, symbol:nse123490,....})

I want to insert it into an excel sheet using pandas data frame with python xlwings lib. with some selected data please help me

 wb = xw.Book('Easy_Algo.xlsx')
 ts = wb.sheets['profile']
 pdata=sas.get_profile()
df = pd.DataFrame(pdata)
ts.range('A1').value = df[['symbol','product','avg price','buy avg']]

output like this :

please help me... how to insert data into excel only selected.

CodePudding user response：

Considering that the dataframe below is named df and the type of the column positions is dict, you can use the code below to transform the keys to columns and values to rows.

out = df.join(pd.DataFrame(df.pop('positions').values.tolist()))

out.to_excel('Easy_Algo.xlsx', sheet_name=['profile'], index=False) #to store the result in an Excel file/spreadsheet.

Note : Make sure to add these two lines below if the type of the column positions is not dict.

import ast

df['positions']=df['positions'].apply(ast.literal_eval)

#A sample dataframe for test :

import pandas as pd
import ast

string_dict = {'{"Symbol": "NIFTY2292218150CE NFO", "Produc": "NRML", "Avg. Price": 18.15, "Buy Avg": 0}',
'{"Symbol": "NIFTY22SEP18500CE NFO", "Produc": "NRML", "Avg. Price": 20.15, "Buy Avg": 20.15}',
'{"Symbol": "NIFTY22SEP16500PE NFO", "Produc": "NRML", "Avg. Price": 16.35, "Buy Avg": 16.35}'}

df = pd.DataFrame(string_dict, columns=['positions'])

df['positions']=df['positions'].apply(ast.literal_eval)

out = df.join(pd.DataFrame(df.pop('positions').values.tolist()))

`>>> print(out)`

                  Symbol Produc  Avg. Price  Buy Avg
0  NIFTY22SEP16500PE NFO   NRML       16.35    16.35
1  NIFTY22SEP18500CE NFO   NRML       20.15    20.15
2  NIFTY2292218150CE NFO   NRML       18.15     0.00

CodePudding user response：

If i understood correctly, you want only those columns written to an excel file

df = df[['symbol','product','avg price','buy avg']]

df.to_excel("final.xlsx") 
df.to_excel("final.xlsx", index = False) # in case there was a default index generated by pandas and you wanna get rid of it.

i hope this helps.