osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

pandas split and melt()


Hi

Having fun with pandas filtering a work excel file.
My current script opens selected and filters the data and saves as excel.

import pandas as pd 
import numpy as np

log = pd.read_excel("log_dump_py.xlsx")
df = log.filter(items=['Completed', 'Priority', 'Session date', 'Consultant', 'Coach',
       'Delivery Method', 'Focus of Coaching', 'Leader', 'Site',
       'Coaching Description','Motor/Property', 
       ],)
completed_tasks = df.loc[(df['Completed'] == 'Yes') & (df['Motor/Property'] == 'Motor') & (df['Delivery Method'] == 'Group Coaching')]
print(completed_tasks.head(n=5))
completed_tasks.to_excel("filtered_logs.xlsx")

This leaves me with a set of several columns. The main column of concern for this example is a consultant

Session date	Consultant
2019-06-21 11:15:00	WNEWSKI, Joan;#17226;#BALIN, Jock;#18139;#DUNE, Colem;#17230;

How can I split the consultant column, keep only names and drop the numbers and for every session date create a line with data and consultants name?

NB. There are varied amounts of consultants so splitting across columns is uneven. if it was even melt seems like it would be good https://dfrieds.com/data-analysis/melt-unpivot-python-pandas


Thanks

Sayth