Python 3.11 | How to have Python collect the values of a certain cell in a readable Excel file IF the cell's row has a certain value in another column

Question

I'm quite new to this data-collecting bit of coding, just trying to make a process a bit more automated.

Currently, I have this excel sheet that python can read just fine, however, I am trying to find out how to extract certain bits of the sheet.

The excel document is formatted so when ran in Python with Pandas

   Chara. ID    Name  Room
0          1     Jon     2
1          2    Mary     4
2          3  George     2
3          4   Henry     1
4          5    Jane     1
5          6  George     4
6          7  Thomas     3
7          8  Hannah     3
8          9     Luz     1
9         10    Ivan     2

With my current code as

import pandas as pd

dataframe1 = pd.read_excel('TestExcel.xlsx')
                                       

print(dataframe1)

What I'm aiming to do is to find all the Chara. IDs for each room (Eg. a list of IDs for room 1, room 2, etc.) Then use that data to copy and send a pre-generated message to the list of ids for that room's location (Eg. "If you are receiving this message, you should report to room 1").

Any help or guidance is greatly appreciated. I appreciate your time! -Fen

Answer 1

You can use groupby for this:

groups = dataframe1.groupby("room")

for room, group in groups:
    print(f"Room: {room}")
    ids = group["Chara. ID"].values
    print(f"IDs: {ids}")

Note that group itself is a DataFrame and you can do every DataFrame-Operation you want on it.