How to transform a CSV column in dataframe to rows

Question

I have a dataframe with CSVs in language column

     Name Language
0       A French,Espanol
1       B Deutsch,English

I wish to transform the above dataframe as below

    Name Language
0      A French
1      A Espanol
2      B Deutsch
3      B English

I tried the below code but couldn't accomplish

df=df.join(df.pop('Language').str.extractall(',$')[0] .reset_index(level=1,drop=True) .rename('Language')) .reset_index(drop=True)

Please post text/code, not images. And check out how-to-ask and How to create a Minimal, Reproducible Example. — FObersteiner
– FObersteiner, Commented Nov 22, 2019 at 10:15
always add code, data and error message in question, not in comment. It will be more readable and more people will see it. — furas
– furas, Commented Nov 22, 2019 at 10:34

FObersteiner · Accepted Answer · 2019-11-22 10:39:35Z

1

pandas.DataFrame.explode should be suited for that task. Combine it with pandas.DataFrame.assign to get the desired column:

import pandas as pd

df = pd.DataFrame({'Name':['A', 'B'], 'Language': ['French,Espanol', 'Deutsch,English']})

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

#   Name Language
# 0    A   French
# 0    A  Espanol
# 1    B  Deutsch
# 1    B  English

answered Nov 22, 2019 at 10:39

FObersteiner

26.1k9 gold badges60 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darak Dir Over a year ago

Awesome Thanks a ton :)

gjiki · Accepted Answer · 2019-11-22 10:39:12Z

1

First create a new dataframe with the same columns, then split second values and appent rows to the dataframe.

import pandas as pd

csv_df  = pd.DataFrame([['1', '2,3'], ['2', '4,5']], columns=['Name', 'Language'])
df = pd.DataFrame(columns=['Name ', 'Language'])

for index, row in csv_df .iterrows():
    name = row['Name']
    s = row['Language']
    txt = s.split(',')
    for x in txt:
        df = df.append(pd.Series([name, x], index=df.columns), ignore_index=True)

print(df)

answered Nov 22, 2019 at 10:39

gjiki

1791 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to transform a CSV column in dataframe to rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related