Pandas combine strings with groupby

Question

I am trying to combine strings in my data frame. The dataframe looks like:

0          code   text1
1        507489   text2
2        507489   text3
3        506141   text4
4        506141   text5
5        504273   text6

My current code:

import pandas as pd

df = pd.read_csv("location.csv", header=None, delimiter=';', dtype='unicode', nrows=100)
new_header = df.iloc[0] 
df = df[1:] 
df.columns = new_header

df.groupby('code').agg('->'.join).reset_index()

df.to_csv (r'new_location\export_dataframe.csv', index = False, header=True)
print(df)

But I am not getting the expected results. The output looks the same as the input while I was expecting:

0          code   text1
1        507489   text2->text3
2        506141   text4->text5
3        504273   text6

Quite new to this so I must be making some easy mistake.

Dataframe that produces same result:

testf = {'code': ['1','2','2','4'],
        'text': [22000,25000,27000,35000]
        }

df = pd.DataFrame(testf, columns = ['code', 'text'])

hi , please add DF that will can run , don't forget we don't have your csv, and that will make time to implement you DF you have to follow this step before stackoverflow.com/help/minimal-reproducible-example — Beny Gj
– Beny Gj, Commented Apr 30, 2020 at 8:08

jezrael · Accepted Answer · 2020-04-30 08:25:48Z

2

It seems you forget assign back, also was removed header=None in read_csv because in file is header used for columns names in DataFrame:

import pandas as pd

df = pd.read_csv("location.csv", sep=';', dtype='unicode', nrows=100)

df = df.groupby('code').agg('->'.join).reset_index()
print (df)
     code         text1
0  504273         text6
1  506141  text4->text5
2  507489  text2->text3

df.to_csv (r'new_location\export_dataframe.csv', index = False)

edited Apr 30, 2020 at 8:25

answered Apr 30, 2020 at 8:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

Elmer Over a year ago

Tried this but still producing the same result.

jezrael Over a year ago

@Elmer - Solution was simplify, can you test?

Elmer Over a year ago

Removed header=None but still doesn't work. How do do you mean forgot assign back? The .reset.index()?

jezrael Over a year ago

@Elmer - I think df.groupby('code').agg('->'.join).reset_index() vs df = df.groupby('code').agg('->'.join).reset_index()

Elmer Over a year ago

Solved. It was indeed df.groupby('code').agg('->'.join).reset_index() vs df = df.groupby('code').agg('->'.join).reset_index()

Collectives™ on Stack Overflow

Pandas combine strings with groupby

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related