Is there any way to do vlookup function thing using pandas ?
I've got huge dataset(about 10 Million entries) onto which, I've to do vlookup.
Tried doing merge but did not get the output right
#🔒 VLOOKUP using pandas
safe temple
supple fulcrumBOT
Is there any way to do vlookup function thing using pandas ?
I've got huge datas...
@safe temple
Python help channel opened
subtle hazel
generally speaking that's a merge, yeah
safe temple
the problem I'm facing is multiple values for a single column but I need the first occurence of the value that is being looked up
safe temple
generally speaking that's a `merge`, yeah
multiple occurences are creating multiple entries upon merge
subtle hazel
Simplest way to fix that would be to drop duplicates by the key you're merging by.
safe temple
Simplest way to fix that would be to drop duplicates by the key you're merging b...
thanks
will try that
safe temple
Simplest way to fix that would be to drop duplicates by the key you're merging b...
how do I remove entire row, in case a value is duplicated in a particular column ?
subtle hazel
df.drop_duplicates("some_column") leaves only the first row with a certain value of that column.
safe temple
`df.drop_duplicates("some_column")` leaves only the first row with a certain val...
I'll sort the rows based on date-time column, but how do I make it consistent ?
some rows have :
dd-mm-yyyy while some have dd-mm-yyyy hh:mm
I want all to be on dd-mm-yyyy hh:mm
any way to do it ?
subtle hazel
Sounds like that column is a string one - better convert it all to actual datetimes with pandas.to_datetime
safe temple
Sounds like that column is a string one - better convert it all to actual dateti...
tried that, got error
safe temple
Sounds like that column is a string one - better convert it all to actual dateti...
value error, format not matching
df[column]= pd.to_datetime(df[column], format="%d-%m-%Y %H:%M:%S")
rugged compass
!e I think you'll have to select based on the length, ```py
import pandas as pd
df = pd.DataFrame({'col': ['10-10-2010', '10-10-2010 10:10:10']})
short_format = df["col"].str.len() == len('dd-mm-yyyy')
df.loc[short_format, "col"] = pd.to_datetime(df.loc[short_format, "col"], format='%d-%m-%Y')
df.loc[~short_format, "col"] = pd.to_datetime(df.loc[~short_format, "col"], format='%d-%m-%Y %H:%M:%S')
df["col"] = df["col"].astype('datetime64[ns]')
print(df)
:white_check_mark: Your 3.12 eval job has completed with return code 0.
001 | col
002 | 0 2010-10-10 00:00:00
003 | 1 2010-10-10 10:10:10
also don't use - unless you're using the ISO format
subtle hazel
!e I think you'll have to select based on the length, ```py
import pandas as pd
...
For this example, it actually works to do pd.to_datetime(df["col"],format="mixed",dayfirst=True)
I agree though, this format is quite cursed.
safe temple
For this example, it actually works to do `pd.to_datetime(df["col"],format="mixe...
what is mixed format ?
rugged compass
that tells pandas to infer the format of each row individually instead of assuming they all follow the same format
subtle hazel
"infer separately for each row".
safe temple
that tells pandas to infer the format of each row individually instead of assumi...
but does it convert them to consistent format ?
rugged compass
but does it convert them to consistent format ?
yes, but it is also very likely so give you wrong results like it'll parse 10-30-2020 and 30-10-2020 both into October 30 2020 without errors
>>> pd.to_datetime(['10-30-2020', '30-10-2020'], format='mixed').strftime('%B %d %Y')
Index(['October 30 2020', 'October 30 2020'], dtype='object')
Well, in your example it's the right behaviour, since 30 can't be a month.
And for the cases where it's ambigous - that's why I specify dayfirst=True.
safe temple
And for the cases where it's ambigous - that's why I specify `dayfirst=True`.
that worked
now its the drop duplicates thats causing issue
Column A has multiple duplicates (Entire DF has 3 columns)
Keep First occurence and delete the entire row for next duplicates
supple fulcrumBOT
Python help channel closed