Merging rows and adding columns based on matching index

[pythonnewbie78]

Hello community, tried this a million different ways with pandas groupby but no luck, any help is appreciated. First year learning python and still trying to figure things out. Thanks!

ID name code
ABC hans apple
ABC hans pear
ABC hans kale
DEF holly tomato
HIJ jon grapes

ID name code 1 code 2 code 3
ABC hans apple pear kale
DEF holly tomato
HIJ jon grapes

[deanhystad]

A million different ways? Or maybe 5 different ways. Could you post one of those ways an example? It would provide useful information about the structure of your data and dataframes. It would also provide a lot of information about why you are having difficulties.

[Pedroski55]

With this data:

Output:
    ID   name    code
0  ABC   hans   apple
1  ABC   hans    pear
2  ABC   hans    kale
3  DEF  holly  tomato
4  HIJ    jon  grapes

Could try like this:

df.groupby(['name'])['name'].count()

Output:
name
hans     3
holly    1
jon      1
Name: name, dtype: int64

Or like this:

print(df.groupby('name').groups)

Output:
{'hans': [0, 1, 2], 'holly': [3], 'jon': [4]}

Depends on what you need!

[Pedroski55]

Stuck half way up this mountain, I have time. Playing with your data, I got this:

path2csv2 = '/home/pedro/myPython/pandas/csv_files/info2.csv'
df2 = pd.read_csv(path2csv2)
path2csv3 = '/home/pedro/myPython/pandas/csv_files/info3.csv'
df3 = pd.read_csv(path2csv3)
df2.update(df3)
df4 = pd.merge(df2, df3, left_on=['ID','name'], right_on = ['ID','name'])
df5 = pd.merge(df2, df3, how='left', on=['ID', 'name'])
df6 = pd.merge(df2, df3, how='outer', on=['ID', 'name'])

Output:
    ID   name    code  code 1 code 2 code 3
0  ABC   hans   apple   apple   pear   kale
1  ABC   hans    pear   apple   pear   kale
2  ABC   hans    kale   apple   pear   kale
3  DEF  holly  tomato  tomato    NaN    NaN
4  HIJ    jon  grapes  grapes    NaN    NaN

There are so many settings for pandas, you need to know exactly what you want. how='outer' seems to get a union of sets, whereas how='inner' seems to get an intersection of sets. But you can use many different methods to get what you want.

frames = [df2, df3]
result = pd.concat(frames)

Output:
result
    ID   name    code  code 1 code 2 code 3
0  ABC   hans   apple     NaN    NaN    NaN
1  ABC   hans    pear     NaN    NaN    NaN
2  ABC   hans    kale     NaN    NaN    NaN
3  DEF  holly  tomato     NaN    NaN    NaN
4  HIJ    jon  grapes     NaN    NaN    NaN
0  ABC   hans     NaN   apple   pear   kale
1  DEF  holly     NaN  tomato    NaN    NaN
2  HIJ    jon     NaN  grapes    NaN    NaN