1
$\begingroup$

I have this problem:

import pandas as pd

stripline = "----------------------------"

rawData = {
    'order number': ['11xa', '11xa', '11xa', '21xb', '31xc'],
    'working area': ['LLA', 'LLE', 'LLS', 'MLA', 'MLE'],
    'time': ['1', '6', '13', '35', '24']
}

df = pd.DataFrame(rawData)
print("original data:")
print(df.head())

print(stripline)

rawData2 = {
    'order number': ['11xa', '21xb', '31xc'],
    'working area': ['LLS', 'MLA', 'MLE'],
    'time': ['20', '35', '24']
}
df2 = pd.DataFrame(rawData2)

print("expected result:")
print("group after order number, sum all times to that order and choose working field with the biggest time")
print(df2.head())

How can I manipulate my dataframe df to get the df2?

I want to sum up all values in the time column that correspond to an order number. I want to use the working field with the highest time and especially I want to keep the rest of the data. The new data frame has three orders, the old one five.

$\endgroup$
0
$\begingroup$

1) Convert time column to integer:

df['time'] = df['time'].astype(int)

2) Find working area with maximum time:

for index, row in df.iterrows():
    df.at[index, 'max working area'] = df[df['time'] == df[df['order number'] == row['order number']]['time'].max()]['working area'].values[0]

3) Aggregate time column:

df2 = df.groupby(['order number', 'max working area']).sum()

Is this what you wanted?

$\endgroup$
0
$\begingroup$

This line of code should do it for you :

df.groupby(["order number", "working area"])['time'].agg(sum)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.