0
$\begingroup$

I have the following dataset in df_1 which I want to convert into the format of df_2. In df_2 I have converted the columns of df_1 to rows in df_2 (excluding UserId and Date). I looked up for similar answers but they are providing little complex solutions. Is there a simple way to do this?

df_1

   UserId       Date                   -7  -6  -5  -4  -3  -2  -1   0   1   2   3   4   5   6   7
    87      2011-05-10 18:38:55.030     0   0   0   0   0   0   1   0   0   0   0   0   0   0   0
    487     2011-11-29 14:46:12.080     0   0   1   0   0   0   0   0   0   0   0   0   0   0   0
    21      2012-03-02 14:35:06.867     0   1   0   1   2   0   2   2   0   1   2   2   1   3   1

df_2

day | count
-7   0
-7   0
-7   0
-6   0
-6   0
-6   1
-5   0
-5   1
-5   0 
.    .
.    .(Similarly for other columns in between)
.    .
6   0    
6   0
6   3
7   0
7   0
7   1
$\endgroup$
3
$\begingroup$

Try using pandas melt

dataT = pd.DataFrame({"userID":[1,2,3],"date":["2020-01-01","2019-01-02","2020-08-12"],"-7":[0,1,1],"-6":[1,0,0],"-5":[0,0,0]})

Input:

enter image description here

dataT.melt(value_vars= ["-7","-6","-5"], value_name="count")

Output:

enter image description here

Update

By taking the comment by Benji the code would be:

dataT.melt(id_vars= ["userID","date"], value_name="count")

With output:

enter image description here

| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ Note that the melt function can also use a "blacklist" of columns, id_vars, where the given answer uses value_vars as a "whitelist". So you could do dataT.melt(id_vars=["userID","date"], value_name="count") to melt all the subsequent columns $\endgroup$ – Benji Albert Oct 12 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.