Count in python

[Debutantdata]

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:

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

[Debutantdata]

Nobody? :)

[scidam]

One way to do what you want is to use multi-index, e.g.

import pandas as pd
df = pd.DataFrame({'Company': ['One', 'One', 'Two', 'Two', 'Two', 'Three'],
                   'value': [1, 3, 5, 7, 8, 0],
                   'tag': ['Table', 'Window', 'Plate', 'Board', 'Ship', 'Roof']})
df['counts'] = df.groupby('Company')['Company'].transform('count')
df.index = pd.MultiIndex.from_frame(df[['Company', 'tag']])
df.drop(['Company', 'value', 'tag'], axis=1)