Python How do I get multiple results in the same excel sheet

0 votes

I now use the groupby() function to get the averages of the first 6 columns in a data collection that has 10 columns, as well as the minimum and maximum values for the remaining 4 columns. The problem is that I keep receiving problems saying that the "list" object has no property "to excel" when I try to input the results into a new excel sheet.

avg = df.groupby('column1')[['column2' , 'column3', 'column4' ,'column5',  'column6',  'column7' ]].mean()

c8= (df.assign(c8=df['column8'].abs())
       .groupby(['column1'])['column8'].agg([('min' , 'min'), ('max', 'max')])
       .add_prefix('c8'))

c9= (df.assign(c9=df['column9'].abs())
       .groupby(['column1'])['column9'].agg([('min' , 'min'), ('max', 'max')])
       .add_prefix('c9'))

c10= (df.assign(c10=df['column'].abs())
       .groupby(['column1'])['column10'].agg([('min' , 'min'), ('max', 'max')])
       .add_prefix('c10'))


df = [avg , c8, c9, c10]

#print(df)

wb = pd.ExcelWriter('C:filepath.xlsx' , engine='xlsxwriter')
df.to_excel(wb,sheet_name='Results')
wb.save()
         

Whenever I run any of the functions by themselves, they all work fine, the issue is when I put them together.

What I am expecting is an excel sheet filled with the corresponding data presented like this:

column1 column2 column3 column4 column5 column6 column7 c8min c8max c9min c9max c10min c10max 
Mar 25, 2023 in Others by Kithuzzz
• 38,010 points
256 views

1 answer to this question.

0 votes

Try this:

fmin = lambda x: abs(x).min()
fmax = lambda x: abs(x).max()

out = (
        df.groupby("column1", as_index=False)
          .agg(column2=("column2", "mean"), column3=("column3", "mean"),
               column4=("column4", "mean"), column5=("column5", "mean"),
               column6=("column6", "mean"), column7=("column7", "mean"), # avg
               c8min=("column8", fmin), c8max=("column8", fmax), # c8
               c9min=("column9", fmin), c9max=("column9", fmax), # c9
               c10min=("column10", fmin), c10max=("column10", fmax)) # c10
)


out.to_excel("C:/filepath.xlsx", sheet_name="Results", index=False)
answered Mar 25, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How do I get the current date and time in PHP?

The time would go by your server ...READ MORE

answered Feb 16, 2022 in Others by Aditya
• 7,680 points
502 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
1,284 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
501 views
0 votes
1 answer

How do I set the default paste special in excel to paste only values

I paste the values with a keyboard ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,420 points
2,778 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,727 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
1,704 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,420 points
2,055 views
0 votes
1 answer

Export DataFrame timedelta column to timestamp Excel column

The reason that the column format isn't ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
1,036 views
0 votes
1 answer

How do I stop python from appending data to the same row in excel?

There is no indication in your code ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,420 points
256 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,124 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP