How can I transform a dataframe by putting different values of a column into different columns?

C Cheng Published at Dev

C Cheng

I have a question about my code. My data looks like this:

I want to summarize by SKU # and by reason what percentage (VS Ordered) was not shipped to this format:

What I did is that I used pd.groupby to have the sum of cut by SKU#, then I got a dataframe looks like this: SKU Reason A Transportation A Raw Material B Transportation B Raw Material C Transportation C Raw Material

Just wondering how can I change to this format: A Transportation Raw Material B Transportation Raw Material C Transportation Raw Material

My plan is to merge this one to one that was sum of order amount by SKU number to get the percentages of each reason. I would be more than happy to get a better method as well!

dww142

Create a ratio of unshipped, then sum in a pivot table:

data = {
    'PO Nbr' : [1,2,3,4,5],
    'SKU #' : ['A','B','A','C','A'],
    'Ordered' : [100,100,500,100,200],
    'Shipped' : [100,80,450,100,30],
    'Reason for Not Shipped' : ['', 'raw material','transportation','','transportation']
}

df = pd.DataFrame(data)

df['Pct Not Shipped'] = (df['Ordered'] - df['Shipped']) / df.groupby('SKU #').Ordered.transform('sum')

print(df.pivot_table(index='SKU #',columns='Reason for Not Shipped',values='Pct Not Shipped',aggfunc='sum'))

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-15

Comments

0 comments

How can I count occurences of different integer values (in a particular column) for rows with different factor values(another column)?

How can I create a NEW column in a dataframe based on values of another column in a DIFFERENT dataframe that have common information?

After comparing two columns, how can I remove unique values in one column with its values in other different columns in R

TOP Ranking

Article

How can I transform a dataframe by putting different values of a column into different columns?

How can I transform a dataframe by putting different values of a column into different columns?

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

Emulator wrong screen resolution in Android Studio 1.3

3D Touch Peek Swipe Like Mail

Double spacing in rmarkdown pdf

Svchost high CPU from Microsoft.BingWeather app errors

How to how increase/decrease compared to adjacent cell

Using Response.Redirect with Friendly URLS in ASP.NET

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

BigQuery - concatenate ignoring NULL

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Can a 32-bit antivirus program protect you from 64-bit threats

Make a B+ Tree concurrent thread safe

Bootstrap 5 Static Modal Still Closes when I Click Outside

Vector input in shiny R and then use it

Assembly definition can't resolve namespaces from external packages