Ndtyjky

I have table as below and i want to sort as expected output as below. How to perform using Python. Table is inside Excel/csv format. ?

I want to Match column1 data with Column2 data and want to add new 2 columns(5&6) with sorted data as below.

How to perform above operation using python ?

It looks like your question is trying to do two things:

1. Read the data from excel into your python program.


2. Manipulate the data once it's in python.

For #1, you could use something like Python's xlrd or there are several other xls(x) parsers.

I would start with that and see if you can get the data into python. It would look something like this:

import xlrd



# open your workbook

wb = xlrd.open_workbook('mybook.xlsx')

sh = wb.sheet_by_index(0)



# now go through the rows and do what you want with them

for x in range(sh.nrows):

    for y in range(sh.ncols):

        value = sh.cell(x,y).value

        # and do something with this value.

I hope the above gets you started on the above.

One of the approach is as follows,

Create an empty dataframe and append the matched column values as per your logic

Merge the created dataframe with your original dataframe.

I have made an attempt using your example data and here goes it:

import pandas as pd

dat = pd.read_excel(<location_to_file>) # Reading excel in to pandas

dat = pd.DataFrame(dat) # Converting to a pandas dataframe

dat1 = pd.DataFrame()

for n in range(dat.shape[0]):

    for m in range(dat.shape[0]):

        if dat['Col1'][n] == dat['Col2'][m]:

            dat1 = dat1.append(pd.DataFrame({'Column5': dat.iloc[m][2], 'Column6': dat.iloc[m][3]}, index=[0]), ignore_index=True)

# print(dat1)

df = pd.concat([dat, dat1], axis=1)

print(df)

Input(as dataframe):

  Col1 Col2     Col3  Col4

0  ABC  DEF       12  DGMN

1  PQR  MNO       17  DGSD

2  DEF  JPG   United  DGFS

3  JPG  PQR     21Hi  DFPR

4  SQL  STF      STM  DGBC

5  PQR  YZW  Hello90  DGSF

6  MNO  ABC      DQT  DGCV

7  STF  SQL     A18B  DGFD

Intermediate/Temp dataframe:

  Column5 Column6

0     DQT    DGCV

1    21Hi    DFPR

2      12    DGMN

3  United    DGFS

4    A18B    DGFD

5    21Hi    DFPR

6      17    DGSD

7     STM    DGBC

Output(df):

  Col1 Col2     Col3  Col4 Column5 Column6

0  ABC  DEF       12  DGMN     DQT    DGCV

1  PQR  MNO       17  DGSD    21Hi    DFPR

2  DEF  JPG   United  DGFS      12    DGMN

3  JPG  PQR     21Hi  DFPR  United    DGFS

4  SQL  STF      STM  DGBC    A18B    DGFD

5  PQR  YZW  Hello90  DGSF    21Hi    DFPR

6  MNO  ABC      DQT  DGCV      17    DGSD

7  STF  SQL     A18B  DGFD     STM    DGBC

While this snippet could be improved further for performance by vectorizing the operations. Hope this would help you get started.

Note
Please show your research efforts in addressing/solving the issue you're posting. That would motivate SO members to help you.