Organize columns in dataframe based on condition

user_13712068 0
Viewed 80

I have a dataframe of products that looks like this

category,number of products
Apple pc,3
Lenovo pc,7
HP pc,4
Apple chargher,6
Lenovo charger,9

I want to group categories if they contain the same string (for example pc or charger) and send them to another dataframe like this

category,number of products
pc,14
charger,15

Can i do this using pandas?

pandas dataframe python
4 Answers

Try this

 df['Category'] = df["Category"].apply(lambda x: x.split(" ")[1])
 df1 = df.groupby("Category").sum()

Output

 Category   num_of_product
 charger    15
 pc         14
user_119141420 
import pandas as pd

data = {'Name':['Apple pc','Lenovo pc','HP pc','Apple charger','Lenovo charger'],
        'Unit':[3,7,4,6,9]}

df = pd.DataFrame(data)

print(df)

enter image description here

New_df=pd.DataFrame(df['Name'].str.split(' ',1).tolist(),columns=['Company','type'])

New_df['Units']=data['Unit']

print(New_df)

enter image description here

x = New_df[New_df['type']=='pc']['Units'].sum()

y = New_df[New_df['type']=='charger']['Units'].sum()

dfx = pd.DataFrame({'category':['pc','charger'],'number of products':[x,y]}) #creating a new dataframe

print(dfx)

enter image description here

user_133962180

You can do this in a one liner code

    In [174]: df
    Out[174]:
             category  number of products
    0        Apple pc                   3
    1       Lenovo pc                   7
    2           HP pc                   4
    3  Apple chargher                   6
    4  Lenovo charger                   9
    
    In [175]: df.groupby([df["category"].str.split().str[-1]])["number of products"].sum()
    Out[175]:
    category
    charger      9
    chargher     6
    pc          14
    Name: number of products, dtype: int64
   
    In [177]: pd.DataFrame(df.groupby([df["category"].str.split().str[-1]])["number of products"].sum()).reset_index()
    Out[177]:
   category  number of products
0   charger                   9
1  chargher                   6
2        pc                  14
user_68496820

you can try with:

import pandas as pd

data={'category':['Apple pc','Lenovo pc','HP pc','Apple charger','Lenovo charger'],
      'number of products':[3,7,4,6,9]}

df = pd.DataFrame(data)
new = df["category"].str.split(" ", n = 1, expand = True)
df['brand']=new[0]
df['kind']=new[1]
print(df)

df:

         category  number of products   brand      kind
0        Apple pc                   3   Apple        pc
1       Lenovo pc                   7  Lenovo        pc
2           HP pc                   4      HP        pc
3  Apple chargher                   6   Apple  chargher
4  Lenovo charger                   9  Lenovo   charger

And then do a groupby:

print(df.groupby('kind')['number of products'].sum().sort_values())

Result:

kind
pc         14
charger    15
Name: number of products, dtype: int64
user_127442750
Related