How to Create new columns using other column Values in Python?

Question

I need to create a column Period_Subcategory based on other columns' values: a dictionary of {Period value: [list of Sub_Category values...]}

Input df:

Period   Category    Sub_Category
FY18Q1   Clothing    Shirt    
FY18Q2   Clothing    Trouser
FY18Q1   Clothing    Shirt
FY18Q3   Clothing    Pant 
FY18Q1   Accessories Watch
FY18Q2   Accessories Muff
FY18Q2   Accessories Watch
FY18Q3   Accessories Chains

Desired output df_output:

Category    Period_Subcategory
Clothing    {'FY18Q1':'Shirt','FY18Q2':'Trouser','FY18Q3':'Pant'}
Accessories {'FY18Q1':'Watch','FY18Q2':['muff','Watch'],'FY18Q3':'Chains'}

crayxt · Accepted Answer · 2021-06-09 07:22:43Z

2

One liner

>>> df2 = df.groupby(by=["Category", "Period"]).agg(lambda x: list(set(x))).reset_index().groupby("Category").apply(lambda x: dict(zip(x["Period"], x["Sub_Category"])))
>>> df2
Category
Accessories    {'FY18Q1': ['Watch'], 'FY18Q2': ['Watch', 'Muf...
Clothing       {'FY18Q1': ['Shirt'], 'FY18Q2': ['Trouser'], '...
dtype: object
>>> df2.values
array([{'FY18Q1': ['Watch'], 'FY18Q2': ['Watch', 'Muff'], 'FY18Q3': ['Chains']},
       {'FY18Q1': ['Shirt'], 'FY18Q2': ['Trouser'], 'FY18Q3': ['Pant']}],
      dtype=object)

answered Jun 9, 2021 at 7:22

crayxt

2,4052 gold badges15 silver badges19 bronze badges

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

2 Comments

smci Over a year ago

You want groupby(..., sort=False) to keep the original order 'Clothing','Accessories'.

crayxt Over a year ago

Thanks. Also the structure of values in dictionary column requested by OP was not consistent (either string or list).

Arne · Accepted Answer · 2021-06-09 07:12:15Z

2

Write a function that constructs the dictionary and apply it to your dataframe, grouped by category:

def make_dict(df):
    d = {}
    for period in sorted(set(df.Period)):
        d[period] = list(set(df.Sub_Category[df.Period == period]))
    return d

df_output = df.groupby('Category').apply(make_dict)

answered Jun 9, 2021 at 7:12

Arne

10.6k2 gold badges22 silver badges31 bronze badges

Comments

smci · Accepted Answer · 2021-06-09 09:17:17Z

1

I can nearly get this with:

pd.DataFrame.from_records( [(a,dict(zip(g['Period'],g['Sub_Category'])) ) for (a,g) in df.groupby('Category', group_keys=False)], columns=['Category','Period_Subcategory'] ).set_index('Category')

                                                     Period_Subcategory
Category                                                               
Accessories  {'FY18Q1': 'Watch', 'FY18Q2': 'Watch', 'FY18Q3': 'Chains'}
Clothing     {'FY18Q1': 'Shirt', 'FY18Q2': 'Trouser', 'FY18Q3': 'Pant'}

...except that pd.DataFrame.from_records() doesn't create a list, so it mishandles the duplicate for 'Accessories','FY18Q2'.

edited Jun 9, 2021 at 9:17

answered Jun 9, 2021 at 9:10

smci

34.2k21 gold badges118 silver badges152 bronze badges

Collectives™ on Stack Overflow

How to Create new columns using other column Values in Python?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related