Grouping different Columns in python

Question

I have a csv file which contains ID, Time and its distance like screenshot below. The file contains the distance travelled of some interval of time. Is there any way in python which I can group all 'Time' column in one and all 'Distance' column in one column for that particular ID. My target is to plot time vs distance of that particular ID at the end.

Or is there any function in python which I can plot without grouping Time and Distance Column Values.

I am open to any suggestions :)

Every hour new values for Time & Distance are added to the right, isn't it? And you want to plot only the most right Time vs Distance, right? — koPytok
– koPytok, Commented May 16, 2018 at 11:33
@jezrael, Yes these are same column names. For every time, the distance is increased and it goes on.....So i want to group all Times column into one Time Column and all Distance Column into one Distance column — user96564
– user96564, Commented May 16, 2018 at 11:34
@Kopytok, As for every times, the distance is added to the right. But i want to plot all the distance vs time — user96564
– user96564, Commented May 16, 2018 at 11:36

jezrael · Accepted Answer · 2018-05-16 11:43:28Z

3

I believe after read_excel columns names are changed, so is possible split them by . for MultiIndex and for reshape use stack:

print (df)
   ID  Time(hr)  Distance Km  Time(hr).1  Distance Km.1  Time(hr).2  \
0   1         1            5           2             10           3   
1   2         2            0           2             10           3   

   Distance Km.2  
0             12  
1             15  

d = {'Time(hr)':'Time(hr).0','Distance Km':'Distance Km.0'}
df = df.set_index('ID').rename(columns=d)
df.columns = df.columns.str.split('.', expand=True)
df = df.stack().reset_index(level=1, drop=True).reset_index()
print (df)
   ID  Distance Km  Time(hr)
0   1            5         1
1   1           10         2
2   1           12         3
3   2            0         1
4   2           11         2
5   2           15         3

Last for pivoting is possible use pivot with plot:

df.pivot(index='Time(hr)', columns='ID', values='Distance Km').plot()

edited May 16, 2018 at 11:43

answered May 16, 2018 at 11:36

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jezrael Over a year ago

@user3280146 - Glad can help!

Collectives™ on Stack Overflow

Grouping different Columns in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related