Pandas transforming chronological rows to columns

user_15636371 0
Viewed 30

I have a table of work experiences where each row represents a job in chronological order from the first job to the most recent job. For data science purposes I'm trying to create a new table based on this table that displays new job attributes and old job attributes on the same row. For example, the original table would be like:

uniqueID personID startdate enddate title functions
1 A1 1/1/21 12/1/21 Analyst data science
2 A1 1/1/22 12/1/22 Manager admin

The new table would be something like this:

uniqueID personID new_title new_function old_title old_function
1 A1 Analyst data science nan nan
2 A1 Manager admin Analyst data science

I tried to use some groupby variations but haven't been able to get this result.

pandas dataframe python
1 Answers

If I understand correctly, you're looking for a shift:

cols = ['title', 'functions']
df[['old_' + c for c in cols]] = df.groupby('personID')[cols].shift(1)
df = df.drop(['startdate', 'enddate'], axis=1).rename({c: 'new_' + c for c in cols}, axis=1)

Output:

>>> df
   uniqueID personID new_title new_functions old_title old_functions
0         1       A1   Analyst  data science       NaN           NaN
1         2       A1   Manager         admin   Analyst  data science
Admin1
Related