Search code examples
pythonpandasdataframealternate

Alternate elements of different columns using Pandas


I have 2 dataframes columns like this:

df1 = pd.DataFrame({'A':['CINO','KONO','ROLO','MANE','TUTU']})
df2 = pd.DataFrame({'B':['NION','PONO','RZCO','MPPE','TQAS']})

My idea is to combine the dataframes in such a way that the output looks like this:

        C
CC1  CINO   <---- belongs to A
CC1  NION  <---- belongs to B
CC2  KONO   <---- belongs to A    
CC2  PONO  <---- belongs to B
CC3  ROLO   <---- belongs to A
CC3  RZCO  <---- belongs to B
CC4  MANE   <---- belongs to A
CC4  MPPE  <---- belongs to B
CC5  TUTU   <---- belongs to A
CC5  TQAS  <---- belongs to B

As you can see the items of column B are placed between the rows of column A. Notice how the rows are named in such a way that every pair of rows has the same name.

Can you suggest me a smart way to achieve this goal using some built in Pandas function?


Solution

  • pd.concat(
        [df1, df2], axis=1
    ).stack().reset_index(1, drop=True).to_frame('C').rename(index='CC{}'.format)
    
            C
    CC0  CINO
    CC0  NION
    CC1  KONO
    CC1  PONO
    CC2  ROLO
    CC2  RZCO
    CC3  MANE
    CC3  MPPE
    CC4  TUTU
    CC4  TQAS