How to create a new column from existing row values which have same corresponding value in other column?? (Pandas)

user_16117824 0
Viewed 51

Sorry if the title doesn't make a lot of sense.

I have a large df as follows:

client         referer
IP1            producta
IP2            productb
IP1            productc
IP4            productb
IP5            productd
IP4            producte
IP5            productf
IP5            productg

and so on. For each 'client' value there are sometimes more than 1 'referer' values. I want a df as follows:

client         referer1         referer2         referer3          ...
IP1            producta         productc
IP2            productb
IP4            productb         producte
IP5            productd         productf         productg
.
.
.

I've tried:

d4 = df2.groupby('client') which did nothing.

d4 = df2.pivot_table(index=['client'], columns = 'referer' which gives DataError: No numeric types to aggregate.

('client' and 'referer' columns are both categorical.)

Any ideas what I can do?

EDIT I am working on 10365152 rows so a lot of solutions give me Memoryerrors.

pandas python
4 Answers

groupby is a good solution. Transform the GroupBy object to a list with agg. Finally, you can create a new dataframe with the input list.

>>> pd.DataFrame(df.groupby("client")["referer"]
                   .agg(list).tolist()).add_prefix("referer")

   referer0  referer1  referer2
0  producta  productc      None
1  productb      None      None
2  productb  producte      None
3  productd  productf  productg
user_152399510

I think you had the initial good idea: d4 = df2.groupby('client') Then you create a dictionary, dividing by the clients: dictionary = dict(tuple(d4 .groupby('client'))) Then you can go through each of the dataframes with a for loop or other and put the condition if dataframe.loc[index, referer] != dataframe.loc[index+1 (or-1), referer], then create a new column and put dataframe.loc[index+1 (or-1), referer] in it

user_157368240

Does this work:

df
IP1 producta
IP2 productb
IP1 productc
IP4 productb
IP5 productd
IP4 producte
IP5 productf
IP5 productg

df['ref_count'] = df.groupby('client').cumcount() + 1
df['ref_count'] = df['ref_count'].map(lambda x: 'referer' + str(x) )
df.pivot(index = 'client',columns = 'ref_count', values = 'referer')

df
ref_count   client  referer1    referer2    referer3
0           IP1     producta    productc    NaN
1           IP2     productb    NaN NaN
2           IP4     productb    producte    NaN
3           IP5     productd    productf    productg

You may rename index if you need to.

user_107227520

A slightly modified @Corralien's solution where you don't have to wrap everything in DataFrame:

df.groupby("client")["referer"].agg(list).apply(pd.Series).add_prefix("referer")
user_158625690
Related