Cross-checking dataframes in Python

user_18696224 0
Viewed 41

I am working on a Pandas issue.

Currently in df1:

start Stop
NYPenn WUnion
GCTerm 30thSt
TUStat LAUnio
JaStat MillSt
ChiUnS MonCen
OGTran SouthS

Currently in df2 (Prime):

Train_Code City
NYPenn New York City
WUnion D.C.
GCTerm New York City
30thSt Philadelphia
TUStat Toronto
LAUnio Los Angeles
MonCen Montreal
OGTran Chicago
SouthS Boston

I want to use the train codes to determine which start/stop in df1 contain prime stations. I would need to run each element in both columns in df1 against df2 (Train_Code) to output the results indicating which station was a prime (or if both stations are prime) into another dataframe (df3).

df3 should end up being:

start Stop Results City Results City
NYPenn WUnion Yes New York City Yes D.C.
GCTerm 30thSt
TUStat LAUnio
JaStat MillSt NO NaN NO NaN
ChiUnS MonCen NO NaN Yes Montreal
OGTran SouthS

**Note: I didn't fill in df3 all the way but I gave examples of how it should be filled.

[If I added another column indicating there was a layover station, the code should work run against the layover column as well.]

pandas dataframe python
1 Answers

This will get you close:

df1s = df1.stack().rename('Train_Code').to_frame()
df1s.loc[:,'City'] = df1s['Train_Code'].map(df2.set_index('Train_Code')['City'])
df1s['Results'] = np.where(df1s['City'].notna(), 'Yes', 'NO')
df1s.unstack()

Output:

      Train_Code                   City               Results     
       start    Stop          start          Stop   start Stop
0     NYPenn  WUnion  New York City          D.C.     Yes  Yes
1     GCTerm  30thSt  New York City  Philadelphia     Yes  Yes
2     TUStat  LAUnio        Toronto   Los Angeles     Yes  Yes
3     JaStat  MillSt            NaN           NaN      NO   NO
4     ChiUnS  MonCen            NaN      Montreal      NO  Yes
5     OGTran  SouthS        Chicago        Boston     Yes  Yes
user_63615310
Related