Memorandum (Add name only to people with the same surname in the list)

A word diary: These days, the days of fighting God Excel continue.

subject

No. name age
1 Taro Tanaka 33
2 Suzuki Jiro 22
3 Saburo Tanaka 11

There is nothing wrong with handling the above table as data, When outputting, there is a request that "please do as shown in the table below".

No. name age
1 Tanaka (thick) 33
2 Suzuki 22
3 Tanaka (3) 11

Surprisingly, I couldn't find a page that does this processing itself, so I will post the implementation result.

Implementation

PRG


dataframe_raw = pd.read_excel("Roster.xlsx")
df=dataframe_raw["name"].str.split(' ',expand=True)
#In this case, the name was divided by double-byte space, so split it into two columns.

If you divide by split, dataframe_process will be as shown in the table below, so check for duplicate surnames from here. If there are duplicates, special processing is used. If not, the surname is used as it is.

0 1
Tanaka Taro
Suzuki Jiro
Tanaka Saburo

python


df["name"]=""
 #Add the storage column to the third column

for i in range(0,len(df)):
 #i to 0~Loop to the length of the roster
  NAME = df.at[i,0]
  check_count=(df[0]==NAME).sum()
 #Count how many character strings are stored in the 0th column of the i-th row (if it is 2 or more, there is a person with the same surname).
  if check_count>=2:
    df.at[i,"name"]=df.at[i,0]+"("+df.at[i,1][0]+")"
 #If there are duplicates, the character string in the 0th column +("0th character of the first character string")Substitute
  else :
    df.at[i,"name"]=df.at[i,0]
 #If there is no duplication, the character string in the 0th column of the i-th row +(0th character of the character string in the 1st column of the i-line)Substitute

With this process, the data frame will be as shown in the table below, so you can use it by substituting the name column or combining it.

0 1 name
Tanaka Taro Tanaka (thick)
Suzuki Jiro Suzuki
Tanaka Saburo Tanaka (3)

At the end

I feel that it is relatively clean, but I am afraid of what to do if a list with the same surname and the same name appears.

Recommended Posts

Memorandum (Add name only to people with the same surname in the list)
How to get a list of files in the same directory with python
Load the module with the same name in another location
In Jupyter, add IPerl to the kernel.
[Cloudian # 5] Try to list the objects stored in the bucket with Python (boto3)
Log in to the remote server with SSH
Mathematics memorandum to keep up with the field # 4
A memorandum to make WebDAV only with nginx
Extract only the file name excluding the directory in the directory
(Note) Importing Excel with the same column name
Get only the subclass elements in a list
Detect folders with the same image in ImageHash
Mathematics memorandum to keep up with the field # 1
Mathematics memorandum to keep up with the field # 2
Mathematics memorandum to keep up with the field # 3
How to identify the element with the smallest number of characters in a Python list?
[Note] How to write QR code and description in the same image with python
How to count the number of occurrences of each element in the list in Python with weight
I want to extract only pods with the specified label using Label Selector in Client-go
When a local variable with the same name as a global variable is defined in the function
How to get the variable name itself in python
Add information to the bottom of the figure with Matplotlib
Just add the driver to the shape key with blender
Convert the image in .zip to PDF with Python
Read the linked list in csv format with graph-tool
[Python] How to output the list values in order
Recursively get the Excel list in a specific folder with python and write it to Excel.
Make sure all the elements in the list are the same in Python
When the variable name conflicts with the Devaga command in pdb
A memorandum to register the library written in Hy in PyPI
Access the C structure field with the name reserved in Go.
[pyqtgraph] Add region to the graph and link it with the graph region
How to manipulate the DOM in an iframe with Selenium
How to get the last (last) value in a list in Python
I can't log in to the admin page with Django3
The usual way to add a Kernel with Jupyter Notebook
I want to make the Dictionary type in the List unique
Try to decipher the garbled attachment file name with Python
Command to list all files in order of file name
Methods available in the list
Get the list in the S3 bucket with Python and search with a specific Key. Output the Key name, last update date, and count number to a file.
Output the key list included in S3 Bucket to a file
I tried to describe the traffic in real time with WebSocket
It's faster to add than to join and extend the list, right?
How to handle multiple versions of CUDA in the same environment
Automatically rename a node with the same name found while working
I want to solve APG4b with Python (only 4.01 and 4.04 in Chapter 4)
Output the contents of ~ .xlsx in the folder to HTML with Python
I tried to process the image in "sketch style" with OpenCV
I tried to process the image in "pencil style" with OpenCV
I want to make the second line the column name in pandas
Attempt to extend a function in the library (add copy function to pathlib)
People memorize learned knowledge in the brain, how to memorize learned knowledge in machine learning
How to display bytes in the same way in Java and Python
[Completed version] Try to find out the number of residents in the town from the address list with Python