I have data format in these multiple columns. So I want to bring all 4 columns of data into a single column.
YEAR Month pcp1 pcp2 pcp3 pcp4
1984 1 0 0 0 0
1984 2 1.2 0 0 0
1984 3 0 0 0 0
1984 4 0 0 0 0
1984 5 0 0 0 0
1984 6 0 0 0 1.6
1984 7 3 3 9.2 3.2
1984 8 6.2 27.1 5.4 0
1984 9 0 0 0 0
1984 10 0 0 0 0
1984 11 0 0 0 0
1984 12 0 0 0 0
CodePudding user response:
pd.wide_to_long
pd.wide_to_long(df, i=['YEAR', 'Month'], j='day', stubnames='pcp', suffix='\d ', sep='').reset_index()
YEAR Month day pcp
0 1984 1 1 0.0
1 1984 1 2 0.0
2 1984 1 3 0.0
3 1984 1 4 0.0
4 1984 2 1 1.2
5 1984 2 2 0.0
6 1984 2 3 0.0
7 1984 2 4 0.0
8 1984 3 1 0.0
9 1984 3 2 0.0
10 1984 3 3 0.0
11 1984 3 4 0.0
12 1984 4 1 0.0
13 1984 4 2 0.0
14 1984 4 3 0.0
15 1984 4 4 0.0
16 1984 5 1 0.0
17 1984 5 2 0.0
18 1984 5 3 0.0
19 1984 5 4 0.0
20 1984 6 1 0.0
21 1984 6 2 0.0
22 1984 6 3 0.0
23 1984 6 4 1.6
24 1984 7 1 3.0
25 1984 7 2 3.0
26 1984 7 3 9.2
27 1984 7 4 3.2
28 1984 8 1 6.2
29 1984 8 2 27.1
30 1984 8 3 5.4
31 1984 8 4 0.0
32 1984 9 1 0.0
33 1984 9 2 0.0
34 1984 9 3 0.0
35 1984 9 4 0.0
36 1984 10 1 0.0
37 1984 10 2 0.0
38 1984 10 3 0.0
39 1984 10 4 0.0
40 1984 11 1 0.0
41 1984 11 2 0.0
42 1984 11 3 0.0
43 1984 11 4 0.0
44 1984 12 1 0.0
45 1984 12 2 0.0
46 1984 12 3 0.0
47 1984 12 4 0.0
CodePudding user response:
df.melt(id_vars=["YEAR", "Month"], var_name='Date', value_name="pcp").sort_values(['Month','Date']).reset_index(drop=True)
YEAR Month Date pcp
0 1984 1 pcp1 0.0
1 1984 1 pcp2 0.0
2 1984 1 pcp3 0.0
3 1984 1 pcp4 0.0
4 1984 2 pcp1 1.2
5 1984 2 pcp2 0.0
6 1984 2 pcp3 0.0
7 1984 2 pcp4 0.0
8 1984 3 pcp1 0.0
9 1984 3 pcp2 0.0
10 1984 3 pcp3 0.0
11 1984 3 pcp4 0.0
12 1984 4 pcp1 0.0
13 1984 4 pcp2 0.0
14 1984 4 pcp3 0.0
15 1984 4 pcp4 0.0
16 1984 5 pcp1 0.0
17 1984 5 pcp2 0.0
18 1984 5 pcp3 0.0
19 1984 5 pcp4 0.0
20 1984 6 pcp1 0.0
21 1984 6 pcp2 0.0
22 1984 6 pcp3 0.0
23 1984 6 pcp4 1.6
24 1984 7 pcp1 3.0
25 1984 7 pcp2 3.0
26 1984 7 pcp3 9.2
27 1984 7 pcp4 3.2
28 1984 8 pcp1 6.2
29 1984 8 pcp2 27.1
30 1984 8 pcp3 5.4
31 1984 8 pcp4 0.0
32 1984 9 pcp1 0.0
33 1984 9 pcp2 0.0
34 1984 9 pcp3 0.0
35 1984 9 pcp4 0.0
36 1984 10 pcp1 0.0
37 1984 10 pcp2 0.0
38 1984 10 pcp3 0.0
39 1984 10 pcp4 0.0
40 1984 11 pcp1 0.0
41 1984 11 pcp2 0.0
42 1984 11 pcp3 0.0
43 1984 11 pcp4 0.0
44 1984 12 pcp1 0.0
45 1984 12 pcp2 0.0
46 1984 12 pcp3 0.0
47 1984 12 pcp4 0.0