CODEWITHSUNDEEP
pythonpandasdataframe
Jack Zaki Zakiul Fahmi Jailani
Jack Zaki Zakiul Fahmi Jailani

Reputation: 319

Splitting columns to rows based on timestamp

So I have this dataframe df with the timestamp and numeral value for each city columns:

time          new york     Amsterdam
2020-10-01    24           12
2020-10-02    31           21
2020-10-03    10           23

and I want to split the city columns into rows for each timestamp, just like this:

time          city       number
2020-10-01    new york     24
2020-10-02    new york     31
2020-10-03    new york     10
2020-10-01    Amsterdam    12
2020-10-02    Amsterdam    21
2020-10-03    Amsterdam    23

I tried to transpose the dataframe df like this:

df= df.T
df= df.reset_index()
new_header = df.iloc[0]
df= df[1:]
df.columns=new_header

but instead, I got the result of df like this:

city      2020-10-01   2020-10-02   2020-10-03
new york      24         31             10
Amsterdam     12         21             23

Would somebody help me with the logic of this data handling? thank you

Upvotes: 0

Views: 52

Answers (2)

sunnytown
sunnytown

Reputation: 1996

The answer is pretty straight forward:

df2 = df.melt(id_vars='time', var_name='city', value_name='number')

This is the inverse operation to pivoting, which you can use to get your original back.

Upvotes: 1

wwnde
wwnde

Reputation: 26676

Please pd.melt

pd.melt(df,id_vars=['time'], value_vars=['newyork','Amsterdam'],var_name='city', value_name='number')

Upvotes: 1

Related Questions