1

I am facing an issue while developing a piece of code for the below function. I have a dataframe with the below values

Date Name UserId task Client duration
1/2/2022 'Alex, J' 101 'C' QAT 8
2/2/2022 'Alex, J' 101 'C' QAT 8
1/2/2022 'Marc, B' 'Marc, B' 102 102 'A' 'B' App Dev 8
2/2/2022 'Marc, B' 'Marc, B' 102 102 'A' 'B' App Dev 8

Now, I want to convert to the below dataframe.

Date Name UserId task Client duration
1/2/2022 'Alex, J' 101 'C' QAT 8
2/2/2022 'Alex, J' 101 'A' QAT 8
1/2/2022 'Marc, B' 102 'A' App 4
1/2/2022 'Marc, B' 102 'B' Dev 4
2/2/2022 'Marc, B' 102 'A' App 4
2/2/2022 'Marc, B' 102 'B' Dev 4

I want to separate out the values in Name, UserId, task and Client column and want to divide the duration by the number of tasks for a particular day.

For example, I had 2 tasks here i.e A and B for the same day(1/2/2022). So i divided the duration of 8 by 2 and got 4 for each A and B.

I would request you to please help me in this. Thanks alot.

Improve this question
3
  • 2
    Warm welcome to SO. Please read stackoverflow.com/help/how-to-ask and stackoverflow.com/help/minimal-reproducible-example. Then update your question to help us to help you. Please create your MWE (minimal working example) with test data like df = pd.DataFrame(...). Beside that look at the explode() method in pandas. Commented Jul 27, 2022 at 8:33
  • 1
    Please provide enough code so others can better understand or reproduce the problem. Commented Jul 27, 2022 at 15:00
  • 1
    @buhtz Apologies for the lapse in format. Thank you for pointing out the correct method. Commented Jul 28, 2022 at 9:10

1 Answer 1

Reset to default
2

The key here is to convert your string in each row to list and then use explode:

# Sample data:
df = pd.DataFrame({'user':['102 102', '103 103'],
                   'task':["'A' 'B'", "'A' 'B'"],
                   'duration':[8, 8]})

# Convert to list 
df['user'] = df['user'].str.split(' ')
df['task'] = df['task'].str.split(' ')

# Split row to multiple rows base on list
df.explode(['user', 'task'])

df

If your string is more complicated to separated, consider using import re. Or you can view it here: How to group by columns and merge only the unique strings of another column which are separated by a delimiter?

I think the rest is easy and you can do it well

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much @PTQuoc for this solution. The code worked like a charm :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.