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.