Dynamically explode columns in Pandas row

Question

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!

@Prateek: Series.explode is new in 0.25, it splits "list-likes" (e.g. lists of objects, string lists) into multiple rows. — smci
– smci, Commented Sep 2, 2019 at 21:00

BENY · Accepted Answer · 2018-04-02 21:46:36Z

2

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()

answered Apr 2, 2018 at 21:46

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phil Baines Over a year ago

Thank you, this is exactly what I am looking for

BENY Over a year ago

@PhilBaines yw : -) , one tips suffix='\\w+' this is most confusion part , I used to have problem with it :-)

BENY Over a year ago

@PhilBaines if this what you need would you mind accept it ?

Collectives™ on Stack Overflow

Dynamically explode columns in Pandas row

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related