python pandas cannot assemble with duplicate keys

Question

The purpose of this code is to scrape a bunch of data tables, turn them into pandas data frames, remove some unnecessary columns and fix the date.

Each data frame has 2 columns the first called ('Release Date') in every data frame and the other column have a different name for each data frame.

Then concatenate this tables into a single unified data frame with the 'Release Date' column as an index, So the events that occurs at the same time must be at the same row.

When I tried this code:

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as ec
import pandas as pd


class DataEngine:
    def __init__(self):
        self.urls = open(r"C:\Users\Sayed\Desktop\script\sample.txt").readlines()
        self.driver = webdriver.Chrome(r"D:\Projects\Tutorial\Driver\chromedriver.exe")
        self.wait = WebDriverWait(self.driver, 10)

    def title(self):
        names = []
        for url in self.urls:
            self.driver.get(url)
            title = self.driver.find_element_by_xpath('//*[@id="leftColumn"]/h1').text
            names.append(title)
        return names

    def table(self):
        DataFrames = []
        for url in self.urls:
            self.driver.get(url)
            while True:
                try:
                    item = self.wait.until(
                        ec.visibility_of_element_located((By.XPATH, '//*[contains(@id,"showMoreHistory")]/a')))
                    self.driver.execute_script("arguments[0].click();", item)
                except Exception:
                    break

            df = pd.DataFrame(columns=['Release Date', 'Time', 'Actual', 'Forecast', 'Previous'])
            pos = 0
            for table in self.wait.until(
                    ec.visibility_of_all_elements_located((By.XPATH, '//*[contains(@id,"eventHistoryTable")]//tr'))):
                data = [item.text for item in table.find_elements_by_xpath(".//*[self::td]")]
                if data:
                    df.loc[pos] = data[0:5]
                    pos += 1

            df["Date"] = df["Release Date"].apply(lambda date: date[:12]) + " " + df["Time"]
            df.astype('unicode')
            df = df[['Date', 'Actual', 'Forecast', 'Previous', 'Release Date', 'Time']]
            df = df.drop(df.columns[-4:], axis=1).reset_index(drop=True)
            df = df.head(50)
            pd.to_datetime(df['Date'], format='%b %d, %Y %H:%M')
            DataFrames.append(df)
        return DataFrames

    def rename(self):
        FinalDataFrames = []
        tables = self.table()
        names = self.title()
        for name, table in zip(names, tables):
            table.rename(columns={'Date': 'Release Date', 'Actual': name}, inplace=True)
            FinalDataFrames.append(table)
        return FinalDataFrames

    def finalDF(self):
        dfs = self.rename()
        dfs = [dfi.loc[~dfi.index.duplicated(keep='first')] for dfi in dfs]
        df = pd.concat(dfs, axis=1, join='outer').sort_index(ascending=False)
        df.astype('unicode')
        pd.to_datetime(df['Release Date'], format='%b %d, %Y %H:%M')
        df.set_index('Release Date')
        print(df.head())


if __name__ == "__main__":
    DataEngine().finalDF()

I got this error:

 File "D:/Projects/Tutorial/database.py", line 71, in <module>
    if __name__ == "__main__":

  File "D:/Projects/Tutorial/database.py", line 66, in finalDF
    pd.to_datetime(df['Release Date'], format='%b %d, %Y %H:%M')

File "C:\Users\Sayed\Anaconda3\lib\site-packages\pandas\core\tools\datetimes.py", line 454, in to_datetime
    result = _assemble_from_unit_mappings(arg, errors=errors)

 File "C:\Users\Sayed\Anaconda3\lib\site-packages\pandas\core\tools\datetimes.py", line 520, in _assemble_from_unit_mappings

    raise ValueError("cannot assemble with duplicate keys")

ValueError: cannot assemble with duplicate keys

Answer 1

Duplicated column names

That error is the result of your df having duplicated column names somehow. The source code for pandas that would generete the error in this case looks as follows:

from pandas import to_timedelta, to_numeric, DataFrame
    arg = DataFrame(arg)
    if not arg.columns.is_unique:
        raise ValueError("cannot assemble with duplicate keys")

I think your code is failing becuase the column name that you are referencing with pd.to_datetime(df['Release Date'], format='%b %d, %Y %H:%M') is not unique.

LINK to pandas source