英文:
Merging records based on consecutive dates in python
问题
我想合并我的数据框记录，如果日期相同的话。在下面的示例中，我想将日期（13,14,15），（25,26），（30,31）合并在一起，因为日期是连续的。如果有任何单日间断，我想中断记录的合并。
cust date descriptionCUST123 2020-06-13 观察到增加的损失率CUST123 2020-06-13 切割工作已完成CUST123 2020-06-14 在狭小的区域工作CUST123 2020-06-15 生产关闭了附近的应用程序CUST123 2020-07-17 损失压力慢慢上升，发生了故障CUST123 2020-08-25 建立循环负载CUST123 2020-08-26 执行粘性测试CUST123 2020-08-28 工作会议之前的低能量CUST123 2020-08-30 执行维护服务CUST123 2020-08-31 重新连接控制线
预期输出
cust date descriptionCUST123 2020-06-13 观察到增加的损失率切割工作已完成在狭小的区域工作生产关闭了附近的应用程序CUST123 2020-07-17 损失压力慢慢上升，发生了故障CUST123 2020-08-25 建立循环负载执行粘性测试CUST123 2020-08-28 工作会议之前的低能量CUST123 2020-08-30 执行维护服务重新连接控制线
英文:
I want to merge records of my dataframe if dates are same.Here in the below example I want to merge date (13,14,15), (25,26), (30,31) together as there are continuous dates. I want to break the merging of record if there is any single day break.
cust date descriptionCUST123 2020-06-13 observed increased loss rateCUST123 2020-06-13 cut performed jobCUST123 2020-06-14 working tight areaCUST123 2020-06-15 production shut neighbouring appCUST123 2020-07-17 loss pressure slow gain tripCUST123 2020-08-25 established circulation loadCUST123 2020-08-26 performed sticky testCUST123 2020-08-28 job meeting prior low energyCUST123 2020-08-30 performed maintenance serviceCUST123 2020-08-31 reconnected control line
expected output
cust date descriptionCUST123 2020-06-13 observed increased loss rate cut performed jobworking tight area production shut neighbouring appCUST123 2020-07-17 loss pressure slow gain tripCUST123 2020-08-25 established circulation load performed sticky testCUST123 2020-08-28 job meeting prior low energyCUST123 2020-08-30 performed maintenance service reconnected control line
答案1
得分: 3
为了在日期相同的情况下合并数据框的记录，你可以这样做：
merged_df = df.groupby(['cust', 'date'])['description'].apply(' '.join).reset_index()
输出结果如下：
cust date description0 CUST123 2020-06-13 observed increased loss rate cut performed job1 CUST123 2020-06-14 working tight area2 CUST123 2020-06-15 production shut neighbouring app3 CUST123 2020-07-17 loss pressure slow gain trip4 CUST123 2020-08-25 established circulation load5 CUST123 2020-08-26 performed sticky test6 CUST123 2020-08-28 job meeting prior low energy7 CUST123 2020-08-30 performed maintenance service8 CUST123 2020-08-31 reconnected control line
编辑：如果你想要合并连续的日期，保留连续范围的第一个日期，可以这样做：
# 按照 'date' 列对数据框进行排序（如果 'df' 尚未排序）df.sort_values('date', inplace=True)# 初始化变量merged_data = []prev_row = None# 遍历行for _, row in df.iterrows():if prev_row is None or row['cust'] != prev_row['cust'] or (row['date'] - prev_row['date']).days > 1:merged_data.append({'cust': row['cust'], 'date': row['date'], 'description': row['description']})else:merged_data[-1]['description'] += ' ' + row['description']prev_row = row# 创建合并后的数据框merged_df = pd.DataFrame(merged_data)print(merged_df)
输出结果如下：
cust date description0 CUST123 2020-06-13 observed increased loss rate cut performed job...1 CUST123 2020-07-17 loss pressure slow gain trip2 CUST123 2020-08-25 established circulation load performed sticky ...3 CUST123 2020-08-28 job meeting prior low energy4 CUST123 2020-08-30 performed maintenance service reconnected cont...
英文:
In order to merge records of a dataframe if dates are same, you could do:
merged_df = df.groupby(['cust', 'date'])['description'].apply(' '.join).reset_index()
which outputs:
cust date description0 CUST123 2020-06-13 observed increased loss rate cut performed job1 CUST123 2020-06-14 working tight area2 CUST123 2020-06-15 production shut neighbouring app3 CUST123 2020-07-17 loss pressure slow gain trip4 CUST123 2020-08-25 established circulation load5 CUST123 2020-08-26 performed sticky test6 CUST123 2020-08-28 job meeting prior low energy7 CUST123 2020-08-30 performed maintenance service8 CUST123 2020-08-31 reconnected control line
EDIT: If you want to merge the consecutive dates, keeping the first date of the consecutive range, you could it like this:
# Sort DataFrame by 'date' (in case 'df' is not already sorted)df.sort_values('date', inplace=True)# Initialize variablesmerged_data = []prev_row = None# Loop through the rowsfor _, row in df.iterrows():if prev_row is None or row['cust'] != prev_row['cust'] or (row['date'] - prev_row['date']).days > 1:merged_data.append({'cust': row['cust'], 'date': row['date'], 'description': row['description']})else:merged_data[-1]['description'] += ' ' + row['description']prev_row = row# Create merged DataFramemerged_df = pd.DataFrame(merged_data)print(merged_df)
Output:
0 CUST123 2020-06-13 observed increased loss rate cut performed job...1 CUST123 2020-07-17 loss pressure slow gain trip2 CUST123 2020-08-25 established circulation load performed sticky ...3 CUST123 2020-08-28 job meeting prior low energy4 CUST123 2020-08-30 performed maintenance service reconnected cont...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论