Sorting rows in one dataframe into the columns of another dataframe

Question

I have the following dataframes:

df1 = {'col1': {0: 'IL', 1: 'NE', 2: 'NE', 3: 'IL', 4: 'TX', 5: 'TX'},
 'col2': {0: 'bob', 1: 'fred', 2: 'alex', 3: 'ted', 4: 'frank', 5: 'tim'}}
df2 = {'IL': {0},'NE': {0},'TX': {0}}

dataframes and expected result

I want to add the col 2 information from df1 to the correct column in df2. So far I have the following code:

for i in range(len(df1)):
      if df1.loc[i,'col1'] == #header of df2
             #add df1.loc[i,'col2'] to the header 'col1' matches with

I need help on how I can reference the header of df2 and then add the col 2 code to that specific header it matches with.

kindly post sample data with expected output. Use this as a guide : https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — sammywemmy, Mar 22 '20 at 16:24
kindly share actual data, and not pics. you can do df.head().to_dict() and paste the output — sammywemmy, Mar 22 '20 at 16:27
Please provide a [mcve], and clarify what exactly the issue is. — AMC, Mar 22 '20 at 18:36

score 1 · Answer 1 · answered Mar 22 '20 at 17:10

1

Try:

df1.set_index(['col1', df1.groupby('col1').cumcount()])['col2'].unstack(0)

Output:

col1   IL    NE     TX
0     bob  fred  frank
1     ted  alex    tim

answered Mar 22 '20 at 17:10

Scott Boston

147,308
15
139
187

score 0 · Answer 2 · answered Mar 22 '20 at 17:06

use a pivot table to aggregate col2 into a list,and apply pandas explode to each column :

  (df1.pivot_table(columns='col1',values='col2',aggfunc=list)
   .apply(lambda x: pd.Series.explode(x))
   .reset_index(drop=True)
   )

     col1   IL    NE      TX
       0    bob   fred   frank
       1    ted  alex    tim

Sorting rows in one dataframe into the columns of another dataframe

2 Answers2