Remove duplicate rows in one column

Lรฉa
Level 1
Remove duplicate rows in one column
How can I remove duplicated rows in one column ?
0 Kudos
6 Replies
jereze
Community Manager
Community Manager

There is currently no way to do that in a visual preparation recipe* (because a visual recipe more or less works row by row, and it cannot work on a full column, as it is designed for big data).



It's possible to do so in a visual GROUP recipe: click โ€œShow mass actionsโ€, select all columns, click โ€œuse as grouping keysโ€. If the csv is very big, I suggest synchronizing to a SQL DB first.



You can also do so in coding recipes:




  • In a Python recipe, you can use the Pandas function (see example below) drop_duplicates()

  • In a R recipe, you have several alternative (duplicated(), dplyr, ..): read here

  • In a SQL recipe, I would use a a group by with min or max, or window function with partition by key and keep the first row.



 



 



* There is actually one way to do it in a visual preparation recipe, with a custom Python function, but that will not work all the time (if the recipe is multi-threaded), so I would not recommend this trick:





 



I hope that helps,

Jeremy

Jeremy, Product Manager at Dataiku
Mattsco
Dataiker

Hi,



In a python recipe I would do:




# -*- coding: utf-8 -*-
import dataiku
import pandas as pd

# Recipe inputs
df = dataiku.Dataset("input_dataset").get_dataframe()

df.drop_duplicates(subset=["my_key_colum"], inplace=True)
# or
# df.drop_duplicates(inplace=True)
# to use all columns to compare for duplicates

# Recipe outputs
out = dataiku.Dataset("output_dataset").write_with_schema(df)


Matt



 

Mattsco
0 Kudos
chemical
Level 1

Hello, sorry bringing the thread up, I'd need support because I'm trying deduplicating my output with Python but I can't understand how to edit the code. I need to deduplicate TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined because - as you can see from the screen below - I have for the same ID (first column) duplicate values. Indeed the column CD_BOOKING also has the same repeating values.

dataiku_ded.PNG

This is the Pyton code that I haven't touched yet.

# -*- coding: utf-8 -*-
import dataiku
import pandas as pd, numpy as np
from dataiku import pandasutils as pdu

# Read recipe inputs
TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined = dataiku.Dataset("TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined")
TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined_df = TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined.get_dataframe()


# Compute recipe outputs from inputs
# TODO: Replace this part by your actual code that computes the output, as a Pandas dataframe
# NB: DSS also supports other kinds of APIs for reading and writing data. Please see doc.

phone_deduplicated_df = TDD_FT_CUSTOMER_JOURNEY_joined_filtered_joined_df # For this sample code, simply copy input to output


# Write recipe outputs
phone_deduplicated = dataiku.Dataset("phone_deduplicated")
phone_deduplicated.write_with_schema(phone_deduplicated_df)

 

0 Kudos
Turribeach

Hi Chemical, I suggest you start a new thread since your problem is different. Also you say you want to do it in SQL but you posted Python code.

0 Kudos
chemical
Level 1

Ciao Turribeach, I've slightly adjusted my post in order to be better understandable now, I think my question fits enough with what was asked in this thread.

0 Kudos
Turribeach

Hi, because you are not the original poster of the question you can't mark the question as answered or select the correct response. So I suggest you repost as a new thread. 

Labels

?
Labels (2)
A banner prompting to get Dataiku