I'm looking for a way to do the equivalent to the SQL
SELECT DISTINCT col1, col2 FROM dataframe_table
The pandas sql comparison doesn't have anything about distinct
.
.unique()
only works for a single column, so I suppose I could concat the columns, or put them in a list/tuple and compare that way, but this seems like something pandas should do in a more native way.
Am I missing something obvious, or is there no way to do this?
This question is related to
python
pandas
dataframe
duplicates
distinct
I've tried different solutions. First was:
a_df=np.unique(df[['col1','col2']], axis=0)
and it works well for not object data Another way to do this and to avoid error (for object columns type) is to apply drop_duplicates()
a_df=df.drop_duplicates(['col1','col2'])[['col1','col2']]
You can also use SQL to do this, but it worked very slow in my case:
from pandasql import sqldf
q="""SELECT DISTINCT col1, col2 FROM df;"""
pysqldf = lambda q: sqldf(q, globals())
a_df = pysqldf(q)
I think use drop duplicate
sometimes will not so useful depending dataframe.
I found this:
[in] df['col_1'].unique()
[out] array(['A', 'B', 'C'], dtype=object)
And work for me!
https://riptutorial.com/pandas/example/26077/select-distinct-rows-across-dataframe
You can take the sets of the columns and just subtract the smaller set from the larger set:
distinct_values = set(df['a'])-set(df['b'])
To solve a similar problem, I'm using groupby
:
print(f"Distinct entries: {len(df.groupby(['col1', 'col2']))}")
Whether that's appropriate will depend on what you want to do with the result, though (in my case, I just wanted the equivalent of COUNT DISTINCT
as shown).
There is no unique
method for a df, if the number of unique values for each column were the same then the following would work: df.apply(pd.Series.unique)
but if not then you will get an error. Another approach would be to store the values in a dict which is keyed on the column name:
In [111]:
df = pd.DataFrame({'a':[0,1,2,2,4], 'b':[1,1,1,2,2]})
d={}
for col in df:
d[col] = df[col].unique()
d
Out[111]:
{'a': array([0, 1, 2, 4], dtype=int64), 'b': array([1, 2], dtype=int64)}
Source: Stackoverflow.com