0

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
Improve this question
3
  • Are there actually 31 columns? Commented May 7, 2022 at 5:46
  • is your desired output correct?? month values are ranging from 1-31 Commented May 7, 2022 at 6:07
  • What is your expected output dataframe Commented May 7, 2022 at 7:11

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Changed ur profile I see ... New shubham :)
Empty DataFrame Columns: [YEAR, MN, DRF, DRF30, DRF27, DRF06, DRF04, DRF03, DRF17, DRF10, DRF28, DRF01, DRF18, DRF23, DRF07, DRF13, DRF20, DRF05, DRF24, DRF29, DRF12, DRF15, DRF14, DRF02, DRF21, DRF11, DRF26, DRF25, DRF31, DRF16, DRF22, DRF09, DRF19, DRF08, pcp] Index: []
I am getting this error and not getting single column format
@sammywemmy just new pic but same old me :-)
@DebrupaChatterjee Check your column names in real dataframe and use them accordingly in the wide_to_long formula.
0 
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
Improve this answer

1 Comment

After running this code I am not getting days data in a serial format so facing an issue with this ...... station_df.melt(id_vars=["Year", "Month"], var_name='Date', value_name="pcp").sort_values(['Year','Month','Date']).reset_index(drop=True)

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.