Issue

This Content is from Stack Overflow. Question asked by Gnziet

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.

Solution

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

This Question was asked in StackOverflow by Gnziet and Answered by abokey It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.