Reformatting DataFrame in Python

Question

Below is the data frame I have:

ID  Char  Location
1   a      IN
2   b,c,d  US
3   e,g    IN
4   ,,,    CA

Below is the data frame I desire:

ID   Char  Location
1    a      IN
2    b      US
2    c      US
2    d      US
3    e      IN
3    g      IN
4           CA

How can I transform this data frame?

Does this answer your question? [Split (explode) pandas dataframe string entry to separate rows](https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows) — ranka47, Sep 09 '20 at 17:41

Reza · Accepted Answer · 2020-09-09T18:03:27.373

1

Try this:

df = pd.DataFrame({'ID': [1,2,3,4], 'Char': ['a', 'b,c,d', 'e,g', ',,,'], 'Location': ['IN', 'US', 'IN', 'CA']})

# copy the original order of columns
org_cols = df.columns.copy()

# explode Char column
df = df.drop(columns='Char').join(df['Char'].str.split(',').apply(lambda x: list(set(x))).explode())

# reorder columns
df = df[org_cols]
df

   ID Char Location
0   1    a       IN
1   2    c       US
1   2    b       US
1   2    d       US
2   3    e       IN
2   3    g       IN
3   4            CA

edited Sep 09 '20 at 18:03

answered Sep 09 '20 at 17:40

Reza

1,945
1
9
17

I cannot change the position of the column and besides this is only a sample the actual dataframe is close to a 1 mn row dataframe and there are close to 120 columns – Krishnamurthy Narayanaswamy Sep 09 '20 at 17:47
I just fixed it, but the `'Char'` is going to go to the end. it is not what you want? – Reza Sep 09 '20 at 17:51
no i cannot have the `char` at the end since there are many columns post the char column also – Krishnamurthy Narayanaswamy Sep 09 '20 at 17:54

Reformatting DataFrame in Python

1 Answers1