CODEWITHSUNDEEP
pythonpandasdataframenumpy
me.limes
me.limes

Reputation: 471

Shift values relative to groupby

I want to remove NaN values in my pandas dataframe, and shift values up relative to a groupby on Category and Gender. Here's a sample that I created which sort of imitates the data I'm working with:

import pandas as pd
test = {'Price':
        [20, 10, 'NaN', 'NaN',  'NaN', 'NaN',21, 11,'NaN', 'NaN', 'NaN','NaN'], 
        'Gender':
        ['womens-clothing','womens-clothing','womens-clothing','womens-clothing','womens-clothing','womens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing'],
        'Category':['dresses','dresses','dresses', 'dresses',  'dresses', 'dresses', 'jackets','jackets', 'jackets', 'jackets', 'jackets', 'jackets'],
        'Title':['NaN', 'NaN', 'Cheap Dress', 'First Dress', 'NaN', 'NaN','NaN', 'NaN','Main Jacket', 'Black Jacket','NaN', 'NaN'],
        'Review':['NaN','NaN','NaN','NaN',203,12,'NaN','NaN','NaN','NaN',201, 15]}

df = pd.DataFrame(test)

This is what it looks like:

    Price   Gender     Category Title         Review
0   20  womens-clothing dresses NaN             NaN
1   10  womens-clothing dresses NaN             NaN
2   NaN womens-clothing dresses Cheap Dress     NaN
3   NaN womens-clothing dresses First Dress     NaN
4   NaN womens-clothing dresses NaN             203
5   NaN womens-clothing dresses NaN             12
6   21  mens-clothing   jackets NaN             NaN
7   11  mens-clothing   jackets NaN             NaN
8   NaN mens-clothing   jackets Main Jacket     NaN
9   NaN mens-clothing   jackets Black Jacket    NaN
10  NaN mens-clothing   jackets NaN             201
11  NaN mens-clothing   jackets NaN             15

I want to discard rows with NaN values remaining and values from Gender and Category, then shift the cells one up so it matches like this following:

    Price   Gender     Category Title         Review
0   20  womens-clothing dresses Cheap Dress     203
2   10  womens-clothing dresses First Dress     12
3   21  mens-clothing   jackets Main Jacket     201
4   11  mens-clothing   jackets Black Jacket    15

I have tried:

data = df.apply(lambda x: pd.Series(x.drop(index=x[x[0] == 'NaN'], inplace=True).values))

However I cannot seem to drop specific rows this way. Because these NaN are strings (They're actual NA's for me, I just don't know how to produce them in a dict I can create for reproducible code.)

How can I get the expected output - given the NaNs are actual Nas. I have tried including a groupby in the function above, however I can use this on a numpy array. I can include outside the function, but it doesn't help.

Upvotes: 1

Views: 97

Answers (1)

jezrael
jezrael

Reputation: 862641

In ideal data sample use:

f = lambda x: x.apply(lambda x: x[x!='NaN'])
df = df.set_index(['Gender','Category']).groupby(['Gender','Category'], group_keys=False).apply(f).reset_index()
print (df)
            Gender Category Price         Title Review
0    mens-clothing  jackets    21   Main Jacket    201
1    mens-clothing  jackets    11  Black Jacket     15
2  womens-clothing  dresses    20   Cheap Dress    203
3  womens-clothing  dresses    10   First Dress     12

If general data, it means possible not same number of non NaNs values use:

test = {'Price':
        [20, 10, 'NaN', 'NaN',  'NaN', 'NaN',21, 11,45, 'NaN', 'NaN','NaN'], 
        'Gender':
        ['womens-clothing','womens-clothing','womens-clothing','womens-clothing','womens-clothing','womens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing','mens-clothing'],
        'Category':['dresses','dresses','dresses', 'dresses',  'dresses', 'dresses', 'jackets','jackets', 'jackets', 'jackets', 'jackets', 'jackets'],
        'Title':['NaN', 'NaN', 'Cheap Dress', 'First Dress', 'NaN', 'NaN','NaN', 'NaN','Main Jacket', 'Black Jacket','NaN', 'NaN'],
        'Review':['NaN','NaN','NaN','NaN',203,12,'NaN','NaN','NaN','NaN',201, 15]}

df = pd.DataFrame(test)

f = lambda x: x.apply(lambda x: pd.Series(x[x!='NaN'].to_numpy()))
#if NaNs are missing values
#f = lambda x: x.apply(lambda x: pd.Series(x.dropna().to_numpy()))
df = (df.set_index(['Gender','Category'])
        .groupby(['Gender','Category'])
        .apply(f)
        .droplevel(-1)
        .reset_index())
print (df)
            Gender Category Price         Title Review
0    mens-clothing  jackets    21   Main Jacket    201
1    mens-clothing  jackets    11  Black Jacket     15
2    mens-clothing  jackets    45           NaN    NaN
3  womens-clothing  dresses    20   Cheap Dress    203
4  womens-clothing  dresses    10   First Dress     12

Upvotes: 2

Related Questions