0

I have a requirement to write data in a dataframe and three other values that i should write to an excel file.I will give the example here. I have a dataframe and three other values. enter image description here

I should write data in the above format to excel file.

hemanth sai reddy
  • 17
  • 3
  • I guess `openpyxl` library may help you. https://openpyxl.readthedocs.io/en/stable/ please read this manual and add some code to this question that demonstrates your effort – Sergey Belash Feb 24 '19 at 16:29

1 Answers1

0

It's pretty simple.Create a DataFrame as usual and i would append the last three rows with information in first column and empty spaces in the last. Then i would write my file to excel using to_csv. You can read more about it here: Writing DataFrame to Excel.

Here's the code:

import pandas as pd

my_data={'A':["2017/01/14","2017/01/02"],'M':['C','C'],'P':[8,7],'G':[1.44,1.26],'R':[0.8,0.7],'N':[2,8]}

df=pd.DataFrame(my_data)

#Add the extra rows
df.loc[-1]=['Total M: ',' ',' ',' ',' ',' ']
df.index=df.index+1
df.loc[-1]=['Total G: ',' ',' ',' ',' ',' ']
df.index=df.index+1
df.loc[-1]=['Total R: ',' ',' ',' ',' ',' ']

#Write to excel
df.to_csv('FileName',sep='\t',encoding='utf-8',index=False)

From next time, it would be appreciated if you post what you have tried. Good Luck! May the CODE be with you!

Community
  • 1
  • 1
  • Hi @Me-So-Cool, thanks for the reply , the above code didn't worked for me, `df = pd.DataFrame(mr) df = df.append(pd.Series(t_s_amount, index=df.columns[:len(t_s_amount)]),ignore_index=True) df = df.append(pd.Series(t_gst_amount, index=df.columns[:len(t_s_amount)]), ignore_index=True) df = df.append(pd.Series(t_r_amount, index=df.columns[:len(t_s_amount)]), ignore_index=True) df.to_excel(new_file_name,index=False)` this piece of code is doing the job..... – hemanth sai reddy Feb 25 '19 at 03:59
  • again Hi @Me-So-Cool,but the problem is while i am writing my data to excel, Date is getting changed from **yyyymmdd** to **yyyymmdd hh mm ss** Previouly i worked with XLSXWRITER there i had an option to fomat, but here I am not able to do . Your help will be appreciated. – hemanth sai reddy Feb 25 '19 at 04:04
  • Why isn't the above code working? Can you specify the error? It works fine for me! –  Feb 25 '19 at 06:24
  • Hi @Me-So-Cool, ** ValueError: cannot set a row with mismatched columns** I am getting the above error, but the which i have commented is working for me, and i have tried your code this way, `df = pd.DataFrame(mr) # Add the extra rows df.loc[-1] =t_s_amount df.index = df.index + 1 df.loc[-1] = t_gst_amount df.index = df.index + 1 df.loc[-1] = t_r_amount # Write to excel df.to_csv(new_file_name, sep='\t', encoding='utf-8', index=False) ` – hemanth sai reddy Feb 27 '19 at 04:38
  • Yes @Me-So-Cool my modifications are working, only one problem is when i am writing **Date** information to excel the data is getting stored in **yyyymmdd hh mm ss** instead of **yyyymmdd** . I need the latter format. – hemanth sai reddy Feb 27 '19 at 13:34
  • [Excel Date and time formatting](https://www.myonlinetraininghub.com/excel-date-and-time-formatting) This may help you. And please upvote if the solution worked. Cheers! –  Feb 27 '19 at 13:50
  • hey @Me-So-Cool, my problem is not with excel, while i am writing data frame to excel using df.to_excel(filename), suppose if i have **26-06-1990** , it getting written as ** 26-06-1990 00:00:00** – hemanth sai reddy Feb 27 '19 at 16:57
  • Yeah I get it. So write it in that way, and why not change the format in excel afterwards? –  Feb 27 '19 at 18:19
  • It can be changed after, but i don't want user to do anything , if everything is handled by us then it will be good..!! – hemanth sai reddy Feb 28 '19 at 04:02