Clean up CSV data in Python

Hi, nice to meet you.I'm a beginner, but I appreciate your cooperation.
Suppose you read a CSV file similar to the one shown on the left.
Column A represents a person, and a, b in column B represents height and weight (c is filling in the gap, so don't worry about it).
Column C corresponds to column B.
If the same person has a lot of data, as they measure a and b irregularly, I would like to take out only the latest number in the person (the lower the line, the newer the data) and create a CSV file like the one shown on the right. How can I put it together?
I apologize for not explaining well.

Note: The user ID is worth column A in the figure, the content ID is worth column B, and the value is worth column C.

 column=data [['User ID', 'Content ID', 'Value']]

for i in range(len(column['User ID'])):
    if column.iloc[i,1]!=1003 or column.iloc[i,1]!=1004:
        column.drop(i)

This is the code I made myself.

python pandas

2022-09-30 19:01

1 Answers

In addition to not getting an answer, it was up to -1, so I would like to follow you

This is what your question is all about.

How do I retrieve the last line of each group when I group data frames in Pandas?

Pandas groupby maintains the read order.Also, I think you can achieve your goal by taking out the last line in .last().

#!/usr/bin/env python3
# -*-coding:utf-8-*-
import pandas aspd
from io import StringIO

data='\
User ID, Content ID, Value
1,a,170
1,b,60
1,c,0
1,a,169
2,c,0
2,a,157
2,b,45
2, a, 155
2, b, 46
2,c,0
2, a, 155
2,b,45
3,c,0
3, a, 171
3,b,65
3,a,170
4,c,0
4, a, 158
4,b,51
5, a, 149
5, a, 150
5,b,35
5,c,0
5,b,35
5, a, 151
5,c,0
5,b,38
'''

with StringIO(data)ascsvfile:
    df = pd.read_csv(csvfile)
    summary=df.groupby(['User ID', 'Content ID'].last()
    # If you want to exclude "c":
    # summary=df.query('Content ID!="c") .groupby(['User ID', 'Content ID'].last()
print(summery.to_csv())

User ID, Content ID, Value
1,a,169
1,b,60
1,c,0
2, a, 155
2,b,45
2,c,0
3,a,170
3,b,65
3,c,0
4, a, 158
4,b,51
4,c,0
5, a, 151
5,b,38
5,c,0

2022-09-30 19:01

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656