Conditional pivoting of a pandas Dataframes with duplicate values in columns

Asked Jul 25 '19 at 12:42

Active Jul 25 '19 at 12:52

Viewed 23 times

Say I have a dataframe which looks like following,

df1.head()

ID  NAMES
SAP KASF
SAP HJL
SAP KJOP
LUF LNC1
LUF LNC2
LUF KASF
LUF HJL

And I need to reshape the above dataframe as the following, with NAMES as rows and the values in column ID as columns/headers and fill with 1 or 0.

The values to fill in is based on the matching value in the column NAME for each ID.

df.head()

NAMES SAP LUF
KASF   1   1
HJL    1   1
KJOP   1   0
LNC1   0   1
LNC2   0   1

I have tried,

df.pivot(index='NAME',columns='ID')

And it is throwing,

ValueError: Index contains duplicate entries, cannot reshape

Other solution I tried is,

( 
  df.T
  .set_axis(["ID"],axis=True, inplace=False)
  .rename_axis('NAME',axis=0)
  .reset_index()
)

Which also throwing error due to mismatching vaues for ID and NAME

edited Jul 25 '19 at 12:52

asked Jul 25 '19 at 12:42

ARJ

2,021
4
27
52

`pd.crosstab(df.NAMES,df.ID)` ? – anky Jul 25 '19 at 12:50
I have not tried `crosstab` – ARJ Jul 25 '19 at 12:51
There is a crosstab section of the linked duplicate target https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe – piRSquared Jul 25 '19 at 12:52
@piRSquared The questions are different unfortunately only the headings. Now I have edited my headings of my question. – ARJ Jul 25 '19 at 12:53
1

You'll see under what I've labeled question #9 the solution you are looking for. – piRSquared Jul 25 '19 at 12:54
@piRSquared Thank you – ARJ Jul 25 '19 at 12:56
you might want to start from here; `pd_df.pivot(index='NAMES', columns='ID', values='ID')` (I could not write my whole answer) – Ala Tarighati Jul 25 '19 at 12:58
The solution is: `df.pivot_table(index='NAME',columns='ID',fill_value=0,aggfunc='size')` – ARJ Jul 25 '19 at 13:00

Conditional pivoting of a pandas Dataframes with duplicate values in columns

0 Answers0