Sep-16-2019, 07:31 PM
Dear all,
I am a new joiner in the Python adventure.
I would like to count the data in the column 'Company' and know in which of the 'Selection' they are and then send the result by mail.
I realised the first and the last step but struggle a bit to get all the corresponding 'Selection' for a specific company.
Extract of the excel:
Company Open Selection
Allianz 26151.660156 Topic 2
Zurich 26076.210938 Topic 3
Allianz 26287.839844 Topic 2
Conoco 26519.390625 Topic 5
Conoco 26726.250000 Topic 6
Allianz 26705.250000 Topic 7
Zurich 26601.580078 Topic 8
Allianz 26536.859375 Topic 2
Conoco 26418.269531 Topic 3
Conoco 26407.660156 Topic 4
Current Python:
Best,
T
I am a new joiner in the Python adventure.
I would like to count the data in the column 'Company' and know in which of the 'Selection' they are and then send the result by mail.
I realised the first and the last step but struggle a bit to get all the corresponding 'Selection' for a specific company.
Extract of the excel:
Company Open Selection
Allianz 26151.660156 Topic 2
Zurich 26076.210938 Topic 3
Allianz 26287.839844 Topic 2
Conoco 26519.390625 Topic 5
Conoco 26726.250000 Topic 6
Allianz 26705.250000 Topic 7
Zurich 26601.580078 Topic 8
Allianz 26536.859375 Topic 2
Conoco 26418.269531 Topic 3
Conoco 26407.660156 Topic 4
Current Python:
import pandas as pd import smtplib from email.mime.text import MIMEText data = pd.read_excel (r'/Users/Documents/Test_python.xlsx', sheet_name='Feuil1') df = pd.DataFrame(data, columns= ['Company','volume']) dt = df['Company'].value_counts() print(dt) gmail_user = "XXXX" gmail_appPassword = "XXXXX" sent_from = ['XXXX'] to = ['XXXXX'] text = dt msg = MIMEText(text) server = smtplib.SMTP_SSL('smtp.gmail.com', 465) server.login(gmail_user, gmail_appPassword) server.sendmail(sent_from, to, msg.as_string()) server.quit()Current result...and the idea would be to get below each company in which 'Selection' they are
Output:MS 63
Swiss Re 63
Allianz 53
Liverpool 48
Manchester 15
Conoco 4
Zurich 3
HSBC 1
Goldman Sachs 1
Name: Company, dtype: int64
The expected result would be the following (for example):Output:MS 63
Topic 1
Topic 2
...
Swiss Re 63
Topic 23
Topic 55
...
Allianz 53
Liverpool 48
Manchester 15
Conoco 4
Zurich 3
HSBC 1
Goldman Sachs 1
Thank you in advance for your help!Best,
T