Splitting list into rows

Question

I have dataframe with two columns. One column is ‘Exchange’ and the other is ‘StartToEnd’. I had the issue that each row in the exchange column would contain more than one exchange name. So like:

London Stock Exchange Berlin Stock Exchange

However python would read that as 1 string so I used str.split and it made each row into a list so now each row looks like:

['London Stock Exchange', 'Berlin Stock Exchange']

How could I split that one row into two individual rows but also duplicate the StartToEnd value for both new rows. So it would look like:

Exchange.               StartToEnd
London Stock Exchange.    15.4
Berlin Stock Exchange.    15.4

Answer 1

You can use pd.melt for this:

df = pd.DataFrame({ "Exchange" : [['London Stock Exchange', 'Berlin Stock Exchange']], 'StartToEnd': [15.4]  })

df[["London","Berlin"]] = pd.DataFrame(df.Exchange.tolist())
df = df.drop('Exchange', axis=1)

df.melt(id_vars = ["StartToEnd"], value_name = 'Exchange').drop('variable',axis=1)

yields:

    StartToEnd  Exchange
0   15.4    London Stock Exchange
1   15.4    Berlin Stock Exchange