Issue while converting column value to row value in pandas python?

user_8888469 0
Viewed 70

I have data frame like

County  section
 A       S1,S2
 C       ALL
 B       S1

Expected Output

County    section
A          S1
A          S2
C          S1
C          S2
B          S2

My code

df =df.assign(sections=df.sections.replace({'ALL':df.loc[df.sections.str.split(',').str.len().idxmax(),'sections']}).str.split(',')).explode('sections')

But the above code only works when we have comma separated multiple sections (S1,S2,S3) . But doesn't work when we have section value without comma separated as shown below. How can make the code to work for both scenarios together 

County  Section
A       ALL
B       S1
C       ALL
D       ALL

Expected Output

  County  Section
    A       S1
    B       S1
    C       S1
    D       S1

Code should work in both scenarios

pandas dataframe python-3.x python
2 Answers

Let us change the replace part 

s=df[df.section.ne('ALL')]
toreplace=s.loc[s.section.str.split(',').str.len().idxmax(),'section']
df.assign(section=df.section.replace({'ALL':toreplace}).str.split(',')).explode('section')
  County section
0      A      S1
1      B      S1
2      C      S1
3      D      S1
user_79645270

A little late for the party:

all_sec = df.section.eq('ALL')
non_sec_cols = [col for col in df.columns if col != 'section']

df1 = (df.drop('section', axis=1)
         .join(df.loc[~all_sec, 'section'].str.get_dummies(','))
         .fillna(1)
         .melt(non_sec_cols, var_name='section')
         .query('value==1')
         .drop('value', axis=1)
      )

Output:

  County section
0      A      S1
1      C      S1
2      B      S1
3      A      S2
4      C      S2
user_42384080
Related