1
$\begingroup$

I know how to read in the data frame in Pandas and do the basic manipulation, but how do I populate the order column based on the ID column? For instance, if bike occurs twice such as seen below, how can I populate the order column with 1 for the first occurrence and then 2 for the second occurrence and then do the same for car.

    ID      Color       Order?????
    bike    red         1
    bike    black       2
    car     green       1
    car     orange      2
    car     blue        3
$\endgroup$
1
  • $\begingroup$ stackoverflow.com/questions/26720916/… PS. As a good practice, when you post a question like this, it's easier to answer if you provide some short code to create the dataframe. Also, these types of programming questions are better suited for StackOverflow. $\endgroup$ Oct 4, 2018 at 21:40

2 Answers 2

2
$\begingroup$

Welcome to Data Science! Here I create your data frame and show one way to create the column you need. I use numpy in addition to pandas:

import pandas as pd
import numpy as np

# Create df
ID = ['bike', 'bike', 'car', 'car', 'car']
Color = ['red', 'black', 'green', 'orange', 'blue']
df = pd.DataFrame(data={'ID': ID, 'Color': Color})

Add a new column called 'Order', with counts based on each group - these are mini dataframes that contain only one of the ID values only. This makes use of the groupby method on a Pandas dataframe.

df['Order'] = df.groupby('ID').transform(lambda x: 1 + np.arange(len(x)))

The transform method takes a function and applies it to each group. I use an anonymous function (just a function with no name), also called lambda functions, using the keyword lambda.

The result:

    Color    ID  Order
0     red  bike      1
1   black  bike      2
2   green   car      1
3  orange   car      2
4    blue   car      3

As anymous.asker mentioned, it would be helpful in the future if you post the code that creates your dataframe!

$\endgroup$
0
0
$\begingroup$

This can be done with GroupBy.cumcount (pandas package):

df['Order'] = df.groupby('ID').cumcount()

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.cumcount.html

$\endgroup$

Your Answer

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

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