Move the last word from one column to next row in Pandas Dataframe-CodePudding

I have a DataFrame with values like the following

| Fruits         | Price | Year |
| Apple Orange   | 50    | 2015 |
| Grape          | 22    | 2018 |
| Orange Mango   | 25    | 2019 |
| Apple Melon    | 30    | 2015 |
| Apple          | 32    | 2020 |

I want to move the last word of the values with more than one word from column "Fruits" to the next row while keeping the values from the "Price" and "Year". I expect the new DataFrame to be like

| Fruits         | Price | Year |
| Apple Orange   | 50    | 2015 |
| Orange         | 50    | 2015 |
| Grape          | 22    | 2018 |
| Orange Mango   | 25    | 2019 |
| Mango          | 25    | 2019 |
| Apple Melon    | 30    | 2015 |
| Melon          | 30    | 2015 |
| Apple          | 32    | 2020 |

CodePudding user response：

Split the words on the Fruits column then keep only the rows where there are at least 2 items and finally join this filtered dataframe to the original one:

df1 = (df['Fruits'].str.split().loc[lambda x: x.str.len() > 1].str[-1]
                   .to_frame().join(df.drop(columns='Fruits')))
out = pd.concat([df, df1], axis=0).sort_index(ignore_index=True)
print(out)

# Output
         Fruits  Price  Year
0  Apple Orange     50  2015
1        Orange     50  2015
2         Grape     22  2018
3  Orange Mango     25  2019
4         Mango     25  2019
5   Apple Melon     30  2015
6         Melon     30  2015
7         Apple     32  2020

CodePudding user response：

Basing on finding the last separator in values with multiple words (if occurs) to gather each entry in 2-cell sequence, then just transforming from lists/tuples to rows with DataFrame.explode:

df['Fruits'].apply(lambda x: (x, x[x.rfind(' ') 1:]) if ' ' in x else (x, None))
df = df.explode('Fruits').dropna()

         Fruits  Price  Year
0  Apple Orange     50  2015
0        Orange     50  2015
1         Grape     22  2018
2  Orange Mango     25  2019
2         Mango     25  2019
3   Apple Melon     30  2015
3         Melon     30  2015
4         Apple     32  2020