how to convert this wide format data to long format data frame

tidyverse
1

Hello,

I need to convert this wide format data to long format data for panel data analysis.
Please help me.

Thanks in advance

2024	2023	2022	2021	2020	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000	2024	2023	2022	2021	2020	2019	2018	2017	2016	2015	2014	2013	2024	2023	2022	2021	2020	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000
bank	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Interest Expense	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Net Interest Income - Actual	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total	Interest & Dividend Income - Finance - Total
HFBK.PK	5.70647	1.078357	1.549388	2.379481																																		27.153711	20.426367	19.140877	17.767558																					
FDLB.PK	4.004	3.827	6.022	8.501	8.457	9.442		11.587	13.831	15.525	10.264	5.171	4.729	5.219	6.677	6.56	6.307	6.294	6.13																			8.483	6.65	9.034	11.455	12.1	13.611		17.192	20.282	21.53	15.792	8.71	7.196	7.727	9.042	8.922	8.577	8.345	8.141						
FNMA.PK														275542	74535	103023	120555	73728	100017	89227	88780	97511	41080	37107	30601.00096	28773	29423	29587	24866	20962	20951	20733	21295	21409	19968	22404		137392	119641	99512	107288	119176	116595	109360	105778	109299	114295	117374	128994	142826	154124	38998	41784	43711	42851	44441	47818	49064	50924	49170	42781	35495
FITB.OQ	3933	978	441	790	1457	1043	691	578	495	451	412	512	661	885	1313	2094	3018	3082	2030	1102	1086	1430	2278	2692	2022	5827	5609	4770	4782	4797	4140	3798	3615	3533	3579	3561	3595	9760	6587	5211	5572	6254	5183	4489	4193	4028	4030	3973	4107	4218	4489	4667	5608	6027	5955	4995	4114	3991	4129	4709	4947	4199
RF.N	1577	316	167	368	851	602	373	313	268	309	384	603	842	1248	1984	2720	3676	2340.816	1489.756	842.651	744.532	1039.401	1630.144	1845.446	1428.831	5320	4786	3914	3894	3745	3735	3540	3398	3307	3279	3262	3300	6897	5102	4081	4262	4639	4393	3987	3814	3603	3589	3647	3903	4252	4637	5277	6563	8074	5649.118	4271.145	2955.685	2219.13	2536.989	3055.637	3234.243	2854.686
FBNC.OQ	142.101	16.103	9.523	19.562	33.903	23.777	12.671	7.607	6.908	8.223	10.985	17.32	23.565	31.907	48.895	61.303	69.658	54.671	32.838	20.303	18.907	23.871	35.72	34.22	26.488	346.658	324.854	246.395	218.122	216.204	207.43	164.711	123.38	119.747	131.609	136.526	152.52	488.759	340.957	255.918	237.684	250.107	231.207	177.382	130.987	126.655	139.832	147.511	152.52	155.768	159.261	155.991	147.862	148.942	126.925	99.757	81.049	73.888	73.261	76.773	72.915	61.591
FCNCA.OQ	3679	467	60.676	95.857	92.642	36.857	43.794	43.082	44.304	50.351	56.618	90.148	144.192	195.125	227.644	314.945	423.714	353.737	218.151	133.826	148.537	214.018	346.51	342.828	281.542	6712	2946	1390.334										10391	3413	1451.01	1484.026	1404.011	1245.757	1103.69	987.757	969.209	760.448	796.804	1004.836	1015.159	969.368	738.159	813.351	902.181	828.508	669.54	521.117	510.477	596.169	715.427	708.17	633.891
MTB.N	3109	425.164	114.006	326.395	749.329	526.409	386.751	425.984	328.257	280.431	284.105	343.169	402.331	462.269	669.449	1337.795	1694.576	1496.552	994.351	564.16	527.81	594.514	943.597	918.597	719.234	7115	5821.956	3824.778	3866.317	4130.264	4072.302	3781.044	3469.887	2842.587	2676.446	2673.229	2598.516	10224	6247.12	3938.784	4192.712	4879.593	4598.711	4167.795	3895.871	3170.844	2956.877	2957.334	2941.685	2792.087	2729.795	2725.197	3277.591	3544.813	3314.093	2788.694	2298.732	2126.565	1842.099	2101.885	1772.784	1478.631
PCLB.PK	2.453334	0.725181					0.547174	0.549199	0.572558	0.582437	0.651828	0.838937	1.276256	2.062489	3.117886	5.587033		6.723	4.717	3.516	3.905	5.983	9.192	10.573	9.238													13.872796	12.102573					7.805292	7.608806	7.823629	7.377987	7.596287	8.213362	9.09345	9.752007	10.668059	12.457812		13.265	11.559	10.636	11.287	13.104	15.823	17.805	16.129
FFBC.OQ	275.234	65.863	31.099	68.452	123.324	91.147	49.528	33.279	23.257	19.234	16.888	27.589	44.921	67.992	57.245	67.103	87.942	80.452	67.73	56.29	59.997	78.251	126.78	145.424	117.194	627.77	519.143	452.118	456.511	484.254	449.235	283.545	272.671	246.502	228.625	228.32	253.341	903.004	585.006	483.217	524.963	607.578	540.382	333.073	305.95	269.759	247.859	245.208	280.93	308.817	343.502	233.228	183.305	206.442	205.525	200.697	196.472	200.686	241.008	289.745	313.303	281.018
FLIC.OQ	68.618	18.497	16.152	29.188	43.681	35.73	21.709	18.002	16.529	15.048	12.364	16.127	17.567	16.774	18.334	16.743	16.269	12.949	7.426	3.665	3.878	5.111	9.451	13.106	9.513	86.865	115.713	106.807	102.028	100.169	102.507	96.556	86.121	75.606	66.928	62.487	60.102	155.483	134.21	122.959	131.216	143.85	138.237	118.265	104.123	92.135	81.976	74.851	76.229	76.312	72.403	66.274	59.686	53.023	49	42.689	38.407	36.968	36.929	37.989	38.822	33.963
FRME.OQ	348.486	84.803	35.952	66.381	108.745	69.087	37.612	26.839	24.794	21.842	16.569	23.613	37.89	97.602	137.509	90.089	222.424	182.965	113.813	51.585	52.388	53.759	105.592	114.416	89.71	545.4	503.448	410.68	382.127	356.66	338.857	277.284	226.473	196.404	187.037	154.265	152.336	893.886	605.006	446.632	448.508	465.405	407.944	314.896	253.312	221.198	208.879	170.834	175.949	181.245	199.171	229.955	219.473	230.561	207.733	176.945	156.809	155.53	146.682	119.536	115.862	100.463
USB.N	12611	3217	993	2015	4442	3254	1966	1468	1401	1453	1681	2138	2516	2579	3020	4686	6447	5522	3496	2075	2068.7	2679.6	4595.7	5894.7	4729.1	17396	14728	12494	12825	13052	12919	12241	11528	11214	10775	10604	10969	30007	17945	13487	14840	17494	16173	14346	13134	12402	12228	12285	12883	12639	12158	11538	12418	13136	12263	10551	9186	9258	9493.9	10946.4	11885.1	10510.6
SRCE.OQ	138.26	30.347	18.134	37.211	59.011	43.41	26.754	22.101	18.163	18.225	22.768	30.309	39.123	53.129	72.2	103.148	134.677	102.561	70.104	52.749	59.07	80.817	126.957	134.288	100.726	278.647	263.469	236.638	225.82	223.866	213.906	185.631	169.659	166.521	160.329	62.44		416.907	293.816	254.772	263.031	282.877	257.316	212.385	191.76	184.684	178.554	179.585	182.085	187.523	200.626	200.412	235.308	253.587	208.994	168.532	151.437	162.322	199.503	245.566	238.222	200.429
FHN.N	1560	291	164	236	414.153	325.704	147.616	88.825	82.685	81.531	94.679	110.286	131.605	149.448	216.471	711.6	1365.3	1332.2	856.147	310.491	247.586	286.581	512.596	764.695	617.654	2540	2392	1994	1662	1210.187	1220.317	842.314	729.084	653.72	627.718	637.374	688.667	4100	2683	2158	1898	1631.526	1556.576	989.93	817.909	736.405	709.249	732.053	798.953	832.437	880.286	992.939	1606.7	2305.9	2329.1	1840.174	1166.802	1053.37	1042.195	1201.51	1363.046	1207.164
CWBC.OQ	19.989	3.422	1.302	1.595	2.559	1.484	1.137	1.096	1.047	1.156	1.385	1.883	2.942	4.283	6.627	7.278	8.058	6.559	4.139	1.978	2.29	2.728	4.138	4.247	3.344	81.12	79.566	72.554	64.423	63.772	62.703	56.239	45.58	40.775	39.883	33.451	29.937	102.418	82.988	73.856	66.018	66.331	64.187	57.376	46.676	41.822	41.039	34.836	31.82	34.299	36.013	40.734	31.845	32.566	30.932	26.07	16.799	14.97	14.535	14.577	13.56	11.353
FULT.OQ	418.95	83.204	59.682	113.671	176.917	128.058	93.502	82.328	83.795	81.211	82.495	103.168	133.538	186.627	265.513	343.346	450.833	378.944	213.219	135.994	131.094	158.219	227.962	243.874	199.128	854.286	781.634	663.731	629.207	648.389	630.456	575.364	520.772	499.994	514.867	544.474	544.328	1273.236	864.838	723.412	742.878	825.306	758.514	668.866	603.1	583.789	596.078	609.689	647.496	693.698	745.373	786.467	867.494	939.577	864.507	625.767	493.643	435.531	469.288	518.68	519.661	465.298
GBCI.N	325.973	41.261	18.558	27.315	42.773	35.531	29.864	29.631	29.275	26.966	28.758	35.714	44.494	53.634	57.167	90.372	121.291	95.038	59.978	39.892	38.478	47.522	65.546	37.357	27.635	691.682	788.379	662.516	599.749	503.404	433.465	345.158	314.522	290.406	272.953	234.818	218.043	1017.655	829.64	681.074	627.064	546.177	468.996	375.022	344.153	319.681	299.919	263.576	253.757	280.109	288.402	302.494	302.985	304.76	253.326	189.985	147.285	130.83	133.989	138.03	78.837	64.719
GHI.N	69.066763	30.464451	21.943885	21.215888	24.717294	23.190012	22.155443	15.469639	14.826217	11.165911	6.990844	5.530995	5.4417	1.887823	4.202126	4.106417	2.595616	1.30376	1.176	1.18	1.615	3.703	1.895	1.443	0.088																																					
GSBC.OQ	103.62	27.363	20.752	40.565	54.602	37.757	27.905	22.119	15.997	15.801	19.203	28.377	35.146	47.85	66.605	73.231	92.466	80.854	56.097	37.233	31.729	30.337	45.907	48.861	35.464	193.215	199.614	177.921	177.138	180.392	168.192	155.156	164.634	168.354	167.561	159.592	165.131	296.835	226.977	198.673	217.703	234.994	205.949	183.061	185.175	184.351	183.362	178.795	193.508	198.667	173.191	155.868	144.814	163.871	150.081	114.495	87.059	76.179	80.161	89.497	86.15	68.038
HWC.OQ	522.898	87.06	49.023	115.458	230.565	179.43	108.269	73.051	54.472	38.119	41.479	51.682	70.971	82.345	95.3	126.002	140.236	119.863	74.819	57.27	57.961	72.053	101.362	94.25	83.962	1108.706	1147.411	944.414	955.523	909.991	848.838	792.312	659.116	625.174	654.694	691.141	722.452	1620.497	1137.063	982.258	1057.981	1125.782	1028.268	900.581	732.167	679.646	692.813	722.21	762.549	592.204	352.558	323.727	335.437	345.697	344.063	263.631	226.774	218.149	230.782	234.869	216.945	207.673
HARL.PK	3.876	3.157	4.833	6.684	7.053	6.767	7.496	8.147	8.823	10.161	12.527	15.095	19.034	21.747	24.886	29.016	28.806	26.366	22.747	20.391	21.672	24.225	26.583	22.795	20.199													34.232	27.315	27.323	29.62	31.624	30.329	28.56	28.704	28.792	29.238	30.587	34.533	37.573	40.018	41.343	43.076	40.289	39.09	35.902	32.636	33.122	35.218	36.304	33.182	29.716
ORRF.OQ	44.991	9.024	6.721	16.024	23.699	13.511	7.644	5.417	4.301	4.159	5.011	7.548	10.754	12.688	16.5	19.408	22.986	17.371	9.537	6.986	6.757	7.985	10.677	10.318		104.906	99.63	86.974	83.607	69.295	51.37	43.371	36.545	34.334	34.024	8.513	37.888	149.897	108.654	93.695	99.631	92.994	65.667	51.453	41.962	38.635	38.183	37.098	45.436	60.361	58.423	53.07	52.313	53.106	44.788	32.415	25.892	23.484	23.173	23.978	21.758	
HFWA.OQ	59.31	8.072	7.042	13.323	18.168	12.413	8.346	6.006	6.12	5.681	3.724	4.534	6.582	8.511	11.645	18.606	25.77	19.465	11.547	7.217	7.334	10.224	18.558	19.401	13.066	225.155	219.385	205.789	200.997	199.682	186.946	139.534	132.506	129.619	115.425	67.704	64.575	284.465	227.457	212.831	214.32	217.85	199.406	147.709	138.512	135.739	121.106	71.428	69.109	74.12	59.522	53.341	56.948	62.391	55.237	45.428	38.944	37.151	40.427	45.19	44.242	36.524
HIFS.OQ	125.96	32.894	8.026	21.158	40.05	28.384	17.261	13.385	10.91	9.936	10.502	10.937	12.618	15.098	17.599	21.98	26.073	21.212	12.389	9.154	9.035	10.422	12.571	12.105	9.968													174.262	139.028	110.491	106.362	106.952	94.169	81.059	71.769	61.859	56.193	49.342	48.831	48.444	46.825	44.798	43.309	42.101	37.089	30.418	26.151	25.558	25.801	25.968	23.946	20.259
FUNC.OQ	24.286	4.789	5.714	9.655	11.529	8.112	7.371	8.223	9.407	10.87	11.732	13.965	21.206	29.164	32.104	43.043	49.331	39.335	29.413	24.016	23.601	25.702	33.378	35.039	27.146	56.87	57.633	52.542										81.156	62.422	58.256	58.201	57.92	52.294	46.949	45.863	45.032	46.386	49.914	53.111	59.496	70.747	85.342	95.216	93.565	80.269	69.756	60.682	57.703	57.589	63.229	63.516	54.843
HBAN.OQ	3477	696	89	423	988	760	431	262.795	163.784	139.321	156.029	219.739	341.056	526.587	813.855	1266.631	1441.451	1051.342	679.354	435.941	456.77	543.621	939.501	1166.073	984.24	5439	5273	4102	3224	3213	3189	3002	2369.318	1950.737	3674.282	1731.9	1710.524	8916	5969	4191	3647	4201	3949	3433	2632.113	2114.521	1976.462	1860.637	1930.263	1970.226	2145.392	2238.142	2798.322	2742.963	2070.519	1641.765	1347.315	1305.756	1293.195	1654.789	2108.505	2026.002
AROW.OQ	57.732	11.308	5.195	12.694	21.71	12.485	7.006	5.356	4.813	5.767	7.922	11.957	18.679	23.695	26.492	32.277	36.311	34.743	24.114	19.206	21.61	25.106	33.172	37.368	29.266	104.832	118.343	110.355	99.202	88.049	84.018	77.651	72.839	65.925	61.094	56.216	57.422	162.564	129.651	115.55	111.896	109.759	96.503	84.657	76.915	70.738	66.861	64.138	69.379	76.791	84.972	86.857	89.508	86.577	80.611	72.127	68.443	70.731	75.145	78.357	75.624	67.135
ASB.N	918.479	187.931	72.334	149.883	336.936	274.557	145.385	84.295	77.384	55.778	63.44	92.292	128.791	172.347	255.251	430.561	631.899	609.83	421.77	214.495	216.602	290.84	458.637	547.59	418.775	1039.573	957.321	725.855	762.957	835.674	879.58	741.22	707.273	676.278	680.967	645.543	625.992	1958.052	1145.252	798.189	912.84	1172.61	1154.137	886.605	791.568	753.662	736.745	708.983	718.284	741.622	806.126	981.256	1126.709	1275.712	1279.379	1094.025	767.122	727.364	792.106	880.622	931.157	814.52
INDB.OQ	189.205	29.591	13.717	34.341	53.879	25.536	18.334	18.793	20.617	20.417	23.336	23.393	28.672	38.763	51.995	58.926	63.555	65.038	49.818	36.797	32.533	40.794	54.478	55.419	50.178	606.521	613.249	401.559	367.728	393.135	298.165	258.86	227.844	214.928	196.042	182.578	172.799	795.726	642.84	415.276	402.069	447.014	323.701	277.194	246.637	235.545	216.459	205.914	196.192	195.751	202.724	202.689	175.44	158.524	167.693	155.661	134.613	128.306	140.825	145.069	127.566	112.006
IBCP.OQ	83.348	19.447	8.315	16.217	26.347	17.491	9.123	6.882	5.856	7.299	9.162	13.143	20.193	37.198	50.533	73.587	102.663	93.698	63.099	42.99	44.113	48.008	62.46	67.865	58.73	156.329	149.561	129.765	123.612	122.581	113.282	89.186	79.641	74.986	73.256	77.959	86.255	239.677	169.008	138.08	139.829	148.928	130.773	98.309	86.523	80.842	80.555	87.121	99.398	114.762	148.851	189.056	203.736	223.254	216.895	193.035	154.226	139.366	129.815	134.502	132.841	125.51
2

Take a look at the pivot_longer function in the tidyr library.

3

i am trying but not getting the desired dataframe

4

We lack context, is there any structure here ? there seems to be a distinct lack of column names and rownames that would make sense.

6

please find attached the sample data frame

	var1	var1	var1	var1	var2	var2	var2	var2	var3	var3	var3	var3
	2024	2023	2022	2021	2024	2023	2022	2021	2024	2023	2022	2021
HFBK.PK	5.70647	1.078357	1.549388	2.379481					27.153711	20.426367	19.140877	17.767558
FDLB.PK	4.004	3.827	6.022	8.501					8.483	6.65	9.034	11.455
FNMA.PK					28773	29423	29587	24866	137392	119641	99512	107288
FITB.OQ	3933	978	441	790	5827	5609	4770	4782	9760	6587	5211	5572
RF.N	1577	316	167	368	5320	4786	3914	3894	6897	5102	4081	4262
FBNC.OQ	142.101	16.103	9.523	19.562	346.658	324.854	246.395	218.122	488.759	340.957	255.918	237.684
FCNCA.OQ	3679	467	60.676	95.857	6712	2946	1390.334		10391	3413	1451.01	1484.026
MTB.N	3109	425.164	114.006	326.395	7115	5821.956	3824.778	3866.317	10224	6247.12	3938.784	4192.712
PCLB.PK	2.453334	0.725181							13.872796	12.102573		
FFBC.OQ	275.234	65.863	31.099	68.452	627.77	519.143	452.118	456.511	903.004	585.006	483.217	524.963
FLIC.OQ	68.618	18.497	16.152	29.188	86.865	115.713	106.807	102.028	155.483	134.21	122.959	131.216
FRME.OQ	348.486	84.803	35.952	66.381	545.4	503.448	410.68	382.127	893.886	605.006	446.632	448.508
USB.N	12611	3217	993	2015	17396	14728	12494	12825	30007	17945	13487	14840
SRCE.OQ	138.26	30.347	18.134	37.211	278.647	263.469	236.638	225.82	416.907	293.816	254.772	263.031
FHN.N	1560	291	164	236	2540	2392	1994	1662	4100	2683	2158	1898
CWBC.OQ	19.989	3.422	1.302	1.595	81.12	79.566	72.554	64.423	102.418	82.988	73.856	66.018
FULT.OQ	418.95	83.204	59.682	113.671	854.286	781.634	663.731	629.207	1273.236	864.838	723.412	742.878
GBCI.N	325.973	41.261	18.558	27.315	691.682	788.379	662.516	599.749	1017.655	829.64	681.074	627.064
GHI.N	69.066763	30.464451	21.943885	21.215888								
GSBC.OQ	103.62	27.363	20.752	40.565	193.215	199.614	177.921	177.138	296.835	226.977	198.673	217.703
HWC.OQ	522.898	87.06	49.023	115.458	1108.706	1147.411	944.414	955.523	1620.497	1137.063	982.258	1057.981
HARL.PK	3.876	3.157	4.833	6.684					34.232	27.315	27.323	29.62
ORRF.OQ	44.991	9.024	6.721	16.024	104.906	99.63	86.974	83.607	149.897	108.654	93.695	99.631
HFWA.OQ	59.31	8.072	7.042	13.323	225.155	219.385	205.789	200.997	284.465	227.457	212.831	214.32
HIFS.OQ	125.96	32.894	8.026	21.158					174.262	139.028	110.491	106.362
FUNC.OQ	24.286	4.789	5.714	9.655	56.87	57.633	52.542		81.156	62.422	58.256	58.201
HBAN.OQ	3477	696	89	423	5439	5273	4102	3224	8916	5969	4191	3647
AROW.OQ	57.732	11.308	5.195	12.694	104.832	118.343	110.355	99.202	162.564	129.651	115.55	111.896
ASB.N	918.479	187.931	72.334	149.883	1039.573	957.321	725.855	762.957	1958.052	1145.252	798.189	912.84
INDB.OQ	189.205	29.591	13.717	34.341	606.521	613.249	401.559	367.728	795.726	642.84	415.276	402.069
IBCP.OQ	83.348	19.447	8.315	16.217	156.329	149.561	129.765	123.612	239.677	169.008	138.08	139.829
BOH.N	313.35	56.808	29.426	50.102	497.025	540.558	497.29	496.322	810.375	597.366	526.716	546.424

test_data.pdf (19.0 KB)

7

that doesnt really help anything.
Do you understand if your data has any meaning ? can you read it and extract useful information from it ?

8

Here is my attempt to make sense of the data. Does this help at all?

DF <- read.csv("~/R/Play/Dummy.csv", sep = "\t")
LINES <- readLines("~/R/Play/Dummy.csv", n = 2)
library(tidyverse)
#> Warning: package 'ggplot2' was built under R version 4.3.3
Header1 <- str_split_1(LINES[1], "\t")
Header2 <- str_split_1(LINES[2], "\t")
NewHeader <- paste(Header1, Header2[2:13], sep = "_")
NewHeader
#>  [1] "var1_2024" "var1_2023" "var1_2022" "var1_2021" "var2_2024" "var2_2023"
#>  [7] "var2_2022" "var2_2021" "var3_2024" "var3_2023" "var3_2022" "var3_2021"
colnames(DF) <- NewHeader
DF <- DF[-1, ]
DF <- rownames_to_column(DF)
DFlong <- pivot_longer(DF, cols = -rowname)
DFlong <- separate(DFlong,col = "name", into = c("Var","Year"))
DFlong
#> # A tibble: 384 × 4
#>    rowname Var   Year  value
#>    <chr>   <chr> <chr> <dbl>
#>  1 HFBK.PK var1  2024   5.71
#>  2 HFBK.PK var1  2023   1.08
#>  3 HFBK.PK var1  2022   1.55
#>  4 HFBK.PK var1  2021   2.38
#>  5 HFBK.PK var2  2024  NA   
#>  6 HFBK.PK var2  2023  NA   
#>  7 HFBK.PK var2  2022  NA   
#>  8 HFBK.PK var2  2021  NA   
#>  9 HFBK.PK var3  2024  27.2 
#> 10 HFBK.PK var3  2023  20.4 
#> # ℹ 374 more rows

Created on 2024-05-03 with reprex v2.0.2

2 Likes
9

I read it the first data example using fread from {data.table} which completely ignored the first row and decided that the second row was the column names. fread can be a bit idiosyncratic.

suppressMessages(library(data.table))
suppressMessages(library(tidyverse))
suppressMessages(library(janitor))

DT <- fread("zeeshan0112.csv")  %>%  clean_names()

Anyway I got something looking like this.

head(DT[1:5, 1:5])
bank interest_expense interest_expense_2 interest_expense_3 interest_expense_4
   <char>            <num>              <num>              <num>              <num>
1: HFBK.PK          5.70647           1.078357           1.549388           2.379481
2: FDLB.PK          4.00400           3.827000           6.022000           8.501000
3: FNMA.PK               NA                 NA                 NA                 NA
4: FITB.OQ       3933.00000         978.000000         441.000000         790.000000
5:    RF.N       1577.00000         316.000000         167.000000         368.000000

Then when I melted it (pivot_longer in {dplyr} terms).

melt(DT, id.vars = "bank")
         bank                                  variable      value
       <char>                                    <fctr>      <num>
   1: HFBK.PK                          interest_expense    5.70647
   2: FDLB.PK                          interest_expense    4.00400
   3: FNMA.PK                          interest_expense         NA
   4: FITB.OQ                          interest_expense 3933.00000
   5:    RF.N                          interest_expense 1577.00000
  ---                                                             
1918: HBAN.OQ interest_dividend_income_finance_total_25 2026.00200
1919: AROW.OQ interest_dividend_income_finance_total_25   67.13500
1920:   ASB.N interest_dividend_income_finance_total_25  814.52000
1921: INDB.OQ interest_dividend_income_finance_total_25  112.00600
1922: IBCP.OQ interest_dividend_income_finance_total_25  125.51000

Is this anything close to what you want? It totally ignores the dates on the first row.

2 Likes
10

[quote="FJCC, post:8, topic:186271"]

	var1	var1	var1	var1	var2	var2	var2	var2	var3	var3	var3	var3
	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021
HFBK.PK	5.70647	1.078357	1.549388	2.379481					27.153711	20.426367	19.140877	17.767558
FDLB.PK	4.004	3.827	6.022	8.501					8.483	6.65	9.034	11.455
FNMA.PK					28773	29423	29587	24866	137392	119641	99512	107288
FITB.OQ	3933	978	441	790	5827	5609	4770	4782	9760	6587	5211	5572
RF.N	1577	316	167	368	5320	4786	3914	3894	6897	5102	4081	4262
FBNC.OQ	142.101	16.103	9.523	19.562	346.658	324.854	246.395	218.122	488.759	340.957	255.918	237.684
FCNCA.OQ	3679	467	60.676	95.857	6712	2946	1390.334		10391	3413	1451.01	1484.026
MTB.N	3109	425.164	114.006	326.395	7115	5821.956	3824.778	3866.317	10224	6247.12	3938.784	4192.712
PCLB.PK	2.453334	0.725181							13.872796	12.102573		
FFBC.OQ	275.234	65.863	31.099	68.452	627.77	519.143	452.118	456.511	903.004	585.006	483.217	524.963
FLIC.OQ	68.618	18.497	16.152	29.188	86.865	115.713	106.807	102.028	155.483	134.21	122.959	131.216
FRME.OQ	348.486	84.803	35.952	66.381	545.4	503.448	410.68	382.127	893.886	605.006	446.632	448.508
USB.N	12611	3217	993	2015	17396	14728	12494	12825	30007	17945	13487	14840
SRCE.OQ	138.26	30.347	18.134	37.211	278.647	263.469	236.638	225.82	416.907	293.816	254.772	263.031
FHN.N	1560	291	164	236	2540	2392	1994	1662	4100	2683	2158	1898
CWBC.OQ	19.989	3.422	1.302	1.595	81.12	79.566	72.554	64.423	102.418	82.988	73.856	66.018
FULT.OQ	418.95	83.204	59.682	113.671	854.286	781.634	663.731	629.207	1273.236	864.838	723.412	742.878
GBCI.N	325.973	41.261	18.558	27.315	691.682	788.379	662.516	599.749	1017.655	829.64	681.074	627.064
GHI.N	69.066763	30.464451	21.943885	21.215888								
GSBC.OQ	103.62	27.363	20.752	40.565	193.215	199.614	177.921	177.138	296.835	226.977	198.673	217.703
HWC.OQ	522.898	87.06	49.023	115.458	1108.706	1147.411	944.414	955.523	1620.497	1137.063	982.258	1057.981
HARL.PK	3.876	3.157	4.833	6.684					34.232	27.315	27.323	29.62
ORRF.OQ	44.991	9.024	6.721	16.024	104.906	99.63	86.974	83.607	149.897	108.654	93.695	99.631
HFWA.OQ	59.31	8.072	7.042	13.323	225.155	219.385	205.789	200.997	284.465	227.457	212.831	214.32
HIFS.OQ	125.96	32.894	8.026	21.158					174.262	139.028	110.491	106.362
FUNC.OQ	24.286	4.789	5.714	9.655	56.87	57.633	52.542		81.156	62.422	58.256	58.201
HBAN.OQ	3477	696	89	423	5439	5273	4102	3224	8916	5969	4191	3647
AROW.OQ	57.732	11.308	5.195	12.694	104.832	118.343	110.355	99.202	162.564	129.651	115.55	111.896
ASB.N	918.479	187.931	72.334	149.883	1039.573	957.321	725.855	762.957	1958.052	1145.252	798.189	912.84
INDB.OQ	189.205	29.591	13.717	34.341	606.521	613.249	401.559	367.728	795.726	642.84	415.276	402.069
IBCP.OQ	83.348	19.447	8.315	16.217	156.329	149.561	129.765	123.612	239.677	169.008	138.08	139.829
BOH.N	313.35	56.808	29.426	50.102	497.025	540.558	497.29	496.322	810.375	597.366	526.716	546.424

Does it make sense now?

Thanks in advance

11

Thank a lot for your help.

Apart from these three columns, I need an additional column consisting of year.

12

I am not sure of what you want.

If I edit your last data set to this and then melt it we get:

suppressMessages(library(data.table)) 
suppressMessages(library(tidyverse))
suppressMessages(library(janitor))

DTC <- as.data.table(structure(list(bank = c("HFBK.PK", "FDLB.PK", "FNMA.PK", "FITB.OQ", 
"RF.N", "FBNC.OQ", "FCNCA.OQ", "MTB.N", "PCLB.PK", "FFBC.OQ", 
"FLIC.OQ", "FRME.OQ", "USB.N", "SRCE.OQ", "FHN.N", "CWBC.OQ", 
"FULT.OQ", "GBCI.N", "GHI.N", "GSBC.OQ", "HWC.OQ", "HARL.PK", 
"ORRF.OQ", "HFWA.OQ", "HIFS.OQ", "FUNC.OQ", "HBAN.OQ", "AROW.OQ", 
"ASB.N", "INDB.OQ", "IBCP.OQ", "BOH.N"), Mar.2024 = c(5.70647, 
4.004, NA, 3933, 1577, 142.101, 3679, 3109, 2.453334, 275.234, 
68.618, 348.486, 12611, 138.26, 1560, 19.989, 418.95, 325.973, 
69.066763, 103.62, 522.898, 3.876, 44.991, 59.31, 125.96, 24.286, 
3477, 57.732, 918.479, 189.205, 83.348, 313.35), Mar.2023 = c(1.078357, 
3.827, NA, 978, 316, 16.103, 467, 425.164, 0.725181, 65.863, 
18.497, 84.803, 3217, 30.347, 291, 3.422, 83.204, 41.261, 30.464451, 
27.363, 87.06, 3.157, 9.024, 8.072, 32.894, 4.789, 696, 11.308, 
187.931, 29.591, 19.447, 56.808), Mar.2022 = c(1.549388, 6.022, 
NA, 441, 167, 9.523, 60.676, 114.006, NA, 31.099, 16.152, 35.952, 
993, 18.134, 164, 1.302, 59.682, 18.558, 21.943885, 20.752, 49.023, 
4.833, 6.721, 7.042, 8.026, 5.714, 89, 5.195, 72.334, 13.717, 
8.315, 29.426), Mar.2021 = c(2.379481, 8.501, NA, 790, 368, 19.562, 
95.857, 326.395, NA, 68.452, 29.188, 66.381, 2015, 37.211, 236, 
1.595, 113.671, 27.315, 21.215888, 40.565, 115.458, 6.684, 16.024, 
13.323, 21.158, 9.655, 423, 12.694, 149.883, 34.341, 16.217, 
50.102), Mar.2024.1 = c(NA, NA, 28773, 5827, 5320, 346.658, 6712, 
7115, NA, 627.77, 86.865, 545.4, 17396, 278.647, 2540, 81.12, 
854.286, 691.682, NA, 193.215, 1108.706, NA, 104.906, 225.155, 
NA, 56.87, 5439, 104.832, 1039.573, 606.521, 156.329, 497.025
), Mar.2023.1 = c(NA, NA, 29423, 5609, 4786, 324.854, 2946, 5821.956, 
NA, 519.143, 115.713, 503.448, 14728, 263.469, 2392, 79.566, 
781.634, 788.379, NA, 199.614, 1147.411, NA, 99.63, 219.385, 
NA, 57.633, 5273, 118.343, 957.321, 613.249, 149.561, 540.558
), Mar.2022.1 = c(NA, NA, 29587, 4770, 3914, 246.395, 1390.334, 
3824.778, NA, 452.118, 106.807, 410.68, 12494, 236.638, 1994, 
72.554, 663.731, 662.516, NA, 177.921, 944.414, NA, 86.974, 205.789, 
NA, 52.542, 4102, 110.355, 725.855, 401.559, 129.765, 497.29), 
    Mar.2021.1 = c(NA, NA, 24866, 4782, 3894, 218.122, NA, 3866.317, 
    NA, 456.511, 102.028, 382.127, 12825, 225.82, 1662, 64.423, 
    629.207, 599.749, NA, 177.138, 955.523, NA, 83.607, 200.997, 
    NA, NA, 3224, 99.202, 762.957, 367.728, 123.612, 496.322), 
    Mar.2024.2 = c(27.153711, 8.483, 137392, 9760, 6897, 488.759, 
    10391, 10224, 13.872796, 903.004, 155.483, 893.886, 30007, 
    416.907, 4100, 102.418, 1273.236, 1017.655, NA, 296.835, 
    1620.497, 34.232, 149.897, 284.465, 174.262, 81.156, 8916, 
    162.564, 1958.052, 795.726, 239.677, 810.375), Mar.2023.2 = c(20.426367, 
    6.65, 119641, 6587, 5102, 340.957, 3413, 6247.12, 12.102573, 
    585.006, 134.21, 605.006, 17945, 293.816, 2683, 82.988, 864.838, 
    829.64, NA, 226.977, 1137.063, 27.315, 108.654, 227.457, 
    139.028, 62.422, 5969, 129.651, 1145.252, 642.84, 169.008, 
    597.366), Mar.2022.2 = c(19.140877, 9.034, 99512, 5211, 4081, 
    255.918, 1451.01, 3938.784, NA, 483.217, 122.959, 446.632, 
    13487, 254.772, 2158, 73.856, 723.412, 681.074, NA, 198.673, 
    982.258, 27.323, 93.695, 212.831, 110.491, 58.256, 4191, 
    115.55, 798.189, 415.276, 138.08, 526.716), Mar.2021.2 = c(17.767558, 
    11.455, 107288, 5572, 4262, 237.684, 1484.026, 4192.712, 
    NA, 524.963, 131.216, 448.508, 14840, 263.031, 1898, 66.018, 
    742.878, 627.064, NA, 217.703, 1057.981, 29.62, 99.631, 214.32, 
    106.362, 58.201, 3647, 111.896, 912.84, 402.069, 139.829, 
    546.424)), class = "data.frame", row.names = c(NA, -32L))
)

DTC_M <- melt(DTC, id.vars = "bank")

which gives us this

  bank   variable      value
      <char>     <fctr>      <num>
  1: HFBK.PK   mar_2024    5.70647
  2: FDLB.PK   mar_2024    4.00400
  3: FNMA.PK   mar_2024         NA
  4: FITB.OQ   mar_2024 3933.00000
  5:    RF.N   mar_2024 1577.00000
 ---                              
380: AROW.OQ mar_2021_3  111.89600
381:   ASB.N mar_2021_3  912.84000
382: INDB.OQ mar_2021_3  402.06900
383: IBCP.OQ mar_2021_3  139.82900
384:   BOH.N mar_2021_3  546.42400

My guess is that this is not what you want but I am not sure how to handle dates if we are using your first data set. Does @ FJCC's approach make sense?

1 Like
13

Not yet, for me at least: You have 12 column names, but there's no spatial regularity to the rows, so when there's data missing in a row, it's not clear what columns the missing data corresponds to.

15 
var1	var1	var1	var1	var2	var2	var2	var2	var3	var3	var3	var3
bank	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021
HFBK.PK	5.70647	1.078357	1.549388	2.379481	N/A	N/A	N/A	N/A	27.153711	20.426367	19.140877	17.767558
FDLB.PK	4.004	3.827	6.022	8.501	N/A	N/A	N/A	N/A	8.483	6.65	9.034	11.455
FNMA.PK	N/A	N/A	N/A	N/A	28773	29423	29587	24866	137392	119641	99512	107288
FITB.OQ	3933	978	441	790	5827	5609	4770	4782	9760	6587	5211	5572
RF.N	1577	316	167	368	5320	4786	3914	3894	6897	5102	4081	4262
FBNC.OQ	142.101	16.103	9.523	19.562	346.658	324.854	246.395	218.122	488.759	340.957	255.918	237.684
FCNCA.OQ	3679	467	60.676	95.857	6712	2946	1390.334	N/A	10391	3413	1451.01	1484.026
MTB.N	3109	425.164	114.006	326.395	7115	5821.956	3824.778	3866.317	10224	6247.12	3938.784	4192.712
PCLB.PK	2.453334	0.725181	N/A	N/A	N/A	N/A	N/A	N/A	13.872796	12.102573	N/A	N/A
FFBC.OQ	275.234	65.863	31.099	68.452	627.77	519.143	452.118	456.511	903.004	585.006	483.217	524.963
FLIC.OQ	68.618	18.497	16.152	29.188	86.865	115.713	106.807	102.028	155.483	134.21	122.959	131.216
FRME.OQ	348.486	84.803	35.952	66.381	545.4	503.448	410.68	382.127	893.886	605.006	446.632	448.508
USB.N	12611	3217	993	2015	17396	14728	12494	12825	30007	17945	13487	14840
SRCE.OQ	138.26	30.347	18.134	37.211	278.647	263.469	236.638	225.82	416.907	293.816	254.772	263.031
FHN.N	1560	291	164	236	2540	2392	1994	1662	4100	2683	2158	1898
CWBC.OQ	19.989	3.422	1.302	1.595	81.12	79.566	72.554	64.423	102.418	82.988	73.856	66.018
FULT.OQ	418.95	83.204	59.682	113.671	854.286	781.634	663.731	629.207	1273.236	864.838	723.412	742.878
GBCI.N	325.973	41.261	18.558	27.315	691.682	788.379	662.516	599.749	1017.655	829.64	681.074	627.064
GHI.N	69.066763	30.464451	21.943885	21.215888	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
GSBC.OQ	103.62	27.363	20.752	40.565	193.215	199.614	177.921	177.138	296.835	226.977	198.673	217.703
HWC.OQ	522.898	87.06	49.023	115.458	1108.706	1147.411	944.414	955.523	1620.497	1137.063	982.258	1057.981
HARL.PK	3.876	3.157	4.833	6.684	N/A	N/A	N/A	N/A	34.232	27.315	27.323	29.62
ORRF.OQ	44.991	9.024	6.721	16.024	104.906	99.63	86.974	83.607	149.897	108.654	93.695	99.631
HFWA.OQ	59.31	8.072	7.042	13.323	225.155	219.385	205.789	200.997	284.465	227.457	212.831	214.32
HIFS.OQ	125.96	32.894	8.026	21.158	N/A	N/A	N/A	N/A	174.262	139.028	110.491	106.362
FUNC.OQ	24.286	4.789	5.714	9.655	56.87	57.633	52.542	N/A	81.156	62.422	58.256	58.201
HBAN.OQ	3477	696	89	423	5439	5273	4102	3224	8916	5969	4191	3647
AROW.OQ	57.732	11.308	5.195	12.694	104.832	118.343	110.355	99.202	162.564	129.651	115.55	111.896
ASB.N	918.479	187.931	72.334	149.883	1039.573	957.321	725.855	762.957	1958.052	1145.252	798.189	912.84
INDB.OQ	189.205	29.591	13.717	34.341	606.521	613.249	401.559	367.728	795.726	642.84	415.276	402.069
IBCP.OQ	83.348	19.447	8.315	16.217	156.329	149.561	129.765	123.612	239.677	169.008	138.08	139.829
BOH.N	313.35	56.808	29.426	50.102	497.025	540.558	497.29	496.322	810.375	597.366	526.716	546.424

[/quote]

16

Thanks, that's much better, although better still if you could share the contents of a data frame in the future. For example, I placed the text you posted above:

original data 
var1	var1	var1	var1	var2	var2	var2	var2	var3	var3	var3	var3
bank	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021	Mar-2024	Mar-2023	Mar-2022	Mar-2021
HFBK.PK	5.70647	1.078357	1.549388	2.379481	N/A	N/A	N/A	N/A	27.153711	20.426367	19.140877	17.767558
FDLB.PK	4.004	3.827	6.022	8.501	N/A	N/A	N/A	N/A	8.483	6.65	9.034	11.455
FNMA.PK	N/A	N/A	N/A	N/A	28773	29423	29587	24866	137392	119641	99512	107288
FITB.OQ	3933	978	441	790	5827	5609	4770	4782	9760	6587	5211	5572
RF.N	1577	316	167	368	5320	4786	3914	3894	6897	5102	4081	4262
FBNC.OQ	142.101	16.103	9.523	19.562	346.658	324.854	246.395	218.122	488.759	340.957	255.918	237.684
FCNCA.OQ	3679	467	60.676	95.857	6712	2946	1390.334	N/A	10391	3413	1451.01	1484.026
MTB.N	3109	425.164	114.006	326.395	7115	5821.956	3824.778	3866.317	10224	6247.12	3938.784	4192.712
PCLB.PK	2.453334	0.725181	N/A	N/A	N/A	N/A	N/A	N/A	13.872796	12.102573	N/A	N/A
FFBC.OQ	275.234	65.863	31.099	68.452	627.77	519.143	452.118	456.511	903.004	585.006	483.217	524.963
FLIC.OQ	68.618	18.497	16.152	29.188	86.865	115.713	106.807	102.028	155.483	134.21	122.959	131.216
FRME.OQ	348.486	84.803	35.952	66.381	545.4	503.448	410.68	382.127	893.886	605.006	446.632	448.508
USB.N	12611	3217	993	2015	17396	14728	12494	12825	30007	17945	13487	14840
SRCE.OQ	138.26	30.347	18.134	37.211	278.647	263.469	236.638	225.82	416.907	293.816	254.772	263.031
FHN.N	1560	291	164	236	2540	2392	1994	1662	4100	2683	2158	1898
CWBC.OQ	19.989	3.422	1.302	1.595	81.12	79.566	72.554	64.423	102.418	82.988	73.856	66.018
FULT.OQ	418.95	83.204	59.682	113.671	854.286	781.634	663.731	629.207	1273.236	864.838	723.412	742.878
GBCI.N	325.973	41.261	18.558	27.315	691.682	788.379	662.516	599.749	1017.655	829.64	681.074	627.064
GHI.N	69.066763	30.464451	21.943885	21.215888	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
GSBC.OQ	103.62	27.363	20.752	40.565	193.215	199.614	177.921	177.138	296.835	226.977	198.673	217.703
HWC.OQ	522.898	87.06	49.023	115.458	1108.706	1147.411	944.414	955.523	1620.497	1137.063	982.258	1057.981
HARL.PK	3.876	3.157	4.833	6.684	N/A	N/A	N/A	N/A	34.232	27.315	27.323	29.62
ORRF.OQ	44.991	9.024	6.721	16.024	104.906	99.63	86.974	83.607	149.897	108.654	93.695	99.631
HFWA.OQ	59.31	8.072	7.042	13.323	225.155	219.385	205.789	200.997	284.465	227.457	212.831	214.32
HIFS.OQ	125.96	32.894	8.026	21.158	N/A	N/A	N/A	N/A	174.262	139.028	110.491	106.362
FUNC.OQ	24.286	4.789	5.714	9.655	56.87	57.633	52.542	N/A	81.156	62.422	58.256	58.201
HBAN.OQ	3477	696	89	423	5439	5273	4102	3224	8916	5969	4191	3647
AROW.OQ	57.732	11.308	5.195	12.694	104.832	118.343	110.355	99.202	162.564	129.651	115.55	111.896
ASB.N	918.479	187.931	72.334	149.883	1039.573	957.321	725.855	762.957	1958.052	1145.252	798.189	912.84
INDB.OQ	189.205	29.591	13.717	34.341	606.521	613.249	401.559	367.728	795.726	642.84	415.276	402.069
IBCP.OQ	83.348	19.447	8.315	16.217	156.329	149.561	129.765	123.612	239.677	169.008	138.08	139.829
BOH.N	313.35	56.808	29.426	50.102	497.025	540.558	497.29	496.322	810.375	597.366	526.716	546.424

into a file called "temp.txt" and ran the command:

bank_data <- read_table('temp.txt', col_names = T, skip = 1)

to create a table called bank_data, and then ran the command:

dput(bank_data)

to produce code that anyone can use to recreate the table bank_data:

output of dput(), saved as "bank_data" 
bank_data <- 
structure(list(bank = c("HFBK.PK", "FDLB.PK", "FNMA.PK", "FITB.OQ", 
"RF.N", "FBNC.OQ", "FCNCA.OQ", "MTB.N", "PCLB.PK", "FFBC.OQ", 
"FLIC.OQ", "FRME.OQ", "USB.N", "SRCE.OQ", "FHN.N", "CWBC.OQ", 
"FULT.OQ", "GBCI.N", "GHI.N", "GSBC.OQ", "HWC.OQ", "HARL.PK", 
"ORRF.OQ", "HFWA.OQ", "HIFS.OQ", "FUNC.OQ", "HBAN.OQ", "AROW.OQ", 
"ASB.N", "INDB.OQ", "IBCP.OQ", "BOH.N"), `Mar-2024` = c("5.70647", 
"4.004", "N/A", "3933", "1577", "142.101", "3679", "3109", "2.453334", 
"275.234", "68.618", "348.486", "12611", "138.26", "1560", "19.989", 
"418.95", "325.973", "69.066763", "103.62", "522.898", "3.876", 
"44.991", "59.31", "125.96", "24.286", "3477", "57.732", "918.479", 
"189.205", "83.348", "313.35"), `Mar-2023` = c("1.078357", "3.827", 
"N/A", "978", "316", "16.103", "467", "425.164", "0.725181", 
"65.863", "18.497", "84.803", "3217", "30.347", "291", "3.422", 
"83.204", "41.261", "30.464451", "27.363", "87.06", "3.157", 
"9.024", "8.072", "32.894", "4.789", "696", "11.308", "187.931", 
"29.591", "19.447", "56.808"), `Mar-2022` = c("1.549388", "6.022", 
"N/A", "441", "167", "9.523", "60.676", "114.006", "N/A", "31.099", 
"16.152", "35.952", "993", "18.134", "164", "1.302", "59.682", 
"18.558", "21.943885", "20.752", "49.023", "4.833", "6.721", 
"7.042", "8.026", "5.714", "89", "5.195", "72.334", "13.717", 
"8.315", "29.426"), `Mar-2021` = c("2.379481", "8.501", "N/A", 
"790", "368", "19.562", "95.857", "326.395", "N/A", "68.452", 
"29.188", "66.381", "2015", "37.211", "236", "1.595", "113.671", 
"27.315", "21.215888", "40.565", "115.458", "6.684", "16.024", 
"13.323", "21.158", "9.655", "423", "12.694", "149.883", "34.341", 
"16.217", "50.102"), `Mar-2024_1` = c("N/A", "N/A", "28773", 
"5827", "5320", "346.658", "6712", "7115", "N/A", "627.77", "86.865", 
"545.4", "17396", "278.647", "2540", "81.12", "854.286", "691.682", 
"N/A", "193.215", "1108.706", "N/A", "104.906", "225.155", "N/A", 
"56.87", "5439", "104.832", "1039.573", "606.521", "156.329", 
"497.025"), `Mar-2023_1` = c("N/A", "N/A", "29423", "5609", "4786", 
"324.854", "2946", "5821.956", "N/A", "519.143", "115.713", "503.448", 
"14728", "263.469", "2392", "79.566", "781.634", "788.379", "N/A", 
"199.614", "1147.411", "N/A", "99.63", "219.385", "N/A", "57.633", 
"5273", "118.343", "957.321", "613.249", "149.561", "540.558"
), `Mar-2022_1` = c("N/A", "N/A", "29587", "4770", "3914", "246.395", 
"1390.334", "3824.778", "N/A", "452.118", "106.807", "410.68", 
"12494", "236.638", "1994", "72.554", "663.731", "662.516", "N/A", 
"177.921", "944.414", "N/A", "86.974", "205.789", "N/A", "52.542", 
"4102", "110.355", "725.855", "401.559", "129.765", "497.29"), 
    `Mar-2021_1` = c("N/A", "N/A", "24866", "4782", "3894", "218.122", 
    "N/A", "3866.317", "N/A", "456.511", "102.028", "382.127", 
    "12825", "225.82", "1662", "64.423", "629.207", "599.749", 
    "N/A", "177.138", "955.523", "N/A", "83.607", "200.997", 
    "N/A", "N/A", "3224", "99.202", "762.957", "367.728", "123.612", 
    "496.322"), `Mar-2024_2` = c("27.153711", "8.483", "137392", 
    "9760", "6897", "488.759", "10391", "10224", "13.872796", 
    "903.004", "155.483", "893.886", "30007", "416.907", "4100", 
    "102.418", "1273.236", "1017.655", "N/A", "296.835", "1620.497", 
    "34.232", "149.897", "284.465", "174.262", "81.156", "8916", 
    "162.564", "1958.052", "795.726", "239.677", "810.375"), 
    `Mar-2023_2` = c("20.426367", "6.65", "119641", "6587", "5102", 
    "340.957", "3413", "6247.12", "12.102573", "585.006", "134.21", 
    "605.006", "17945", "293.816", "2683", "82.988", "864.838", 
    "829.64", "N/A", "226.977", "1137.063", "27.315", "108.654", 
    "227.457", "139.028", "62.422", "5969", "129.651", "1145.252", 
    "642.84", "169.008", "597.366"), `Mar-2022_2` = c("19.140877", 
    "9.034", "99512", "5211", "4081", "255.918", "1451.01", "3938.784", 
    "N/A", "483.217", "122.959", "446.632", "13487", "254.772", 
    "2158", "73.856", "723.412", "681.074", "N/A", "198.673", 
    "982.258", "27.323", "93.695", "212.831", "110.491", "58.256", 
    "4191", "115.55", "798.189", "415.276", "138.08", "526.716"
    ), `Mar-2021_2` = c("17.767558", "11.455", "107288", "5572", 
    "4262", "237.684", "1484.026", "4192.712", "N/A", "524.963", 
    "131.216", "448.508", "14840", "263.031", "1898", "66.018", 
    "742.878", "627.064", "N/A", "217.703", "1057.981", "29.62", 
    "99.631", "214.32", "106.362", "58.201", "3647", "111.896", 
    "912.84", "402.069", "139.829", "546.424")), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -32L), spec = structure(list(
    cols = list(bank = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021_2` = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 2L), class = "col_spec"))

(Note: a full reprex is at the bottom of this post.)

Now, looking at the contents of bank_data:

bank_data
#>        bank  Mar-2024  Mar-2023  Mar-2022  Mar-2021 Mar-2024_1 Mar-2023_1
#> 1   HFBK.PK   5.70647  1.078357  1.549388  2.379481        N/A        N/A
#> 2   FDLB.PK     4.004     3.827     6.022     8.501        N/A        N/A
#> 3   FNMA.PK       N/A       N/A       N/A       N/A      28773      29423
#> 4   FITB.OQ      3933       978       441       790       5827       5609
#> 5      RF.N      1577       316       167       368       5320       4786
#> 6   FBNC.OQ   142.101    16.103     9.523    19.562    346.658    324.854

you can the see how to tell the pivot_longer() function to process the column names:

library(tidyr)

bank_data |> 
  pivot_longer(
    # exclude first column
    !1,
    # separate other columns names into year portion and suffix portion
    names_to = c('year', 'var'),
    # year is four digits, the underscore is optional, and rest is suffix
    names_pattern = "Mar-([0-9]{4})_?(.*)"
  )
#> # A tibble: 384 × 4
#>    bank    year  var   value    
#>    <chr>   <chr> <chr> <chr>    
#>  1 HFBK.PK 2024  ""    5.70647  
#>  2 HFBK.PK 2023  ""    1.078357 
#>  3 HFBK.PK 2022  ""    1.549388 
#>  4 HFBK.PK 2021  ""    2.379481 
#>  5 HFBK.PK 2024  "1"   N/A      
#>  6 HFBK.PK 2023  "1"   N/A      
#>  7 HFBK.PK 2022  "1"   N/A      
#>  8 HFBK.PK 2021  "1"   N/A      
#>  9 HFBK.PK 2024  "2"   27.153711
#> 10 HFBK.PK 2023  "2"   20.426367
#> # ℹ 374 more rows

Created on 2024-05-05 with reprex v2.0.2

After that, you'll likely want to clean up the result, but this gives you a first pass at a long version of the original data.

full reprex 
bank_data <- 
structure(list(bank = c("HFBK.PK", "FDLB.PK", "FNMA.PK", "FITB.OQ", 
"RF.N", "FBNC.OQ", "FCNCA.OQ", "MTB.N", "PCLB.PK", "FFBC.OQ", 
"FLIC.OQ", "FRME.OQ", "USB.N", "SRCE.OQ", "FHN.N", "CWBC.OQ", 
"FULT.OQ", "GBCI.N", "GHI.N", "GSBC.OQ", "HWC.OQ", "HARL.PK", 
"ORRF.OQ", "HFWA.OQ", "HIFS.OQ", "FUNC.OQ", "HBAN.OQ", "AROW.OQ", 
"ASB.N", "INDB.OQ", "IBCP.OQ", "BOH.N"), `Mar-2024` = c("5.70647", 
"4.004", "N/A", "3933", "1577", "142.101", "3679", "3109", "2.453334", 
"275.234", "68.618", "348.486", "12611", "138.26", "1560", "19.989", 
"418.95", "325.973", "69.066763", "103.62", "522.898", "3.876", 
"44.991", "59.31", "125.96", "24.286", "3477", "57.732", "918.479", 
"189.205", "83.348", "313.35"), `Mar-2023` = c("1.078357", "3.827", 
"N/A", "978", "316", "16.103", "467", "425.164", "0.725181", 
"65.863", "18.497", "84.803", "3217", "30.347", "291", "3.422", 
"83.204", "41.261", "30.464451", "27.363", "87.06", "3.157", 
"9.024", "8.072", "32.894", "4.789", "696", "11.308", "187.931", 
"29.591", "19.447", "56.808"), `Mar-2022` = c("1.549388", "6.022", 
"N/A", "441", "167", "9.523", "60.676", "114.006", "N/A", "31.099", 
"16.152", "35.952", "993", "18.134", "164", "1.302", "59.682", 
"18.558", "21.943885", "20.752", "49.023", "4.833", "6.721", 
"7.042", "8.026", "5.714", "89", "5.195", "72.334", "13.717", 
"8.315", "29.426"), `Mar-2021` = c("2.379481", "8.501", "N/A", 
"790", "368", "19.562", "95.857", "326.395", "N/A", "68.452", 
"29.188", "66.381", "2015", "37.211", "236", "1.595", "113.671", 
"27.315", "21.215888", "40.565", "115.458", "6.684", "16.024", 
"13.323", "21.158", "9.655", "423", "12.694", "149.883", "34.341", 
"16.217", "50.102"), `Mar-2024_1` = c("N/A", "N/A", "28773", 
"5827", "5320", "346.658", "6712", "7115", "N/A", "627.77", "86.865", 
"545.4", "17396", "278.647", "2540", "81.12", "854.286", "691.682", 
"N/A", "193.215", "1108.706", "N/A", "104.906", "225.155", "N/A", 
"56.87", "5439", "104.832", "1039.573", "606.521", "156.329", 
"497.025"), `Mar-2023_1` = c("N/A", "N/A", "29423", "5609", "4786", 
"324.854", "2946", "5821.956", "N/A", "519.143", "115.713", "503.448", 
"14728", "263.469", "2392", "79.566", "781.634", "788.379", "N/A", 
"199.614", "1147.411", "N/A", "99.63", "219.385", "N/A", "57.633", 
"5273", "118.343", "957.321", "613.249", "149.561", "540.558"
), `Mar-2022_1` = c("N/A", "N/A", "29587", "4770", "3914", "246.395", 
"1390.334", "3824.778", "N/A", "452.118", "106.807", "410.68", 
"12494", "236.638", "1994", "72.554", "663.731", "662.516", "N/A", 
"177.921", "944.414", "N/A", "86.974", "205.789", "N/A", "52.542", 
"4102", "110.355", "725.855", "401.559", "129.765", "497.29"), 
    `Mar-2021_1` = c("N/A", "N/A", "24866", "4782", "3894", "218.122", 
    "N/A", "3866.317", "N/A", "456.511", "102.028", "382.127", 
    "12825", "225.82", "1662", "64.423", "629.207", "599.749", 
    "N/A", "177.138", "955.523", "N/A", "83.607", "200.997", 
    "N/A", "N/A", "3224", "99.202", "762.957", "367.728", "123.612", 
    "496.322"), `Mar-2024_2` = c("27.153711", "8.483", "137392", 
    "9760", "6897", "488.759", "10391", "10224", "13.872796", 
    "903.004", "155.483", "893.886", "30007", "416.907", "4100", 
    "102.418", "1273.236", "1017.655", "N/A", "296.835", "1620.497", 
    "34.232", "149.897", "284.465", "174.262", "81.156", "8916", 
    "162.564", "1958.052", "795.726", "239.677", "810.375"), 
    `Mar-2023_2` = c("20.426367", "6.65", "119641", "6587", "5102", 
    "340.957", "3413", "6247.12", "12.102573", "585.006", "134.21", 
    "605.006", "17945", "293.816", "2683", "82.988", "864.838", 
    "829.64", "N/A", "226.977", "1137.063", "27.315", "108.654", 
    "227.457", "139.028", "62.422", "5969", "129.651", "1145.252", 
    "642.84", "169.008", "597.366"), `Mar-2022_2` = c("19.140877", 
    "9.034", "99512", "5211", "4081", "255.918", "1451.01", "3938.784", 
    "N/A", "483.217", "122.959", "446.632", "13487", "254.772", 
    "2158", "73.856", "723.412", "681.074", "N/A", "198.673", 
    "982.258", "27.323", "93.695", "212.831", "110.491", "58.256", 
    "4191", "115.55", "798.189", "415.276", "138.08", "526.716"
    ), `Mar-2021_2` = c("17.767558", "11.455", "107288", "5572", 
    "4262", "237.684", "1484.026", "4192.712", "N/A", "524.963", 
    "131.216", "448.508", "14840", "263.031", "1898", "66.018", 
    "742.878", "627.064", "N/A", "217.703", "1057.981", "29.62", 
    "99.631", "214.32", "106.362", "58.201", "3647", "111.896", 
    "912.84", "402.069", "139.829", "546.424")), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -32L), spec = structure(list(
    cols = list(bank = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021_1` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2024_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2023_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2022_2` = structure(list(), class = c("collector_character", 
    "collector")), `Mar-2021_2` = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 2L), class = "col_spec"))

bank_data
#>        bank  Mar-2024  Mar-2023  Mar-2022  Mar-2021 Mar-2024_1 Mar-2023_1
#> 1   HFBK.PK   5.70647  1.078357  1.549388  2.379481        N/A        N/A
#> 2   FDLB.PK     4.004     3.827     6.022     8.501        N/A        N/A
#> 3   FNMA.PK       N/A       N/A       N/A       N/A      28773      29423
#> 4   FITB.OQ      3933       978       441       790       5827       5609
#> 5      RF.N      1577       316       167       368       5320       4786
#> 6   FBNC.OQ   142.101    16.103     9.523    19.562    346.658    324.854
#> 7  FCNCA.OQ      3679       467    60.676    95.857       6712       2946
#> 8     MTB.N      3109   425.164   114.006   326.395       7115   5821.956
#> 9   PCLB.PK  2.453334  0.725181       N/A       N/A        N/A        N/A
#> 10  FFBC.OQ   275.234    65.863    31.099    68.452     627.77    519.143
#> 11  FLIC.OQ    68.618    18.497    16.152    29.188     86.865    115.713
#> 12  FRME.OQ   348.486    84.803    35.952    66.381      545.4    503.448
#> 13    USB.N     12611      3217       993      2015      17396      14728
#> 14  SRCE.OQ    138.26    30.347    18.134    37.211    278.647    263.469
#> 15    FHN.N      1560       291       164       236       2540       2392
#> 16  CWBC.OQ    19.989     3.422     1.302     1.595      81.12     79.566
#> 17  FULT.OQ    418.95    83.204    59.682   113.671    854.286    781.634
#> 18   GBCI.N   325.973    41.261    18.558    27.315    691.682    788.379
#> 19    GHI.N 69.066763 30.464451 21.943885 21.215888        N/A        N/A
#> 20  GSBC.OQ    103.62    27.363    20.752    40.565    193.215    199.614
#> 21   HWC.OQ   522.898     87.06    49.023   115.458   1108.706   1147.411
#> 22  HARL.PK     3.876     3.157     4.833     6.684        N/A        N/A
#> 23  ORRF.OQ    44.991     9.024     6.721    16.024    104.906      99.63
#> 24  HFWA.OQ     59.31     8.072     7.042    13.323    225.155    219.385
#> 25  HIFS.OQ    125.96    32.894     8.026    21.158        N/A        N/A
#> 26  FUNC.OQ    24.286     4.789     5.714     9.655      56.87     57.633
#> 27  HBAN.OQ      3477       696        89       423       5439       5273
#> 28  AROW.OQ    57.732    11.308     5.195    12.694    104.832    118.343
#> 29    ASB.N   918.479   187.931    72.334   149.883   1039.573    957.321
#> 30  INDB.OQ   189.205    29.591    13.717    34.341    606.521    613.249
#> 31  IBCP.OQ    83.348    19.447     8.315    16.217    156.329    149.561
#> 32    BOH.N    313.35    56.808    29.426    50.102    497.025    540.558
#>    Mar-2022_1 Mar-2021_1 Mar-2024_2 Mar-2023_2 Mar-2022_2 Mar-2021_2
#> 1         N/A        N/A  27.153711  20.426367  19.140877  17.767558
#> 2         N/A        N/A      8.483       6.65      9.034     11.455
#> 3       29587      24866     137392     119641      99512     107288
#> 4        4770       4782       9760       6587       5211       5572
#> 5        3914       3894       6897       5102       4081       4262
#> 6     246.395    218.122    488.759    340.957    255.918    237.684
#> 7    1390.334        N/A      10391       3413    1451.01   1484.026
#> 8    3824.778   3866.317      10224    6247.12   3938.784   4192.712
#> 9         N/A        N/A  13.872796  12.102573        N/A        N/A
#> 10    452.118    456.511    903.004    585.006    483.217    524.963
#> 11    106.807    102.028    155.483     134.21    122.959    131.216
#> 12     410.68    382.127    893.886    605.006    446.632    448.508
#> 13      12494      12825      30007      17945      13487      14840
#> 14    236.638     225.82    416.907    293.816    254.772    263.031
#> 15       1994       1662       4100       2683       2158       1898
#> 16     72.554     64.423    102.418     82.988     73.856     66.018
#> 17    663.731    629.207   1273.236    864.838    723.412    742.878
#> 18    662.516    599.749   1017.655     829.64    681.074    627.064
#> 19        N/A        N/A        N/A        N/A        N/A        N/A
#> 20    177.921    177.138    296.835    226.977    198.673    217.703
#> 21    944.414    955.523   1620.497   1137.063    982.258   1057.981
#> 22        N/A        N/A     34.232     27.315     27.323      29.62
#> 23     86.974     83.607    149.897    108.654     93.695     99.631
#> 24    205.789    200.997    284.465    227.457    212.831     214.32
#> 25        N/A        N/A    174.262    139.028    110.491    106.362
#> 26     52.542        N/A     81.156     62.422     58.256     58.201
#> 27       4102       3224       8916       5969       4191       3647
#> 28    110.355     99.202    162.564    129.651     115.55    111.896
#> 29    725.855    762.957   1958.052   1145.252    798.189     912.84
#> 30    401.559    367.728    795.726     642.84    415.276    402.069
#> 31    129.765    123.612    239.677    169.008     138.08    139.829
#> 32     497.29    496.322    810.375    597.366    526.716    546.424

library(tidyr)

bank_data |> 
  pivot_longer(
    !1,
    names_to = c('year', 'var'),
    names_pattern = "Mar-([0-9]{4})_?(.*)"
  )
#> # A tibble: 384 × 4
#>    bank    year  var   value    
#>    <chr>   <chr> <chr> <chr>    
#>  1 HFBK.PK 2024  ""    5.70647  
#>  2 HFBK.PK 2023  ""    1.078357 
#>  3 HFBK.PK 2022  ""    1.549388 
#>  4 HFBK.PK 2021  ""    2.379481 
#>  5 HFBK.PK 2024  "1"   N/A      
#>  6 HFBK.PK 2023  "1"   N/A      
#>  7 HFBK.PK 2022  "1"   N/A      
#>  8 HFBK.PK 2021  "1"   N/A      
#>  9 HFBK.PK 2024  "2"   27.153711
#> 10 HFBK.PK 2023  "2"   20.426367
#> # ℹ 374 more rows

Created on 2024-05-05 with reprex v2.0.2

1 Like
17

I am new learner please, I just want to know how to read a spreadsheet as data frame please

18

@David_T You should ask this as a new question, not as a comment/reply to this question.

1 Like
19

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.