CODEWITHSUNDEEP
pythonpython-3.xpython-2.7pandasdataframe
Phil Baines
Phil Baines

Reputation: 467

Dynamically explode columns in Pandas row

I have a DataFrame with multiple columns per row which need to be split into a new rows per column

The DataFrame currently (shortened down) looks like this

|---------------------------------------------------------------------------------------------------------------------------------|
|institution_short_name |interest_paid1 |interest_paid2 |product_detail_value_min |term_01m_value |term_02y_value |term_03m_value |
|---------------------------------------------------------------------------------------------------------------------------------|
|One                    |Z              |Q              |2000                     |0.50           |0.75           |0.75           |
|One                    |Z              |Q              |5000                     |0.50           |3.65           |3.75           |
|One                    |M              |M              |20000                    |Nan            |3.65           |Nan            | 
|---------------------------------------------------------------------------------------------------------------------------------|

For each term column, e.g i would like to extract the 01m, 02y, 03m from the name of the column as well as the value for that row under that column and explode it like the following:

|----------------------------------------------------------------------------------------------------------|
|institution_short_name |interest_paid1 |interest_paid2 |product_detail_value_min |Term            |Value  |
|----------------------------------------------------------------------------------------------------------|
|One                    |Z              |Q              |2000                     |01m             |0.5    |
|One                    |Z              |Q              |2000                     |02y             |3.75   |
|One                    |Z              |Q              |2000                     |03m             |0.75   |
|One                    |Z              |Q              |5000                     |01m             |0.5    |
|One                    |Z              |Q              |5000                     |02y             |3.65   |
|One                    |Z              |Q              |5000                     |03m             |3.75   |
|One                    |M              |M              |20000                    |02y             |3.65   |
|----------------------------------------------------------------------------------------------------------|

I am not asking for a complete solution, just something to get me started. Thanks!

Upvotes: 1

Views: 189

Answers (1)

BENY
BENY

Reputation: 323236

You can check wide_to_long

pd.wide_to_long(df.repalce('Nan',np.nan),['term'],i=['institution_short_name','interest_paid1','interest_paid2','product_detail_value_min'],j='Term',suffix='\\w+').reset_index().dropna()

Upvotes: 2

Related Questions