How to implode (reverse of explode) only non-null values in pandas. Merge multiple rows into single row using pandas group by

user_12328021 0
Viewed 998

I am working on Python Pandas.

I have a pandas dataframe with columns like this:

ID Cities
1 New York
1 ''
1 Atlanta
2 Tokyo
2 Kyoto
2 ''
3 Paris
3 Bordeaux
3 ''
4 Mumbai
4 ''
4 Bangalore
5 London
5 ''
5 Bermingham

Note the empty cells in the column are either empty string ('') or Nan or None. (For simplicity lets just say they are empty strings ('')).

And I want the result to be like this:

ID Cities
1 New York, Atlanta
2 Tokyo, Kyoto
3 Paris, Bordeaux
4 Mumbai, Bangalore
5 London, Bermingham

In short, I want to group by ID and then getting the list (by removing the empty strings).

I have a sample code for this but it actually gives me result with empty strings, I want to remove empty strings.

dataFrame.groupby(['ID'], as_index=False)
                  .agg({'Cities': lambda x: x.tolist()})

It gives me result like this:

ID Cities
1 New York, ,Atlanta
2 Tokyo, Kyoto,
3 Paris, Bordeaux,
4 Mumbai, , Bangalore
5 London, , Bermingham

But I dont want empty strings...

Please help me here.

Thank you so much for you help.

pandas-groupby pandas aggregate-functions dataframe python
2 Answers

You can try replacing empty string by NaN and then add .dropna() to the aggregate lambda function, as follows:

df['Cities'] = df['Cities'].replace('', np.nan)

(df.groupby('ID', as_index=False)
                  .agg({'Cities': lambda x: x.dropna().tolist()})
)

Result:

   ID                Cities
0   1   [New York, Atlanta]
1   2        [Tokyo, Kyoto]
2   3     [Paris, Bordeaux]
3   4   [Mumbai, Bangalore]
4   5  [London, Bermingham]
user_150706970

We can also perform the operations at the Series level, by mask out the unneeded values like empty string (''), dropna to remove the missing/empty values, then groupby aggregate into whatever type needed, like a list:

new_df = (
    df['Cities']
        .mask(df['Cities'].eq(""))  # Replace Empty String with NaN
        .dropna()  # Exclude NaN
        .groupby(df['ID'])  # Groupby ID
        .aggregate(list)  # Join Into List
        .reset_index()  # Convert Back to DataFrame
)

Or filter out unneeded rows by condition:

new_df = (
    # Filter out by condition
    df.loc[df['Cities'].ne("") & df['Cities'].notnull(), 'Cities']
        .groupby(df['ID'])  # Groupby ID
        .aggregate(list)  # Join Into List
        .reset_index()  # Convert Back to DataFrame
)

new_df:

   ID                Cities
0   1   [New York, Atlanta]
1   2        [Tokyo, Kyoto]
2   3     [Paris, Bordeaux]
3   4   [Mumbai, Bangalore]
4   5  [London, Bermingham]

Setup:

import pandas as pd

df = pd.DataFrame({
    'ID': [1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5],
    'Cities': ['New York', "", 'Atlanta', 'Tokyo', 'Kyoto', "", 'Paris',
               'Bordeaux', "", 'Mumbai', "", 'Bangalore', 'London', "",
               'Bermingham']
})
user_154978880
Related