Multiple results for one row PANDAS

user_11407400 0
Viewed 73

I have a data frame with Names and departments. All these Names can map to 4 different departments(Department A, B, C, and D). For example, each name has to get mapped to Department A, B, C, D. In the initial data, each name is only mapped to one department. In the final dataset, each name maps to four different departments. I need a way to insert 3 additional rows for each name and also add the 3 other departments for them.

Example:

Initial Data:

Name Department
Joe  A
Ellen A
Zach  A

Final Data:

Name Department
Joe  A
Joe  B
Joe  C
Joe  D
Ellen A
Ellen B
Ellen C
Ellen D
Zach  A
Zach  B
Zach  C
Zach  D

I know this can be done in a for loop of some sort but I am very new in Pandas any help will be very much appreciated.

pandas dataframe dictionary python
4 Answers

You can try to assign to each name all departments as a list and then .explode:

df = df.assign(Department=[["A", "B", "C", "D"]] * len(df)).explode(
    "Department"
)
print(df)

Prints:

    Name Department
0    Joe          A
0    Joe          B
0    Joe          C
0    Joe          D
1  Ellen          A
1  Ellen          B
1  Ellen          C
1  Ellen          D
2   Zach          A
2   Zach          B
2   Zach          C
2   Zach          D
user_100359850

Assuming just the product of values is needed itertools.product has much less overhead than pandas functions:

from itertools import product

import pandas as pd

df = pd.DataFrame({'Name': ['Joe', 'Ellen', 'Zach'],
                   'Department': ['A', 'A', 'A']})

Compute new DataFrame from unique names:

df = pd.DataFrame(product(df['Name'].unique(),
                          ['A', 'B', 'C', 'D']),
                  columns=df.columns)

df:

     Name Department
0     Joe          A
1     Joe          B
2     Joe          C
3     Joe          D
4   Ellen          A
5   Ellen          B
6   Ellen          C
7   Ellen          D
8    Zach          A
9    Zach          B
10   Zach          C
11   Zach          D
user_154978880

Yet another approach using pandas.DataFrame.groupby

>>> departments = ['A', 'B', 'C', 'D']
>>> df.groupby('Name').apply(lambda _, L=departments: L).explode().to_frame('Department')
      Department
Name            
Ellen          A
Ellen          B
Ellen          C
Ellen          D
Joe            A
Joe            B
Joe            C
Joe            D
Zach           A
Zach           B
Zach           C
Zach           D

Or pd.Series.reset_index has a name argument: convenient in such configuration:

>>> df.groupby('Name').apply(lambda _, L=departments: L).explode().reset_index(name='Department')
     Name Department
0   Ellen          A
1   Ellen          B
2   Ellen          C
3   Ellen          D
4     Joe          A
5     Joe          B
6     Joe          C
7     Joe          D
8    Zach          A
9    Zach          B
10   Zach          C
11   Zach          D
user_41940790

We do merge

out = df.merge(pd.DataFrame({'New Dept':list('ABCD')}),how='cross')
Out[373]: 
     Name Department New Dept
0     Joe          A        A
1     Joe          A        B
2     Joe          A        C
3     Joe          A        D
4   Ellen          A        A
5   Ellen          A        B
6   Ellen          A        C
7   Ellen          A        D
8    Zach          A        A
9    Zach          A        B
10   Zach          A        C
11   Zach          A        D
user_79645270
Related