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
Improve this question
$\endgroup$

1 Answer 1

Reset to default
2
$\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
  • 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$
    – Ben
    Oct 12, 2020 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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