Duplicate rows by list len in dataframe and add them as index

Question

I have a dataframe where one of the columns has 2 or more elements inside a list format, like the following:

                     Email                           Country
0              [email protected]                    [Czech Republic, Singapore, United Kingdom]
1             [email protected]                 [Singapore, United Kingdom]   
2              [email protected]                  [United Kingdom, Czech Republic]

I need to do the following: - Duplicate the number of rows by list lenght in "Country" (so for example, first row would be duplicated twice) - For each row, I would need to have as index one of the list elements (so for example, one of them would be Czech Republic, the other row Singapore and the other row United Kingdow as index).

Does someone know how could I do it?

Thank you!

René · Accepted Answer · 2020-05-14 08:29:03Z

1

You can use .explode() to 'duplicate' the rows:

import pandas as pd

df = pd.DataFrame([['[email protected]', ['Czech Republic', 'Singapore', 'United Kingdom']],
                   ['[email protected]', ['Singapore', 'United Kingdom']],
                   ['[email protected]', ['United Kingdom', 'Czech Republic']]
                  ], columns = ['Email', 'Country'])
df.explode('Country')

Result:

                Email         Country
0      [email protected]  Czech Republic
0      [email protected]       Singapore
0      [email protected]  United Kingdom
1  [email protected]       Singapore
1  [email protected]  United Kingdom
2    [email protected]  United Kingdom
2    [email protected]  Czech Republic

To set the index use:

df.explode('Country').set_index('Country')

answered May 14, 2020 at 8:29

René

4,9195 gold badges29 silver badges59 bronze badges

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

3 Comments

Paulo Cortez Over a year ago

Hello Rene, thanks for the quick reply! I am getting the error that dataframe has no attribute "explode"

René Over a year ago

What version of pandas do you use?

Paulo Cortez Over a year ago

Yeah, just checked I need to update Pandas version, thanks!

Collectives™ on Stack Overflow

Duplicate rows by list len in dataframe and add them as index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related