CODEWITHSUNDEEP
pythonpandasdataframe
AbbySG
AbbySG

Reputation: 31

Python Pandas, Dataframe and reading from excel file

How do I merge data with similar values reading from an excel file?

import pandas as pd
import numpy as np
df = pd.read_excel("testfile.xlsx")
print(df)

File example: testdata.xlsx

Identifier   Dates
123456       1/1/2021
789101       2/2/2021
221342       3/3/2021
231344       1/1/2021
134562       2/2/2021
135650       2/2/2021
135677       2/2/2021
2246         1/1/2021
24682        3/3/2021
245684       1/1/2021

Output data wanted (merge the data corresponding to a certain date):

2/2/2021   789101 134562 135650 135677  
1/1/2021   245684   2246 231344
3/3/2021   24682  221342

Upvotes: 3

Views: 117

Answers (1)

Akshay Sehgal
Akshay Sehgal

Reputation: 19307

Does this solve your problem?

df.groupby(['Dates'])['Identifier'].apply(list)

Dates
1/1/2021      [123456, 231344, 2246, 245684]
2/2/2021    [789101, 134562, 135650, 135677]
3/3/2021                     [221342, 24682]
Name: Identifier, dtype: object

If you dont want this as a list, but as a string with spaces separated, as you indicate in your question, then try this -

df.astype({'Identifier':str}).groupby(['Dates'])['Identifier'].apply(' '.join)

Dates
1/1/2021      123456 231344 2246 245684
2/2/2021    789101 134562 135650 135677
3/3/2021                   221342 24682
Name: Identifier, dtype: object

Upvotes: 3

Related Questions