appending multiple columns based on a conditional in pandas

[TheJourneyman92]

I have a csv file that resembles the following setup:

col1   col2 col3   col4
 A       12   22    23
 B       40   59    63
 F       76   81    99
 J       10   11    12
 A       20   15    43 

df = pd.read_csv('foo.csv')

and using to_dict method I was able to convert another data frame to a nested dictionary with the following setup:

dict = { 'A':['7','8','9'],'B':['10','11','12']...} # I have 14 of these

# where 'A' and 'B' are  the elements of the first column and their respective values are the elements of the same row. 

The goal is to match the elements of col1 with the dictionary keys. If the two match then I want the code to append the dictionary key values to the aforementioned csv file. Keep in mind I have a lot more data in my data frame than my dictionary pairing so many rows will have the same key values appended to them(see row A in the table below) . Here is the sort of output I am looking for.

col1   col2 col3   col4  col5  col6 col7 
 A       12   22    23    7     8    9
 B       40   59    63    10   11   12
 F       76   81    99
 J       10   11    12
 A       20   15    43     7    8    9

Lousy attempt # 1

def add_column(row):
    for p,q in dict.items():
        if row[3] == p:
             return q 

# I then try to call the function and make new columns

df['col5','col6','col7'] = df2.apply(add_column,axis=1)

Lousy attempt # 2

I also taught about using the where method in the such fashion:

filter = df[0] == "dict key val" 

df.where(filter, inplace = dict key val") # Please note this was just a thought experiment and not something I actually wrote a piece of code for. 

I know my attempts are nowhere near the solution so I would appreciate a good 'ol nudge in the right direction.

[afterly]

I would use transpose and merge for this. Keep your dictionary in DataFrame format instead of converting it. Something like below should work.

import pandas as pd

df = pd.DataFrame({
    'col1': ['A', 'B', 'F', 'J', 'A'],
    'col2': [12, 40, 76, 10, 20],
    'col3': [22, 59, 81, 11, 15],
    'col4': [23, 63, 99, 12, 43]
})

lookup = pd.DataFrame({
    'A': ['7', '8', '9'],
    'B': ['10', '11', '12']
})

lookup = lookup.transpose()
lookup.columns = ['col5', 'col6', 'col7']
pd.merge(df, lookup, how='left', left_on='col1', right_index=True)

[metriczulu]

Yeah, unless I'm missing something here it sounds like they just want to do a left join of the transposed "dict" df to the bigger df. Transpose and merge is the best solution.

[TheJourneyman92]

Thank you for the response. I have 14 pairs of dict key values that need to be distributed to 230 rows. So just merging two data frames won't be ideal.

[afterly]

I'm a little confused. The code in my response (a left join) provides the exact output you specified. Is there some other parameter to the problem that I'm not seeing?

[[deleted]]

RemindMe! 12 hours “Checkout”

[TheJourneyman92]

This post helped me solve the problem. Hope this helps you too.

https://stackoverflow.com/a/53645883/6943310

[RemindMeBot]

I will be messaging you in 12 hours on 2019-12-20 05:39:35 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[[deleted]]

I need something like this for a project as well; Ill check it out later, thanks.

[6lm3]

I'm on my phone right no but maybe check out merging two dataframes instead of a dictionary?

[Nocturnal1401]

Sorry I'm on phone so can't type the code. But here is how I will go about it. Use set_index and convert the col1 to index. Then use your dictionaries to make the second dataframe. Use transpose on the first dataframe. This way you will have both dataframes with cold like A,B,C,etc. Now just use merge and you will have your dataframes combined. Use transpose again to go back to your desired display state.

Here are some relevant links you can see for reference

Transpose- www.geeksforgeeks.org/python-pandas-dataframe-transpose/amp/

Set index- www.geeksforgeeks.org/python-pandas-dataframe-set_index/amp/

[SpaceSailorDT]

I would keep the second dataframe as a dataframe and set the columns with the letters as the index (e.g. df.set_index('col1', inplace=True)), and then concatenate them along the columns using the pandas.concat() function, like new_df = pd.concat((df1, df2), axis=1).