python pandas pivot dataframe removing duplicates

Question

Let's assume we have a denormalized board of Servers hostname, ip (1 hostname --> 1 ip, 1-to-1 relationship) with N oracle clients installed on it.

col_server      =   ['server_A','server_A','server_A']
col_ip          =   ['ip_A'    , 'ip_A'   , 'ip_A'   ]
col_ora_client  =   ['11'      ,'12'      ,'19'      ]
df              =   pd.DataFrame(data=list(zip(col_server,col_ip,col_ora_client)) , columns=["server","ip","ora_client"])
print(tabulate(df, headers='keys', tablefmt='psql'))

throws this output

+----+----------+------+--------------+
|    | server   | ip   |   ora_client |
|----+----------+------+--------------|
|  0 | server_A | ip_A |           11 |
|  1 | server_A | ip_A |           12 |
|  2 | server_A | ip_A |           19 |
+----+----------+------+--------------+

But what I want is

+----------+------+----+----+----+
|  server  |  ip  | 11 | 12 | 19 |
+----------+------+----+----+----+
| server_A | ip_A |  1 |  1 |  1 |
+----------+------+----+----+----+

I've tried pd.crosstab, such as

df_b            =   pd.crosstab([df['server'] , df['ip']] , df['ora_client'])
print(tabulate(df_b, headers='keys', tablefmt='psql'))

and I get an undesired first column of tuples

+----------------------+------+------+------+
|                      |   11 |   12 |   19 |
|----------------------+------+------+------|
| ('server_A', 'ip_A') |    1 |    1 |    1 |
+----------------------+------+------+------+

How can I achieve my needs?

Any help shall be much appreciated!

akuiper · Accepted Answer · 2022-09-12 15:03:38Z

1

You can use pivot_table:

pd.pivot_table(
  df, 
  index=['server', 'ip'], 
  columns=['ora_client'], 
  values=['ora_client'], 
  aggfunc='size'
).reset_index()

#ora_client    server    ip  11  12  19
#0           server_A  ip_A   1   1   1

answered Sep 12, 2022 at 15:03

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

Collectives™ on Stack Overflow

python pandas pivot dataframe removing duplicates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related