pythonpandasdataframetranspose

Transpose a column in pandas DataFrame

I have a below dataframe, I'm trying to transpose the data based on the column Place. For each list of value in Place column, I need to generate a each row.

Language   Capital   Place
Tamil      Chennai   ['Chennai', 'Vellore', 'Trichy', 'Madurai']
Kerala     Kochi     ['Kochi', 'Trivandrum']

Expected result

Language     Capital     Place
Tamil        Chennai     Chennai
Tamil        Chennai     Vellore
Tamil        Chennai     Trichy
Tamil        Chennai     Madurai 
Kerala       Kochi       Kochi
Kerala       Kochi       Trivandrum

I have tried many ways, using Pandas transpose, but I am unable to get the expected result. I have also retrieved and converted Place column to series of dataframe, still unable to get the result.

Solution

  • Use ast.literal_eval to convert string representations to actual lists:

    import ast
df['Place'] = df['Place'].apply(ast.literal_eval)

    Transform each list element in 'Place' into a separate row replicate the other column values and reset the index of the DataFrame for cleaning:

    df_exploded = df.explode('Place').reset_index(drop=True)
print(df_exploded)