python pandas pivot dataframe removing duplicates

glezo 0
Viewed 21

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!

pivot pandas python
1 Answers

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
Psidom0
Related