Concatenate strings of a groupedBy pandas dataframe

Question

From ang SQL query, I got a DataFrame similar to this one:

df = pd.DataFrame([
        ['ABC', 'Order'],
        ['ABC', 'Address'],
        ['ABC', 'Zip'],
        ['XYZ', 'Customer'],
        ['XYZ', 'Name']
    ],
    columns=("Table", "Column"))

  Table    Column
0   ABC     Order
1   ABC   Address
2   ABC       Zip
3   XYZ  Customer
4   XYZ      Name

I am trying to save info in a separate file, like:

Table ABC has columns: Order, Address, Zip

One line for each table (and only once).

How I can achieve this?

I already tried:

for table_name in df.TABLE_NAME:
  output = "Table" + Table_name + "are" + (df.iloc[:,2])

But I am not getting any desired output.

When posting Pandas questions, it's much easier for people to help you if you post the data as an easily [reproducible example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) rather than a picture. Also, have you tried anything so far? If so can you please post [your code](https://stackoverflow.com/help/minimal-reproducible-example)? — David Buck, Nov 25 '19 at 10:43
Did you try any use of [`groupby`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html). `df.groupby(['Table'])['Column'].transform(lambda x: ','.join(x))` — AlexisBRENON, Nov 25 '19 at 10:46
print(df.shape) for Table_NAME in df.TABLE_NAME: output = "List of columns for" +Table_name "are" +(df.iloc[:,2]) but am not getting any desired output — Geetha Anand, Nov 25 '19 at 10:47
@AlexisBRENON, my main focus is about only how to generate the output /print my custom message with those values in Python.... — Geetha Anand, Nov 25 '19 at 10:50
Instead of adding comments, edit your question to add the code to generate an example dataframe, and what you already tried. — AlexisBRENON, Nov 25 '19 at 10:50

score 1 · Accepted Answer · answered Nov 25 '19 at 11:02

Making some string manipulation while grouping by your Table name can give you what you expect.

import pandas as pd

if __name__ == '__main__':
    df = pd.DataFrame([
        ['ABC', 'Order'],
        ['ABC', 'Address'],
        ['ABC', 'Zip'],
        ['XYZ', 'Customer'],
        ['XYZ', 'Name']
    ],
    columns=("Table", "Column"))

    pretty = pd.concat(
        (df['Table'],
        df.groupby("Table")['Column'].transform(lambda x: ", ".join(x))),
        axis=1
    ).drop_duplicates()

    for _, row in pretty.iterrows():
        print("Table '{}' has columns: {}".format(row['Table'], row['Column']))

Table 'ABC' has columns: Order, Address, Zip
Table 'XYZ' has columns: Customer, Name

Concatenate strings of a groupedBy pandas dataframe

1 Answers1