Creating a string text from values and indexes that meet condition-CodePudding

Hello Guys I have the following dataset:

# creating dataset
dataset = pd.DataFrame()
dataset['name'] = ['Alex', 'Alex', 'Alex','Alex','Alex',
                   'Marie', 'Marie', 'Marie','Marie','Marie',
                   'Luke', 'Luke', 'Luke','Luke','Luke']
dataset['sales'] = [690,451,478,524,750,452,784,523,451,125,854,745,856,900,105]

dataset.info()
dataset.shape

I want to create and print a string that will let me know the sales reps whose mean sale's are above 500 units, in order to achieve this I first grouped the data and calculated the mean by name like so:

result_grouped=dataset.groupby(['name']).aggregate({'sales': 'mean'})

If I can use the following code to filter my target sales reps

print(list(result_grouped.index[(result_grouped['sales']>500)]))
result_grouped[(result_grouped['sales']>500)]

which gives:

['Alex', 'Luke']
        sales
name    
Alex    578.6
Luke    692.0

but my desired output will be something like so:

a printable string in the format:

"The reps in the target metrics are: name1 with mean1, name2 with mean2, ... , namen with meann"

for this example my output will be:

"The reps in the target metrics are Alex with 578.6, Luke with 692.0"

I am very new to python and in the verge of a mental breakdown I know that this in the code genre does not seeem too hard but guys I come from an R enviroment and Python just seems to be very difficult for me I trully appreciate your help with this thank you so much for your help

CodePudding user response：

You can chain the pandas functions then use a list comp to unpack the list of tuples into a sentence.

result_grouped = (
    dataset
    .groupby(['name'])
    .aggregate({'sales': 'mean'})
    .query('sales.gt(500)')
    .reset_index()
    .to_records(index=False)
    .tolist()
)


print(f"The reps in the target metrics are {', '.join([f'{x[0]} with {x[1]}' for x in result_grouped])}")

Output:

The reps in the target metrics are Alex with 578.6, Luke with 692.0

CodePudding user response：

you can use:

result_grouped=dataset.groupby(['name']).aggregate({'sales': 'mean'}).reset_index()
result_grouped=result_grouped[(result_grouped['sales']>500)]

result_grouped['text']=result_grouped['name']   ' with '   result_grouped['sales'].astype(str)
listt=', '.join(result_grouped['text'].to_list())
final="The reps in the target metrics are {}".format(listt)

print(final)
'''
The reps in the target metrics are Alex with 578.6, Luke with 692.0
'''