Pandas aggregate function output to xlsx

Question

I have sqlite queries that I have turned to pandas dataframes. I have passed these dataframes to a function to get aggregate information. How can I populate an excel worksheet with the results of the function? ie- how can I turn the function output into a dataframe? (Note- I am using openpyxl to create the workbook)

Here is the code for the df and function:

# Nationwide measure statistics
nationwide_measures = pd.read_sql_query("""select state,
          measure_id,
          measure_name,
          score
from timely_and_effective_care___hospital;""", conn)

# Remove the non-numeric string values from 'score'
nationwide_measures1 = nationwide_measures[nationwide_measures['score'].astype(str).str.isdigit()]

# Change score to numeric
nationwide_measures1['score'] = pd.to_numeric(nationwide_measures1['score'])

# Function to grab measure values
def get_stats(group):
    return {'Minimum': group.min(), 'Maximum': group.max(), 'Average': group.mean(), 'Standard Deviation': group.std()}

# Function output    
nationwide_measures1['score'].groupby(nationwide_measures1['measure_id']).apply(get_stats).unstack()

I tried:

# Function to grab measure values
def get_stats(group):
    return pd.DataFrame({'Minimum': group.min(), 'Maximum': group.max(), 'Average': group.mean(), 'Standard Deviation': group.std()})

but this returns "Value Error: If using all scalar values, you must pass an index"

I also tried:

# Function to grab measure values
def get_stats(group):
    df = pd.DataFrame({'Measure Name': group.columns['measure_name'],'Minimum': group.min(), 'Maximum': group.max(), 'Average': group.mean(), 'Standard Deviation': group.std()}, index = [0])
    return df

But this gives the error: "AttributeError: 'Series' object has no attribute 'columns'"

Pandas aggregate function output to xlsx

1 Answers