Explode command not working while calling excel or csv file

Question

i have been working with this explode command. please here is my code fyr.

import pandas as pd
import os
os.getcwd()
os.chdir('C:/Users/nick/Documents/Python') # NOTE csv_filepath
df= pd.read_excel('output1.xlsx', usecols=['PartNumber', 'RefList'], index_col=False)
print(df.explode('RefList'))
file_name ='output2.xlsx'
df.to_excel(file_name)

Input file data // output1.xlsx

PartNumber	RefList
ram	apple,orange
ravi	banan,pinapple

receiving output data // output2.xlsx "same" i don't know where iam making a mistake.

PartNumber	RefList
ram	apple,orange
ravi	banan,pinapple

if I try to work with this below code it works

  import pandas as pd
  import numpy as np
  df = pd.DataFrame({'animals': [['koala', 'kangaroo', 'echidna'], 
                           ['sloth', 'alpaca'], 
                           ['zebra', 'lion', 'baboon']],
               'diet': [['herbivorous', 'herbivorous', 'carnivorous'], 
                        ['omnivorous', 'herbivorous'], 
                        ['herbivorous', 'carnivorous', 'omnivorous']],
               'country': ['Australia', 'Peru', 'Kenya']})
                print`(df)`

I have work with explode command not working. try to compile it with .data frame it works fine. if i call a excel or csv file not working.

Answer 1

Example

import pandas as pd
data = {'PartNumber': ['ram', 'ravi'], 'RefList': ['apple,orange', 'banan,pinapple']}
df = pd.DataFrame(data)

df:

    PartNumber  RefList
0   ram         apple,orange
1   ravi        banan,pinapple

Code

df.assign(RefList=df['RefList'].str.split(',')).explode('RefList')

output:

PartNumber  RefList
0   ram     apple
0   ram     orange
1   ravi    banan
1   ravi    pinapple

then use following code

file_name ='output2.xlsx'
df.assign(RefList=df['RefList'].str.split(',')).explode('RefList').to_excel(file_name)

Answer 2

import pandas as pd
import os
os.getcwd()
os.chdir('C:/Users/nick/Documents/Python') # NOTE csv_filepath
df1= pd.read_excel('output1.xlsx', usecols=['PartNumber', 'RefList'])


df2 = df1['RefList'].str.split(' ',expand=True)

df3 = pd.concat([df1, df2], axis=1)

df2 = df3.melt(id_vars=['PartNumber', 'RefList'])#, var_name='df.columns.values')
 
df2.explode ('RefList')

file_name='output2.xlsx'

df2.to_excel(file_name)

output table #

PartNumber	RefList	variable	value
ram	apple orange	0	apple
ravi	banan pinapple	0	banan
ram	apple orange	1	orange
ravi	banan pinapple	1	pinapple

Answer 3

df['RefList'] = df['RefList'].str.strip('[]').str.split(',')

print(df.explode('RefList'))

output #

   | PartNumber |  RefList |
   |------------|----------|
  |  ram   |  apple  |
  |  ram   | orange  |
  | ravi   |  banan  |
  | ravi | pinapple  |